Forum Discussion
Data Model with Multiple Companies
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
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.
9 Replies
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.
Finally someone with the same problem that I have. I just spent a lot of time looking for any suggestion to this and I can't find nothing
My data model is even simplier. A sales fact table, Customers and Products dim tables, as always the Calendar table and now ... the country table, linked to both dim tables. So, imagine a very simple report with only a table with products on columns and country/customers on rows, showing the total sale on each cell. As in the data model I only can activate either the country->customer or country->product relation, the report is always show me the wrong total sales in each cell
The only way I can imagine to solve this is duplicating the country table and make the relations separated. But then, how can I handle if I want to add a slicer with the country in my report?
