• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Updating Data in the Data Model

Chase650

New Member
Here's my scenario. I'm working on a data summary project where I'm taking massive amounts of labor data and combining it into one excel document going back several years to track labor hours by serial number across a company. Going back to 2015 I have about 6million rows of data. What I'm wanting to do is append these different tables that each have around 800,000 rows together in the data model. Here's the catch, Because there is so much data when I add the new data every month and try to refresh it is too much for my computer to handle. Furthermore with all of that data sitting in different excel worksheets the file is extremely sluggish. I'm looking for a way to append all of the tables together and then keep them in the data model and delete them from the worksheets then maintain one worksheet for the current year. I would then update it periodically and have it refresh in the data model then at the end of the year delete that years table and start a new connection with a new year. Is there a way to append tables within the data model so that there aren't issues of maintain the connections in the query?
 
There are many routes you can take with this.

1. Use query on the folder (where each quarter of year data is kept in separate workbook). Use Combine Files feature in PQ to merge all data.

2. Have staging SQL db store all info. Use query to it to bring in necessary data.

3. Instead of using folder or From Workbook, use ODBC connection. Aggregate the data before it's brought into PQ. So you have less data.

Though PQ does not offer incremental load...
In most cases, PQ & PowerPivot will comfortably handle 10mil + rows of data.
 
Back
Top