Building a Multidimensional SSAS Database with Biml

Why Build a Cube From Biml

Biml is best known for SSIS automation, but the same syntax can describe a multidimensional Analysis Services database. A single project can hold the staging tables, the warehouse tables, the dimensions, the measure groups, and the cube definition that ties them together. Regenerating the project rebuilds every artifact in step. The result is an SSAS project under the project's output folder ready to deploy.

The shape this walkthrough builds is a single cube with three dimensions, one of them used as a role playing date dimension. The cube has one measure group with one partition. The technique scales to projects with many dimensions and many measure groups by repeating the same patterns.

Defining Connections

The project needs an Analysis Services connection for the cube and an OleDb connection for the relational warehouse that backs the data source view:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <AnalysisServicesConnection Name="OlapTarget"
      Server="localhost"
      ConnectionString="Provider=MSOLAP;Server=localhost;Database=SalesOlap;Integrated Security=SSPI" />
    <OleDbConnection Name="WarehouseConn"
      ConnectionString="Provider=SQLNCLI11;Data Source=.;Integrated Security=SSPI;Initial Catalog=SalesDw" />
  </Connections>
  <Databases>
    <Database Name="SalesDw" ConnectionName="WarehouseConn" />
  </Databases>
  <Schemas>
    <Schema Name="dbo" DatabaseName="SalesDw" />
  </Schemas>
</Biml>

This part is the same shape as any Biml project that targets a relational database, with the addition of an 'AnalysisServicesConnection' for the SSAS server.

A Simple Dimension

Dimensions in Biml are defined inside the underlying 'Table'. The table needs a key, supplied through the 'Keys' element, plus an 'AnalysisMetadata' block that holds the 'Dimension' definition. The smallest useful example is a single attribute dimension:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Tables>
    <Table Name="DimCurrency" SchemaName="SalesDw.dbo" FriendlyName="Source Currency">
      <Columns>
        <Column Name="CurrencyKey" />
        <Column Name="CurrencyName" DataType="String" Length="50" />
      </Columns>
      <Keys>
        <Identity Name="PK_DimCurrency_CurrencyKey">
          <Columns>
            <Column ColumnName="CurrencyKey" />
          </Columns>
        </Identity>
      </Keys>
      <AnalysisMetadata>
        <Dimension Name="Currency" DimensionType="Currency">
          <Attributes>
            <Attribute Name="Currency" Usage="Key" AttributeType="CurrencyIsoCode" OrderBy="Name">
              <KeyColumns>
                <KeyColumn ColumnName="CurrencyKey" />
              </KeyColumns>
              <NameColumn ColumnName="CurrencyName" />
            </Attribute>
          </Attributes>
        </Dimension>
      </AnalysisMetadata>
    </Table>
  </Tables>
</Biml>

The 'Identity' inside 'Keys' marks 'CurrencyKey' as the primary key for the relational side. The 'Dimension' inside 'AnalysisMetadata' supplies the SSAS metadata: a single Key 'Attribute' with a 'KeyColumn' for the natural key column and a 'NameColumn' for the display name.

A Dimension With a Hierarchy

Most real dimensions hold several attributes and at least one hierarchy. The 'AttributeHierarchies' element groups attributes into hierarchy levels and the 'Relationships' element wires the parent to child chain that the storage engine relies on:

