Forum Discussion
Data Model with Multiple Companies
In order to deal with potential duplicated IDs, you'll probably need to re-index the Customers, Salesperson, Buyers, and Vendor tables so that it's possible to have, for example, the same customer/buyer/vendor associated with multiple companies. Sales and Purchases should get a new Company ID column each. The other dimension tables could as well, but don't necessarily need active relationships with Company.
This is my basic idea:
Disclaimer: Since I haven't spent more than a few minutes considering this, consider it more of a possible approach to mull over rather than anything definitive. You know your own situation far better than I do.
Hi,
Having a similar issue with my ERP model. I've 10 subsidiary companies and need to combine them into a single data model. I've tried creating similar dim & fact tables for each company and then combined them into one table ( consolidated table). But it creates issues in refreshing. I've also tried the composite data model. Then stuck in schedule refreshing.
please see my posted question below,
https://community.powerbi.com/t5/Desktop/COMPOSITE-DATA-MODEL-WITH-NUMEROUS-APIS/m-p/2588804#M913394
So, did you get to succeed on your model? any suggestions for me?
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.
The reasons for not stacking the data by the company to the same table and keeping each company separately are -
* if any APIs failed at a company level it wouldn't block the loading of other companies' data.
* there are some companies with larger data sets, which create issues in loading
* if we need to add a new company, we can create a data model separately and add it to the composite table without changing every single table
When I tried to build this data model and refresh the composite data model I received the error
"Microsoft.PowerBI.AS.ConnectionDetailsProvider, Single sign-on (SSO) credential is not provided which is required to execute the current request. Note that performing a refresh over a dataset with a calculated table or a calculated column which references a Direct Query data source is not supported within the Power BI service.."
@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.