/*
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

