Hi Mynda
I looked at pbi_4_ContosoSales_after file on Power BI.
If I understand well here the dimension is the subcategory of the foreign key on the fact table.
In this ex the Factsales contains aggregated data (sum of sales amount, sum of sales quantity etc) or non aggrated data such as Channelkey in FactBudget.
If I take Channelkey as the foreignkey I then could have the following dimensions:
- Catalog
- Online
- Reseller
- Store
I note that a foreign key can become a dimension? channelkey in FactBudget becomes a dimension in DimChannel unless this are referring to different things as one is lower case c and the other one upper case C.
Then what is the difference between the DimProductSubcategory and the DimProduct? I mean I understand what they include but why not just have one dimensionkey as Product. Does it make the analysis easier? faster?
Thanks for your help
Regards
Dieneba
Hi Dieneba,
I think you're overcomplicating things.
"If I understand well here the dimension is the subcategory of the foreign key on the fact table." - There is no 'subcategory' field in the FactSales table, so I'm not sure what you're referring to.
"In this ex the Factsales contains aggregated data (sum of sales amount, sum of sales quantity etc) or non aggrated data such as Channelkey in FactBudget." - the FactSales table does not contain aggregated data. Each row in that table represents a sale. We then summarise/aggregate those sales to analyse the data by summing the SalesQuantity, SalesAmount columns etc..
A foreign key can be a dimension and usually is.
If you look at the columns in the DimProductSubcategory and DimProduct tables you can see the difference is the product table (with 1690 rows) contains more detailed information about the products, whereas the product subcategory table (with 44 rows) contains ways to group the products e.g. into categories and subcategories. If you were to combine these two tables into one, it would result it much more data to store because you would have to add 1690 rows x 5 columns (i.e. all the columns in the subcategory table except for the Product Category Key column which is already in the Product table). This may seem like it would be faster, but it wouldn't because relational databases, like Power Pivot, are designed to work efficiently with data in a star schema. And by adding more columns to the product table, you'd be increasing the file size.
I hope that helps.
Mynda
