Doing reporting on the processing times of SSAS cubes is actually surprisingly tedious so I figured I’d write a script for collecting this data and share it with you guys. Hopefully you’ll save some time:

SET NOCOUNT ON

DECLARE @SQL nvarchar(2000)
DECLARE @CubeName varchar(200)
DECLARE @ConnString varchar(2000) 
DECLARE @ConnString_part1 varchar(2000) = 'Data Source=servername\tabular;Initial Catalog='
DECLARE @ConnString_part2 varchar(2000) = ';User ID=domain\username;Password=xyzsomepasswordxyz'

DECLARE @Cubenames table (
	ID INT IDENTITY(1, 1),
	Cubename varchar(200)
)
DECLARE @Processingtimes table (
	ID INT IDENTITY(1, 1),
	Cubename varchar(200),
	Last_data_update datetime
)

--> Create a table with all the cube names. Connect to any of the cubes on the server for this query:
SET @ConnString = @ConnString_part1 + 'MySalesCube' + @ConnString_part2
SET @SQL = 
	'SELECT a.*  
	FROM OPENROWSET(''MSOLAP'', ''' + @ConnString + ''',
		''SELECT [CATALOG_NAME]
		FROM $system.dbschema_catalogs'') AS a;'
INSERT INTO @Cubenames (Cubename)
EXEC sp_executesql @SQL

--> Check which cubes that are collected
--> SELECT * FROM @Cubenames

--> Then iterate through each cube, connect to it and find the LAST_DATA_UPDATE for each of them
DECLARE cur CURSOR FOR
SELECT Cubename FROM @Cubenames

OPEN cur
FETCH NEXT FROM cur INTO @CubeName

WHILE (@@FETCH_STATUS = 0)
	BEGIN
		SET @ConnString = @ConnString_part1 + @CubeName + @ConnString_part2
		
		SET @SQL = 
			'SELECT Cubename = ''' + @Cubename + ''', a.*  
			FROM OPENROWSET(''MSOLAP'', ''' + @ConnString + ''',
				''SELECT TOP 1 [LAST_DATA_UPDATE]
				FROM $System.MDSCHEMA_CUBES
				ORDER BY [LAST_DATA_UPDATE] DESC'') AS a;'
		INSERT INTO @Processingtimes (Cubename, Last_data_update)
		EXEC sp_executesql @SQL


		FETCH NEXT FROM cur INTO @CubeName
	END

CLOSE cur
DEALLOCATE cur

SELECT * FROM @Processingtimes