Biml Basics for SSAS

Generate SQL Server Analysis Services (SSAS) projects programmatically using Biml. This guide covers both Multidimensional and Tabular models, with automation patterns that reduce development time significantly.

Why Use Biml for SSAS

Task	Manual Approach	Biml Approach
Create multi-tenant cubes	Build each cube individually	Generate from metadata template
Add dimensions to multiple cubes	Edit each cube manually	Update template, regenerate all
Deploy to multiple servers	Manual deployment per server	Automated generation per environment
Process dimensions and cubes	Create SSIS packages manually	Generate processing packages dynamically

Key benefits:

• Scalability: Multi-tenancy and multi-server environments become manageable
• Consistency: Every cube follows the same patterns and standards
• Maintainability: Change the template, regenerate all projects
• Integration: Generate SSIS packages that automate SSAS processing

When to use Biml for SSAS:

• Multi-tenant deployments with similar cube structures
• Environments requiring consistent cube generation across Dev/Test/Prod
• Automated processing pipelines integrated with ETL workflows

When manual development may be better:

• One-off cube development with complex, unique structures
• Highly customized SSAS projects with extensive MDX/DAX calculations

Prerequisites

Before starting, complete these guides:

• Biml Basics - Core syntax and naming conventions
• Biml for Relational DBs - Connections and table definitions

BimlStudio Required

Analysis Services projects are only supported in BimlStudio. BimlExpress does not support SSAS project generation.

---

Quick Start: Your First Dimension

Create an SSAS dimension from a table definition. This example uses the AdventureWorks data warehouse.

Step 1: Define Connections

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AnalysisServicesConnection Name="OLAP_Cube" Server="localhost"
            ConnectionString="provider=MSOLAP;Server=localhost;Database=Analysis;Integrated Security=SSPI" />
        <OleDbConnection Name="Source_DWH"
            ConnectionString="provider=SQLNCLI11;data source=.;integrated security=SSPI;Initial Catalog=AdventureWorksDW2014" />
    </Connections>
    <Databases>
        <Database Name="AW_DW" ConnectionName="Source_DWH" />
    </Databases>
    <Schemas>
        <Schema Name="dbo" DatabaseName="AW_DW" />
    </Schemas>
</Biml>

Step 2: Add AnalysisMetadata to a Table

Transform a regular table into an SSAS dimension by adding the AnalysisMetadata tag:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Tables>
        <Table Name="DimCurrency" SchemaName="AW_DW.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>

Build this Biml to generate an SSAS dimension with a single attribute.

---

Core Concepts

Connections for SSAS

SSAS projects require two connection types:

Connection Type	Purpose
AnalysisServicesConnection	Connect to SSAS server for deployment
OleDbConnection	Connect to source data warehouse

<Connections>
    <AnalysisServicesConnection Name="OLAP" Server="localhost"
        ConnectionString="provider=MSOLAP;Server=localhost;Database=MyCube;Integrated Security=SSPI" />
    <OleDbConnection Name="DWH"
        ConnectionString="provider=SQLNCLI11;data source=.;integrated security=SSPI;Initial Catalog=DataWarehouse" />
</Connections>

Dimensions

Dimensions are defined by adding AnalysisMetadata to table definitions. The metadata specifies attributes, hierarchies, and relationships.

Attributes

Every dimension needs at least one attribute with Usage="Key":

<AnalysisMetadata>
    <Dimension Name="Product" AttributeAllMemberName="All Products">
        <Attributes>
            <Attribute Name="Product" Usage="Key" OrderBy="Name">
                <KeyColumns>
                    <KeyColumn ColumnName="ProductKey" />
                </KeyColumns>
                <NameColumn ColumnName="ProductName" />
            </Attribute>
        </Attributes>
    </Dimension>
</AnalysisMetadata>

Attribute properties:

Property	Purpose
Name	Display name in the cube
Usage	Set to Key for the primary attribute
OrderBy	Sort order (Key, Name, AttributeKey, AttributeName)
AttributeType	Semantic type (Date, Years, Months, CurrencyIsoCode, etc.)

Hierarchies

Create drill-down paths with AttributeHierarchies:

