You need to know:
The reasoning why I am thinking about doing this:
Advantages I see for model 3:
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.
@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?...
@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!!
@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:
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:
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.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Learn about the exciting things that happened in July.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.