Forum Discussion
How to solve ambiguity path
Merge into DimCustomers any columns from DimSalesTerritory and DimGeography any columns that you want to associate with customers. You can still keep DimSalesTerritory as a separate table to filter FactInternetSales.
I'm guessing it's possible to have a sale in a geography that isn't the same as the geography associated with a customer. The method I'm suggesting allows you to have e.g. SalesCountry (from DimSalesTerritory) and CustomerCountry (from the merged/denormalized DimCustomer) as independent dimensions that don't necessarily match. If this isn't how your data works (e.g. if you only have geographic data associated with sales or customers but not both), then my suggested method is not what you want.
The star schema approach tends to work best for Power BI.
With this in mind, I'd recommend connecting DimSalesTerritory only to FactInternetSales (not DimGeography) and denormalizing and including any geography and territory information directly relevant to customers in DimCustomer. Similarly, DimCategory and DimProduct can be denormalized into a single table.
Hi Alexis,
Means to say "denormalizing and including any geography and territory information directly relevant to customers in DimCustomer." is to merge together SalesTeritory+Geography+Customers into 1 table then make a relation between Customer to FactInternetSales only ?
I will try this, thank you.
Merge into DimCustomers any columns from DimSalesTerritory and DimGeography any columns that you want to associate with customers. You can still keep DimSalesTerritory as a separate table to filter FactInternetSales.
I'm guessing it's possible to have a sale in a geography that isn't the same as the geography associated with a customer. The method I'm suggesting allows you to have e.g. SalesCountry (from DimSalesTerritory) and CustomerCountry (from the merged/denormalized DimCustomer) as independent dimensions that don't necessarily match. If this isn't how your data works (e.g. if you only have geographic data associated with sales or customers but not both), then my suggested method is not what you want.