Direct Query in Service - Mashup Engine / Caching

[NevZab72]

I have built a report with many pages and visualisations in PBI Desktop. The data set is linked to SQL Server and all tables are using direct query storage mode. I have 2 questions that pertain to viewing the report in the service post publishing.

1. When I open the first page of the report it can take 10 to 20 seconds to refresh the visualisations. Having run the SQL Profiler, I can see that most of the activity is driven by the Mashup Engine. What is happening? Is this equivalent to the process that is run when you click refresh button in Power BI Desktop?

2. After the initial Mashup process (point 1), things get much faster. There appears to be caching going on as when I return to visualisations the SQL queries are not executing and the data is presented instantly. My understanding was that there is no caching when direct query storage mode is used. Am I wrong?

[V-lianl-msft]

Hi @NevZab72 ,

1.No.

2. All DirectQuery requests are sent to the source database,as you create or interact with a visualization, Power BI Desktop queries the underlying data source, so you’re always viewing current data.

When you publish the report to the service, you need to configure a gateway (local database) or provide credential information (azure database).

In service, the status of the last operation of the report is cached, so you can configure to schedule cache refresh or manually refresh the dataset.

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem 

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-enterprise-manage-sql 

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

[NevZab72]

Okay, I think you've answered one of my questions with "In service, the status of the last operation of the report is cached, so you can configure to schedule cache refresh or manually refresh the dataset." This makes complete sense.

I am not sure that my first point has been answered or clarified, and please note that my gateway was already set up and in working order, however, I don't this this is applicable. Let me explain with some simple steps:

1. Open PBIX file 1 and click report page 1. As expected, this executes the underlying SQL queries and the visuals are updated almost instantly. All good!

2. Open PBIX file 1 and publish the same report as per step 1 to a workspace in PBI service.

3. Log into PBI service and run page 1 (same page as per step 1). The Mashup Engine runs a load of Mashup of scripts and the visuals are update after around 15 seconds. Not good!

In summary, I am asking what is going on in those 15 seconds and why do Desktop or Service work differently when launching the same page?

Cheers,

Nev.