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.
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?