<Table Name="DimSalesTerritory" SchemaName="AW_DW.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">
            <Columns>
                <Column ColumnName="SalesTerritoryKey" />
            </Columns>
        </Identity>
    </Keys>
    <AnalysisMetadata>
        <Dimension Name="Sales Territory" AttributeAllMemberName="All Sales Territories">
            <Attributes>
                <Attribute Name="Sales Territory Region" OrderBy="Name" Usage="Key">
                    <KeyColumns>
                        <KeyColumn ColumnName="SalesTerritoryKey" />
                    </KeyColumns>
                    <NameColumn ColumnName="SalesTerritoryRegion" />
                </Attribute>
                <Attribute Name="Sales Territory Country" OrderBy="Name">
                    <KeyColumns>
                        <KeyColumn ColumnName="SalesTerritoryCountry" />
                    </KeyColumns>
                    <NameColumn ColumnName="SalesTerritoryCountry" />
                </Attribute>
                <Attribute Name="Sales Territory Group" OrderBy="Name">
                    <KeyColumns>
                        <KeyColumn ColumnName="SalesTerritoryGroup" />
                    </KeyColumns>
                    <NameColumn ColumnName="SalesTerritoryGroup" />
                </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>

Relationship direction: Relationships build up from the lowest level (grain) to the highest aggregation. If your hierarchy is Group → Country → Region, the first relationship parent is Region (lowest) and child is Country.

Time Dimensions

Time dimensions use special DimensionType and AttributeType values for time intelligence:

<Table Name="DimDate" SchemaName="AW_DW.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 Usage="Key" Name="Date" 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>

Note: The Month attribute uses multiple key columns (Year + MonthNumber) to ensure uniqueness across years.

Measure Groups

Measure groups are defined on fact tables using MeasureGroup within AnalysisMetadata:

<Table Name="FactInternetSales" SchemaName="AW_DW.dbo">
    <Columns>
        <TableReference Name="OrderDateKey" TableName="AW_DW.dbo.DimDate" />
        <TableReference Name="ShipDateKey" TableName="AW_DW.dbo.DimDate" />
        <TableReference Name="CurrencyKey" TableName="AW_DW.dbo.DimCurrency" />
        <TableReference Name="SalesTerritoryKey" TableName="AW_DW.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>

Key points:

• Use TableReference instead of Column for foreign keys to dimension tables
• Each Measure references a column and defines its display name
• Default aggregation is SUM; specify AggregateFunction for others (Count, Min, Max, etc.)

Cubes

The cube brings together dimensions and measure groups:

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

Role-playing dimensions: Notice that DimDate is referenced twice with different names (Order Date and Ship Date). This creates role-playing dimensions from a single source dimension.

---

Multidimensional Projects

Project Structure

BimlStudio organizes SSAS Multidimensional projects in the Analysis Services folder:

Analysis Services/
├── Multidimensional/
│   ├── Projects/     # Project settings
│   ├── Cubes/        # Cube definitions
│   ├── Dimensions/   # Dimension definitions
│   └── Measure Groups/

Complete Example

Combine the elements above into a complete project:

File 1: Environment.biml (Connections and schemas)

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AnalysisServicesConnection Name="OLAP_Cube" Server="localhost"
            ConnectionString="provider=MSOLAP;Server=localhost;Database=Analysis;Integrated Security=SSPI" />
        <OleDbConnection Name="Source_DWH"
            ConnectionString="provider=SQLNCLI11;data source=.;integrated security=SSPI;Initial Catalog=AdventureWorksDW2014" />
    </Connections>
    <Databases>
        <Database Name="AW_DW" ConnectionName="Source_DWH" />
    </Databases>
    <Schemas>
        <Schema Name="dbo" DatabaseName="AW_DW" />
    </Schemas>
</Biml>

File 2: Dimensions.biml (All dimension tables with AnalysisMetadata)

File 3: Facts.biml (Fact tables with MeasureGroup definitions)

File 4: Cube.biml (Cube definition referencing dimensions and measure groups)

Build the solution to generate the SSAS project in the Output\SSAS folder.

---

Tabular Projects

Tabular models use a different structure within AnalysisMetadata. BimlStudio supports SQL Server 2016+ for Tabular projects.