<Table Name="DimSalesTerritory" SchemaName="SalesDw.dbo" FriendlyName="Sales Territory">
  <Columns>
    <Column Name="SalesTerritoryKey" />
    <Column Name="SalesTerritoryRegion" DataType="String" Length="50" />
    <Column Name="SalesTerritoryCountry" DataType="String" Length="50" />
    <Column Name="SalesTerritoryGroup" DataType="String" Length="50" IsNullable="true" />
  </Columns>
  <Keys>
    <Identity Name="PK_DimSalesTerritory_SalesTerritoryKey">
      <Columns>
        <Column ColumnName="SalesTerritoryKey" />
      </Columns>
    </Identity>
  </Keys>
  <AnalysisMetadata>
    <Dimension Name="Sales Territory" AttributeAllMemberName="All Sales Territories">
      <Attributes>
        <Attribute Name="Sales Territory Region" Usage="Key" OrderBy="Name">
          <KeyColumns>
            <KeyColumn ColumnName="SalesTerritoryKey" />
          </KeyColumns>
          <NameColumn ColumnName="SalesTerritoryRegion" />
        </Attribute>
        <Attribute Name="Sales Territory Group" OrderBy="Name">
          <KeyColumns>
            <KeyColumn ColumnName="SalesTerritoryGroup" />
          </KeyColumns>
          <NameColumn ColumnName="SalesTerritoryGroup" />
        </Attribute>
        <Attribute Name="Sales Territory Country" OrderBy="Name">
          <KeyColumns>
            <KeyColumn ColumnName="SalesTerritoryCountry" />
          </KeyColumns>
          <NameColumn ColumnName="SalesTerritoryCountry" />
        </Attribute>
      </Attributes>
      <AttributeHierarchies>
        <Hierarchy Name="Sales Territory">
          <Levels>
            <Level Name="Group" AttributeName="Sales Territory Group" />
            <Level Name="Country" AttributeName="Sales Territory Country" />
            <Level Name="Region" AttributeName="Sales Territory Region" />
          </Levels>
        </Hierarchy>
      </AttributeHierarchies>
      <Relationships>
        <Relationship Name="Sales Territory Country"
                      ParentAttributeName="Sales Territory Region"
                      ChildAttributeName="Sales Territory Country" />
        <Relationship Name="Sales Territory Group"
                      ParentAttributeName="Sales Territory Country"
                      ChildAttributeName="Sales Territory Group" />
      </Relationships>
    </Dimension>
  </AnalysisMetadata>
</Table>

Hierarchy and relationship traverse the chain in opposite directions. The hierarchy lists the levels from the highest aggregation down. The relationship pairs each level with its immediate child going up. Grouping the metadata next to the table that produces it keeps the dimension definition self contained.

A Time Dimension

A time dimension is an ordinary Biml dimension with 'DimensionType="Time"'. Each attribute that participates in time intelligence (year, quarter, month, day) is tagged with an 'AttributeType' so SSAS can apply the right time calculations:

<Table Name="DimDate" SchemaName="SalesDw.dbo" FriendlyName="Date">
  <Columns>
    <Column Name="DateKey" />
    <Column Name="FullDateAlternateKey" DataType="Date" />
    <Column Name="CalendarYear" DataType="Int16" />
    <Column Name="EnglishMonthName" DataType="String" Length="10" />
    <Column Name="MonthNumberOfYear" DataType="Byte" />
  </Columns>
  <Keys>
    <PrimaryKey Name="PK_DimDate_DateKey">
      <Columns>
        <Column ColumnName="DateKey" />
      </Columns>
    </PrimaryKey>
  </Keys>
  <AnalysisMetadata>
    <Dimension Name="Date" DimensionType="Time">
      <Attributes>
        <Attribute Name="Date" Usage="Key" AttributeType="Date">
          <KeyColumns>
            <KeyColumn ColumnName="DateKey" />
          </KeyColumns>
          <NameColumn ColumnName="FullDateAlternateKey" />
        </Attribute>
        <Attribute Name="Month Name" AttributeType="Months">
          <KeyColumns>
            <KeyColumn ColumnName="CalendarYear" />
            <KeyColumn ColumnName="MonthNumberOfYear" />
          </KeyColumns>
          <NameColumn ColumnName="EnglishMonthName" />
        </Attribute>
        <Attribute Name="Calendar Year" AttributeType="Years">
          <KeyColumns>
            <KeyColumn ColumnName="CalendarYear" />
          </KeyColumns>
          <NameColumn ColumnName="CalendarYear" />
        </Attribute>
      </Attributes>
      <Relationships>
        <Relationship Name="Month Name"
                      ParentAttributeName="Date" ChildAttributeName="Month Name" />
        <Relationship Name="Calendar Year"
                      ParentAttributeName="Month Name" ChildAttributeName="Calendar Year" />
      </Relationships>
      <AttributeHierarchies>
        <Hierarchy Name="Calendar">
          <Levels>
            <Level Name="Year" AttributeName="Calendar Year" />
            <Level Name="Month" AttributeName="Month Name" />
            <Level Name="Date" AttributeName="Date" />
          </Levels>
        </Hierarchy>
      </AttributeHierarchies>
    </Dimension>
  </AnalysisMetadata>
