cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
v-anabat Frequent Visitor
Frequent 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
Highlighted
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.
3 REPLIES 3
Super User
Super User

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!  

Highlighted
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.
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.