Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
DataSkills
Helper II
Helper II

Best practice - related columns in Power Query

Hello there, 

 

I am busy starting a new project and using a datamart on the Power BI service to create my model. The data I am connecting to sits in an Azure SQL server. 

 

When I connect to my tables, many of them already have related columns where the DBA has set these up.  For example, the company table has these related table fields. 

DataSkills_0-1688641212399.png

In this company table, I have the appropriate field which allows me to get this same data from the related table, by setting up the join in the Model View. 

 

My question is, do I simply delete these related columns, and rather rely on the relationships I set up in the model view or do I keep these related tables (and presumably I would have to expand them and select which fields to retain).  I am not sure how to handle these. 

 

I want to set up my model in the RIGHT way, so that all the reports I build on this model work effectively. But I have searched around and didn't find any suggestions or help on what the best way is to handle this scenario! 

Thanks in advance. 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

You have correctly identified the 2 options.  There is no 'best practice' in this scenario -  whatever gets you to the model that you want is the correct way to go.  Powerbi bringing back the related tables is a feature that you may or may not want to use. It can be handy, especially if you want to make use of the existing relationships in the source without digging around in the source db.  However if you have full knowledge of the source data and you know how you want to transform data into the powerbi model, you may want to delete the related table columns and import them separately.

--

As long as you use a star schema and identify the Fact and Dimension tables you'll be on the right track.  The way you get there is up to you.

View solution in original post

3 REPLIES 3
HotChilli
Super User
Super User

My opinion is that it's not really about the relationships.  It's about getting the data into powerbi.

SQL databases are usually normalised and optimised for storage based on selects/ inserts/updates/deletes i.e. the relational model.  For powerbi, you will want a dimensional model (star schema) because it's purely about selects.

--

So it's not about a larger model, it's about the right model.

DataSkills
Helper II
Helper II

Thank you @HotChilli

 

Would I be correct in saying that if I elected to use the existing relationships in the SQL database, and expand these columns out, I would end up with a larger data model, because the data would be less normalised? So, that would be the downside of using the existing relationships (vs the benefit of not having to understand the data structure because I would simply leverage what is in the database.)

HotChilli
Super User
Super User

You have correctly identified the 2 options.  There is no 'best practice' in this scenario -  whatever gets you to the model that you want is the correct way to go.  Powerbi bringing back the related tables is a feature that you may or may not want to use. It can be handy, especially if you want to make use of the existing relationships in the source without digging around in the source db.  However if you have full knowledge of the source data and you know how you want to transform data into the powerbi model, you may want to delete the related table columns and import them separately.

--

As long as you use a star schema and identify the Fact and Dimension tables you'll be on the right track.  The way you get there is up to you.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors