Forum Discussion

Sparks's avatar
Sparks
Helper I
5 years ago
Solved

Need help with a performance issue - optimizing QPU usage

Hi, I have a fairly simple semantic model hosted on Azure Analysis Service (S2 tier). The volume in each of the table is quite high (millions). There is also a Power BI report hosted in the Power BI...
Need Help
  • HotChilli's avatar
    HotChilli
    5 years ago

    Here are my thoughts (from the supplied sample pbix - so may not carry through to the real thing but hey-ho, there we go):

    Do we need the filter to check 'open' or 'closed' status ? Seems redundant.

    Do we need SkillName in the JobPostingSkillName table? Seems redundant.

    Does the measure need to use DISTINCT? We can already see that the CALCULATE is redundant but why are we checking for duplicate 'JobId' s for the same skill?  Even if there are (which would probably be a data quality issue) is it significant in terms of the count?  If not, then the measure is a simpler COUNT (and you can replace the check for blank with '+0' too).

    In business terms, is it important to see 12,855 records in the visual.  No-one is going to scroll through them.  What is it important to see here? Top 50? Maybe putting some thought into that would result in rewrite of the measure.

    ---

    Also, in terms of the model, not this particular visual, the jobPostingAds is being used as a dimension table(even though it's really another Fact table) and it's got 10 million records so that's going to cause it's own performance problems.

    ----

    Let me know what you think.

     

selimovd's avatar
selimovd
Most Valuable Professional
5 years ago

Hey Sparks ,

 

in general DISTINCTCOUNT is an expensive operation.

I don't really understand why you use a CALCULATE in this case as from my point of view it doesn't make sense. But anyway I don't think this is the bottleneck.

 

In some cases the combination SUMX and DISTINCT is faster, maybe give it a try:

SUMX ( DISTINCT ( JobPostingSkillDemand[JobId] ), 1 )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
Sparks's avatar
Sparks
Helper I
5 years ago

Hi selimovd , 

SUMX is causing more rows to be read and high CPU compared to the DISTINCTCOUNT.

 

Here is the DAX query generated:

 

// DAX Query --SUMX ( DISTINCT ( JobPostingSkillDemand[JobId] ), 1 )
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"Open",
      "Close"}, 'JobPostingAds'[JobStatus])

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'JobPostingSkillDemand'[SkillName],
      __DS0FilterTable,
      "Measure", 'JobPostingSkillDemand'[Measure]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(1001, __DS0Core, [Measure], 0, 'JobPostingSkillDemand'[SkillName], 1)

EVALUATE
  __DS0PrimaryWindowed
ORDER BY
  [Measure] DESC, 'JobPostingSkillDemand'[SkillName]

 

Any other solution?

 

Regards

Sparks