How To: Get data from OLAP cubes


cuberubikToday I had to read some data from different OLAP cubes and show this information in our application. This could be accomplished using ADOMD.NET client library, which is the multidimensional equivalent to the ADO.NET libraries.

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

Maybe the easiest way to read data is using the class AdomdDataReader. This object retrieves a read-only, forward-only, flattened stream of data from an analytical data source, and also implements the IDataReader interface (like the good-old DataReader).

In non dynamic scenarios, we developers, tend to use ORMs like NHibertate, 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<T> of objects.

Let’s see how it works before dig 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();
}

First of all we have a SQL script that returns some sample data of 3 different measures year by year (gains in period, gains year-to-date and the amount of money).

mddata

And we’d like to translate these data into a generic List of my own DTO:

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

view raw
Adomd_DTO.cs
hosted with ❤ by GitHub

Notice that in C# properties can not 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<T> Method. This method is just an extension method of the AdomdConnection object (Dapper style) and it’s 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<MyResultDTO>
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>();
}

view raw
Adomd_Query.cs
hosted with ❤ by GitHub

We’ll focus on the third one. Let’s create another extension method of the IDataReader interface. OMG! Code on interfaces? Yes… I’ll burn in hell, I know it… :D

This is the extension method with some auxiliary functions:

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();
}

EDIT (2018-12-11) : Added custom attribute to the post, used to decorate our DTO.

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

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

adomd_result

(*) image courtesy of oz-code, the best debugger addin for visual studio #imho ;)

Hope this helps! ;)

Published by

Lluis Franco

The one who loves programming. In love with C#, Java, F#, SQL, async patterns. Blogger, Speaker, Geek & family guy living in Andorra. Microsoft MVP 2002-2016

2 thoughts on “How To: Get data from OLAP cubes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s