DAX Performance

DAX DAX Calculations
21
Cardinality(1000s) Table(1000s) Col Size(1000s) Rel Size %DB No of Columns
Fact 15000 500000 500000 700000 84 30
Dim1 90 30000 30000 5 10
Dim2 80 25000 25000 4.5 10
Dim3 43 10000 10000 1.5 30
Dim4 500 3000 3000 0.5 5
Dim5 40 10000 10000 1 20
Dim6 30 10000 10000 1 10
Dim7 20 3000 3000 30 0.4 20
Dim8 10 1000 1000 10
Dim9 1 300 300 20
Dim10 1 300 300 10
Dim11 1 300 300 10
Dim12 1 300 300 10
Dim13 1 200 200 10
Dim14 0 200 200 10
Dim15 0 30 30 10
Dim16 0 150 150 10
Dim17 0 85 85 10
Dim18 0 200 200 10
Dim19 0 200 200 10
Dim20 0 100 100 10
Dim21 0 100 100 10
Slicer Table 0 20 20 1
Slicer Table 0 20 20 1
Slicer Table 0 20 20 1
Measure 0 0 0 1
Measure 0 0 0 1
22

The Data model is generally a star schema.
The data model is close to the 1:M from fact out.

There are two exception.
One dimension table hangs off another dimension table

One dimension table has a many to many join and is a bit of an issue. It is basically a dimension table with 10 different versions of the dimension.

I am looking at trying to fix the two model items
Thanks
E

23

Hi @ells, did the response provided by the contributors you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

24

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up

25

Just in the last few moments of testing a new model.

26

Have converted the last chained dimension. When I look at DAX Studio and look at the Metrics I dont notice a big difference. The spoace is saved by not having the key so cardinality is lower.

Performance seems to be the same. Looks like we have narrowed down the issue.
Thanks
E

27

I know that the performance of one of my dashboards was improved after using Power Query to filter down the data first. That might help with DAX performance if the issue is large amounts of data.

28

@powerbi_jp
Still a little stuck here.

  • So I went full star schema that gave a modest improvement.
  • All of the monetary values I went to 2 decimal places - this affected the cardinality of the monetary values and reduced the size of the data model. This was a big boost
  • Visuals - I have removed some extra calculations as they were adding to the time to complete the page in the report
  • Another issue appears to be the laptop. I moved the fact out to a data flow and refreshed it there. I could squeeze in an extra year of data doing it in a data flow.

The data set is now the minimum and it still takes 10 seconds for some report pages to complete. I just know this is not going to go down well.
Thnaks
E

29

This morning I went back to looking at this again.
I am creating a data flow. This consists of

  • 6 queries (to get the fact)
  • All 6 are set to disable load
  • the 6 are then appended into a new view (Fact)
  • Then I copy the table combine but strip all columns bar the Surrogate key and the Profit Column for the Query Profit
  • Then I want to creat a query the same as Profit but with zero for the profit value (ProfitZero)
  • Then I want to combine Profit and Profit Zero

Each of the six queries (for Fact) holds 4 million records
Profit will have 46 M records ( 3 columns = Surrogate Key, Profit(Money), Profit = TRUE (boolean)
ProfitZero will have 46 M records ( 3 columns = Surrogate Key, Profit(Zero as Money), Profit = FALSE(boolean)
So Joint Profit will have 48M records

The problem I am having is to get the data in a data flow. So far my laptop has been useless at loading the data to Power BI desktop.

I just dont get why this is such hard work for the Power BI service? I have been waiting hours for the queries to validate. They have failed once already and I suspect they will either timeout or sit there hour after hour!
E