topic Re: The Power BI Community Show Ep 5 - The Importance of Data Modeling in Webinars and Video Gallery

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

danielzana — Wed, 08 Jun 2022 21:32:57 GMT

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/

watch?v=PoCUHCAt_DM

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

b5lurker — Mon, 23 May 2022 18:20:37 GMT

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

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

parishdapbi — Mon, 23 May 2022 18:34:42 GMT

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

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

UB400 — Mon, 23 May 2022 18:32:28 GMT

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?

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

carlnasc — Mon, 23 May 2022 19:01:00 GMT

Thanks for the session, it was very informative!!

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

Anonymous — Mon, 23 May 2022 19:04:21 GMT

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

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

daviddonoso — Mon, 23 May 2022 19:11:26 GMT

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!

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

jeroenterheerdt — Mon, 23 May 2022 19:55:59 GMT

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.

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

jeroenterheerdt — Mon, 23 May 2022 19:56:12 GMT

glad you liked it!

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

jeroenterheerdt — Mon, 23 May 2022 19:57:10 GMT

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

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

jeroenterheerdt — Mon, 23 May 2022 19:59:15 GMT

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

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

jeroenterheerdt — Mon, 23 May 2022 19:59:28 GMT

I fully agree!

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

jeroenterheerdt — Mon, 23 May 2022 20:07:52 GMT

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.

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

daviddonoso — Mon, 23 May 2022 20:31:40 GMT

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!

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

jeroenterheerdt — Mon, 23 May 2022 23:08:48 GMT

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.

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

NickOP — Mon, 23 May 2022 23:35:27 GMT

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

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

UB400 — Tue, 24 May 2022 04:13:26 GMT

Thanks for the reply, much appreciated.

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

berntmagne — Tue, 24 May 2022 13:59:10 GMT

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.

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

jeroenterheerdt — Tue, 24 May 2022 18:29:53 GMT

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.

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

nawaz13 — Wed, 25 May 2022 15:52:07 GMT

Why is this hosted on YouTube and not MSN videos ?