Building a multidimensional SSAS database with Biml

Building a multidimensional SSAS database with Biml

Diesen Artikel finden Sie hier auch in deutscher Sprache.

As you may or may not know – Biml is not just about SSIS! You can do a lot of other stuff with it as well – like building SSAS databases.

This post assumes, that you’ve already familiarized yourself with the basic concepts of Biml so it will mainly focus on the additional attributes and tags needed. If you feel like you’re not quite there yet, don’t worry. Just go through the basics of Biml first (see posts at the end) and then come back here :). It also requires basic knowledge of multidimensional SSAS.

This sample will create one single cube with one partition and one measuregroup. It will have 3 dimensions (one of them being a role playing time dimension), to give you a basic understanding. It is based on the AdventureWorks 2014 DW (for sentimental reasons, I did not feel quite ready to abandon AdventureWorks) so that will be the prerequisit for you to install.

Once that database is available in your SQL Instance, we can get started with a very simple Biml file just to provide the connection strings etc.:

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

As you can see, there is nothing new to it except maybe the SSAS Connection. Also worth mentioning: This code sample consists exclusively of static Biml files. We are not using any BimlScript at all. Obviously, BimlScript could help us here do a lot of magic but we can always improve later 🙂

Let’s start with a very simple example to build a currency dimension. This will be done by adding additional information to your table in Biml.

We assume this is the code you’re using to define your currency table in Biml:

<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>
        </Table>
    </Tables>
</Biml>

To make an OLAP Dimension out of that, we will need to add a Primary Key as well as some Analysis Metadata:

<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>

You’ll see 2 new tags: Keys and AnalysisMetadata.

The Keys-Tag is used to define the primary key, which we will need to link the fact table to the dimension table.
The AnalysisMetadata-Tag consists of the Dimension-Metadata in this case, which will hold a collection of attributes (just one for this example) which will be defined by a Name, KeyColumns, a NameColumn as well as potentially additional attributes like an OrderBy or the AttributeType.

Most dimensions consist of multiple attributes and at least one hierarchy:

 <Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Tables>
		<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_SalesTerritoryKey">
					<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 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>
	</Tables>
</Biml>

The Sales Territory Dimension has 2 more Attributes – to bring them into context, we add one Hierarchy (using the AttributeHierarchies-Tag) and connect them by defining the relationships. For the hierarchy, we simply name it and add the levels by linking them to attributes. For the relationships, we add one relationship-tag per level, which will again have a name as well as a parent and a child attribute. Be aware, this works the opposite way of the hierarchy, so if your hierarchy would be Country-City-Customer, your first parent attribute would be the customer and the first child attribute would be the City, so while the hierarchy works it’s way down from the highest aggregation, the relationship will build up from the lowest level.

We will also need a time dimension:

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

Except for the DimensionType and AttributeTypes which we will need for SSAS to be able to run YTD etc., there is not much new here.
By the way: the month attribute will use multiple key columns here, making it obvious why key columns are a collection oposed to the single NameColumn.

Now, that we’ve got our dimensions set up, we need our measuregroup:

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

Compared to a „normal“ table as you may know it from your SSIS Biml projects, you will see, that it also has an AnalysisMetadata Tag (this time with a MeasureGroup instead of a dimension definition) which is pretty straight forward. It simply defines all the columns you want to use as measures.
The other part that might be new: some columns are defined as TableReference instead of columns as those reflect the foreign keys to be linked to our dimension tables.

Now we have our dimensions and a measuregroup – all that is missing is a cube!

The cube is mainly a collection of existing Biml objects:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Cubes>
        <Cube Name="MyCube" ConnectionName="AW_Cube">
            <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="AW_DWH" TableName="AW_DW.dbo.FactInternetSales" />
                        </Partition>
                    </Partitions>
                </CubeMeasureGroup>
            </CubeMeasureGroups>
            <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>
        </Cube>
    </Cubes>
</Biml>

By defining the CubeDimensions (which link back to our previously defined dimensions), CubeMeasureGroups (which link back to the previously defined measuregroup), CubeDimensionBindings (which we’re also using here to define a role playing dimension by linking to the „Date“ dimension twice) and potentially multiple partitions (in this case just one), we have everything we need for Biml to create our SSAS project. If you build your solution, you will find the SSAS project in the Output\SSAS folder.

Any questions or comments? We’d love to hear from you at biml@solisyon.de!

Further Information on Biml in English can be found on our English Biml Page.

Happy Biml’ing!

Facebook