cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
v-anabat Regular Visitor
Regular Visitor

How to design a better Power BI model with many tables?

Let's say if I have a model with snowflakes schema. An initial Table t1 which is connected to t2 and there are many tables which are connected to t2 (say t3,t4,t5,t6.......t25). Here t1 has higher level information, so mapping from t1 to t2 is 1 to many. Also from t2 to other tables relationship is 1 to many (and few cases bi-directional). Since we have too many tables(more than 20 which are joined on the same ID ) at t3 level, will it affect the performance of the model? So, is it better to have 2 tables of t2 so that we will have fewer number relations on the same ID and flow will be less?

 

Basically, my question here is if I have a table with more than 20 tables surrounding it, is it better to duplicate the surrounded table into 2 tables, and share the child tables for 2 tables for better performance.

sample image.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to design a better Power BI model with many tables?

Hi @v-anabat ,

 

To optimize your model here are Some best practices:

  • Tables or columns that are unused should be removed if possible. 
  • Avoid distinct counts on fields with high cardinality – that is, millions of distinct values.  
  • Take steps to avoid fields with unnecessary precision and high cardinality. For example, you could split highly unique datetime values into separate columns – for example, month, year, date, etc. Or, where possible, use rounding on high-precision fields to decrease cardinality – (for example, 13.29889 -> 13.3).
  • Use integers instead of strings, where possible.
  • Be wary of DAX functions, which need to test every row in a table – for example, RANKX – in the worst case, these functions can exponentially increase run-time and memory requirements given linear increases in table size.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
LivioLanzo Super Contributor
Super Contributor

Re: How to design a better Power BI model with many tables?

Hi @v-anabat 

 

yes, for performance reasons it is better that you apply the second logic you have outlines and create less relationships. This would also make your model much easier to use and maintain 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Community Support Team
Community Support Team

Re: How to design a better Power BI model with many tables?

Hi @v-anabat ,

 

To optimize your model here are Some best practices:

  • Tables or columns that are unused should be removed if possible. 
  • Avoid distinct counts on fields with high cardinality – that is, millions of distinct values.  
  • Take steps to avoid fields with unnecessary precision and high cardinality. For example, you could split highly unique datetime values into separate columns – for example, month, year, date, etc. Or, where possible, use rounding on high-precision fields to decrease cardinality – (for example, 13.29889 -> 13.3).
  • Use integers instead of strings, where possible.
  • Be wary of DAX functions, which need to test every row in a table – for example, RANKX – in the worst case, these functions can exponentially increase run-time and memory requirements given linear increases in table size.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Community Support Team
Community Support Team

Re: How to design a better Power BI model with many tables?

Hi @v-anabat ,

 

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 294 members 2,829 guests
Please welcome our newest community members: