As many of you already know, in order to figure out which workbooks had succeeded or failed execute in their data refresh, you need only to go the PowerPivot Management Dashboard (Central Administration > General Application Settings > PowerPivot Management Dashboard) in order to see the status of your data refresh as noted in the figure below.

Figure 1: PowerPivot Management Dashboard: Data Refresh Web Parts

 

But how about if you wanted to create a SSRS report of the same (or even more) information so that way you can create alerts and/or have an atom feed of this information?  After all, as an IT Pro you’ll have tens or hundreds of these workbooks.  You really want to have an automated process that notifies you of the status of these workbooks instead of constantly reviewing that information.

 

Well, let’s take advantage of SSRS integrated within our SharePoint Farm to produce this report.  Your data source can be either the ITOps_<Guid> Analysis Services database that is the source for your PowerPivot Management Dashboard or it can be the SQL database (PowerPivotServiceApplication_<Guid>) that is the source of that ITOps_<Guid> cube.  In this case, I’ve chosen the SQL database since I have a multi-server SharePoint Farm and I can just query my dedicated SQL Server database to get this data.  To do this, create a Report Builder 3.0 report that uses the SQL database as its data source.  You’ll notice that there are tables with the DataRefresh schema within that database.  A rather simple query that you can modify for your purposes to query for today and yesterday’s Data Refresh run is noted below.

 

select f.FriendlyName, r.Result, f.RunStartTime, f.RunEndTime, f.[Source], f.[Catalog], f.ConnectionString
  from DataRefresh.RunDetails f
    inner join DataRefresh.RunResults r
      on r.ResultKey = f.ResultKey
where RunEndTime >= dateadd(dd, -1, cast(cast(GETDATE() as varchar(11)) as smalldatetime))
order by
       f.FriendlyName

 

With this query as my source, I created a chart and table to go against this dataset.  As you can see from the figure below, now you have a simple SSRS report that provides you with the day’s refresh events in a chart and sort-able tablix.  You can also get the sample RDL at my Skydrive Location Data Refresh RDL (you will need to replace the $<>$ values to your environment)

 

Figure 2: PowerPivot Data Refresh SSRS Report

 

You will probably want to modify it so it lists the data by day, or put a date parameter, or … anything!  Nevertheless, now that you have a SSRS report you can subscribe to it and/or export it as a data feed within the PowerPivot Gallery.  Or you can place the report in a regular SSRS instance (after all, it’s just a RDL report off a SQL database, not off one of the PowerPivot workbooks) and you can setup alerts or subscriptions.

 

Enjoy!