Forum Discussion
SQL View vs Star Schema Model
Power BI newbie here with a hypothetical question. If, instead of building out a star schema model within Power BI, I created a single view in SQL Server that joined the fact and dimensions in the underlying query the same way that I would have modeled it in Power BI anyway, is there any functionality loss within Power BI if I just import that single view?
I'll toy around with this on my own, but I figured I'd ask here since I'm not well-versed with all of the expected functionality yet...so I might overlook something initially.
Thanks in advance!
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.
4 Replies
hi, Anonymous
If you model in SQL and import it(data) into power bi, you also need to do some model for it unless you use live connection to connect to the model. and when you import data, it has the full functionality, only DirectQuery and Live connection will have some
limitations on modeling.
https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about
Best Regards,
Lin
Hi 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.
