Forum Discussion
SQL View vs Star Schema Model
Anonymous- yes that is fair to say.
Power BI/Tabular models can't see how your database behind the view is structures - it only see the table(s) you add to the model.
Solution SageHi Anonymous,
I would ALWAYS build a Star Schema model - try reading this article from SQLBI.com. It describes just one of the issues you can have with not building a Star Schema model: https://www.sqlbi.com/articles/understanding-dax-auto-exist/
Conclusion from the article is:
In simpler data models with only one table and with a fancy data distribution of values, it might be possible to run into auto-exist problems. When this happens, the easiest solution is to avoid using a single table and to build a proper star schema instead.
The golden rule of data modeling is always the same: always use star schemas. If a column has to be used to slice and dice, then it needs to belong to a dimension. Numbers to aggregate, on the other hand, are stored in fact tables. Tabular lets a developer deviate from the regular star schema architecture. This does not mean that doing it is always a good idea. It seldom is.
Thanks, sdjensen! So, if I'm reading the article correctly, even if my hypothetical view really was modeled as a star schema "behind the scenes," because it's brought into Power BI as a single table, DAX would potentially have issues interpreting the data in the way I expected. Is that fair to say?
Thanks again! This is really helpful information.
- sdjensen
Anonymous- yes that is fair to say.
Power BI/Tabular models can't see how your database behind the view is structures - it only see the table(s) you add to the model.