Skip to main content

SSAS Dimension Processing with Biml

Why Process Dimensions Separately​

When the cube cannot be fully processed inside the maintenance window, the standard pattern is to process dimensions as ProcessUpdate first and then process only the most recently changed fact partitions. ProcessUpdate keeps existing fact data in place. Hand-coding the dimension list across every cube is the kind of work that benefits from generation. A small Biml script that reads the SSAS database through AMO can emit the dimension processing task for any database, with parallel processing turned on.

A Dimension Lookup Helper​

Dimensions are usually shared across cubes, so the database is the right place to enumerate them. The DimensionKeys helper returns a dictionary of dimension IDs to display names. It needs a reference to the AMO assembly.

using System.Collections.Generic;
using Microsoft.AnalysisServices;

static Dictionary<string, string> DimensionKeys(string Server, string Database)
{
  Dictionary<string, string> result = new Dictionary<string, string>();
  Server amoServer = new Server();
  amoServer.Connect(Server);
  Database amoDb = amoServer.Databases[Database];
  foreach (Dimension dim in amoDb.Dimensions)
    result.Add(dim.ID, dim.Name);
  amoServer.Disconnect();
  amoServer.Dispose();
  return result;
}

A Single AnalysisServicesProcessing Task​

The first pattern places one AnalysisServicesProcessing task in the package and adds a DimensionProcessingConfiguration child for every dimension in the database. The task itself can request parallel execution.

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

<#
  String olapServer = @".\OlapInstance";
  String olapDatabase = "RetailMart";
  String processType = "ProcessUpdate";
#>

<#+
Dictionary<string, string> DimensionKeys(string Server, string Database)
{
  Dictionary<string, string> result = new Dictionary<string, string>();
  Server amoServer = new Server();
  amoServer.Connect(Server);
  Database amoDb = amoServer.Databases[Database];
  foreach (Dimension dim in amoDb.Dimensions)
    result.Add(dim.ID, dim.Name);
  amoServer.Disconnect();
  amoServer.Dispose();
  return result;
}
#>

<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="ProcessOlapDimensions" ConstraintMode="Linear">
      <Tasks>
        <AnalysisServicesProcessing Name="ProcessDimensions" ConnectionName="olap">
          <ProcessingConfigurations>
            <#
              Dictionary<string, string> dims = DimensionKeys("Data Source=" + olapServer, olapDatabase);
              foreach (string dimId in dims.Keys) { #>
              <DimensionProcessingConfiguration DatabaseId="<#= olapDatabase #>" ProcessingOption="<#= processType #>" DimensionId="<#= dimId #>" />
            <# } #>
          </ProcessingConfigurations>
        </AnalysisServicesProcessing>
      </Tasks>
    </Package>
  </Packages>
</Biml>

The important properties on each DimensionProcessingConfiguration are the dimension ID (not its display name) and the processing option. Dimension ID and dimension name often differ.

One Task per Dimension Inside a Parallel Container​

The second pattern creates a separate AnalysisServicesProcessing task for each dimension and wraps them all in a Container with parallel constraints. This makes individual dimension processing visible in the SSIS designer, and parallelism is governed by the container constraint mode.

<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="ProcessOlapDimensions" ConstraintMode="Linear">
      <Tasks>
        <Container Name="Process Dimensions" ConstraintMode="Parallel">
          <Tasks>
            <#
              Dictionary<string, string> dims = DimensionKeys("Data Source=" + olapServer, olapDatabase);
              foreach (string dimId in dims.Keys) { #>
            <AnalysisServicesProcessing Name="Process_<#= dimId #>" ConnectionName="olap">
              <ProcessingConfigurations>
                <DimensionProcessingConfiguration DatabaseId="<#= olapDatabase #>" ProcessingOption="<#= processType #>" DimensionId="<#= dimId #>" />
              </ProcessingConfigurations>
            </AnalysisServicesProcessing>
            <# } #>
          </Tasks>
        </Container>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Wrap-Up​

Either pattern produces a dimension-only ProcessUpdate package as the first stage of an out-of-window cube refresh. Pair it with a partition processing pattern for fact data and a cube indexes pass to complete the workflow.