SSAS Multidimensional Cubes: Finding Actual Partition File Size on Disc

As a SSAS DBA, or even as a SSAS developer who has responsibility for partition file sizes and multidimensional cube performance considerations, knowing the actual file size of partitions on disc is critical.  This is not 'estimated size' that you will get from $system.discover_partition_stat, that is reflected in SSMS (SQL Server Management Studio) properties, or is found in the Visual Studio project source code.  I am talking about actual file size on physical disc.  I recently posted a blog with source code that provides t-SQL to get actual aggregation file size on disc.  With a little updating, you can use the same script to get patition file sizes.  

Note: Partition sizes should be at least 4 GB in size.  Smaller, larger or unbalanced partitions sizes and you are risking increasing query response times and query processing.

I hear there is a blog post showing how to get this information in Excel, but I'm a t-SQL girl and I want to use SSMS  to get my cube metadata.  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_partition_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
*/

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 '%string.data%'
  AND DataFileName NOT LIKE '%fact.data%'

DELETE FROM #DataFileList
WHERE DataFileName NOT LIKE '%.prt\%'
  OR DataFileName LIKE '%.agg.%'
  or DataFileName is NULL

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)

/*
--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))
    
IF @lPrintIt = 1
    SELECT * FROM #FinalOutput WHERE DatabaseID like @i_DatabaseId

--get total partition sizes on disc
select 
     DatabaseID
    ,CubeID
    ,MeasureGroupID
    ,PartitionID
    ,SumOfSizeInKB = SUM(SizeInKB)
    ,SumOfSizeInMB = SUM(SizeInMB) 
    ,SumOfSizeInGB = 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