Forum Discussion

mawh's avatar
mawh
Regular Visitor
4 years ago

PowerBI battering the database

Our database is a fairly low-powered Azure instance that houses a large amount of data and has one chief summarizing query in a stored procedure that takes anywhere between 30s and 2 minutes to compl...
Need Help
mawh's avatar
mawh
Regular Visitor
4 years ago

I've dug into the PBI file a little (I'm very new to PBI) and I see:

 

A page with 10 measures on

 

Each measure is slightly different, for example here is one:

 

CountLocalHighAppleAllocation = CALCULATE(COUNTROWS(GetSummaryMSTVF),
GetSummaryMSTVF[NumberOfApples],
GetSummaryMSTVF[PercentAllocatedLocally] > 0.5,
GetSummaryMSTVF[PercentAllocatedLocally] < 0.75 )
 
And another one:
CountLocalExcessiveAppleAllocation = CALCULATE(COUNTROWS(GetSummaryMSTVF),
GetSummaryMSTVF[NumberOfApples],
GetSummaryMSTVF[PercentAllocatedLocally] > 0.75,
GetSummaryMSTVF[PercentAllocatedLocally] <= 1.0 )
 
 
There are 10 like this, all doing some variation on counting or summing the number of apples that have been low, high or excessively allocated. Opening the page fires 10 queries at the DB, basically just running the TVF (If I ask for currently executing queries I get the TVF definition) so I now understand the why. Perhaps there is some other technique for working with this data (I didn't come up with this way but i can relay suggestions back) - it's a "for these ~100 rows, bucket into 3 bands based on percentage X and give a count/sum/average of 2 different data items from the bucket.
 
If it were SQL I'd describe it as something like:
 


SELECT SUM(apples), COUNT(apples), SUM(oranges), COUNT(oranges)
...
GROUP BY CASE WHEN percent < 0.5 THEN 1 WHEN percent < 0.75 THEN 'high' ELSE 'excess'

 
 
---
 
I could look at the query again, but there wasn't much to do with it when it was last reviewed; it's got a huge amount of data to summarize..
 
 
  • ToddChitt's avatar
    ToddChitt
    Super User
    4 years ago

    >>it's got a huge amount of data to summarize..<<

    How big is "huge"? how much space does the table take up?

    And this is accessed via a stored procedure, and via Direct Query? I tried to spin up something similar but it failed. I could do IMPORT of a stored procedure, but not a Direct Query.

    Can you share the exact (or near exact) Power Query M statement used? (In Power Query, click Advanced Editor).

    And can you share the contents of the stored procedure? Is it calling Functions?