locked
Last Refresh Date RRS feed

  • Question

  • Hello

    Is there a(n easy) way to retrieve  the last refresh date per PowerPivot table or connection?

    We currently have about 10 reports which have connections to a common Excel workbook, multiple SQL Server DBs and multiple SSAS Cubes. Each report/workbook has multiple pivottables and a named ranges containing some variance information.  Another workbook is consolidating/collecting the data of the named ranges from the 10 different workbooks by way of VBA. While retrieving the information from the files, pivottables are refreshed first and some file properties are read too.

    For the SQL server connection type we could add a column GETDATE() but for the other connection types it is not always that simple or even possible.

     

          

     


    Eddy N.
    Monday, January 31, 2011 1:59 PM

Answers

  • One workaround that I can think of is developing a layer of WCF data service  (http://msdn.microsoft.com/en-us/data/odata.aspx) which is responsible to connect to different types of data sources and return the result together with connection time back to the client. As the client, PowerPivot can connect to the data service via connection using Other Feeds in the From Other Sources


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    • Marked as answer by Eddy Nijs Friday, February 4, 2011 1:15 PM
    Friday, February 4, 2011 8:26 AM

All replies

  • Eddy,

    You can add a calculated column into your PowerPivot table, saying adding a column named "LastRefreshedTime" with the expression:"=FORMAT(NOW(),"YYYY-MM-DD HH:mm:ss")".

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, February 3, 2011 7:16 AM
  • Charles

    Thank you for your answer. The proposed solution is not quite correct because an expression rather contains the last calculation date/time. If "Refresh All" is executed and a single connection fails no recalculation is done (this is OK). But a successful refresh of a single table will trigger a new calculation for all PowerPivot tables, so expressions will update their results. 

    I'm looking for the last successful connection to the database per connection type. I've been searching in the unzipped xlsx file but couldn't find any valuable data. 

     Best,


    Eddy N.
    Thursday, February 3, 2011 9:09 AM
  • One workaround that I can think of is developing a layer of WCF data service  (http://msdn.microsoft.com/en-us/data/odata.aspx) which is responsible to connect to different types of data sources and return the result together with connection time back to the client. As the client, PowerPivot can connect to the data service via connection using Other Feeds in the From Other Sources


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    • Marked as answer by Eddy Nijs Friday, February 4, 2011 1:15 PM
    Friday, February 4, 2011 8:26 AM
  • What an option can be is the use of DMV's (Dynamic Management View). Check next blog of Chris Webb http://cwebbbi.wordpress.com/2011/02/23/querying-powerpivot-dmvs-from-excel/
    Eddy N.
    Monday, March 7, 2011 2:56 PM