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”.

Data Ribbon

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.

Powershell 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.

DataId Distribution Example

DataId Distribution ExampleDataId Distribution ExampleDataId Distribution ExampleDataId Distribution Example

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

Loading

Using Powershell to create Local Users from AW Customers

As a part of my upcomming presentation at SqlSaturday Cambridge, I will probably be needing a lot of users to demo some aspects of security in Analysis Services. Therefore I have created a Powershell script, my first actually, to generate a bunch of users. The first source for a lot of users that came to mind was the company AD, but that probably wouldn’t be given the green light, if I asked. So what to do? Adventure Works to the rescue!

The Customer dimension in the Adventure Works database has roughly 18.000 entries. This would make a nice pile of users for a demo. So, connecting to the database, selecting the distinct customers and just adding them as local users can actually be done rather quickly through Powershell. The run-time is just around 10 minutes, to create or delete.

The script looks like this:

PowerShell_Script1

Just to take a peek at what this script generates, here is a screendump:

Users

I have attached both a create and a delete script: PS_Create Local Users From AW

As this is my first script, I’d be happy to hear if there is a better way of doing this!?

Loading