Power BI Dataset Gives Mashup Timeout Error While Refreshing

Help – my Power BI dataset is giving mashup timeout error on refresh

The mashup timeout error

Incremental refreshes are a great benefit to have in order to reduce refresh times – but not when they give a mashup timeout error. Although they are meant to be a performance enhancement, they aren’t always the best approach as they fire parallel queries to the source and apply date time filters. Despite the default timeout being 2 hours, or 5 hours for premium workspaces, a timeout error can occur due to the underlying nature of the query and incremental refresh as this 2 minutes is extended when run alongside other queries.

Incremental Refresh

This is the configuration of the incremental refresh:

• Archived data is 6 months
• Refreshed data / Refresh period is 7 days

This would look something like this (and maintained going forward automatically by Power BI)

 

What does this mean?

This means that each scheduled refresh looks back 7 days with each day being a single partition. It will also create the relevant partitions if they don’t already exist. This is why you have to set a filter on a specific date time column – so it filters to a day/ week/ month for the relevant partition.

Lets say your source data exists as a view in SQL Server. The refresh query will be like this:

SELECT

[Column1],

[Column2],

…

FROM dbo.view1
WHERE

[IncrementalDateTimeColumn] >= ‘2024-01-17 00:00:00.000’ AND

[IncrementalDateTimeColumn] < ‘2024-01-18 00:00:00.000’

You can find the exact query by viewing the native query, just go to:

• ‘Edit Query’ in Power BI desktop
• Then right click the latest step
• Then ‘View Native Query’

If this option is greyed out – you are doing too much in Power BI most likely, such as indexing.

This is important to understand when it comes to testing the query in SSMS. The view’s underlying query can not be tested, rather a query on the view to attain an accurate estimate of performance. Incremental refreshes are ideal as it filters to a much smaller subset of data, compared to the entire table. However, it can not always be performed in this way if the views underlying query is complex, contains CTEs, temp tables, window functions etc. It needs to be easily filtered by the where clause that identifies a partition.

What to look for in your view query

Code

Window functions such as ROW_NUMBER(), CASTs / CONVERTs are a key thing to consider when looking at performance. If you run the underlying query, the filter is applied first, then the window function. However, if the filter is against the view, the underlying query runs against the entire table, applies the functions then applies the filter in the where clause then any functions in the SELECT statement against the view. Think about if you can move this functionality to the dataset and M code.

CTEs are also important as the WHERE clause is not propagated back through the query to earlier CTEs and queries. Unless a WHERE clause exists in this earlier CTE or it points to a small subset of data, it will run on the entire table – this extends load times and risks this timeout error. A way to get around this without losing the functionality, complexity and accurate data is to use a parameterized stored procedure to generate the same set of data – view the following blog on how to use a stored procedure as a source in Power BI with parameters for incremental refresh – here

Execution Plans

As discussed above, the behaviour of the underlying query of a view is different to a simple SELECT against that view. This is evident in the execution plans. If you get the estimated execution plan of the views underlying query, then the same with a query against that view, you will see that they are different – most likely with the query against the view being much less optimal.

Keep an eye out for a SORT process is the largest cost of the query despite there not being an order by clause or window function over the view. But, it is getting all the data and performing a SORT despite nothing in this query suggesting so. An efficient query will have a SORT operation with a much lower cost and the ‘Index Seek’ instead of the ‘Table Scan’. This is more efficient and quick, coupled with the reduced SORT, this is why it performs so much better than the view query above.

The underlying query is able to apply the WHERE clause much sooner, reducing the number of results significantly before applying any functions. Whereas the view gets all the results, then applies the filter – a much more costly, seemingly unavoidable approach.

 

What Can I Do – I need the logic in place for my dataset?

If the underlying logic is important, as well as CASTs, CONVERTs etc then you will need to switch the source for your PBI  dataset from a view to a stored procedure. This is the best solution for maintaining your functionality and incremental refresh approach, as well as boosting the query performance. View my other blog post on implementing this.

Also…

Look at your Power BI gateway and make sure it is the latest version. Sometimes, an older version throws this error. You will see a message in your Admin Portal if you have an out of date gateway.

Tags: Business Intelligence, Power BI, SQL Server