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.
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.
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?
However, won't this cause an issue where the Company table will be filtering the Inventory table via both Sales and Purchases?
You are correct. That does create an ambiguous filter path and one possible resolution would be to relate Company directly to Inventory and de-activate the other two relationships.
In general, relationships between fact tables are not a great idea, so you may want to consider relating Inventory directly to its relevant dimension tables rather than through Sales and Purchases.
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.
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?