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:
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
1 Comment
Andy Wright
10/22/2018 04:17:28 am
Hi Delora,
Reply
Leave a Reply. |
Microsoft Data & AI | SQL Server Analysis Services |