/* Script written by Delora J Bradish, www.delorabradish.com Script snippet taken from http://stackoverflow.com/questions/7952406/get-each-file-size-inside-a-folder-using-sql as indicated below To use: 1. Change the @i_DataFileLocation to the folder location of your SSAS MultiD cube data If you don't know where this is, look under [Your SSAS Instance] --> Properties --> General --> DataDir 2. Change the @i_DatabaseId to be your database Id. This is NOT the database name. Find you database Id by looking under [Your SSAS Instance] --> [Your Deployed Database Name] --> Properties --> Database --> ID 3. Change the @i_CubeId to be your cube Id. This is NOT the cube name. Find your cube Id by looking under [Your SSAS Instance] --> [Your SSAS Database Name] --> [Your Cube Name] --> Properties --> General -> ID 4. Set @lPrintIt to one (1) to see the detail data extracted behind the final output 4. Make sure xp_cmdshell is enabled on your SQL Server Database instance. You will need to be a sysadmin on your DB instance to run this t-SQL. -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO 5. You will need to have read access to the physical drives where your SSAS data is stored. Use Windows File Explorer and make sure your AD account can navigate to and see *.db, *.cub, *.det, and *.prt files. Note: If you receive blank output from these SELECT statements, chances are the values entered in the input variables are incorrect. If you do not recognize the PartitionID values, you may want to drop and recreate your partitions so that their IDs match their names Remember that SSAS only allows one (1) active aggregation design for each partition. */ DECLARE @i_DataFileLocation varchar(max) = 'C:\PW_DEV\SQL Server\MSSQL\OLAP\' , @i_DatabaseId varchar(500) = '%AW MultiD%' , @i_CubeId varchar(100) = '%Adventure Works%' DECLARE @lCnt int, @lComm varchar(1000), @lResultString varchar(max), @lPrintIt int, @lSuccess int, @CurrentDirectory varchar(1000), @1MB DECIMAL, @1KB DECIMAL SELECT @lPrintIt = 0, @lSuccess = 0, @1MB = 1024 * 1024, @1KB = 1024 --drop temp tables if they already exist IF OBJECT_ID(N'tempdb..#DataFileList') IS NOT NULL DROP table #DataFileList IF OBJECT_ID(N'tempdb..#tempFilePaths') IS NOT NULL DROP TABLE #tempFilePaths IF OBJECT_ID(N'tempdb..#tempFileInformation') IS NOT NULL DROP TABLE #tempFileInformation IF OBJECT_ID(N'tempdb..#FinalOutput') IS NOT NULL DROP TABLE #FinalOutput -- Create the temp table that will hold the data file names CREATE TABLE #DataFileList(DataFileName varchar(max)) IF @@error <> 0 SELECT @lResultString = '1.a Unable to create #DataFileList' CREATE TABLE #tempFilePaths (Files VARCHAR(500)) IF @@error <> 0 SELECT @lResultString = '1.a Unable to create #tempFilePaths' CREATE TABLE #tempFileInformation (FilePath VARCHAR(500), FileSize VARCHAR(100)) IF @@error <> 0 SELECT @lResultString = '1.a Unable to create #tempFileInformation' CREATE TABLE #FinalOutput (FullPathAndFile varchar(400), NameOfFile VARCHAR(400), SizeInMB DECIMAL(13,2), SizeInKB DECIMAL(13,2)) IF @@error <> 0 SELECT @lResultString = '1.a Unable to create #FinalOutput' --get the list of data files SELECT @lComm = 'dir "' + @i_DataFileLocation + '*.data*" /b /s' INSERT #DataFileList EXEC master..xp_cmdshell @lComm --Clean up the list DELETE FROM #DataFileList WHERE DataFileName NOT LIKE '%.agg%' IF @lPrintIt = 1 SELECT * FROM #DataFileList --add an identity seed for remaining rows to use in WHILE loop ALTER TABLE #DataFileList ADD RowNum int Identity(1,1) --loop through DataFileName values and get file size -- from http://stackoverflow.com/questions/7952406/get-each-file-size-inside-a-folder-using-sql SET @lcnt = (select count(*) from #DataFileList) WHILE @lcnt <> 0 BEGIN BEGIN DECLARE @filesize INT SET @CurrentDirectory = (SELECT DataFileName FROM #DataFileList WHERE RowNum = @lcnt) SET @lcomm = 'dir "' + @CurrentDirectory +'"' ------------------------------------------------------------------------------------------ -- Clear the table DELETE FROM #tempFilePaths INSERT INTO #tempFilePaths EXEC MASTER..XP_CMDSHELL @lcomm --delete all directories DELETE #tempFilePaths WHERE Files LIKE '%