Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!

1 ACCEPTED SOLUTION

@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.

/sdjensen

View solution in original post

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

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

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.

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.

/sdjensen
Anonymous
Not applicable

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.

@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.

/sdjensen

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.