Get SSAS cube last process time

sql excel vba ssas olap-cube
Kyle Weller picture Kyle Weller · Nov 13, 2013 · Viewed 22.3k times · Source

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.

Answer

Kyle Weller picture Kyle Weller · Nov 16, 2013

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.

Related questions

Accessing SQL Database in Excel-VBA

I am copying an VBA code snippet from MSDN that shows me how to grab results from a SQL query into excel sheet (Excel 2007): Sub GetDataFromADO() 'Declare variables' Set objMyConn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = …

Read More
Using Excel VBA to export data to MS Access table

I am currently using following code to export data from worksheet to MS Access database, the code is looping through each row and insert data to MS Access Table. Public Sub TransData() Application.ScreenUpdating = False Application.EnableAnimations = False Application.EnableEvents = …

Read More
Excel VBA - connect to sql with a trusted connection (no uid/pwd)

Is there a way to have Excel connect to Sql Server using the current user's network windows authentication (trusted connection/integrated security)? I don't want to rely on a sql login or have a uid/password in my code.

Read More