Getting data from OLAP cubes

Overview

So, you need to pull data from OLAP cubes and show it in your app? Been there! The go-to tool for this is ADOMD.NET, which is basically ADO.NET’s multidimensional cousin.

This library provides a set of classes (Connections, Commands and Readers) that allow developers to connect, query and get data from these multidimensional sources in the same way the classical ADO.NET library does.

ADOMD.NET gives you the usual suspects: Connections, Commands, and Readers. You connect, run queries, and grab data from cubes just like you would with classic ADO.NET.

The easiest way to get data is with AdomdDataReader. It’s a forward-only, read-only stream—think of it as DataReader for cubes.

In non dynamic scenarios, we developers, tend to use ORMs like NHibernate, EF or Dapper (my favorite), to translate data from our models to classes. But these ORMs doesn’t support multidimensional sources, because after all, they are only supersets or extensions of the ADO.NET model and are based on this existing object model.

So, our goal here will be replicate in a multidimensional environment the way Dapper allows you to connect to a server, read some data and (the most important part) return these data as a typed List of objects.

Testing the query

Let’s see how it works before digging into the details:

using (var conn = new AdomdConnection(connectionString))
{
    conn.Open();
    var commandText = @"SELECT
                        {
                        [Measures].[Gains Period],
                        [Measures].[Gains YTD],
                        [Measures].[Amount]
                        } ON COLUMNS,
                        [Valuation Dates Accumulated].[Hierarchy].[Year].Members ON ROWS
                        FROM Accumulated
                        Where ({ @PortfolioId })";
    var data = conn.Query<MyResultDTO>(commandText,
        new AdomdParameterMultipleValues<int>()
        {
            ParameterName = "@PortfolioId",
            MemberName = "[Portfolios].[Port Code]",
            Values = new List<int>() { 282, 185 }
        }
    );
    conn.Close();
}

Here’s a sample MDX query that grabs three measures (period gains, YTD gains, and amount) by year. Simple enough.

The goal: turn that data into a List of our DTOs:

internal class MyResultDTO
{
    [OLAPMemberNameAttribute("[Valuation Dates Accumulated].[Hierarchy].[Year].[MEMBER_CAPTION]")]
    public string Year { get; set; }
    [OLAPMemberNameAttribute("[Measures].[Gains Period]")]
    public decimal? GainsPeriod { get; set; }

    [OLAPMemberNameAttribute("[Measures].[Gains YTD]")]
    public decimal? GainsYTD { get; set; }

    [OLAPMemberNameAttribute("[Measures].[Amount]")]
    public decimal? Amount { get; set; }
}

EDIT (2018-12-11): Added a custom attribute to decorate our DTO properties.

public class OLAPMemberNameAttribute : System.Attribute
{
    string _Description = string.Empty;
    public OLAPMemberNameAttribute(string description)
    {
        _Description = description;
    }
    public string Description { get { return _Description; } }
}

Notice that in C# properties cannot contain spaces so we previously have to ‘map’ the DataReader column names into valid property names. The trick here is create an attribute for decorating each property with the equivalent column name in the reader.

The magic here is inside the generic Query Method. This method is just an extension method of the AdomdConnection object (Dapper style) and its mission will be:

1. Replace the parameters in the command string “…Where ({ @PortfolioId })”
2. Execute the command against a valid connection
3. Transform the returned IDataReader into a List

public static List<T> Query<T>(
    this AdomdConnection conn, string commandText, params object[] parameters)
{
    var commandTextParameters = ReplaceCommandTextParameters(commandText, parameters);
    var cmd = new AdomdCommand(commandTextParameters, conn);
    var dr = cmd.ExecuteReader();
    return dr.ToList<T>();
}

Now let’s focus on the third point and create another extension method of the IDataReader interface. This method will transform the returned result set into a generic List

OMG dude! Code on interfaces? Yes… I’ll burn in hell, I know it :)

public static List<T> ToList<T>(this IDataReader dr)
{
    var list = new List<T>();
    var obj = default(T);
    while (dr.Read())
    {
        obj = Activator.CreateInstance<T>();
        foreach (var prop in obj.GetType().GetProperties())
        {
            var columnDescription = getPropertyDescription(obj, prop.Name);
            if (existsDataReaderColumn(dr, columnDescription)) copyColumnValueToProperty(dr, obj, prop);
        }
        list.Add(obj);
    }
    return list;
}

static void copyColumnValueToProperty<T>(IDataReader dr, T obj, PropertyInfo prop)
{
    try
    {
        var columnDescription = getPropertyDescription(obj, prop.Name);
        var columnOrdinal = dr.GetOrdinal(columnDescription);
        var value = dr[columnOrdinal];
        var canBeNull = !prop.PropertyType.IsValueType || (Nullable.GetUnderlyingType(prop.PropertyType) != null);
        var castToType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
        if (canBeNull && value == null)
            prop.SetValue(obj, null, null);
        else
            prop.SetValue(obj, Convert.ChangeType(value, castToType, CultureInfo.InvariantCulture), null);
    }
    catch { }
}

static bool existsDataReaderColumn(IDataReader dr, string propertyName)
{
    try
    {
        var obj = dr[propertyName];
        return true;
    }
    catch { return false; }
}

private static string getPropertyDescription(object value, string propname)
{
    var propinfo = value.GetType().GetProperty(propname);
    var attributes =
        (OLAPMemberNameAttribute[])propinfo.GetCustomAttributes(
        typeof(OLAPMemberNameAttribute), false);
    if (attributes != null && attributes.Length > 0)
        return attributes[0].Description;
    else
        return value.ToString();
}

As you can see the method iterates over the reader’s data and uses reflection to create an object per each row. Then map each column’s value to a property and add the new object to the returned collection. Easy!

Hope you enjoy it!