Tabular Connection

Tabular connections require impersonation settings:

<Connections>
    <Connection Name="AdventureWorksDW"
        ConnectionString="Provider=SQLOLEDB.1;Data Source=localhost;Initial Catalog=AdventureWorksDW;Integrated Security=SSPI;">
        <AnalysisMetadata ImpersonationMode="ImpersonateAccount" AccountToImpersonate="Domain\User" />
    </Connection>
</Connections>

Tabular Tables

Use the Tabular element within AnalysisMetadata:

<Table Name="DimDate" SchemaName="AdventureWorksDW.dbo">
    <Columns>
        <Column Name="DateKey" DataType="Int32" IsNullable="false" />
        <Column Name="FullDateAlternateKey" DataType="Date" IsNullable="false" />
        <Column Name="DayNumberOfWeek" DataType="Byte" IsNullable="false" />
        <Column Name="EnglishDayNameOfWeek" DataType="String" Length="10" IsNullable="false" />
        <Column Name="MonthNumberOfYear" DataType="Byte" IsNullable="false" />
        <Column Name="CalendarYear" DataType="Int16" IsNullable="false" />
    </Columns>
    <Keys>
        <PrimaryKey Name="PK_DimDate">
            <Columns>
                <Column ColumnName="DateKey" SortOrder="Asc" />
            </Columns>
        </PrimaryKey>
    </Keys>
    <AnalysisMetadata>
        <Tabular Name="DimDate" AutoCreateSinglePartition="true">
            <Columns>
                <Column Name="DateKey" SourceColumnName="DateKey" IsHidden="true" />
                <Column Name="FullDateAlternateKey" SourceColumnName="FullDateAlternateKey" />
                <Column Name="DayNumberOfWeek" SourceColumnName="DayNumberOfWeek" />
                <Column Name="EnglishDayNameOfWeek" SourceColumnName="EnglishDayNameOfWeek"
                    SortByColumnName="AdventureWorksDW.dbo.DimDate.DayNumberOfWeek" />
                <Column Name="MonthNumberOfYear" SourceColumnName="MonthNumberOfYear" />
                <Column Name="CalendarYear" SourceColumnName="CalendarYear" />
            </Columns>
        </Tabular>
    </AnalysisMetadata>
</Table>

Tabular element attributes:

Attribute	Purpose
AutoCreateColumns	Auto-create columns from table definition
AutoCreateSinglePartition	Create default partition automatically
IsHidden	Hide table from client tools
Name	Display name for the tabular object

Tabular Measures with DAX

Define measures using DAX expressions:

<Table Name="FactSalesQuota" SchemaName="AdventureWorksDW.dbo">
    <Columns>
        <Column Name="SalesQuotaKey" DataType="Int32" IsNullable="false" />
        <Column Name="EmployeeKey" DataType="Int32" IsNullable="false" />
        <Column Name="DateKey" DataType="Int32" IsNullable="false" />
        <Column Name="CalendarYear" DataType="Int16" IsNullable="false" />
        <Column Name="SalesAmountQuota" DataType="Currency" IsNullable="false" />
    </Columns>
    <Keys>
        <PrimaryKey Name="PK_FactSalesQuota">
            <Columns>
                <Column ColumnName="SalesQuotaKey" SortOrder="Asc" />
            </Columns>
        </PrimaryKey>
    </Keys>
    <AnalysisMetadata>
        <Tabular Name="SalesAmountQuota">
            <Partitions>
                <Partition Name="2023">
                    <SourceQuery>SELECT [SalesQuotaKey], [EmployeeKey], [DateKey], [SalesAmountQuota]
                        FROM [dbo].[FactSalesQuota] WHERE [CalendarYear] = 2023</SourceQuery>
                </Partition>
                <Partition Name="2024">
                    <SourceQuery>SELECT [SalesQuotaKey], [EmployeeKey], [DateKey], [SalesAmountQuota]
                        FROM [dbo].[FactSalesQuota] WHERE [CalendarYear] = 2024</SourceQuery>
                </Partition>
            </Partitions>
            <Columns>
                <Column Name="SalesQuotaKey" SourceColumnName="SalesQuotaKey" />
                <Column Name="EmployeeKey" SourceColumnName="EmployeeKey" />
                <Column Name="DateKey" SourceColumnName="DateKey" />
                <Column Name="SalesAmountQuota" SourceColumnName="SalesAmountQuota" />
            </Columns>
            <Measures>
                <Measure Name="SalesQuota" DataType="Decimal" FormatString="$#,0.00;($#,0.00);$#,0.00">
                    <Expression>SUM(SalesAmountQuota[SalesAmountQuota])</Expression>
                </Measure>
            </Measures>
        </Tabular>
    </AnalysisMetadata>
