Forum Discussion
Star Schema Modelling issue when using Multiple datasets
No I cant share this pbix file.
And its all sorted. I created the dimension with the ID. removed the ID from the fact table. And I kept the higher dim with just the type in it for the star schemas at the higher level of granularity.
Took a while but it was worth it and everything is working well now
Looks like this has been out here for a couple years but was wondering if the issue was actually one of not considering the purpose of the fact table.
Why not simply have a FactCost fact table containing all "Costs" regardless of the cost type with columns WorkKey, CostKey, and Cost. A dimension DimWork with columns WorkType, WorkKey, and ID. A dimension DimCost with columns CostTypeKey, CostKey, and CostDescr. A dimension DimCostType with columns CostKeyType and CostTypeDescr.
This would allow for any new cost types to be added without the need for additional Fact tables. Also allow simple aggregations to provide total costs, measures filtered by Cost Type for segmentation, and a much easier time doing calculations like cost or cost type as a percentage of total cost.