jeffmasse Tracker

Re: Data Model with Multiple Companies

jeffmasse — Wed, 31 Aug 2022 10:30:37 GMT

@ravjay

One approach to stacking, which may alleviate your concerns is by using dataflows to connect to each of your individual company datasets, then stack the dataflows. This would address the following concers:

1. If one dataflow failes, the stacking will still take place (it will rely on an earlier refresh).

2. Refresh speed should be better, because it is not unlike have individual datasets refresh against your companies.

3. Adding new companies is in fact easier under this method - you could achieve it without modifying the PBIX file at all. Whereas in your composite model, you would need to edit the composite file for the addition of each company.

I successfully use this approach in a few scenarios. It is very flexible and scalable. The approach you are taking with composite models is likely to cause frustrations in the future.

Re: Data Model with Multiple Companies

jeffmasse — Wed, 03 Aug 2022 13:34:44 GMT

I was able to get my model to work the way I wanted.

For your situation, I recommend combining the same data (e.g., POs) from multiple companies into a single table. Can you explain what your refresh issues were? I may have suggestions to resolve your refresh issue if you provide details.

I think a composite model in this case likely won't be the best approach. I think you want to "stack" your data by company into the same tables.

Re: Data Model with Multiple Companies

jeffmasse — Fri, 29 Oct 2021 14:56:59 GMT

I agree with your comment that relating fact tables is not a good idea. I've committed a bit of a moral sin on this model in that respect. However, I can't come up with a way around it, given what I'm trying to achieve. A specific use case of this model is that we want to analyze how our sales are being fulfilled (e.g., we select a customer and can see which vendors supplied the inventory for those sales - we use a specific identification inventory system). This allows us to easily evaluate profitability by customer, vendor, salesperson, buyer, etc. Or if a specific Sales Order loses money, we can see the specific vendor that supplied the product for that order. We need high level analysis at the lookup table level as well as granular details for specific sales orders, etc.

I realize tyring to figure out how to make this model into a better star schema is not within the scope of my question here. But that's the context as to why I've build the model in this way and why I'm struggling to figure out the best way to implement this model for multiple companies.

Re: Data Model with Multiple Companies

jeffmasse — Fri, 29 Oct 2021 10:28:40 GMT

Thanks for the quick response on this! I was anticipating the need to re-index my lookup tables due to duplicate values.

I like your suggestion to introduce Company where you did. However, won't this cause an issue where the Company table will be filtering the Inventory table via both Sales and Purchases? I could get around this by deactivating the relationships from Company to Sales/Purchases in the model and then using CROSSFILTER to activate in my measures as necessary. Is this along the lines of what you were suggesting?

Data Model with Multiple Companies

jeffmasse — Fri, 29 Oct 2021 00:40:56 GMT

I've spent a considerable amount of time building a complex data model for a single company. This company was recently acquired by another company with approximately 30 subsidiaries. Each company in the group (around 30 companies in total) all use the same ERP system. But each company runs its own instance of the ERP database - same table definitions, but separate DBs.

I'm trying to decide how to incorporate a "Company" table into my existing model and which table(s) to relate this Company table to. I've considered inactive relationships to lookup or fact tables. I've considered making the Company table "disconnected" and using FILTER in my measures to only include Companies that are selected in my slicer. I'm sure there are other options out there too. This is going to be a significant endeavor, so I'm trying to come up with a "tried and tested" approach so that I hopefully avoid some mistakes.

My existing model is made up of 50+ tables loaded to a model. At the core, I've got Sales, Purchases and Inventory. I've also got many look-ups, some side fact tables (e.g., inventory re-work, etc). And I'm able to link my Sales and Purchases tables together through my Inventory table.

For simplicity, assume my model looks like the following:

My general plan to get all data from all companies into a single model is as follows:

Create dataflows for each company. Each dataflow would be the same code, but would point to each individual company DB.
Append all the dataflows together into a single data model. e.g., the Customers table would get appended 30x. I would likely introduce a Company ID column into the table in case of duplicate Customer IDs.

But my issue is determining where/how to introduce a Company table. Again, considering relationships (active/inactive) to either/both of lookup/fact tables. Also, considering a disconnected Company table and using FILTER in my measures to filter Sales, etc. on the selected company(ies).

I would love to hear of an approach someone else has taken and has had success with. Particularly where there are multiple lookup and fact tables involved.

Thanks in advance!

Jeff

Re: Refresh on Service takes longer than Desktop

jeffmasse — Mon, 24 Sep 2018 10:48:05 GMT

Sorry for the late reply - I needed to work with my IT Admin to get the latest Gateway installed. I've now got the September Gateway installed and still having the same refresh timing issues.

Not sure what you mean by updating the PBIX online? I uploaded my PBIX file and immediately refreshed it.

Refresh on Service takes longer than Desktop

jeffmasse — Mon, 17 Sep 2018 18:45:55 GMT

I've recently made changes to a Power BI model that has significantly slowed down the refresh time. Before the changes discussed below, a typical refresh on both Desktop/Online would take approximately 20-30 minutes.

I introduced the following change:

- Sales table has approximately 3.5 million rows

- Cost lookup table has approximately 20,000 rows

- I performed a MERGE (left-outer) inside the Sales table to the Cost table. I merged on a field that is unique in the Cost table.

When I first implemted this change, the refresh time for both Desktop and Online was approximately the same (2+ hours or timing out).

I did some research to determine how to get the refresh time down. I was pointed to a blog by Chris Webb (link) which discussed adding a Primary Key to the lookup table (i.e., Cost in my scenario). This SIGNIFICANTLY improved the refresh time on Desktop - from 2 hours+ down to approximately 10 minutes. However, this performance improvement didn't seem to translate to Power BI Online. Refreshes are still timing out after 2+ hours.

A bit of information on my setup

- I'm connecting from my PC through VPN to the host server

- My desktop - I'm running i7-7700, 24GB RAM, SSD, 100MB fibre connection from my PC to the host DB. Running Power BI 64bit September update.

- Server - I'm not 100% what the specs are on that - could find out. Running July gateway (had updated to August, but it was causing some failures so reverted back to July).

Is there a difference in how Power BI Online processes a query refresh compared to Desktop?