Why we make data model when we have data in one excel file

Power BI
1

Dear Experts,
I have power Bi interview and have been provided data set which contains one table.
I have broken it down into Star schema and normalise the tables.

I am sure they are going to ask me the reason for doing this and how it will effect if the same was not done.
Can you please advise an answer.
Attached is the excel data set and my power Bi data model screen shot.
Thank you.
Sample_Data.xlsx (12.5 KB)

Model_Powerbi
Model_Powerbi1288Ă—717 27.6 KB

2

one table you need in the model is a date table. Its best practice to be able to do any time intelligence calculation. Dim tables are needed make your calculation easier too.

Maybe check on the modelling videos on edna for some your questions too.

check out the workout modelling section too. @BrianJ just posted this a couple days ago about star scheme why it needs to be done

I hope this helps.
Keith

1 Like
3

Hello @EnanBahadur , and best of luck on the upcoming PowerBI Interview.

Arguably, the top four reasons why:

  1. Usability
  2. Simpler DAX
  3. Performance
  4. Faster Refreshes

Being helpful, I will share several resources which resonate with me on this topic:

First, one of the best articles by Marco Russo & Alberto Ferrari, which gives fatherly advice on a cornerstone of PowerBI and Tabular modeling:

“While not always the only choice, is often the best choice”.

Their article: A classic modeling question: Is it better to build a model as a regular star schema or as a single table in Power BI? Also the companion video Star schema or single table in Power BI

Second, The Microsoft Learn article Understand star schema and the importance for Power BI which gives the following advice:

Star schema relevance to Power BI model:
Star schema design and many related concepts introduced in this article are highly relevant to developing Power BI models that are optimized for performance and usability.

Consider that each Power BI report visual generates a query that is sent to the Power BI model (which the Power BI service calls a dataset). These queries are used to filter, group, and summarize model data. A well-designed model, then, is one that provides tables for filtering and grouping, and tables for summarizing. This design fits well with star schema principles:

  • Dimension tables support filtering and grouping
  • Fact tables support summarization

There’s no table property that modelers set to configure the table type as dimension or fact. It’s in fact determined by the model relationships. A model relationship establishes a filter propagation path between two tables, and it’s the Cardinality property of the relationship that determines the table type. A common relationship cardinality is one-to-many or its inverse many-to-one. The “one” side is always a dimension-type table, while the “many” side is always a fact-type table.

Third, the Guy In A Cube video Why Power BI loves a Star Schema where Patrick explores what a STAR SCHEMA is and why you should be using it.

@BrianJ 's wonderful LinkedIn post on Data Modeling brings it all together. BTW, the books in the image within his article are must-haves relating to this most important topic.

Once again, best of luck on the PowerBI Interview! I do encourage you to watch @BrianJ 's video referenced above.

Cheers,
@ystroman

2 Likes
4

Hello @EnanBahadur

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

5

@ystroman Thanks alot for the detailed explanation.
Really Appreciate.

1 Like