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 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
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