locked
Preserving old data after a data refresh RRS feed

  • Question

  • Hi,

    I have an issue I hope someone can help me with.

    Unlike sales data I have a set of data that isnt transactional, the SQL view simply gets replaced every day. So once I schedule a daily excel services refresh, the data gets updated.

    I need to compare some of the data to what it was the previous day or week. I know we can create daily copies of the original data in SQL but is there any trick I can perform in powerpivot?

    Something that allows me to automatically save off some summary information into a table so that it is one refresh behind.

    Note that I publish these reports to Excel Services which doesnt support VBA.

    Many thanks in advance for any help on this.

    Regards,

    Steve.

    Friday, September 26, 2014 8:34 AM

Answers

  • Hi,

    I'm also comparing the recent result and old result. My resolution is to save "change history" data in SQL, just like

     CustomerID  Attribute   Status   StartTime   EndTime

            1          Amount   $1000    9/4/2014    9/28/2014

            1          Amount   $2000    9/28/2014  9/30/2014

    Then import this table into power pivot. With the help of "Calendar" table as following, we can do further caculation.

      Date

      9/27/2014

      9/28/2014

      9/29/2014

    Amount:=SUMX('Amount_History',IF(CountRows(Filter('CalendarTable', 'CalendarTable'[Date]>='Amount_History'[StartTime] && 'CalendarTable'[Date]<'Amount_History'[EndTime]))>0, 'Amount_History'[Status],0)

    This measure can calculate amount for any selected period - yesterday, previous week and others.

    The the calculation in powerpivot is based on the data in it. No historic data, and no historic calculated result.

    Thx

    Tuesday, September 30, 2014 3:10 AM

All replies

  • Is the data actually deleted from your SQL server, or is the SQL view that you are referencing simply selecting only the current day's values?

    I would talk to your DBA and ask if there's a way you can access older data. 

    What you're asking isn't really possible in Power Pivot. If you were using Tabular you could set up a process add refresh daily, but the equivalent is not available in Power Pivot.

    Friday, September 26, 2014 2:57 PM
  • Appreciate the reply thanks, just wondering if there was some trick I could use that could fill some sort of historic table in Excel that could be linked to powerpivot. 
    Friday, September 26, 2014 3:14 PM
  • If your data volume is small enough ( <1M rows), you can create a linked table in Excel. You would have to manually add the rows every day, though, since a table fed directly by your SQL Server would exhibit the same behavior as your Power Pivot table. Thus you'd have to copy and paste this data to the Excel table daily.

    Similarly, you could, rather than create a table in Power Pivot from a data connection to SQL Server or a linked table, paste the data into Power Pivot and then paste append daily.

    Both of these would be a completely manual process, which it seemed like you were trying to avoid.

    Edit: Also, you'd have to re-upload the workbook daily with these.
    Friday, September 26, 2014 3:33 PM
  • Hi,

    I'm also comparing the recent result and old result. My resolution is to save "change history" data in SQL, just like

     CustomerID  Attribute   Status   StartTime   EndTime

            1          Amount   $1000    9/4/2014    9/28/2014

            1          Amount   $2000    9/28/2014  9/30/2014

    Then import this table into power pivot. With the help of "Calendar" table as following, we can do further caculation.

      Date

      9/27/2014

      9/28/2014

      9/29/2014

    Amount:=SUMX('Amount_History',IF(CountRows(Filter('CalendarTable', 'CalendarTable'[Date]>='Amount_History'[StartTime] && 'CalendarTable'[Date]<'Amount_History'[EndTime]))>0, 'Amount_History'[Status],0)

    This measure can calculate amount for any selected period - yesterday, previous week and others.

    The the calculation in powerpivot is based on the data in it. No historic data, and no historic calculated result.

    Thx

    Tuesday, September 30, 2014 3:10 AM