The Dimensional Dilemma and Power BI

If star schema design is a data modeling best practice for Power BI, why isn't everyone doing it? Just because the other database practitioners aren't onboard with dimensional modeling best practices doesn't mean that we can't design efficient BI solutions. This demonstration shows how to transform a flat source data set into a fact table and related dimensions.

[@drivetrainerYT]

Thank you, Paul. I've realized I' do exactly this sequence of pre-processing my extracted data from Odoo ERP data base before I produce operational or analytical reports in excel Power Query / Power Pivot. Been doing that intuitively, just based on habits internalized whilst workin in dBase envos years ago. Thought it was a quirk of mine, to ease up things for an old-schooler, but now even more relieved it not only worked for my team, but is a never going away classical approach. Much kudos to you and your work here and on your blog. I recomend your lessons to any starter I know.

[@normanli2943]

Found this very interesting and inspiring video on your blog, which was mentioned in your presentation at Power BI Summit 2023~ Bravo Paul! Already subscribed.

[@pabeader1941]

Good job Paul! I like your style and speaking speed. Will be sharing this one with my folks at work.

[@Ploplox]

Whats the difference between reference and duplicate when copying tables?

[@PaoloTroia-h3e]

Thank you Paul. If I have two datasets, I need to create two different star schemas? If yes, how can I handle Dim Tables and Facts Tables from both datasets?

[@Milhouse77BS]

Preach on, Brother Paul. (better with sound :)) I find a larger problem to be where the relational database, including header-detail relationships, is used to build the Power BI model, instead of transforming to a dimensional model. Easier, and "works" for smaller sized models, but the header-detail will blow up in terms of size and query time, especially as number of rows gets larger than, say, 100,000 rows.

[@carltonseymour869]

Fantastic video. At 5.55 minutes into the video, you mention 'now we have a distinct product list'. Would it be more correct to say we now have a unique product list?

[@joelluis4938]

Hi ! Amazing video ! But I have a question . Why did you use reference to create a new version of your fact table ? Why didn't you use the first Version of your fact to work ? The first table need to be uploaded to power bi anyway . So I don't understand the reason behind your decision . There is any advantage ? I would have selected Duplicate table for every DIM created in the video .

[@miazhao1339]

Hi Paul, good video, I like the star schema. Thanks for sharing. It worked perfectly fine on desktop, however, I had an issue with the index column after I published the report onto the PBI service. The index gets changed after the scheduled refresh which made the relationship fail (the index in the dimension table matched the wrong one in the fact table). Do we have some walkarounds? Thanks

[@ricardosoler]

Great content. Congrats!

[@krzysztofmmichaluk]

Great video. Thanks.

[@Milhouse77BS]

I like how you have Fact, Dim as suffixes instead of prefixes. Easier for me to read that way.

[@martyc5674]

Brilliant- we get frankentables where I work- seems to be the norm. I had often thought of doing this but thought I would have performance issues, that trick at the start not loading it is key 👌

[@sravanihanumantha7081]

very helpful

[@happyheart9431]

Million thanks

[@stephanyhope7076]

'promosm'