</Table>

Note: DAX expressions must use the format Table[Column] for column references.

Tabular Models

Create a tabular model that combines tables with relationships:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <TabularModels>
        <TabularModel Name="AdventureWorksDW" OutputLabel="AdventureWorksDW-SalesQuota">
            <Tables>
                <Table TabularTableName="AdventureWorksDW.dbo.DimDate.DimDate" OutputLabel="Date" />
                <Table TabularTableName="AdventureWorksDW.dbo.DimEmployee.DimEmployee" OutputLabel="Employee" />
                <Table TabularTableName="AdventureWorksDW.dbo.FactSalesQuota.SalesAmountQuota" />
            </Tables>
            <Relationships>
                <Relationship
                    TabularColumnName="AdventureWorksDW.dbo.FactSalesQuota.SalesAmountQuota.DateKey"
                    TargetTabularColumnName="AdventureWorksDW.dbo.DimDate.DimDate.DateKey" />
                <Relationship
                    TabularColumnName="AdventureWorksDW.dbo.FactSalesQuota.SalesAmountQuota.EmployeeKey"
                    TargetTabularColumnName="AdventureWorksDW.dbo.DimEmployee.DimEmployee.EmployeeKey" />
            </Relationships>
            <Perspectives>
                <Perspective Name="Sales Overview">
                    <Tables>
                        <Table TabularTableName="AdventureWorksDW.dbo.DimDate.DimDate" IncludeAllColumns="true" />
                        <Table TabularTableName="AdventureWorksDW.dbo.FactSalesQuota.SalesAmountQuota" IncludeAllColumns="true" />
                    </Tables>
                </Perspective>
            </Perspectives>
        </TabularModel>
    </TabularModels>
</Biml>

Tabular Project

Define the project that references the tabular model:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Projects>
        <TabularProject Name="AdventureWorksDW" ServerName="localhost\TABULAR" Database="AdventureWorksDW">
            <TabularModels>
                <TabularModel TabularModelName="AdventureWorksDW" />
            </TabularModels>
        </TabularProject>
    </Projects>
</Biml>

---

Automation Patterns

Pattern 1: Metadata-Driven Dimension Generation

Generate dimensions dynamically from metadata:

<#@ template tier="20" #>
<#
var dimensions = new[] {
    new { Table = "DimProduct", Name = "Product", KeyColumn = "ProductKey", NameColumn = "ProductName" },
    new { Table = "DimCustomer", Name = "Customer", KeyColumn = "CustomerKey", NameColumn = "CustomerName" },
    new { Table = "DimStore", Name = "Store", KeyColumn = "StoreKey", NameColumn = "StoreName" }
};
#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Tables>
        <# foreach (var dim in dimensions) { #>
        <Table Name="<#= dim.Table #>" SchemaName="DW.dbo">
            <Columns>
                <Column Name="<#= dim.KeyColumn #>" />
                <Column Name="<#= dim.NameColumn #>" DataType="String" Length="100" />
            </Columns>
            <Keys>
                <Identity Name="PK_<#= dim.Table #>">
                    <Columns>
                        <Column ColumnName="<#= dim.KeyColumn #>" />
                    </Columns>
                </Identity>
            </Keys>
            <AnalysisMetadata>
                <Dimension Name="<#= dim.Name #>">
                    <Attributes>
                        <Attribute Name="<#= dim.Name #>" Usage="Key" OrderBy="Name">
                            <KeyColumns>
                                <KeyColumn ColumnName="<#= dim.KeyColumn #>" />
                            </KeyColumns>
                            <NameColumn ColumnName="<#= dim.NameColumn #>" />
                        </Attribute>
                    </Attributes>
                </Dimension>
            </AnalysisMetadata>
        </Table>
        <# } #>
    </Tables>