</Table>

The Month attribute uses two key columns because year plus month number is what uniquely identifies a month across years.

A Measure Group on the Fact Table

A measure group lives inside its fact 'Table'. Its foreign key columns are declared as 'TableReference' columns rather than ordinary 'Column' elements, which is how Biml understands that they point at dimension tables. The 'AnalysisMetadata' block holds the 'MeasureGroup' definition with one 'Measure' per numeric column to aggregate:

<Table Name="FactInternetSales" SchemaName="SalesDw.dbo">
  <Columns>
    <TableReference Name="OrderDateKey"      TableName="SalesDw.dbo.DimDate" />
    <TableReference Name="ShipDateKey"       TableName="SalesDw.dbo.DimDate" />
    <TableReference Name="CurrencyKey"       TableName="SalesDw.dbo.DimCurrency" />
    <TableReference Name="SalesTerritoryKey" TableName="SalesDw.dbo.DimSalesTerritory" />
    <Column Name="OrderQuantity" DataType="Int16" />
    <Column Name="SalesAmount"   DataType="Currency" />
  </Columns>
  <AnalysisMetadata>
    <MeasureGroup Name="Internet Sales">
      <Measures>
        <Measure AggregateColumnName="OrderQuantity" Name="Order Quantity" />
        <Measure AggregateColumnName="SalesAmount"   Name="Sales Amount" />
      </Measures>
    </MeasureGroup>
  </AnalysisMetadata>
</Table>

The 'AggregateColumnName' on each 'Measure' picks the column to roll up. Two foreign key references to 'DimDate' set up the role playing date dimension that the cube definition will name 'Order Date' and 'Ship Date'.

Tying It Together as a Cube

The cube combines existing pieces. 'CubeMeasureGroups' references the measure group by its 'FactName' (the schema-qualified table name plus the measure group name). 'CubeDimensionBindings' wires each dimension to its foreign key column. 'CubeDimensions' lists the dimensions the cube exposes, and the same dimension can appear twice under different 'Name' values to play more than one role:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Cubes>
    <Cube Name="SalesCube" ConnectionName="OlapTarget">
      <CubeMeasureGroups>
        <CubeMeasureGroup Name="Internet Sales"
                          FactName="SalesDw.dbo.FactInternetSales.Internet Sales">
          <CubeDimensionBindings>
            <CubeDimensionBinding CubeDimensionName="Order Date"
              FactColumnName="SalesDw.dbo.FactInternetSales.OrderDateKey" />
            <CubeDimensionBinding CubeDimensionName="Ship Date"
              FactColumnName="SalesDw.dbo.FactInternetSales.ShipDateKey" />
            <CubeDimensionBinding CubeDimensionName="Currency"
              FactColumnName="SalesDw.dbo.FactInternetSales.CurrencyKey" />
            <CubeDimensionBinding CubeDimensionName="Sales Territory"
              FactColumnName="SalesDw.dbo.FactInternetSales.SalesTerritoryKey" />
          </CubeDimensionBindings>
          <Partitions>
            <Partition Name="Internet Sales Partition">
              <DsvTableSource ConnectionName="WarehouseConn"
                              TableName="SalesDw.dbo.FactInternetSales" />
            </Partition>
          </Partitions>
        </CubeMeasureGroup>
      </CubeMeasureGroups>
      <CubeDimensions>
        <CubeDimension Name="Currency"
                       DimensionName="SalesDw.dbo.DimCurrency.Currency" />
        <CubeDimension Name="Order Date"
                       DimensionName="SalesDw.dbo.DimDate.Date" />
        <CubeDimension Name="Ship Date"
                       DimensionName="SalesDw.dbo.DimDate.Date" />
        <CubeDimension Name="Sales Territory"
                       DimensionName="SalesDw.dbo.DimSalesTerritory.Sales Territory" />
      </CubeDimensions>
    </Cube>
  </Cubes>
</Biml>

The role playing date dimension is the pair of 'CubeDimension' entries that both point at 'SalesDw.dbo.DimDate.Date' under different cube names. The partition uses 'DsvTableSource' to bind one chunk of fact data to the warehouse table; additional partitions can split a large fact along ranges or filters.

When the project is built, the SSAS project lands under the output folder for deployment to the target Analysis Services instance.