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:

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:

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>

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:

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

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! ;)

Serializar listas genéricas en aplicaciones WinRT

andorraenamora

Edit (11/03/2013): Gracias a una sugerencia del colega MVP Javier Torrecilla se ha modificado el código para especificar el Encoding (UTF8Encoding) y así evitar problemas. Thx dude! :)

 

Hola de nuevo,

Rompiendo un poco con la serie de posts sobre las ‘Parallel Series’, hoy quiero escribir acerca de algo totalmente distinto. Y es que ando haciendo mis pinitos con mi primera aplicación Windows Store, y me estoy encontrando con bastantes cosas que no conozco, y que desde mi absoluto desconocimiento de la plataforma, encuentro bastante tediosas de realizar.

Hoy por ejemplo estaba tratando de almacenar ciertos datos en local -ya que para esta aplicación no quiero depender de ninguna base de datos porque en realidad son cuatro datos- y no he encontrado una forma directa de persistir listas genéricas en el sistema de ficheros local.

Si que es cierto que las herramientas están ahí para ser usadas ‘a mano’, pero he echado en falta una forma más sencilla de hacerlo (que no quiere decir que no exista), de modo que he creado una clase para facilitarme el proceso.

Su propósito es muy específico, pero básicamente permite guardar y leer cualquier lista genérica List<T> a un fichero, que puede ser guardado en la carpeta Local, Temporal o de Roaming.

La clase es la siguiente (aunque supongo que es muy mejorable):

using System;
using System.Collections.Generic;
using System.IO;
using System.Threading.Tasks;
using System.Xml.Serialization;
using Windows.Storage;

namespace MySampleApp.Domain
{
    public class LocalStorage<T>
    {
        public List<T> Data { get; set; }
        public string Filename { get; set; }
        public StorageFolder StorageFolder { get; set; }

        public LocalStorage(string filename)
        {
            Filename = filename;
            Data = new List<T>();
            StorageFolder = ApplicationData.Current.LocalFolder;
        }

        public async Task SaveAsync()
        {
            if (!await fileExistAsync(Filename))
            {
                await StorageFolder.CreateFileAsync(
                    Filename, CreationCollisionOption.ReplaceExisting);
            }
            await saveToLocalStorageAsync();
        }

        public async Task<List<T>> LoadAsync()
        {
            if (await fileExistAsync(Filename))
            {
                return await loadFromLocalStorageAsync();
            }
            else
            {
                await StorageFolder.CreateFileAsync(Filename);
                return null;
            }
        }

        public async void DeleteAsync()
        {
            try
            {
                if (await fileExistAsync(Filename))
                {
                    var file = await StorageFolder.GetFileAsync(Filename);
                    if (file != null) await file.DeleteAsync();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        private async Task<bool> fileExistAsync(string fileName)
        {
            try
            {
                await StorageFolder.GetFileAsync(fileName);
                return true;
            }
            catch
            {
                return false;
            }
        }

        private async Task saveToLocalStorageAsync()
        {
            var sessionFile = await StorageFolder.CreateFileAsync(
                Filename, CreationCollisionOption.ReplaceExisting);
            using (var sessionRandomAccess = await sessionFile.OpenStreamForWriteAsync())
            {
                var serializer = new XmlSerializer(typeof(List<T>), new Type[] { typeof(T) });
                serializer.Serialize(new StreamWriter(sessionStream, new UTF8Encoding()), Data);
                await sessionStream.FlushAsync();
            }
        }

        private async Task<List<T>> loadFromLocalStorageAsync()
        {
            var sessionFile = await StorageFolder.CreateFileAsync(
                Filename, CreationCollisionOption.OpenIfExists);
            if (sessionFile == null) return null;
            using (var sessionInputStream = await sessionFile.OpenReadAsync())
            {
                var reader = XmlReader.Create(new StreamReader(
                    await sessionFile.OpenStreamForReadAsync(), new UTF8Encoding()));
                var serializer = new XmlSerializer(typeof(List<T>), new Type[] { typeof(T) });
                Data = (List<T>)serializer.Deserialize(reader);
                return Data;
            }
        }
    }
}

Y para utilizarla basta con disponer de una lista de objetos de un tipo T:

var customers = new List<Customer>();
customers.AddRange(getSampleCustomers());

Y posteriormente crear una instancia de LocalStorage:

var storage = new MySampleApp.Domain.LocalStorage<Customer>("customers.xml");
//Opcionalmente podemos cambiar la carpeta de destino (por defecto es LocalStorage)
storage.StorageFolder = Windows.Storage.ApplicationData.Current.RoamingFolder;
//Añadimos los datos a persistir
storage.Data.AddRange(customers );
//Guardar los datos al fichero customers.xml
await storage.SaveAsync();
//Recuperar los datos del fichero customers.xml
await storage.LoadAsync();
//Eliminar el fichero customers.xml
storage.DeleteAsync();

Si alguien tiene alguna sugerencia o mejora que no dude en escribir un comentario.

Nos vemos! :)