</Biml>

Pattern 2: SSAS Processing in SSIS

Generate SSIS packages that process SSAS objects:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AnalysisServicesConnection Name="OLAP"
            ConnectionString="Data Source=localhost;PROVIDER=MSOLAP;Impersonation Level=Impersonate;Initial Catalog=Analysis"
            Server="localhost" />
    </Connections>
    <Packages>
        <Package Name="Process_SSAS_Cube" ConstraintMode="Linear">
            <Tasks>
                <AnalysisServicesProcessing Name="Process Dimensions" ConnectionName="OLAP">
                    <ProcessingConfigurations>
                        <DimensionProcessingConfiguration DatabaseId="Analysis"
                            ProcessingOption="ProcessUpdate" DimensionId="Currency" />
                        <DimensionProcessingConfiguration DatabaseId="Analysis"
                            ProcessingOption="ProcessUpdate" DimensionId="Date" />
                        <DimensionProcessingConfiguration DatabaseId="Analysis"
                            ProcessingOption="ProcessUpdate" DimensionId="Sales Territory" />
                    </ProcessingConfigurations>
                    <ErrorConfiguration KeyDuplicate="IgnoreError" KeyErrorAction="ConvertToUnknown"
                        KeyErrorLimit="0" KeyNotFound="IgnoreError" NullKeyNotAllowed="IgnoreError"
                        NullKeyConvertedToUnknown="IgnoreError" />
                </AnalysisServicesProcessing>
                <AnalysisServicesProcessing Name="Process Cube" ConnectionName="OLAP" ProcessingOrder="Parallel">
                    <ProcessingConfigurations>
                        <CubeProcessingConfiguration DatabaseID="Analysis"
                            ProcessingOption="ProcessFull" CubeId="SalesCube" />
                    </ProcessingConfigurations>
                    <ErrorConfiguration KeyDuplicate="IgnoreError" KeyErrorAction="ConvertToUnknown"
                        KeyErrorLimit="0" KeyNotFound="IgnoreError" NullKeyNotAllowed="IgnoreError"
                        NullKeyConvertedToUnknown="IgnoreError" />
                </AnalysisServicesProcessing>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Processing configuration types:

Configuration	Purpose
CubeProcessingConfiguration	Process entire cube
DimensionProcessingConfiguration	Process specific dimension
DatabaseProcessingConfiguration	Process entire SSAS database
MeasureGroupProcessingConfiguration	Process specific measure group
PartitionProcessingConfiguration	Process specific partition

Pattern 3: AMO-Based Dynamic Processing

Use Analysis Management Objects (AMO) to dynamically generate processing packages:

<#@ template language="C#" #>
<#@ assembly name="Microsoft.AnalysisServices" #>
<#@ import namespace="Microsoft.AnalysisServices" #>
<#
var olapServer = "localhost";
var olapDb = "Analysis";
var connection = new Server();
connection.Connect(olapServer);
#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AnalysisServicesConnection Name="OLAP"
            ConnectionString="Data Source=<#= olapServer #>;PROVIDER=MSOLAP;Impersonation Level=Impersonate;Initial Catalog=<#= olapDb #>"
            Server="<#= olapServer #>" />
    </Connections>
    <Packages>
        <Package Name="Process_SSAS_Dynamic" ConstraintMode="Linear">
            <Tasks>
                <!-- Process all dimensions sequentially -->
                <AnalysisServicesProcessing Name="Process All Dimensions" ConnectionName="OLAP">
                    <ProcessingConfigurations>
                        <# foreach (Dimension dim in connection.Databases[olapDb].Dimensions) { #>
                        <DimensionProcessingConfiguration DatabaseId="<#= olapDb #>"
                            ProcessingOption="ProcessUpdate" DimensionId="<#= dim.ID #>" />
                        <# } #>
                    </ProcessingConfigurations>
                    <ErrorConfiguration KeyDuplicate="IgnoreError" KeyErrorAction="ConvertToUnknown"
                        KeyErrorLimit="0" KeyNotFound="IgnoreError" NullKeyNotAllowed="IgnoreError"
                        NullKeyConvertedToUnknown="IgnoreError" />
                </AnalysisServicesProcessing>

                <!-- Process all cubes in parallel -->
                <Container Name="Process Cubes" ConstraintMode="Parallel">
                    <Tasks>
                        <# foreach (Cube cube in connection.Databases[olapDb].Cubes) { #>
                        <AnalysisServicesProcessing Name="Process Cube <#= cube.Name #>"
                            ConnectionName="OLAP" ProcessingOrder="Parallel">
                            <ProcessingConfigurations>
                                <CubeProcessingConfiguration DatabaseID="<#= olapDb #>"
                                    ProcessingOption="ProcessFull" CubeId="<#= cube.ID #>" />
                            </ProcessingConfigurations>
                            <ErrorConfiguration KeyDuplicate="IgnoreError" KeyErrorAction="ConvertToUnknown"
                                KeyErrorLimit="0" KeyNotFound="IgnoreError" NullKeyNotAllowed="IgnoreError"
                                NullKeyConvertedToUnknown="IgnoreError" />
                        </AnalysisServicesProcessing>
                        <# } #>
                    </Tasks>
                </Container>
            </Tasks>
        </Package>
    </Packages>
