Relational model vs Star model

[Anonymous]

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.

[Anonymous · Accepted solution]

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

[Anonymous]

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]

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

[Anonymous · Accepted solution]

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

[Anonymous]

Hi adetogni, Laz,

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

[Anonymous · Accepted solution]

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