How to solve ambiguity path

[KenMann]

Hi guys,

In my FactInternetSales table, I have columns : SalesTeritory and Customers.

In my Relationship diagram, I am now set like this :

As the above, Sales Teritory already have path like this : SalesTeritory -> Geography -> Customers -> FactInternetSales

How to connect also SalesTeritory to FactInternetSales ?

Cause if I do this, it will produce ambiguity. But I'm thinking what if I want to slice my Fact data directly based on SalesTeritory, provided whatever the customer is ?

Thanks.

[AlexisOlson]

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.

[KenMann]

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.

[AlexisOlson · Accepted solution]

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.