Performance issues loading data from Excel stored on SharePoint

[Anonymous]

Hi,

I have been loading Excel data stored on SharePoint into PBI Desktop for a couple of months now, and it has been more or less fine although I do feel it is a little slow but I put this down to needing to authenticate every time. (Fact row count is only 500k although ~30 columns plus a few small dimension tables)

However, recently I have added a couple more files to SharePoint and built new queries for them which require either appending to existing queries or joining them to make new queries and this has caused PBI Desktop to slow down significantly and quite often I get a Formula.Firewall error and have to try refreshing the preview again. Often this causes other queries to 'error' (i.e. have the exclaimation mark by their name) and I have to refresh each preview to get the new one to work). But this is very SLOW and really making development almost impossible.

I have tried the suggestions in the below blog post about separating out the excel extract and transformation steps, which does seem to have helped but I have just loaded in another block of fact data (new region) and I am unable to apply the query changes or do a full refresh as it just evaluates for a long time (its been  evaluating for over 20 mins now...)

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

Does anyone know if this is due to loading all this data from SharePoint? Are there known issues and should start I thinking about storing the data in a different manner?

Many thanks,

Tom

[marclelijveld]

Hi @Anonymous

Probably you're throttled by the SharePoint connector. This simply is a very slow connector unfortunatly. 

When I'm working with multiple files on SharePoint, what I normally do is creating 1 table (per file/column structure) which includes all data, and afterwards I create additional tables which are referencing the all containing table. In this case there will only 1 table who's querying the datasource and all others will refer that table. To do this, you can right-click a table an reference, so do not duplicate! 

Will this work for you? 

- Marc

[Anonymous]

Hi Marc, 

Thanks for the quick reply!

The data stored in sharepoint is currently 3 main excel files holding fact data for 3 different regions, as well as some very small dimensional files.

The fact data is broken down as follows:

Region 1 = 100k rows

Region 2 = 250k rows

(Region 3 = 700k rows)

I had previously loaded in 1 and 2 fine, appending them together into one query, but adding the third has caused the load not work.

For each Region, I split out the queries into 1. Load excel sheet, then a new query for 2. perform any transformations to get data into correct format, 3. Append data to main query that is the fact table for the front end to use. Each of these steps is referencing the previous query for each region.

I have not yet tested if the performance is improved by loading from local excel copies, but I suppose that is the next logical step and then if that does fix the issue, I need to think about how we can store the data going forward, as these regional files are updated daily by an offshore team.

Thanks,

Tom

[marclelijveld]

Hi Tom, 

Hmm... The way you created your queries looks good. The excel files are kind of big, but that isn't the issue I guess. However it also depends on the number of columns and datatype you're importing in the query. As long as the files are simple flat files, this still should work, although there are better sources for this amount of data. 

You told there is a query where you do all transformations. What kind of transformations are you doing? What may have a big impact, is repeating steps. The best practice is to group query steps as much as possible. So try change all your data types at one time, rename columns all at one time and so on... I know that this is not always possible, but try to do it as much as possible. 

Like you suggest yourselves, try to run this query refering to local files on your machine. So you can test if the issue is throthling in the SharePoint connector, your query or the amount of data. 

- Marc

[Anonymous · Accepted solution]

Hi @marclelijveld,

Thanks for the helpful tips, naturally you tend you group up the query steps but I hadn't consciously considered that before, there probably is a small amount of tidying up I can do.

As for my problem, I realised I was creating a new table which aggregated the Total Spend for each of the 3 Regions, which of course for the Preview to load would need to go through all ~1m rows of data. Having removed this and moved the requirement to a DAX table this has fixed the issues I was facing more or less. However, having said that, loading from Excel locally was significantly quicker than loading from Excel stored in SharePoint so something to consider going forward.

Many thanks!

Tom