3

In Excel I make an Analysis Services connection to a data cube. I would like to be able to show a user how current the data is by showing them when the last cube processing time occurred. Making an analysis services connection to the cube in SQL Server Management Studio (SSMS), I can right click on the cube and see the property of the last cube processing time exists. I can also create an MDX query as follows to return the last process time:

SELECT LAST_DATA_UPDATE FROM $system.mdschema_cubes

I would like to be able to retrieve this same information in Excel whether it is via VBA or some other method as long as it can be done in Excel without some external tool.

Kyle Weller
  • 2,533
  • 9
  • 35
  • 45

4 Answers4

6

I actually found a way to do it in Excel without having to create any views or new measures. In Excel 2013, PowerPivot allows you to create your own custom MDX queries against a cube. You can open PowerPivot, make the connection to your cube, paste in the MDX query I used in SSMS to return the cube process time,

SELECT LAST_DATA_UPDATE FROM $system.mdschema_cubes

and then export this to a pivot table. I did not need to modify anything outside of Excel. Here is a document with step by step procedures.

Kyle Weller
  • 2,533
  • 9
  • 35
  • 45
  • I think this definitely works, but it requires that the end users have Power Pivot enabled. And if they are on different versions of Excel, requires that you maintain to separate workbooks with this in it (one for Excel 2010 and one for Excel 2013). It sounds like users are connecting directly to SSAS in Excel rather than going through Power Pivot, so this also changes/adds another connection to the workbook. This all may be fine, but I wanted to point this out. – mmarie Nov 16 '13 at 19:49
  • Yes, thank you, those points are definitely important for future readers of the question as well. I only support Excel 2013 anyways and requiring users to enable power pivot is no big deal. Having another connection also is not a problem. So this is the best solution for me since I wanted to avoid changing anything in my cubes themselves. Your answer is also great as well. – Kyle Weller Nov 19 '13 at 04:46
  • This was really helpful for someone who isn't super savvy when it comes to cubes. – Matt Cushing May 05 '20 at 16:57
4

I had the same need on a project, to show cube last processed date/time in Excel. This may be a little hokie but it definitely works. I added a query against my database in my DSV (technically I made a view since all of my source data came from views rather than named queries or tables) that was just 

Select CURRENT_TIMESTAMP as CubeLastRefreshed

I made it a dimension that is related to nothing. Then users can pull it into Excel. You can make a pivot table with just that in it. Or you can write a cube function in Excel to show it at the bottom of the report. It would look something like

=cubemember("Cube","[Cube Process Date].[Cube Last Processed].firstchild")

Just make sure to pay attention to when this dimension gets processed. If you only process certain dimensions or measures on certain days, make sure processing of this dimension is included in the correct places.

mmarie
  • 5,598
  • 1
  • 18
  • 33
0

You could use the CubeInfo procedures from the AS StoredProcedures project on Codeplex. You would have to deploy the assembly that you can download there to your server, and then define measures similar to those described in the WITH clauses of the query at the bottom of the above referenced page.

All the source code of the stored procedures is available at CodePlex as well.

FrankPl
  • 13,205
  • 2
  • 14
  • 40
-2

I use the following VBA to place current date-time into a cell in the spreadsheet whenever any pivot table (which includes those populated from SSAS) is updated:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Range("Documentation!B3").Value = Now()
End Sub

This captures the time when the spreadsheet extracted data from the cube rather than the time the cube was processed, but perhaps may be of some help to you.

MattClarke
  • 1,647
  • 1
  • 11
  • 32
  • 1
    Thanks. As you mentioned, this is going to show the time Excel got the data from the cube. But I specifically need the time that the cube was processed. – Kyle Weller Nov 21 '13 at 20:56