Danny Quinn
.NET & SQL Server

When I need to display data from a cube on a web page, I usually store the MDX query in a text file and make this file part of a resource file.  This allows me to store the MDX in a readable format.

In the image below, I have two files in the App_GlobalResources folder.  The first is the resource file and the second the text file the contains the query.  One the left you can see that the text file is embedded in the resource file.

The MyQuery.txt file may contain something simple such as

SELECT
    {
        [Measures].[Reseller Sales Amount]
    } ON COLUMNS,
    {
        [Date].[Calendar].[Month].&[<<YEAR>>]&[<<MONTH>>]
    } ON ROWS
FROM
    [Adventure Works]

To use this script in code you can simply access the resource as below.

string mdx = Resources.MDX.MyQuery;

AdomdCommand com = new AdomdCommand(mdx,cxn);

Notice that the Date hierarchy contains the strings <<YEAR>> and <<MONTH>>.  I do this to create place holders so that I can specify different values to adjust the query.

string mdx = Resources.MDX.MyQuery;

mdx = mdx.Replace("<<YEAR>>","2008");
mdx = mdx.Replace("<<MONTH>>","3");

I read an article about T4 templates the other day and wondered if I could replace my text file resources with these templates.  That’s not to say there is anything wrong with the method above.  I have been using this method for quite a while now and haven’t had any problems.  Really I just wanted to try it in order to learn a bit more about T4 templates.

To start with I add a new Preprocessed Text Template to my web project.  I choose to store these templates in a specific folder in App_Code but you don’t have to.

When you add a template (using Visual Studio 2010), the IDE creates two files, one with a .tt extension and another with a .cs extension.  The code in the .cs file is auto-generated and a comment at the top advises against changing this file directly.

To start with the MyQuery.tt file only contains one line, a directive.

The query above contained two placeholders that allowed values to be set a runtime and I want to be able to do the same thing using templates.  There are a couple of ways to do this.  First then with parameters.

<#@ template language="c#" #>
<#@ parameter name="Year" type="System.Int32"#>
<#@ parameter name="Month" type="System.Int32"#>
SELECT
    {
        [Measures].[Reseller Sales Amount]
    } ON COLUMNS,
    {
        [Date].[Calendar].[Month].&[<#=Year#>]&[<#=Month#>]
    } ON ROWS
FROM
    [Adventure Works]

When I save the template file Visual Studio will create a class in the .cs file for me that will render my template.  There is actually quite a lot of code generated and I believe this is done so that you don’t have to distribute the T4 assemblies with the code.

Now to use the template just create an instance of this class, set the parameters and call the TransformText() method.  If you’re not sure of the class name, open the .cs file.

using MDXTemplates;
...
...
    // Create an object from the class
    MyQuery m = new MyQuery();

    // Set the parameters
    m.Session = new Dictionary<string,object>();

    m.Session.Add("Year",2008);
    m.Session.Add("Month",3);

    // Call Initialize to copy the parameters across.
    m.Initialize();

    // Get the MDX

    string mdx = m.TransformText();

Instead of using parameters, you can also add properties and methods to the class (it is marked as partial).  First, go back to the MyQuery.tt file and remove the two parameter directives.

<#@ template language="c#" #>
SELECT
    {
        [Measures].[Reseller Sales Amount]
    } ON COLUMNS,
    {
        [Date].[Calendar].[Month].&[<#=Year#>]&[<#=Month#>]
    } ON ROWS
FROM
    [Adventure Works]

Next create a new class in the same namespace and with the same name as the generated class (don’t forget the partial keyword).

namespace MDXTemplates
{
    public partial class MyQuery
    {
        public int Year{get;set;}
        public int Month{get;set;}
    }
}

The class can now be used like this

using MDXTemplates;
...
...
    // Create an object from the class
    MyQuery m = new MyQuery();

    m.Year = 2008;
    m.Month = 3;

    string mdx = m.TransformText();

Taking this one step further, by adding a constructor to this class….

public partial class MyQuery
{
    public MyQuery(int year,int month)
    {
        Year = year;
        Month = month;
    }
    public int Year{get;set;}
    public int Month{get;set;}
}

I can now load the MDX in one line.

string mdx = new MyQuery(2008,3).TransformText();