</Biml>

<# connection.Disconnect(); #>

This pattern:

1. Connects to the SSAS server at compile time
2. Iterates through all dimensions and cubes
3. Generates a package that processes dimensions first (sequential), then cubes (parallel)
4. Maximizes parallelism within SQL Server Standard Edition limitations

---

CI/CD Integration

Version Control Best Practices

Organize SSAS Biml files by tier:

/biml
  /environment          # Tier 10: Connections, databases
  /dimensions           # Tier 20: Dimension tables with AnalysisMetadata
  /facts                # Tier 30: Fact tables with MeasureGroups
  /cubes                # Tier 40: Cube definitions
  /processing           # Tier 50: Processing package generation
  /output               # Generated projects (add to .gitignore)

Commit:

• Biml/BimlScript source files
• Configuration files

Exclude (.gitignore):

• Generated SSAS projects
• Build output folders

Command-Line Build

# BimlStudio command line
& "C:\Program Files\Varigence\BimlStudio\Biml.exe" `
    /build /project:SSASProject.mst /output:.\output

Azure DevOps Pipeline

trigger:
  - main

pool:
  vmImage: 'windows-latest'

steps:
- task: PowerShell@2
  displayName: 'Build SSAS Project'
  inputs:
    targetType: 'inline'
    script: |
      & "C:\Program Files\Varigence\BimlStudio\Biml.exe" `
        /build /project:SSASProject.mst /output:$(Build.ArtifactStagingDirectory)

- task: PublishBuildArtifacts@1
  inputs:
    PathtoPublish: '$(Build.ArtifactStagingDirectory)'
    ArtifactName: 'SSASProjects'

---

Beyond SSAS: Modern Platforms

The patterns you learn for SSAS translate to modern analytical platforms:

SSAS Concept	Azure Analysis Services	Power BI Premium
Multidimensional Cube	Not supported	Not supported
Tabular Model	Fully supported	Dataset
Dimension	Table	Table
Measure	Measure	Measure (DAX)
Partition	Partition	Incremental refresh

Platform documentation:

• Azure Data Factory - For orchestrating SSAS processing
• BimlFlex - Enterprise platform with UI-driven metadata management

---

Conclusion

Biml provides comprehensive support for SSAS project generation:

• Multidimensional models: Dimensions, measure groups, cubes with partitions
• Tabular models: Tables, relationships, perspectives, DAX measures
• Processing automation: Generate SSIS packages that process SSAS objects dynamically
• Multi-tenancy: Scale to dozens of similar cubes from a single template

Next Steps

1. Biml for SSIS - Generate ETL packages that feed your cubes
2. Common Patterns - Reusable patterns for staging and loading
3. Automation and Patterns - Metadata-driven development concepts
4. Troubleshooting Guide - Debug compilation errors

Reference Documentation

• Cube Element Reference
• Dimension Element Reference
• TabularModel Element Reference
• AnalysisServicesProcessing Task Reference