SSAS Partition Processing with Biml

Why Process Only Recent Partitions

When the cube is too large to process in full inside the maintenance window, the standard answer is to process only the partitions that have changed. The pattern picks the most recent partitions per measure group based on the LastProcessed date. As a new partition is added, the partition with the oldest processed date drops off the working set automatically. The trick is to avoid hand-coding the partition list. A Biml script that walks the SSAS database through AMO can produce the entire AnalysisServicesProcessing task from a single setting that says how many recent partitions to include.

The Logic in Pseudocode

The shape of the algorithm is small.

server <- connect to SSAS server
database <- connect to a database on server
foreach (cube in database)
{
  foreach (measure group in cube)
  {
    foreach (partition in measure group)
      get the last n processed partitions
  }
}

Note that PartitionId and PartitionName may differ. The script that emits PartitionProcessingConfiguration uses the ID, while the generated package shows the friendly name.

A Helper Function

The Partitions function returns a list of PartitionInfo records sorted by LastProcessed in descending order.

public static List<PartitionInfo> Partitions(string ServerName, string DatabaseName, string CubeId, string MeasureGroupId)
{
  var list = new List<PartitionInfo>();
  Server amoServer = new Server();
  amoServer.Connect(ServerName);
  Database amoDb = amoServer.Databases[DatabaseName];
  Cube cube = amoDb.Cubes[CubeId];
  MeasureGroup mg = cube.MeasureGroups[MeasureGroupId];
  foreach (Partition p in mg.Partitions)
    list.Add(new PartitionInfo(p.Name, p.ID, p.LastProcessed));
  amoServer.Disconnect();
  amoServer.Dispose();
  list.Sort((y, x) => x.ProcessDate.CompareTo(y.ProcessDate));
  return list;
}

public struct PartitionInfo
{
  public readonly string PartitionName;
  public readonly string PartitionId;
  public readonly DateTime ProcessDate;
  public PartitionInfo(string name, string id, DateTime processed)
  {
    PartitionName = name;
    PartitionId = id;
    ProcessDate = processed;
  }
}

The Full Biml Script

The script declares the AMO assembly, sets the server, database, processing type, and the count of recent partitions to include. The AnalysisServicesProcessing task carries one PartitionProcessingConfiguration per recent partition per measure group.

<#@ assembly name="C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.AnalysisServices.dll" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="Microsoft.AnalysisServices" #>
<#@ template language="C#" #>

<#
  String olapServer = @".\OlapInstance";
  String olapDatabase = "RetailMart";
  String processType = "ProcessData";
  int recentCount = 2;
#>

<#+
public static List<PartitionInfo> Partitions(string ServerName, string DatabaseName, string CubeId, string MeasureGroupId)
{
  var list = new List<PartitionInfo>();
  Server amoServer = new Server();
  amoServer.Connect(ServerName);
  Database amoDb = amoServer.Databases[DatabaseName];
  Cube cube = amoDb.Cubes[CubeId];
  MeasureGroup mg = cube.MeasureGroups[MeasureGroupId];
  foreach (Partition p in mg.Partitions)
    list.Add(new PartitionInfo(p.Name, p.ID, p.LastProcessed));
  amoServer.Disconnect();
  amoServer.Dispose();
  list.Sort((y, x) => x.ProcessDate.CompareTo(y.ProcessDate));
  return list;
}

public struct PartitionInfo
{
  public readonly string PartitionName;
  public readonly string PartitionId;
  public readonly DateTime ProcessDate;
  public PartitionInfo(string name, string id, DateTime processed)
  {
    PartitionName = name;
    PartitionId = id;
    ProcessDate = processed;
  }
}
#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <AnalysisServicesConnection Name="olap" ConnectionString="Data Source=<#= olapServer #>;PROVIDER=MSOLAP;Impersonation Level=Impersonate;" Server="<#= olapServer #>" />
  </Connections>
  <Packages>
    <Package Name="ProcessOlapPartitions" ConstraintMode="Linear">
      <Tasks>
        <AnalysisServicesProcessing Name="Process Partitions" ConnectionName="olap">
          <ProcessingConfigurations>
            <#
              Server amoServer = new Server();
              amoServer.Connect(olapServer);
              Database amoDb = amoServer.Databases[olapDatabase];
              foreach (Cube cube in amoDb.Cubes) {
                foreach (MeasureGroup mg in cube.MeasureGroups) {
                  List<PartitionInfo> recent = Partitions(olapServer, olapDatabase, cube.ID, mg.ID);
                  for (int i = 0; i < recentCount && i < recent.Count; i++) { #>
              <PartitionProcessingConfiguration DatabaseId="<#= amoDb.ID #>" CubeId="<#= cube.ID #>" MeasureGroupId="<#= mg.ID #>" PartitionId="<#= recent[i].PartitionId #>" ProcessingOption="<#= processType #>" />
            <# }
                }
              } #>
          </ProcessingConfigurations>
        </AnalysisServicesProcessing>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Wrap-Up

Pointing the script at a different SSAS database produces a different processing package without further code changes. Adjusting recentCount controls how many partitions are processed each run. The same AMO walk extends to dimension processing and full database processing patterns.