Why there is a delay in Query Begin on Power BI ?

[Kheranooh]

I have a PowerBI report published on PowerBI Service, this report is connecting to On-Premise SSAS Tabular Model cube. This report has 10 visuals, here are my observations when an user opens the report and traces are shown in below image,

1. It starts running 4 DAX queries in parallel (row number 1,2,3 and 4 in below image), remaining 6 visuals DAX queries haven't started. So Is it fair to say that PowerBI Service runs max 4 queries concurrently per user per report page at a time. ?
2. Once  a query execution ends, a random query gets picked up from those remaining 6 queries, and it starts running it immediately. So basically I assume it's trying run 4 queries concurrently at any given moment. It picks new queries to run as soon as any query existing query ends. My understanding is clear till here, please feel free to correct me if I misunderstood something here.
3. Now my doubt comes at row number 11 in below image where a query ended on 12:40:21, so I expect that it should pick the next query and start running it immediately which isn't happening. In fact next Query begins on 12:40:41 (row number 15), because of this I lost 20 seconds in between without knowing the reason why this delay happened in Query Begin ?

I repeated the similar exercise with multiple users and different reports, different cubes, but every time I see the same pattern happening. It's a black box for me and would really appreciate some help in understanding it.

SSAS Trace

[Choca]

Hi, I lnow this is an old topic but do you guy find any info about this ? I'm facing the exact same behaviors with parallelism and delay time between the dax query start and the sql query start. 

[lbendlin]

Keep in mind that this is also a function of the capacity. A premium SKU will behave different than a shared capacity, and Gen2 will behave different than legacy.

[v-deddai1-msft]

Hi @Kheranooh ，

In my environment, it is different from yours:

In my option, it is also related to number of CPU cores.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Kheranooh]

@v-deddai1-msft That's may be because your visuals might be simple ones (meaning DAX might be completing quickly within few seconds or milliseconds). also don't run the report on PowerBI Desktop because it runs all the queries in parallel and we are seeing this issue on PowerBI Service. Therefore please run the report on PowerBI Service or Reporting Server only. 

To replicate my scenario, you can intensionally put 8-10 visuals and each of these visual's DAX should take atleast 5 or 10 seconds duration. that's when you would see that it is starting only 4 queries in parallel and maintaining that concurrency also you might get to see some delay in query starting as reported by me. Please keep me posted 🙂 

[v-deddai1-msft]

Hi @Kheranooh ,

Unfortunately I did not find any accurate parallel running figures supported by the documentation. As far as I know,  we should consider queries run in parallel instead of visuals. Because one visual always contains more than one query. And if  one query depends on the other query , it will also act like your situation.

To increase parallelism in ssas during querying for servers with multiple processors, consider modifying the Threadpool\Query\MaxThreads and Threadpool\Process\MaxThreads options to be a number that depends on the number of server processors.  Please refer to /* Tune processor usage8*/ in https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/cc966527(v=tech... and https://docs.microsoft.com/en-us/analysis-services/server-properties/thread-pool-properties?view=asa... 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Kheranooh]

Hello @v-deddai1-msft , I changed the MaxThreads count to a higer number, but still outcome is same. Only 4 queries running in parallel and also seeing a delay in starting of a DAX query through PowerBI.  Although there is no document which provides an accurate parallel running figures, but here (https://youtu.be/kkIXtvU1AiM?t=436) Adam Saxton mentioned count of 4. Therefore i would request you to please check further and help me these two issues Concurrency Number and Query Delay. 

[v-deddai1-msft]

Hi @Kheranooh ,

The delay may caused by running out of memory. So it needs to wait for resource to be released. Plesae also refer to https://www.mssqltips.com/sqlservertip/5323/sql-server-analysis-services-processing-order-options/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Kheranooh]

@v-deddai1-msft Thanks for the reply. It doesn't look like a memory issue as our SSAS cube server has enough memory and CPU is also not an issue, Please refer the screenshots below.

One question related to concurrency : How many visuals/DAX queries can run in parallel at a time through PowerBI Service(Cloud) per user per report?

CPUMemory

[lbendlin]

Fun fact: Power BI renders five visuals at a time.  If you cram 50 visuals onto a page that means 45 of them will have to wait until the first five have finished rendering, and so on.

Use Small Multiples, and other ways to reduce the number of visuals on a page.

[Kheranooh]

Thanks, but cube trace speaks otherwise, showing 4 visuals rendering at a time. and moreover I am curious to know why there was delay in query begin as highlighted in the trace screenshot above.