Using Powershell to view Partition Slice Information in Excel

Challenge

In a blog post written by Chris Webb (b|t), years ago, Chris displays how to query the ASSP.DiscoverPartitionSlices(…). I stumbled upon the post while investigating solutions to problems with partition slicing. But since there were about 20 cubes, each containing several measure groups, I decided to let Powershell do the bulk of the work.

Data collection

I created a script, that loops all databases on a given server, loops all measure groups in every cube and records the result from calling ASSP.DiscoverPartitionSlices(…). The result is then dumped in a .CSV file at a location of your choosing, and the data is ready to be explored.

#Import Assemblies            
[System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.AnalysisServices' ) | out-null;            
[System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.AnalysisServices.AdomdClient' ) | out-null;            
[System.Reflection.Assembly]::LoadWithPartialName( 'System.IO' ) | out-null;            
[System.Reflection.Assembly]::LoadWithPartialName( 'System.Text' ) | out-null;            

#Instantiate and connect            
$Server = New-Object('Microsoft.AnalysisServices.Server' );            
$StringBuilder = New-Object( 'System.Text.StringBuilder' );            
$DataSet = New-Object( "System.Data.DataSet" );            
$Con = New-Object('Microsoft.AnalysisServices.AdomdClient.AdomdConnection')            

#Set the server to explore            
$ServerName = ""            

#Create Connection            
$Server.Connect($ServerName);            
$Con.ConnectionString = [string]::Format("Provider=MSOLAP.4;Data Source={0};Initial Catalog={1};Connect Timeout=0", $ServerName, $Database.Name);            
$Con.Open();            

#Loop the databases            
foreach( $Database in $Server.Databases ) {                
    #Loop the cubes                
    foreach( $Cube in $Database.Cubes ) {            
        #Loop the measure groups            
        foreach( $MeasureGroup in $Cube.MeasureGroups ) {            
            #Only query processed MeasureGroups            
            if( $MeasureGroup.State = "Processed" ) {                             
                #Create Adapter            
                $Adapter = New-Object( 'Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter' );            
                #Create Command            
                $Cmd = New-Object('Microsoft.AnalysisServices.AdomdClient.AdomdCommand' );            
                #Set Command            
                $Cmd.Connection = $Con;            
                $Cmd.CommandText = "call assp.DiscoverPartitionSlices('{0}', '{1}') " -f $Cube.Name, $MeasureGroup.Name                        
                #Set Adapter            
                $Adapter.SelectCommand = $Cmd;            
                $DataTable = New-Object( "System.Data.DataTable" );            
                #Fill data table (kill any output, can be verbose)                        
                $Adapter.Fill( $DataTable ) | out-null;            
                #Loop the rows of the result            
                foreach ($Row in $DataTable.Rows)            
                {            
                    $line = "";            
                    #Loop each "cell" in the current row            
                    foreach( $Cell in $Row.ItemArray ) {            
                        $line += "," + $Cell;            
                    }            
                    #Remove pre-fixed comma            
                    $line = $line.SubString( 1, $line.Length - 1 );                                
                    #Append line to collection            
                    $StringBuilder.AppendLine($line);                            
                }                           
            }            
        }                    
    }                
}            
#Write content of the string collection to a file            
[System.IO.File]::WriteAllText( "<INSERT PATH HERE>", $StringBuilder.ToString());

Data preparation

As in Chris’ blog post, I’d like for the data to be shown in the preffered data visualization tool: Excel. But there are no range-bar chart types in Excel, but here’s a trick to make it look like one. Once the script has produced the data file, we need to open it with Excel. In here, we can manipulate the output, to better suit our needs. Of course, this could have been done programatically, but this isn’t the point of this excercise, so some manual labour is involved. First we need to convert the comma separated text lines, into columns. Excel has a feature for this on the Data-ribbon. Here you’ll find a button labeled “Text to Columns”.

When the column containing the text lines is highlighted, and we press this button, the data will automatically become rows and columns. Once this is done we can then load the data into PowerPivot, by pressing the “Create Linked Table” in the PowerPivot-ribbon.

In PowerPivot, we can add a column that calculates the difference between the min count and the max count using the following formula (replace table name accordingly):

=Table[MAX]-Table[MIN]+1

The +1 is to ensure, that when min == max, there is actually something to see on the chart, not just a blank (which would indicated something entirely different).
NB! Hence the results of the DataIds in the PowerPivot cannot be directly used to track dimension members!

 Ready, Set, Browse

Using these results, you are able to very quickly browse and identify if there is any anomalies to the distribution, that you would be able to take advantage of. Following this guide on how to create Range-Bar charts in Excel, we are able to display the data in a very illustrative an intuit way.
I have added some examples, from some of the attributes.

That being said, it is important to remember, that because you have no explicit control over what DataIds are assigned, you could end up causing a havoc. DataIds are assigned in no particullar order, so the range of years {2010, 2011, 2012, 2013} could have the corresponding DataIds {10, 13, 11, 12}

I have attached the Powershell script : Partition Slice View Powershell Script