topic Re: High CPU refreshing Excel and power BI (totaly measures) in DAX Commands and Tips

High CPU refreshing Excel and power BI (totaly measures)

Momie2005 — Sat, 23 Apr 2022 19:05:31 GMT

Hello all,

I've a report financial(based on a lot of measures HTD,QTD,YOY, MOM...),connected to a SQL SERVER ANALYSIS tabular 2016 cube (where the measures and logic is done), it works fine when theire is 2-3 users rereshing the report(with hight CPU usage 99% on the server), but when it comes to a lot of users in the same time doing filters it really crushes 😕 ...

Is it normal? is theire any suggestion to do in the cube or in the report?

Here is an exemple of our report:

For every row-Hierarchie- ( sometimes 120 rows it show the 12 measures) ,and i have more than 4 pages on my report like this.

ps: when it come to Excel just one user take a loot of time to Refresh the Excel 😕

Many thanks in advance .

Re: High CPU refreshing Excel and power BI (totaly measures)

SpartaBI — Sat, 23 Apr 2022 20:18:48 GMT

Either you improve the performance of your measures or you buy more cores to the AS 🙂.

Also, maybe you don't need to show all rows and measures in the default state of the report. Start with less and let the users add stuff if they need. Will save a lot because at the moment everytime a user open the file all of these measures × rows are running

Re: High CPU refreshing Excel and power BI (totaly measures)

Momie2005 — Sun, 24 Apr 2022 17:48:16 GMT

Hello,

Thanks for your answer, For the server It's on his TOP (512 G RAM ,and 32 Cores CPU).

I'm starting to do some Dax improvisations (using / instead of DEVIDE()...),any other suggestions on the calculate or filter functions?

Re: High CPU refreshing Excel and power BI (totaly measures)

Momie2005 — Sun, 24 Apr 2022 17:53:03 GMT

I have to add that on partition(for just one year) Its +100 Millions Rows

Re: High CPU refreshing Excel and power BI (totaly measures)

SpartaBI — Sun, 24 Apr 2022 18:41:32 GMT

1st of all make sure your model is the best one you can have, even before going to the DAX.
Look that you didn't bring any redundent columns, that you don't use bi directional filters and that you have a star schema and not a snowflake.
After all that, regarding DAX, there are endless suggestions 🙂 but I'll go with one that has a lot of potential to help: look to see if your filters are on entire tables instead what thwy should bu -> on the minimun number of columns necessary for the porpuse of that filter, and fix that. This is usually the devil.
Finally, just to make sure I was clear in my previous message: I didn't mean you remove rows from the data set, I meant the the default state of the visuals won't show all your measures on the start. This alone will save you a lot of memory. Sometimes end user need to do extra steps in order for you to be able to give them reports that are not crushing.
Hope these help
Just to make