Strategy for Incremental Datasource fetchings in Excel

Question

I am in an scenario with a table that is refresh by a third app every week. I need to keep accumulating all data in Excel, using an ODBC connection to the database.

I am wondering

Approach 1: Is there a way to force Excel to append results for every update (this update would be triggered according to a parameter that indicates week)? I tried to define the table for which the connection loads using a dynamic reference but once is anchored first time, table position is never redefined

Approach 2: Use an ETL to accumulate all weekly results into a staging table and then connect Excel to it in real time. But, I would need a mechanism for caching old data, as I cannot grow exponentially the time Excel opens. Imagine after 10 years, Excel would need to update at opening 10 years fo data before showing it. Is there a way to store already fetched data and increment it at real time (when book is opened) by selecting new data (with a query/filter of something)

Thanks

EDIT: Maybe it's better to ask it that way: What is the optimal strategy for a table that keeps growing and needs to be read in real time by Excel? I just don't want to fetch absolutely all data after some months...

Answer 1

I would use the Power Query Add-In for Excel to tackle this. It can handle appending results from a SQL query to an existing table.

http://office.microsoft.com/en-au/excel-help/append-queries-HA104149760.aspx?CTT=5&origin=HA103993872

If you store the data in the Excel Data Model format (not in an Excel Table), the data is highly compressed - there are reports of people storing 160 million rows in this format (with 64-bit Excel). You can then access the data back into Excel via Pivot Tables and/or Cube Formulas.

Answer 2

I found the selected answer a bit lacking (but a great start :)

Using the latest PowerQuery (starting from Nov2014, you can load to the workbook in addition to loading to the data model)

Here, the Source data is just a table in Excel but you can pull from anywhere including a SQL DB.

Step one. Create an initial load. The Query is named FROM (The name is inherited from the table name: FROM)

let
    //Load from Table FROM
    Source = Excel.CurrentWorkbook(){[Name="FROM"]}[Content],
    AddCustom = Table.AddColumn(Source, "Load Date", each DateTimeZone.UtcNow())
in
    AddCustom

Load to the data model AND to the worksheet. The worksheet table is called FROM_2 by default.

Update the FROM query like so:

let
    //Load from Table FROM
    Source = Excel.CurrentWorkbook(){[Name="FROM"]}[Content],
    AddCustom = Table.AddColumn(Source, "Load Date", each DateTimeZone.UtcNow()),
    //Load from Table FROM_2  (this is just a copy of what's in the DataModel)
    Custom1 = Excel.CurrentWorkbook(){[Name="FROM_2"]}[Content],
    ChangedType = Table.TransformColumnTypes(Custom1 ,{{"Load Date", type datetimezone}}),
    //Append the two Loads.  The New data and the existing loads in the DataModel
    Append = Table.Combine({ChangedType,AddCustom})
in
    Append

So, now you can just, for example, add a Pivot table pointing to the DataModel or to the FROM_2 table. If you need another table with the data in the DataModel where you can add other colums you can just create another Query pointing to the FROM_2 table.

Since you're using a table in the worksheet as an intermediate step, I doubt this will scale for a lot of data.