SSAS Multidimensional Cubes: Finding Actual Aggregation Size on Disc

I recently posted a blog with source code that provides t-SQL to get actual partition file size on disc.  With a little updating, you can use the same script to get aggregation file sizes.  

​Why do we care?
1. Aggregations are a shortcut to acquiring a SUM() or a COUNT() of a measure.  Think of aggregations as pre-calculated totals.

2.  Correctly designed aggregations have a direct and SIGNIFICANT influence on end-user query performance.

3.  Aggregations are not automatic.  You must create them in the cube on the Aggregations tab, and as a general rule of thumb, aggregations should not exceed 30% of partition size.  

4. Monitoring partition and aggregation file sizes is an on-going effort and it is often the place where multidimensional cubes "fall down" -- no one is paying attention.  Developers move on to new projects, and even large companies often do not have dedicated SSAS DBAs.  However, this can be an easy win if given an Outlook calendar reminder and a little effort.

Reminder: Each partition can only have one (1) active aggregation design; however, because a measure group can have > 1 partition, measure groups can have > 1 aggregation design.

The output of the t-SQL statement I am providing is what you see here, but with your cube and partition names:

dba_get_ssas_aggregation_file_sizes.sql
File Size:	8 kb
File Type:	sql

Download File

---

 If you are unable to download the above t-SQL file, I am pasting the source code here, but my blog service provider doesn't always format it as nicely as SSMS.

​Enjoy!

/*
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 '%<dir>%'

                --delete all informational messages
                DELETE #tempFilePaths WHERE Files LIKE ' %'

                --delete the null values
                DELETE #tempFilePaths WHERE Files IS NULL

                --get rid of dateinfo
                UPDATE #tempFilePaths SET files =RIGHT(files,(LEN(files)-20))

                --get rid of leading spaces
                UPDATE #tempFilePaths SET files =LTRIM(files)

                --split data into size and filename
                ----------------------------------------------------------
                -- Clear the table
                DELETE FROM #tempFileInformation;

                -- Store the FileName & Size
                INSERT INTO #tempFileInformation
                SELECT  
                        RIGHT(files,LEN(files) -PATINDEX('% %',files)) AS FilePath,
                        LEFT(files,PATINDEX('% %',files)) AS FileSize
                FROM    #tempFilePaths

                --------------------------------
                --  Remove the commas
                UPDATE  #tempFileInformation
                SET     FileSize = REPLACE(FileSize, ',','')

                --------------------------------------------------------------
                -- Store the results in the output table
                --------------------------------------------------------------

                INSERT INTO #FinalOutput (FullPathAndFile, NameOfFile, SizeInMB, SizeInKB)
                SELECT  
                        @CurrentDirectory,
                        FilePath,
                        CAST(CAST(FileSize AS DECIMAL(13,2))/ @1MB AS DECIMAL(13,2)),
                        CAST(CAST(FileSize AS DECIMAL(13,2))/ @1KB AS DECIMAL(13,2))
                FROM    #tempFileInformation

            --------------------------------------------------------------------------------------------

            Set @lcnt = @lcnt -1
           END

    END

ALTER TABLE #FinalOutput
ADD
     DatabaseID varchar(500)
    ,CubeID varchar(500)
    ,MeasureGroupID varchar(500)
    ,PartitionId varchar(500)
    ,AggregationID varchar(500)

/*
--Run the following script for trouble shooting undesired output
SELECT *
    ,(CHARINDEX('.det\',FullPathAndFile,1)) as EndHere
    ,(CHARINDEX('.cub\',FullPathAndFile,1)) as StartHere
    ,(CHARINDEX('.prt\',FullPathAndFile,1) + 4) - (CHARINDEX('.det\',FullPathAndFile,1) + 5)
    ,SUBSTRING(FullPathAndFile,CHARINDEX('.det\',FullPathAndFile,1) + 5,25)
    ,SUBSTRING(FullPathAndFile,CHARINDEX('.det\',FullPathAndFile,1) + 5,(CHARINDEX('.prt\',FullPathAndFile,1) + 4) - (CHARINDEX('.det\',FullPathAndFile,1) + 5))
FROM #FinalOutput
*/

UPDATE #FinalOutput
SET  DatabaseID = SUBSTRING(FullPathAndFile,LEN(@i_DataFileLocation) + 1,CHARINDEX('.db\',FullPathAndFile,1) - LEN(@i_DataFileLocation) + 2)
    ,CubeID = SUBSTRING(FullPathAndFile,CHARINDEX('.db\',FullPathAndFile,1) + 4,CHARINDEX('.cub\',FullPathAndFile,1) - CHARINDEX('.db\',FullPathAndFile,1))
    ,MeasureGroupID = SUBSTRING(FullPathAndFile,CHARINDEX('.cub\',FullPathAndFile,1) + 5,CHARINDEX('.det\',FullPathAndFile,1) - CHARINDEX('cub\',FullPathAndFile,1))
    ,PartitionID = SUBSTRING(FullPathAndFile,CHARINDEX('.det\',FullPathAndFile,1) + 5,(CHARINDEX('.prt\',FullPathAndFile,1) + 4) - (CHARINDEX('.det\',FullPathAndFile,1) + 5))
    ,AggregationID = SUBSTRING(FullPathAndFile,CHARINDEX('.prt\',FullPathAndFile,1) + 5,250)
    
IF @lPrintIt = 1
    SELECT * FROM #FinalOutput WHERE DatabaseID like @i_DatabaseId

--get total partition sizes on disc
select 
     DatabaseID
    ,CubeID
    ,MeasureGroupID
    ,PartitionID
    ,SumOfAggSizeInKB = SUM(SizeInKB)
    ,SumOfAggSizeInMB = SUM(SizeInMB) 
    ,SumOfAggSizeInGB = SUM(SizeInMB) / 1000
FROM #FinalOutput
WHERE DatabaseID like @i_DatabaseId AND CubeID like @i_CubeId
GROUP BY DatabaseID, CubeID, MeasureGroupID, PartitionID
ORDER BY DatabaseID, CubeID, MeasureGroupID, PartitionID

return