The Power BI Community Show Ep 5 - The Importance of Data Modeling

[danielzana]

This is a live show hosted by the Community Engagement Lead for Power BI, Kelly Kaye. She is passionate about the Power BI Community, founder of the Power BI Women User Group, and a Power BI user herself.

On this episode, Kelly will speak with Jeroen ter Heerdt who will discuss The importance of Data Modeling. With different segments each episode, you'll want to make sure to check out future episodes as well.

Jeroen's Links:

-Data modeling webinar series:  https://docs.microsoft.com/power-bi/fundamentals/webinars 

-Learn module on data modeling:  https://docs.microsoft.com/learn/modules/design-model-power-bi/

-Connect with J on Twitter:  https://twitter.com/jeroenterheerdt

-Connect with J on Linkedin: https://www.linkedin.com/in/jeroenterheerdt/

Follow us on Twitter - https://twitter.com/mspowerbi

[nawaz13]

Why is this hosted on YouTube and not MSN videos ?

[berntmagne]

Interestning to learn about the bridge table that is created in many-to-many relationship, comforting to know that reports are not useless when this is used. 

[NickOP]

Any recommendation on books/websites for building robust data models within Power BI?

[jeroenterheerdt]

there are multiple books out there, some focused on Power BI, some technology agnostic. The classic book about dimensional data modeling is Kimball's Datawarehousing toolkit. There are books on Data modeling for Power BI published by Packt as well. I have personally never read it, but the Star Schema reference is also mentioned very often.

[daviddonoso]

Hi, please help.

I have dimension tables on my project and mantain them on Google Spreadsheets (for example the Stores list with their state, creation date, size and manager). Each column of this table has a responsible (for example I create each new store and other person adds the state/location).

Is there a best practice to store this table and mantain them? A software from microsoft or something similar??

I protect cells, but i want that if I create a new store I want the responsables to be able to add their information. It doesnt seem the right way to mantain Slowly Changing Dimensions (SCD).

I Dont know if Dataverse, Power Apps, Lists or other are more appropiate for this purpose.

Thanks!

[jeroenterheerdt]

hi, thanks for posting your question! Not sure I understand what exactly you're after, but it looks like need some data store between the spreadsheet and Power BI, such as Microsoft Synapse, Azure SQL or Dataverse, or whatever helps you to store the data and track it over time.

[daviddonoso]

Hi Jeroen, thanks for your reply. I´ll try to explain better.

I have many dimension tables. Some are from the softwares we use (so those are fine). Others are mantained on Google Spreadsheets that are updated manually. This last ones are, Dim_Stores, Dim_Accounts groupings, Dim_StoreSalesGoals, Dim_Store characteristics (when it opened, when it closed, who is the responsable manager, the size of the store, etc).

So when I create a new Store, I have to create that new store on every dimension so others can complete their information on the different dimension tables. The new store doesnt appear blank on the other dimension tables y have to create a new line on each one.

I imagined a Form or some similar tool better made for SCD tables. ¿Which of the ones you mentioned is the most basic to manage?

Thanks!

[jeroenterheerdt]

problem is that you need some way of storing SCD tables? It looks to me that the spreadsheet will only ever contain the latest status, right? It's hard for me to answer what the right place for you is to store that "history", it depends on many things such as what you're used to, the data volume, what technologies you have access to, etc.

[Anonymous]

Q: Is there a relationship/ratio between the number of fact tables and dim tables?

[jeroenterheerdt]

hi, thanks for taking the time to ask the question 🙂 normally I'd expect there to be any number between 4 and 20 dimension tables. Very complex data models might even have 100s of dimension tables.

[carlnasc]

Thanks for the session, it was very informative!!

[jeroenterheerdt]

glad you liked it!

[UB400]

I have question on the example of a sales model you're currently showing, did you bring Sales "Header" info into the Sales "Details" or "Line Items" table in the diagram you're showing?

[jeroenterheerdt]

the 'Sales' table is on the line level, in this example.

[UB400]

Thanks for the reply, much appreciated.

[parishdapbi]

Q: What about joining fact tables together in a relationship? Especially facts at different level of detail. Say Orders -> Lines -> Discounts.

[jeroenterheerdt]

it happens, but not as often. If it is very common in your scenario (i.e. more than 25% of the relationships are fact-fact) you need to rethink your model

[b5lurker]

Great to see content that is "pushing" PBI users towards proper model building in PBI, this the KEY to using PBI in it's most optimized form. Just adding tables to your report without making it into a proper model will make your job so much harder. 🙂

[jeroenterheerdt]

I fully agree!