CODEWITHSUNDEEP
powerbidata-modelingdatamodelstar-schema
variable
variable

Reputation: 9714

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?

Upvotes: 0

Views: 500

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89316

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.

Upvotes: 0

NickW
NickW

Reputation: 9798

Having a separate dimension that links directly to your fact table is the “standard” way of implementing this

Upvotes: 0

Related Questions