'How to model location that is common to 2 dimensions?
I am creating a data model and need guidance.
I have 2 dimensions: customer, product
And 2 fact tables: sales, purchases.
Both the dimensions have Location column. Suppose user selects London then it should show all customer and products from London. So I think it makes more sense to create a new dimension called Location and make it the parent of customer and product. This however makes it a snowflake schema.
Alternatively rather than making the Location as parent of customer and product, I can amend the ETL (to have the LocationID into the fact tables) so as to make the Location dimension to directly point into the fact tables. This will make it star schema.
Is there any negative impact of choosing the later option?
Solution 1:[1]
Having a separate dimension that links directly to your fact table is the “standard” way of implementing this
Solution 2:[2]
If both Customer and Product have a location, then you have a "role playing dimension". You might want to filter by Customer.Location, or you might want to filter by Product.Location. In Power BI you need two copies of your dimension here, one related to Product and one related to Location (see video).
This would also create a snowflake schema, which is not a problem. If you'd prefer a star schema then you would copy the location data into each dimension.
You wouldn't put location on the fact table because then you couldn't distinguish between the Customer's location and the Product's location.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | NickW |
| Solution 2 | David Browne - Microsoft |