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
Advertisements