cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Relational model vs Star model

Hi,

I have a few questions. When I bring in a database model like this into PowerBI.

Then

1) How do I convert this most effectively into a star model?

2) If I don’t convert it to a star model, can reports be built on this model?

3) Where can it go wrong, when reporting on a model like this?

Thank's very much.

2 ACCEPTED SOLUTIONS

Ciao Giovanni
Dimensional modeling is not something that can be resolved in a forum question. There are books about it. Also it's highly correlated to your reporting needs (so without knowing what information you need, it's impossible to solve this for you). Finally, without knowing what is the content of those tables, we can only guess and guessing is noy the right way to dimensional modeling.having said so, however, the beauty of powerbi is that it can also work on a non-star schema. However replicating those relationships in pbi will make it fairly complex to manage in dax




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

Proud to be a Super User!




View solution in original post

Hey ,

Okay so to answer your question as much as possible with this the information posted, using direct query does not give you the flexibility which you are looking for, for developing the data model you wish.

I would advise you go for the import option instead (please read about performance - both for RAM & CPU consumption). Also, understanding the difference between DirectQuery and Import will be helpful to you.

In regards to help in developing the dimensional model you want, again I will re-iterate what I said before and what was also posted here by another community user. This goes beyong Power BI and what we can do for you here. A book I would advise is:

“Data Modeling Made Simple: A Practical Guide for Business and IT Professionals”

Answering the final question if understood correctly, using calculations and filters over multiple tables and multiple relationships does NOT result in definite wrong answers/displays. However, it can be a result of wrong displays as the more over complicated a model, the more room for error.

Laz

View solution in original post

5 REPLIES 5
Laz
Resolver I
Resolver I

Converting the above model into a dimensional model such as a Star Schema goes beyond Power BI and comes down to Data Modelling skills, as I am sure many will agree here. In order to best advise you, can you please let us know what connection type you are using?
LiveConnection, DirectQuery or Import.

Also to answer your questions, yes you can still develop solutions using the above model and in regards to what can go wrong... performance, especially if your are returning data that is not needed. I would advise reading the difference between ERD diagrams and dimensional models (de-normalised), as this will also give you a better understanding on this.
Thanks
Laz
Anonymous
Not applicable

Hi Laz,

Thanks very much.

I'm using Directquery. The questions I still have are, how can I get a star schema with only the dimensions and facts, I need from the model above in the fastest way. So without etl tools. Only with SQL queries and PowerBI.

And does this mean that calculations and filtering over multiple tables and complicated relations can't cause wrong answers in your report?

Thanks again


Hey ,

Okay so to answer your question as much as possible with this the information posted, using direct query does not give you the flexibility which you are looking for, for developing the data model you wish.

I would advise you go for the import option instead (please read about performance - both for RAM & CPU consumption). Also, understanding the difference between DirectQuery and Import will be helpful to you.

In regards to help in developing the dimensional model you want, again I will re-iterate what I said before and what was also posted here by another community user. This goes beyong Power BI and what we can do for you here. A book I would advise is:

“Data Modeling Made Simple: A Practical Guide for Business and IT Professionals”

Answering the final question if understood correctly, using calculations and filters over multiple tables and multiple relationships does NOT result in definite wrong answers/displays. However, it can be a result of wrong displays as the more over complicated a model, the more room for error.

Laz

View solution in original post

Anonymous
Not applicable

Hi adetogniLaz,

Thanks very much for the answers. I will just ask the database engineers if they can do the dimensional modeling, since they are the experts at it. 

For me as the data-analyst, I will just focus on querying the right tables I need and then import the tables into PowerBI or connect to them in PowerBI.

Then in PowerBI, I can create the relational model and then, make the model as star model as possible.

If I can’t create the dimensional and fact tables I need, then I’ll ask the database engineers if they can help me with the dimensional modeling.

Problem solved 😊. Thanks for helping me find the answer.

And thanks for the book, I will read it.

Regards,

Giovanni

Ciao Giovanni
Dimensional modeling is not something that can be resolved in a forum question. There are books about it. Also it's highly correlated to your reporting needs (so without knowing what information you need, it's impossible to solve this for you). Finally, without knowing what is the content of those tables, we can only guess and guessing is noy the right way to dimensional modeling.having said so, however, the beauty of powerbi is that it can also work on a non-star schema. However replicating those relationships in pbi will make it fairly complex to manage in dax




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

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors