Forum Discussion
Loading Sharepoint Data more efficiently (long refreshes...)
There have been similar forums I know but I'm looking for specific advice on improving efficiency.
Currently I have a report with many separate tables all loading from different sharepoint files - .xlsx files to be precise. On each of these files I choose a 'Master' table and load that into the report + append to one Master table. The files are all from the same sharepoint source so the idea has come to me about creating a basic 'Source' query and reference all the tables off that same source query. But before I invest time doing that can anyone actually explain if and why that would actually speed up my refreshes?
To provide context, a refresh often takes over 10mins at the moment which is obviously not ideal. Keen to hear any other ideas.
Hi
The main idead behind what you describe is that you only need to get the data from sharepoint once if you only have one source and all the tables are being referenced. On the other hand, if all the tables have a conection with sharepoint (whitout a reference table) you are retrieving all the same data from sharepoint as many times as the number of tables you have. You can do something like this:
Create a files table whit a sharepoint connection whitout any transformation (I pass the sharepoint URL as a parameter)
Then click on the files table an unmark the "Enable Load" option, so that you are not actualy loading this table in the model (This will be your reference table)
Than just reference this table in all the new tables you want to get and do your transformations there.
Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂
3 Replies
Hi
The main idead behind what you describe is that you only need to get the data from sharepoint once if you only have one source and all the tables are being referenced. On the other hand, if all the tables have a conection with sharepoint (whitout a reference table) you are retrieving all the same data from sharepoint as many times as the number of tables you have. You can do something like this:
Create a files table whit a sharepoint connection whitout any transformation (I pass the sharepoint URL as a parameter)
Then click on the files table an unmark the "Enable Load" option, so that you are not actualy loading this table in the model (This will be your reference table)
Than just reference this table in all the new tables you want to get and do your transformations there.
Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