Star Schema Modelling issue when using Multiple datasets

[DebbieE]

Hi,

I think I have made a modelling mistake that on a single sta schema doesnt cause issues. Its because Im now pulling multiple data sets together and those data sets hold a star schema each. 

So I have the following:

they are at different levels of granularity and the ID is in the fact table so that the actual Work table can be aggregated on just WorkType so its a very small dim table

Using each seperately is fine but now I have hit this pulling in both data sets. I bring them together like this:

And the report Is made up like this

So if you were to drag the ID Key froM fact table B into trips table you can see that its completely disconnected. So putting the ID into the fact table doesnt work when you bring together more than one fact table. I have a few options

1. Add ID into Work making that Dim table a lot bigger and it will be a 1 to 1 join onto the fact table

2. Create a seperate dim just for the ID (But it seems like an odd thing to da)

Is there another way of resolving this so i can leave the IDs in the Fact table. Whats the best practice I should be using here? Any help would be appreciated

[Anonymous]

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. 

[DebbieE]

Thats helped me figure out the plan. Create another dimension at the lowest level of granularity with the ID, And move the ID out of the fact table. Its a bit of work but it needs to be done.

[bcdobbs]

Hi Debbie are you able to share a pbix file and an idea of the desired output.

What you're describing is generally called a degenerate dimension by Kimball (https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimen...).

Normally you would leave it on the fact table but because you're looking to report at that level across two fact tables I think your best course of action it to pull it out as a dimension with just those id's in as you suggest. Anything else just leads to complex dax to pass the filters.

[DebbieE · Accepted solution]

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

[parry2k]

@DebbieE Option 2, a dimension table for ID and all the common columns related to ID will go into this table and it will have relationship with both the fact tables.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.