Forum Discussion
Dimension table on many side. Problematic?
I am really struggling to model my data into a star schema.
I've read tons of documentation and Kimball stuff and I feel like I understand a lot of it
However, when I look at my own data I suddenly have no idea what to do anymore.
I am wondering, is my data so different? Can the rules in my case be different? Or am I being dumb?
Few things to know about my data and my desired result:
- All my dimensions are basically fast changing, at the same rate as my facts.
- I have facts at different granularities.
- My dimensions will be used a lot in visuals therefore they must filter each other. For example I will make gantt charts and show dimensions over time. In regular star schema design dimensions do not filter each other. This is unacceptable for me. I considered creating a link table with all the keys. I tried implementing it and the result looked really stupid like this: dim tables(1)---(*)link table with keys(*)---(1)fact tables. Then I realized it would probably be a bad idea because:
- It would probably just further complicate DAX.
- The key table would quickly grow to hundreds of millions of rows because I had to merge all these keys together.
- I cannot create hierarchies in Power BI because my dimensions are in different tables.
Let's say I have hierarchical dimensions like below. A to G 'could' all be different tables with each having multipe fields.
A can have one B
B can have one C
C can have one or more D
D can have one or more E
E can have one or more F
F can have one or more G
And I have several fact tables at different granularities like below.
FactTable1 has a 1 to 1 relationship with D
FactTable2 has a 1 to 1 relationship with E
FactTable3 has a 1 to 1 relationship with F
Because I decided that a link table with keys was a bad idea, and because I want dimensions to filter each other, and because I want to be able to create hierarchies, and because I want to avoid bidirectional relationships, I decided to merge A to G into 1 big dimension table. I read at several sources, that the fewer tables, the better. I would simply merge dimensions and create folders inside Power BI GUI if I want to bring structure to it for the users. And here a new problem arises:
Because E expands on D, and F expands on E, and G expands on F, extra rows are being created all the time, resulting in the relationships below.
FactTable1 now has a 1 to many relationship with D
FactTable2 now has a 1 to many relationship with E
FactTable3 now has a 1 to many relationship with F
This is the opposite of what I've always learned. Fact tables should be on the many side, dimension tables on the 1 side.
However, when I think about it, I can't figure out what could go wrong:
If I filter A, B, C and D, FactTable1 results 1 row.
If I filter A, B, C, D and E, FactTable2 results 1 row.
If I filter A, B, C, D, E, and F, FactTable3 results 1 row.
This always allows me to navigate down to the granularity I want. It's almost like 'hidden' 1 to 1 relationships inside the model.
Is this a bad model? What could go wrong? I can find nothing on the internet about this situation. Or am I creating this situation for no good reason?
Is anyone able to help me in the right direction?
9 Replies
- sanimesaPost Prodigy
Have you considered creating a denormalized fact table with the dimentions you are having problems with?
I understand your key problem is that the dimensions need to filter each other. This should work if you bring in the dimensions into the fact table itself.
- richard-powerbiPost Patron
sanimesa, but that would result in 1 huge table with everything? And I would have to do this for every fact table at a different granularity? Then dimensions would not be shared but loaded multiple times?
By the way, do you see problems in my model design? Or could it just work? Or will weird things happen when using DAX?
- sanimesaPost Prodigy
richard-powerbi It is hard to say without actually seeing the model but I'd definitely merge some of the dimension tables with theselves or fact tables. If your data source is something like an SQL server, you can actually do some of these at the source level. Merging should not increase the number of rows in the fact tables.
In the past I got into trouble with circular references. Best would be to implement a couple of use cases you have in mind.
Thanks.