Data modeling best practices - Part 1 - in Power BI and Analysis Services

Hi everyone!

Maybe somebody here could help me with this issue:

I have a FactTable with CustomerID and ProductID as ForeignKeys.

The CustomerID has also some columns with information about territory such as (City, Region, Country).

I tried to combine those columns with the Fact Table, create the new Territory Table related to this columns and then delete the columns from the fact table and the Products Table with Power Query, butn if I delete any of this columns, all their fields become null.

As the columns should not repeat in a Star Schema, is there any way that I can separate those columns into a different dimension table and add this new Territory Dimension with his specific Territory Key inserted into the original Fact Table?

Thank you very much!

But what if I have just a wide tables in DB????

awesome tips patrick, love it!

Would love to see more videos like this that I can share with my team.

In particular I've been running into issues where we have a general policy that all transformations/data modeling are to be done on the back end, with I think the intention being that the logic can be accessed by other sources if needed and not just locked inside Power BI.

At the same time though, it just seems like this approach cripples what Power BI is capable of and leads to so many other issues. Especially when I see things like essentially multiple flat-file like views being pulled into a model, with relationships just being created in a really ambiguous way that has caused all kinds of problems that I inevitably end up needing to fix by creating a dimensional model.

Would love to see a video going more in depth on concepts like when is it right to do a calculated column in SQL vs. having a measure in DAX, or elaborating on why its better to have a flexible dimensional model instead of applying all your filtering logic etc. to a view and then using that to drive a single report page.

I've created really clean, robust dimensional models in the past so this sort of thing is infuriating to see, but at the same time I can't seem to break this misconception with people that creating a dimensional model takes too long compared to making a SQL view, or that having a pre-joined view in SQL isn't really a good replacement for a solid data model.

How to create report short cut in iPhone

I make a "model" for every fact table to separate it visually

Great video! Thank you for the very easy to digest examples

Great Mr. Will Smith

I thought it's Will Smith 😀 good info 👍

Hi Patrick, Great Video! But where is part 2 of this video as the title suggests? Thanks!

How many Columns in a Table you would consider it being a wide table?

Can you tell me if we can do Power BI Adhoc Analytics

How can I work with unstructured data, what process should I use to make structure data

Did a Part 2 ever emerge?
Loved this one

Always enjoy your videos -thank you 🙏

Dude, you scared me the way you started this Video.

Hey Patrick, wheres part 2? :)

awesome

How can i narrower my table which have a column with comma separated string values for almost like 20?? And i really have to work with those values..
Really need the help. TIA

how did you create multiple tables ?

Excellent video. Very well explained. Thanks. I would like to see more videos like these around the topid BI. Thanks once again.

Cc

You are a Rockstar. Really Informative ,Great Job ..Keep it up and going

New sub! Awesome vid and down to earth presentation with great humour and knowledge of the topic!

I had the same problem last week. Having two fact tables were i couldn’t use a slicer for a field in both tables correctly. The slicer would filter one table but not the other.

Thanks for your videos. I always enjoy them. I understand star schemas. Been using them for decades. What causes my hangups are filtering directions in the model. Hopefully you have a video discussing that topic.

NO WAY YOU CAN HAVE TWO DATA TABLE DISPOSITIONSSS, my mind just exploded ugh Patrick you're and angel sent from MS Heaven

Hi- Great video! I really need some help and support with my data modelling! Would be great if we good arrange a call maybe?

What will Smith is doing here? 😂

why many to many as both has same key so one can be primary and other can be foreign key ?

Is there a part 2 for this video? Couldn't find it 😢

Hi Patrick, my RMS creates a 24-digit primary key. Power BI displays these in scientific notation, which, of course, creates a ton of duplicates. We have tried to change the data type without success. We are forced to make the data type Text, which of course is a pig when it comes to compression. Is there a way we can have Power BI display the full 24-digit primary key?

Good content Patrick.

Awesome tips. Where could I see the part 2?

Thanks Patrick! Awesome advice.. I am great a fan of both of you guys. Just a question that, Is it a good practice to use SQL DB Views instead of DB Tables?

Hey Will Smith

hey patrick pls do a vedio on how to avoid bidirectional relationship and manu to many relationship

not seeing how this relates to analysis services

Do you guys have a video on how to correctly handle Many to Many?

If Morpheus was an IT guy

Awesome!!! Loved IT:)

Hi Patrick,

Need to understand how you are not getting an inactive relationship when you are dealing with multiple fact tables and multiple conformed tables? Is there any setting that you are making in power query editor etc?

Good day sir.
I would like to ask on how to include a dimension on a hierarchical design (recursion) to a fact?
Kindly set an example and provide explanation if possible.
Thank you.

You guys channel are literally saving thousands of people from losing their jobs

Dang...where's the Power Bi 101 video! I just finished a Power BI course and it was great theory and cool to see all the tool can do, but I'm lost at where to begin! I need some basic report building exercises to show me step by step what to do. Any such resources?

Thanks for waiting that was a great video

nice Patrick, Here multiple Fact tables are available . How can we handle the reports and dax queries..

Part 2? We need more advanced stuff!

Thanks for the video