Predict Premium efficiency FE, SE, SE CPU, and Capacity Units (CUs)

[AxiomaticOne]

How do I determine if a change I make in my semantic model will decrease the number of Capacity Units (all other things being equal) and roughly how much?

Is there a tool (Dax Studio, Performance Analyzer, etc.) that I can run the queries before and after against and measure the difference that I can then use to make a very rough projection?

Background:

• We're maxing out CUs in Premium P3 capacity.
• I've added an aggregate table that is 75% smaller than my initial table.
• I get great results for duration and Total that I would expect when looking at performance analyzer and DAX Studio, but the thing that jumped out to me is SE CPU is 50% higher. WHAT?!
• I've searched google, forums, reddit for the past 2 hrs for an answer.

[GilbertQ · Accepted solution]

Hi @AxiomaticOne 

The storage engine is where it goes to actually get the data from and that is why you will see more capacity units consumed. I'm. not sure why it is not using your aggregated table. You would need to check this using SQL Server Management Studio to make sure that it is using the access from your aggregated table in terms of performance testing. You could look at using studio benchmarking to see if there is any improvement over time. other than that you would need to get in a consultant who understands this better to assist you.

[AxiomaticOne]

@GilbertQ thanks for taking the time to reply. I had run that a couple times and got a higher SE CPU each time. I just don't understand that.

Below for those curious.

That said I did do the Benchmark testing in Dax Studio and got a satisfactory result that I'm going to move forward with this approach.