# ####################################################################################
# Author: Vidas Matelis, http://powerpivot-info.com
# Explanation how this script works and latest version of this script: 
#  http://powerpivot-info.com/post/367-q-how-can-i-find-how-much-space-each-table-uses-in-powerpivot
# ###################################################################################
# To run this script you need first to open PowerPivot workbook and go to PowerPivot window.
# This will force Powerivot to create <temp\Vertipaq*> folder with all data in subfolders.
# Then execute this script. 
# This script iterates through these subfolders and reports how much space each table is using.
# Example how to run: 
# PowerShell c:\PS\vertipaq.ps1 -ShowColumnSizes "Yes"
# ###################################################################################

param([string]$ShowColumnSizes = "No"); # Shows column sizes when parameter value is "Yes"

cls;

$tmpFolder = $Env:Temp #"C:\Users\VMatelis\AppData\Local\Temp"
if (-not [System.IO.Directory]::Exists($tmpFolder)) {"Folder does not exists: " + $tmpFolder; break;}

# From temp folder get all subfolders that start with "vertipaq". 
# Note: normally you should see one subfolder per open PowerPivot workbook
#       but you might also see "leftover" vertipaq subfolders, that should be ignored
$folders = (Get-ChildItem -path $tmpFolder -Filter "vertipaq*" | where-object {$_.PSIsContainer} | sort CreationTime -Descending)
if (-not $folders) { # Vertipaq folders not found
  Write-Output ("*** No PowerPivot folders found. Aborting script. Temp folder: '" + $tmpFolder+"'.");
  break;
}

foreach ($subFolders in $folders)  # Go through all subfolders starting with vertipaq*. Each represents separate workbook
{
 # ############## Block to calculate folder size = workbook data size
 [uint64]$size=0;
 Get-ChildItem -path $subFolders.FullName -Recurse | % {$size=$size+$_.Length}; #Get size of folder
 # ##############
 
 # Print Vertipaq folder (= PowerPivot workbook) name, date created and size.
 Write-Output ("Folder : " + $subFolders.Name + ". Created: " + $subFolders.CreationTime + ". DB Size: " + ([int]($size/1024)).ToString("#,###") + " KB")

 # *.db subfolder is where all table info is stored. 
 $dbFolder = Get-ChildItem $subFolders.FullName -Filter "*.db" | Where-Object {$_.PSIsContainer} | Select-Object -First 1; #Get *.db folder (just one)
 if (-not $dbFolder) { Write-warning "*** no DB folder found."; continue;}
 $tblFolders = Get-ChildItem $dbFolder.FullName -Filter "*.dim" | where {$_.Name -like "*.dim"} # get all *.dim folders
 foreach ($table in $tblFolders)
 {
   if (-not $table) {continue;}

   # ############## Block to calculate subfolder size
   [uint64]$size=0;
   Get-ChildItem -path $table.FullName -Recurse | % {$size=$size+$_.Length};
   # ##############

   # Get Guid from file name - between 0 position and first "."
   $tblGuid = $table.name.substring(0,$table.Name.IndexOf(".")); 
   # Find matching XML file. <GUID>.<Version1>.dim matches <GUID>.<Version2>.dim.xml
   $xmlFile = Get-ChildItem $dbFolder.FullName -Filter ($tblGuid + "*.dim.xml") | Select-Object -First 1; # Should return just 1 file
   if (-not $xmlFile) {Write-Warning ("*** Matching table xml file not found. Table: " + $table.FullName); continue;}
   # Reading xml file and find value for: <Load><ObjectDefinition><Dimension><Name>...
   $tblName = "";
   [xml]$file = Get-Content $xmlFile.FullName;
   $tblName = $file.Load.ObjectDefinition.Dimension.Name; 
   
   Write-Output (" Table : " + $tblName.PadRight(30) + ", " + ([int]($size/1024)).ToString("#,###").PadLeft(10) + " KB") 

   if ($ShowColumnSizes -eq "Yes")
   {
    # Deal with columns in each table
    # Column file example: H$2841eb0c-6ea4-4b19-89b8-1a23cc7d3fe7$Product.0.tbl.xml, pattern is *$*$*.tbl.xml
	# Exclude *.Index.<version>.idf files as they are used for relationships
    $ColFiles = Get-ChildItem $table.FullName -Filter "*$*$*.tbl.xml" ;
    foreach ($colFile in $colFiles)
    {
     # Column name is after last "$" before first "."
     $ColName = $colFile.Name.substring($colFile.Name.LastIndexOf("$")+1, $colFile.Name.indexOf(".") - $colFile.Name.LastIndexOf("$")-1)

	 [uint64]$col_size = 0; # Total size of the column. Two file naming patterns, include both
	 # *<TableGuid>*$ColumnName.*
     Get-ChildItem -path $table.FullName -filter ("*"+$tblGuid+"*$"+$ColName+".*") | % {$col_size=$col_size+$_.Length;};
	 # *<TableGuid>.$ColumnName.*
     Get-ChildItem -path $table.FullName -filter ("*"+$tblGuid+"."+$ColName+".*")  | % {$col_size=$col_size+$_.Length;};
	
     if ($ColFile.Name.Substring(0,2) -eq "R$")
	 {
 	  Write-Output("   Relationship: " + $ColName.PadRight(40) + ", " + ([int]($col_size/1024)).ToString("#,###").PadLeft(10) + " KB");
 	 }
 	 else
	 {
	  Write-Output("         Column: " + $ColName.PadRight(40) + ", " + ([int]($col_size/1024)).ToString("#,###").PadLeft(10) + " KB");
	 } 
    } # foreach ($colFile in $colFiles)
    
	# calculate size of extra "helper" files - <Guid>.1.tbl.xml and also file info.*.xml
    [uint64]$tbl_extra_size = 0;
    Get-ChildItem -path $table.FullName -filter ($tblGuid+"*"+".tbl.xml") | % {$tbl_extra_size=$tbl_extra_size+$_.Length};
    Get-ChildItem -path $table.FullName -filter "info*.xml" | % {$tbl_extra_size=$tbl_extra_size+$_.Length};
    Write-Output("   Tbl metadata: ".PadRight(57) + ", " + ([int]($tbl_extra_size/1024)).ToString("#,###").PadLeft(10) + " KB");
   }
 } # foreach ($table in $tblFolders)
 ""
 ""
} # foreach ($subFolders in $folders)