Using Dapper with MDX

| 2 Comments

At my company we were beginning to use MVC for our next generation web platform reporting.  In the recent past we began using Microsoft SQL Server Analysis Services for our aggregation needs.  Prior to MVC, we were doing custom ASP.Net WebForms applications.  I had written a data object using ADOMD to retrieve the data from our cube and serve it up for reporting.  Through many trials, we had to utilize the LoadXML feature of ADOMD instead of the CellSet.  This offered us much more speed in execution but we lost some of the features of being able to walk up and down the dimensions.

As our focus shifted to MVC, we wanted to migrate away from this layer.  We started to look at our alternatives and we immediately gravitated towards Dapper.  If you haven’t checked out Dapper, I highly recommend that you do. It’s from the super programmers at Stack Exchange and provides some lightning quick execution using IDbConnection objects.  Dapper has two main Query methods, one for generics where you give it a strongly typed class and it maps your query to that class and returns you an IEnumerable of that type.  The second is an IEnumerable of dynamic.  If you aren’t aware, dynamics are new in .NET 4.0 (watch this space for a more in depth discussion of dynamics).  They are similar in nature to anonymous types in that you can build properties on the fly.  One difference is they are late bound or checked at runtime so you don’t get any intellisense with them.   The dynamic object returned has a properties for each field that is returned in your query.

While this is great in general, it gave us a small setback trying to utilize it with an MDX query.  MDX, unlike SQL, doesn’t allow you to alias your column names.  The queries you write can have some very long column names and more importantly they contain letters that make it all but impossible to fully utilize dynamics abilities.  A sample column name may be something like [Measures].[Response Count].  The brackets and periods make it impossible to reference that field off of the returned object.

To combat this issue, I wrote an extension method to Query.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

namespace DapperExtension
{
    public static partial class SqlMapper
    {
        /// <summary>
        /// Return a list of dynamic objects, reader is closed after the call
        /// </summary>
        public static IEnumerable<dynamic> Query(this IDbConnection cnn, IDictionary<string, string> columns, string sql, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            IEnumerable<dynamic> output = Query<FastExpando>(cnn, sql, param as object, transaction, buffered, commandTimeout, commandType);

            if (columns != null)
            {
                List<dynamic> modifiedOutput = new List<dynamic>();

                using (IEnumerator<dynamic> enumerator = output.GetEnumerator())
                {

                    while (enumerator.MoveNext())
                    {
                        IDictionary<string, object> currentRow = enumerator.Current as IDictionary<string, object>;
                        Dictionary<string, object> newRow = new Dictionary<string, object>();
                        if (null != currentRow)
                        {
                            foreach (string key in currentRow.Keys)
                            {
                                if (columns.ContainsKey(key))
                                {
                                    newRow.Add(columns[key], currentRow[key]);
                                }
                                else
                                {
                                    newRow.Add(key, currentRow[key]);
                                }
                            }
                        }
                        else
                        {
                            // exception
                        }

                        modifiedOutput.Add(FastExpando.Attach(newRow));
                    }
                }

                if (null != modifiedOutput && modifiedOutput.Count > 0)
                {
                    output = modifiedOutput;
                }
            }

            return output;
        }
    }
}

First thing you will notice is that I put this extension in the Dapper namespace. I purposely wrote an extension in a different file so that I didn’t have to touch the Dapper code. I know that it’s open source and many people would fork it, but an extension method serves me well. They can keep updating and I don’t have to try and maintain the fork. Also by putting this in the Dapper namespace, I have access to their private classes, of my interest to me is the FastExpando class.

The parameter that drives this logic is the Dictionary<string,string>.  What I am expecting is a dictionary that contains what the column name was from the MDX query and what you would like to call it.  From the previous example we would have key,value pair of [Measures].[Response Count] and ResponseCount.  The method goes through each item in the returned object and makes a new FastExpando (FastExpando is nothing more than an IDictionary<string, object>) and if the key to that item is in the column dictionary, then it adds the value as what was passed in, otherwise it uses what it had.  The method will also just return the original object.

One downside to this extension method is that we have to go through the result set after its already been created.  As I mentioned before, I didn’t want to mess with the core Dapper code to prevent this.  In following posts, you will see how I solved this problem by writing my  our own DBProvider.  Stay tuned.

Author: Rick

I am a .NET developer by day. Recently I have gotten back into web development and and really enjoying learning MVC. In my spare time I have been learning objective-c (have to get on the iOS bandwagon) and ruby on rails. I have pretty much gone full force into Apple fanboyism. Assimilate now, it will be easier. I love gadgets/technology, college football, playing games (Xbox, iOS), reading, and spending time with the wife and kids.

  • zak willis

    Hi Rick, Zak Willis here. So I have been experimenting with this and you are aware that you can do the following by using var parameters= new DynamicParameters; parameters.Add(“~0″,”Companies”); This then allows you to bind the resultset to a class like so:- var results = connection.Query(queryString,parameters); This is without using the dynamic property that you mention in here.

  • Guido Smeets

    @zakwillis:disqus I think you’re not using AdomdClient out of the box, are you? Because what you’re describing doesn’t work. My guess is you’re using a custom client, like MdxClient ( https://github.com/DynamicTyped/MdxClient ).

    Am I correct?