cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Merge hierarchical dim tables to improve model?

mdl2.png

 

  1. I am looking for a theoretical explanation whether it is a valid/smart technique to use model 3 instead of model 1 or 2. I am talking about the idea of merging hierarchical dim tables to simplify/improve the model.
  2. Also I'm wondering if for model 2 I should put the key table above the dim tables like drawn, or if I should put the key table between the dim tables and the fact tables.

You need to know:

  • There is a clear hierarchy between A, B and C. A can have multiple of B, and B can have multiple of C.
  • In reality my hierarchy is bigger, think of A to G.
  • In reality I have more fact tables that connect to different levels in the hierachy of the merged dim table: depending on the granularity of the fact table, the fact table connects to a different level in the hierachy.

The reasoning why I am thinking about doing this:

  • Let's say I start with model 1 because I want to separate dimensions from facts
  • Then I run into a problem: dimensions do not filer each other, this is a strong requirement for my reports
  • Then I decide to make model 2: the key table filters the dim tables
  • Then I realize this model has a lot of tables, a lot of relationships, and a lot of the same keys at several places
  • I get the idea to merge all dim tables to get model 3
  • Because of the cardinality between B and C, extra rows for B are created in the merged dim table 😞
  • Now there is a many to one relationship between the dim table and the fact table for B-B 😞
  • There still is a one to one relationship between the dim table and the fact table for C-C
  • To be able to filter the B-fact table I need to enable bidirectional filtering for B-B 😞

Advantages I see for model 3:

  • Decreases amount of tables
  • Decreases amount of relationships
  • Decreases occurrences of the same data at several places (keys)
  • Allows for creating hierarchies in Power BI
  • Allows my dimension to filter each other (because they are in 1 table)

Disadvantages:

  • Non typical star schema design??
  • The many to one relationship between the dim table and the fact table for B-B worries me: reversed from what it should be in star schema design
  • One has to be careful to use enough fields for filtering inside the merged dim table to filter the fact table(s) down to 1 row
  • ?? other ??

I would love to hear what experts have to say about this.
Personally I feel model 3 is the right one for me, but it being non typical star schema design worries me. I want to expand a lot on this model with extra tables in the future. I hope I don't get stuck with this approach. 

9 REPLIES 9
Highlighted
Super User IV
Super User IV

Re: Merge hierarchical dim tables to improve model?

@richard-powerbi you wanna strive for the star schema which is preferred, Hope this post helps.

 

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

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Post Patron
Post Patron

Re: Merge hierarchical dim tables to improve model?

@parry2k I've read that before. I've also read many other documentation. I hope someone can help me with my specific dilemma.

Highlighted
Super User IV
Super User IV

Re: Merge hierarchical dim tables to improve model?

@richard-powerbi not sure what is the dilemma here, it is fundamental of data warehousing. Anyhow, good luck.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Post Patron
Post Patron

Re: Merge hierarchical dim tables to improve model?

@parry2k Did you actually read my post? I find your answers a bit strange... I've read tons of things about modeling, Kimball, and so on. If I knew exactly what to do I wouldn't be asking these questions right? Isn't that what this forum is for?...

Highlighted
Super User IV
Super User IV

Re: Merge hierarchical dim tables to improve model?

@richard-powerbi indeed forum is to help each other and share knowledge and I read your post in detail and the reason I pointed to the document as it was not mentioned that you read that post. Again, it is all about best practice, all 3 models will work, are scalable, perform well under large dataset etc. There are many other parameters before even someone can answer why choose 1 model over another model. Based on your last reply, you know enough about warehousing and I'm a bit lost what exactly you are looking for. Sorry I might not be the right person or understood your question. Cheers!! 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Post Patron
Post Patron

Re: Merge hierarchical dim tables to improve model?

@parry2k I'm sorry if I didn't manage to explain it well enough. 

 

My dilemma is this:

How can it be that - to me - model 3 seems better, although I have never seen it anywhere. I've searched the Internet endlessly to find something similar, but I can't find documentation about it.

So even thought I think - with my current knowledge - that model 3 'works', the fact that I can't find anything about it makes me doubt it is good model design. So I'm worried I run into problems later when using this in Power BI.

 

Quoting this (following your link)...

There's no table property that modelers set to configure the table type as dimension or fact. It's in fact determined by the model relationships. A model relationship establishes a filter propagation path between two tables, and it's the Cardinality property of the relationship that determines the table type. A common relationship cardinality is one-to-many or its inverse many-to-one. The "one" side is always a dimension-type table while the "many" side is always a fact-type table.

... my conclusion would be that table B in model 3 cannot be a fact table. The cardinality (caused by the merged hierarchies and hereby the extra rows created for B) would define my dim table as a fact table. But I have only facts in table B... so I want to filter them, thus I make the relationship bidirectional so that I can filter my B-facts anyway. At the same time, at a lower granularity in the hierarchy, for example C, has a one to one cardinality. And another fact table (not drawn) with multiple rows for C could have a many to one (dim side) cardinality, making it a perfect star schema part of the model. But as becomes clear, the model seems 'mixed', as in being partly good star schema design, partly not.

 

Depending on the level the 'tables containing facts' connect to the hierarchy inside the 'big merged table containing dimensions', the cardinality is different for 'tables containing  facts' at different granularities. As a result the star schema has different cardinalities at the same time:

  • 'big merged table containing dimensions' -- many to 1-- 'tables containing facts at higher grain'
  • 'big merged table containing dimensions' -- 1 to 1-- 'tables containing facts at equal grain'
  • 'big merged table containing dimensions' -- 1 to many - 'tables containing facts at lower grain'

As far as my understanding goes, this whole concept blurs the lines between what is a dimension and what is a fact inside this model. Although I made clear distinct between what are dimensions (descriptive) and facts (numeric), because I put them in different tables, the changing cardinality defines them otherwise depending on the grain.

 

Whenever I read about star schema design I always read that facts must be one the many side, and dimensions on the one side. So can anyone explain me if this makes model 3 by definition faulty star schema design, or can someone explain me why it could be good design in some cases.  

 

Inside my head this is how I see model 3 'work' as good star schema design:

  • As long as I make sure, that I filter down to the correct level in the hierarchy, I know that the values for the fact tables will be filtered correctly.
  • With DAX I can use functions like ALLEXCEPT to foresee bad filtering.
Highlighted
Post Patron
Post Patron

Re: Merge hierarchical dim tables to improve model?

Anyone?

Highlighted
Community Support
Community Support

Re: Merge hierarchical dim tables to improve model?

hi  @richard-powerbi 

To my knoeledge, Model 3 is not a good choose in power bi for now, dim table in model 3 is like a date hierarchy, but in fact, A,B,C are not like it, they are individual dim table, so the star schema which is preferred.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Post Patron
Post Patron

Re: Merge hierarchical dim tables to improve model?

What is the disadvantage of model 3?

And how would I best let dimensions filter each other in the star schema?

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors