Danny Quinn
.NET & SQL Server

In an earlier post I demonstrated a method I use to update development SSAS databases using powershell.  While this is okay for development servers it doesn’t really work with production boxes containing millions of rows of data.

Generally when I refresh a cube, the only data that will have changed relates to the most recent month or couple of months and so it doesn’t really make a lot of sense to perform a full refresh each day on every measure group.

Before you can apply different processing methods to an individual measure group, you need to partition the measure group.  In my scenario it made sense to partition each measure group into calendar quarters and this meant that on a daily basis I could perform a full update on the partitions containing the current and previous quarter data and default processing to the remaining partitions.  I also decided that once a week I would perform a full update to all the partitions.

Read more on creating partitions and slices in measure groups.

Okay, so I have a measure group named Sales.   I have partitioned the data into calendar quarters (by the Sale Date attribute) and added slice information to each partition.  For example the slice for the current quarter looks like [Sale Date].[Quarter Key].&[201104].

The first thing my Powershell script needs to do is determine the names of the two quarters that will be processed fully.

# Create any array to hold the quarter strings
$quarters = "","";

# Determine which quarter we are currently in.
[int] $q = ((get-date).Month+1)/3);

# Create the strings holding the quarter information.
$quarters[0] = "&["+(get-date).Year.ToString()+$q.ToString().PadLeft(2,'0')+"]";

# If current quarter is 1 then we also need Q4 of last year.
if ($q -eq 1)
{
    $quarters[1] = "&["+((get-date).Year-1).ToString()+"04]";
}
else
{
    $quarters[1] = "&["+((get-date).Year).ToString()+
        ($q-1).ToString().PadLeft(2,'0')+"]";
}

If you test this script $quarters[1] and [2] should contain &[201104] and &[201103].  Any partition that ends with either of these strings will have Full processing applied.  I chose to do it this way as I have several measure groups all partitioned into calendar quarters but the slice attribute is different on each.  However all the slices end with the string &[YYYYQQ].

The next part of the script connects to the database and processes the dimensions.

# Connect to SSAS database
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices");

$server = new-object Microsoft.AnalysisServices.Server;
$server.connect("MY_SSAS_SERVER");

$database = $server.databases["MY_SSAS_DATABASE"];

# Process the dimensions - if you perform a full update on a dimension
# then you have to perform full updates on related measure groups.
# I check the day of the week and only perform a full update on Saturdays

$database.dimensions | foreach-object
{
    if ((get-date).DayOfWeek -eq 'Saturday')
    {
        $_.Process("ProcessFull");
    }
else
    {
        $_.Process("Process Default");
    }
}

Finally process each measure group.  If the day is Saturday then just perform a full update against each partition (all measure groups have at least one partition).  If is not Saturday then look at each partition and if the slice property is not null and ends with either of a values in the $quarters[] array, apply a ProcessFull otherwise ProcessDefault.

# Iterate the cubes
$database.cubes | foreach-object
{
    # Iterate each measure group
    $_.MeasureGroups | foreach-object
    {
        $_.Partitions | foreach-object
        {
            if ((get-date).DayOfWeek -eq 'Saturday')
            {
                $_.Process("ProcessFull");
            }
            else
            {
                if ($_.Slice -ne $Null -and ($_.Slice.EndsWith($quarters[0])
                        -or ($_.Slice.EndsWith($quarters[1])))
                {
                    $_.Process("ProcessFull");
                }
                else
                {
                    $_.Process("ProcessDefault");
                }
            }
        }
    }
}