Forum Discussion
Performance issues loading data from Excel stored on SharePoint
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
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
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