Forum Discussion
Append Multiple tables - Very slow
Hi Guys,
I have a situation in which I need to Append 48 (tables) from excel file together to create 1 only final table in the end. This is obviously slowing my performance a lot, making the refresh time be up to 50 minutes.
The data is budget for 3 years.
The excel file has multiple tabs one for each city.
Each city has 4 "Squares of data" that I need to get it. (YTD, MTD, Cumulative and Movement)
Then I unpivot each section and append them.
Just thinking, is there any other option to do this?
Thank you
Regards,
Hi Anonymous ,
I don't think there's a specific 'right' way to do this, as it will depend so much on your specific scenario e.g. network speeds, RAM, data quantity etc. but here's a few things to think about that may improve your experience:
1) Make sure all of your files to be appended are all in the same location (folder), then use folder/SharePoint folder options in Get Data. This will perform the transformations and appends automatically based on your preparation of a sample file. I am assuming that all of your fles to be appended are identical in terms of structure and field names etc.
2) Pivot/unpivot operations require the entire table to be stored in memory to perform. Therefore, if this is a transformation step that is done prior to appending, you will be lumping 48 tables in to memory either one after the other, or simultaneously if you have the memory capacity. Try to append your tables first if feasible, then perform pivot/unpivot on the resulting single table. This should hopefully gain you some efficiencies.
3) I presume that this process isn't completed very frequently, maybe daily at most. If so, then I would offload the entire process to a dataflow to be run overnight. This will significatly speed up your Desktop filewhile building/developing your reportsbased on this data.
This list isn't exhaustive, just the top 3 things that I'd do in your scenario.
Pete
1 Reply
Hi Anonymous ,
I don't think there's a specific 'right' way to do this, as it will depend so much on your specific scenario e.g. network speeds, RAM, data quantity etc. but here's a few things to think about that may improve your experience:
1) Make sure all of your files to be appended are all in the same location (folder), then use folder/SharePoint folder options in Get Data. This will perform the transformations and appends automatically based on your preparation of a sample file. I am assuming that all of your fles to be appended are identical in terms of structure and field names etc.
2) Pivot/unpivot operations require the entire table to be stored in memory to perform. Therefore, if this is a transformation step that is done prior to appending, you will be lumping 48 tables in to memory either one after the other, or simultaneously if you have the memory capacity. Try to append your tables first if feasible, then perform pivot/unpivot on the resulting single table. This should hopefully gain you some efficiencies.
3) I presume that this process isn't completed very frequently, maybe daily at most. If so, then I would offload the entire process to a dataflow to be run overnight. This will significatly speed up your Desktop filewhile building/developing your reportsbased on this data.
This list isn't exhaustive, just the top 3 things that I'd do in your scenario.
Pete