Slow Report Refresh

[Anonymous]

I'm having issues with refresh on a small dataset (1M).

I'm utilizing current year fiscal data to produce year-end projections. The report takes current year spend and commitments from one spreadsheet, expected future expenditures from 3 additional sources (Sharepoint lists), comments from an additional Sharepoint list, cross-department charges from 2 spreadsheets, additional revenue information from a spreadsheet and a department listing from a final spreadsheet.

The dataset has 25 queries against the 5 Excel spreadsheets and 4 Sharepoint Lists. There are queries to download/transform the base data; the remainder are queries referencing (merges/additional transformations) the original source queries. The longest query is one of a source query that comprises 33 steps, the reference queries are primarily <10 steps. I have auto-refresh set up in the Power BI Service, no gateway and using Anonymous credentials for the queries.

I'm seeing refresh times of anywhere from 5-35 minutes in the Power BI Service and 2-15 minutes in Power BI Desktop, both of which seem excessive given the size of the data. The largest datasource is <2000 records of 16 columns. The majority of the raw data is text; there are a handful (<5) date columns, all with the same date and 10>15 currency columns (spread among the various raw data sources).

One item that may be an issue is that I don't have a true star schema. I have several fact tables, all of which are related to a couple of dimension tables (the department list and a dimension table that is created from the cross-department charges fact tables). The fact tables don't have any relationship to each other, but all provide data for the final report. If anyone has suggestions on how to work with multiple fact tables, that would also be helpful.

I realize this isn't nearly enough for anyone to really offer solutions, I'm hoping for some guidance on how to troubleshoot the queries and guidelines on implementing some best practices that I may need to tighten up on (such as the schema).

[v-shex-msft]

Hi @Anonymous,

Is there any advanced operation in your query tables?(e.g combine, append, reference other query, calculate with external query table, custom function)

If this is a case, it will cause additional cost on calculating with these reference queries.(each row will loop calculation with reference table, it will increase memory usage and calculation time)

Maybe you can try to use List.Buffer or table.Buffer to cache these reference query to memory to reduce additional resource spend.

Reference link:

How to Improve Query Reference performance for large tables

Use of Table.Buffer in references

 
Regards,

Xiaoxin Sheng

[Anonymous]

Thanks @Xiaoxin,

I do have a number of merges and appends as well as a number of queries that reference each base query (25 total queries against 9 data sources). I thought about Table.Buffer, but a lot of the reading I've done on that seems to indicate that it's kind of hit or miss.

Is there a good way to determine empirically whether Table.Buffer would help? FWIW, I'm just an intermediate knowledge user at this point (but looking to learn!); relatively simple methods that aren't too time-consuming (reporting is a secondary function for my role) would be a big help.

[v-shex-msft]

HI @Anonymous,

>>FWIW, I'm just an intermediate knowledge user at this point (but looking to learn!); relatively simple methods that aren't too time-consuming (reporting is a secondary function for my role) would be a big help.

Actually, it not such complex to understanding, you only need to use buffer function to package your advance calculation steps into buffer functions.

Please take a look at following blog, it show how to use buffer functions:

Buffer() M Function in Query Editor (Power BI)

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you for the link Xiaoxin...

I'll review it further later on for future reference, but for at least this report, I've generally solved my issue by using DAX calculations to replace the majority of queries. I've still got a few items remaining, but I reduced the most problematic query to about 2/3 of the steps previously used, particularly removing a couple of merges to speed things up. I've reduced the desktop time from 5-15 minutes to 35secs to 1 min (I haven't published this iteration yet to see the impact at the service level). My guess is that even with the remaining items (which are the most difficult items to work with), I'll probably end up with <10 minute maximums at the service (trying to be really conservative).