Generating SSAS Partitions using T4 Templates

T4 Templates

T4 Templates are a code generation tool that come packaged in Visual Studio. What that means exactly is that if you have a repeatable pattern, you can use this tool to iterate over that pattern and fill in the blanks where there are differences.

Use Case Example

On the project in which I learned how to use this tool, we generated merge procs for an ETL. The T4 Template iterated over a database schema which supplied us with the tables and column names. The template provided all the parts that were the same. Originally, I had crafted something with dynamic SQL, which was convenient and what I knew how to use. I quickly learned that the beauty of the T4 Template is that it leverages the .Net framework, which handles text and formatting much more elegantly than SQL.

Generating Partitions in SSAS

So I recently had the need to create a cube which would have 2 years of daily partitions. If you have ever used the partition editor in Visual Studio, you'd quickly realize that this was intended to only create a handful of partitions. Doing this work manually would be nuts.

I looked in the SSAS project folder and found a file called <Project Name>.partitions, which contained a specification for all of the partitions that I had created so far. It was roughly like this:

<Cube ...bunch of namespaces...>
<ID>MyCube</ID>
<MeasureGroups>
<MeasureGroup>
<ID>MyMeasureGroup</ID>
<Partitions>
<Partition>
<ID>MyPartition</ID>
<Name>MyPartition</Name>
....Other attributes about the partition including the query which defines it...
</Partition>
</Partitions>
</MeasureGroup>
</MeasureGroups>
</Cube>

So in order to create more partitions, we only need to add more partition nodes, changing the Id, name, query which defines it and some guids. I added a new c# project to my solution and added a new T4 Template file called partitions.tt. This looked like below:

When a tt file is saved, it's code is executing and the resulting file is created below it with the specified extension. The syntax of this file is a little funky and there is no highlighting out of the box. For highlighting and intellisense, I used the visual studio extension Devart T4 Editor which is free.

Some key aspects to note are:

Declarations are noted within <#@ #>

i.e. you can reference a data assembly and make a connection out to a database

The default language is c#, but this can be changed to VB if you prefer
"output extension" specifies the file type
All gray text below is left unchanged in the final file
All text within <# #> is c# code

For example, I start by declaring a couple of variables and the reference them in a while loop
At the start of each loop I set variables like the query text
At the end of each loop the date is incremented
To insert the value of a variable, you merely need to break the text with <# variableName #>

Below is a full code example:

<#@ template debug="false" hostspecific="false" language="C#" #>

<#@ output extension=".partitions" #>

<Cube xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" dwd:design-time-name="d9434059-83fc-481e-b3e2-1efc4430eb86" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<ID>MyCube</ID>

<MeasureGroups>

<MeasureGroup>

<ID>MyMeasureGroup</ID>

<Partitions>

<#

DateTime start = Convert.ToDateTime("2014-09-01");

DateTime end = Convert.ToDateTime("2015-09-30");

while(start <= end)

{

Guid partitionId = Guid.NewGuid();

Guid queryId= Guid.NewGuid();

string partitionName = "MyMeasureGroup_" + start.ToString("yyyy_MM_dd");

string queryText = @"SELECT * FROM MyTable WHERE startDate = '" + start.ToString("yyyy-MM-dd") + "'";

#>

<Partition dwd:design-time-name="<#=partitionId.ToString() #>">

<ID><#=partitionName #></ID>

<Name><#=partitionName #></Name>

<CreatedTimestamp>0001-01-01T00:00:00Z</CreatedTimestamp>

<LastSchemaUpdate>0001-01-01T00:00:00Z</LastSchemaUpdate>

<LastProcessed>0001-01-01T00:00:00Z</LastProcessed>

<State>Unprocessed</State>

<Source xsi:type="QueryBinding" dwd:design-time-name="<#=queryId.ToString() #>">

<DataSourceID>PecoAMIMDR</DataSourceID>

<QueryDefinition><#=queryText#></QueryDefinition>

</Source>

<StorageMode>Molap</StorageMode>

<CurrentStorageMode>Molap</CurrentStorageMode>

<ProcessingMode>Regular</ProcessingMode>

</Partition>

<#

start = start.AddDays(1);

}

#>

</Partitions>

</MeasureGroup>

</MeasureGroups>

</Cube>

The resulting code create 2 years of daily partitions in a few seconds. I then replaced the contents of this file with the partitions file in the project. The solution needed to be closed and reopened in order to accept the changes properly. Later, I changed the build process of the T4 Template project to replace the partitions file in a post build step. The learning curve on this is not so steep so I encourage you to give it a go.

Labels: SSAS, T4 Template