cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BIXL Member
Member

Performance best practice with wide tables

Hey All,

In general we know that wide tables=bad performance in Power BI models.

Say for the sake of the example that I have a 500 columns table that contains a lot of measures and some attributes keys.

I'm interested to know if in regard to performance consideration there is any difference from a one 500 columns table than a two 250 columns  tables (with 1 to 1 relationship by pk) or even ten 50 columns tables (with 1 to 1 relationship by pk).

 

If anyone has a solid knowledge about that question I would be happy to have it shared.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Performance best practice with wide tables

@BIXL

 

Separating frequently used column and other useless columns so that it will not consume more memory when calling frequently used columns only. For the internal working of VertiPaq engine, you can refer to this article: The VertiPaq Engine in DAX 

 

Regards,

 

 

View solution in original post

3 REPLIES 3
Moderator v-sihou-msft
Moderator

Re: Performance best practice with wide tables

@BIXL

 

Try to remove useless columns when importing data into Power BI to reduce the data load memory. In your scenario, I think you may split some of columns into dimension tables and connect to fact table on key columns. Depends on usage, you can also include frequently used columns into same table together. 

 

Please refer to an article below:

Data Import Best Practices in Power BI

 

Regards,

BIXL Member
Member

Re: Performance best practice with wide tables

Hi @v-sihou-msft

Thanks for your reply.

I am aware of the best practices described bellow and in the link you provided.

My question was in regard to the way the VertiPaq engine works.

Since there are some unique situation where there are a lot of measures in a single table the question was if in engine performance point of view you should split them into several tables connected by the same PK. 

could you elaborate some more about :

"Depends on usage, you can also include frequently used columns into same table together" - Does this affect performance ? or just a more "user friendly" model, for which I'm totally on.

 

Thanks

Moderator v-sihou-msft
Moderator

Re: Performance best practice with wide tables

@BIXL

 

Separating frequently used column and other useless columns so that it will not consume more memory when calling frequently used columns only. For the internal working of VertiPaq engine, you can refer to this article: The VertiPaq Engine in DAX 

 

Regards,

 

 

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,451)