Forum Discussion

mjmar025's avatar
mjmar025
Helper I
7 years ago
Solved

Model Performance Enhancements?

Hi everyone,   I am looking to speed up a Power BI report that I have with a model that contains around 400k rows. I am pulling in the lowest level of data that my organization captures, but that l...
Need Help
  • MattAllington's avatar
    MattAllington
    7 years ago

    It’s very hard to say without seeing it.  Every relationship has a cost, and the cost is higher the higher the cardinality of the related columns. So you can improve things by removing high cardinality relationships.  This could be done by consolidating fact tables. 

     

    Options I think worthy of consideration include

    Removing columns in fact tables that are not used

    Unpivoting columns in fact tables 

    Consolidating fact tables together

     

    Eg

     

    if you can have 1 fact table with columns

    type, amount

    target, 5

    actual, 4

    other fact, 3

    etc, 8

     

    then you can write 

    Total target = calculate(sum(table[amount]),table[type=“target”)

    Total actual = calculate(sum(table[amount]),table[type=“Actual”)

    Divide([total actual],[total target])

     

mjmar025's avatar
mjmar025
Helper I
7 years ago

Thanks for the reply. I have 25 tables in the model - 4 main fact tables that add up to about 400k rows, several dimension tables, and several complimentary tables with targets and such. The fact table with the most rows (200k) has about 24 columns.

 

The majority of the joins between these tables are done via int keys.

 

I do have a bunch of calculated measures that reference multiple tables in the model - could this be the problem?

For example:

% of target = DIVIDE(sum(Facts[Amount]),sum(Targets[Amount]))*100

 

Or maybe it's just the number of tables and I should somehow consolidate them?

 

Thanks again.

AkhilAshok's avatar
AkhilAshok
Solution Sage
7 years ago

As MattAllington already pointed out 400k is a very small model. It shouln't cause a performance issue. Do you have specific charts/Tables which are slow? If yes, what measures you use in that visual?

  • mjmar025's avatar
    mjmar025
    Helper I
    7 years ago

    I used DAX studio to trace my model and the longest query is only 47 milliseconds. This is making me think that the cause of the slow down is having too many visuals on one page, and not the model or any measures. Currently I have this on one page:

     

    10 Cards

    3 gauges

    2 bar charts

    2 pie charts

    2 bar charts

    2 custom visuals

     

    Is it possible that too many visuals is the cause here?

     

    Thanks.