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

[v-anabat]

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.

[v-frfei-msft · Accepted solution]

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

[v-frfei-msft]

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

[LivioLanzo]

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