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

How to: Retrieve all controls in a form, using generics (II) – Linq at rescue!

Hi again,

In my previous post, I created a recursive function to retrieve all controls inside a form and its containers. Today, my colleague and friend Eduard Tomàs (software architect @ RAONA) has posted another solution to the same topic based on Linq. It’s quite simply and really, really pretty:

tshirt

A new approach:

Before starting, we need to solve this: Linq works over IEnumerable<T>, but Control.Controls property returns a ControlCollection type. In fact, nowadays, since we have Generics this class has no sense (like other 1.000 similar classes), but remember that Generics doesn’t appeared until .NET Framework 2.0. So, our first step will be retrieve an IEnumerable<Control> from a ControlCollection. With using an extensor method this will be really easy:

public static IEnumerable<Control> AsEnumerable
    (this Control.ControlCollection @this)
{
    foreach (var control in @this)
        yield return (Control)control;
}

Note that using this code we are able to transform CollectionControl to IEnumerable<Control>, and get full access to the power of Linq. Now, let’s create a method to retrieve all controls of a type, as follows:

public static IEnumerable<T>
    GetAllControls<T>(this Control @this) where T : Control
{
    return @this.Controls.AsEnumerable().Where(x => x.GetType() == typeof(T)).
    Select(y=>(T)y).
        Union(@this.Controls.AsEnumerable().
        SelectMany(x => GetAllControls<T>(x)).
        Select(y=>(T)y));
}

It looks cool, isn’t it? No loops, no ifs… only pure Linq power! :-)

There’s a small difference with my original solution and this new one. In my original solution I used an internal List<T> to copy all controls references. The new one only iterates over the original collection. There’s no internal lists. This is the power of Linq.

Another difference present in Linq solution is: We are returning an IEnumerable, so, we loose the ForEach method (because IEnumerable doesn’t implements this method). But building our own ForEach method is trivial:

public static void ForEach<T>
    (this IEnumerable<T> @this, Action<T> action)
{
    foreach (T t in @this)
    {
        action(t);
    }
}

HYEI, happy coding!

November 2010

How to: Retrieve all controls in a form, using generics (I)

Note: This is a very common question in MSDN forums. For this reason I decided to write this post, and use it for reference in future questions.

tshirt

The typical question: How to clear the content of all the textboxes in a form?

Answer: Using generics it’s really easy… First of all, let’s create an extensor method, that will returns a collection of all the controls of a type in a Form (or container). And after, we will use this collection to do an action over each returned item.

The extensor method:

public static List<T> GetControls<T>(this Control container) where T : Control
{
    List<T> controls = new List<T>();
    foreach (Control c in container.Controls)
    {
        if (c is T)
            controls.Add((T)c);
        controls.AddRange(GetControls<T>(c));
    }
    return controls;
}

This method retrieves the collection of Controls of a control and then, it call itself recursively, retrieving the content of all his containers.

How to use it:

this.GetControls<TextBox>().ForEach(p => p.Text = string.Empty);

In this code, we will use the extensor method directly in a form (because Form class inherits from ContainerControl, that inherits from ScrollableControl, and ScrollableControl from Control). Or, in other words, our Form will implement our extensor method. So, at compile time, we should specify the type of the controls we want to retrieve (in the example we use TextBox, but you can use Button type instead), and then, use ForEach to apply an action on each one of the items returned.

Moreover, if we want to retrieve only the controls into a container (GroupBox, Panel, TabControl or another), we should call the method for this particular control.

this.GroupBox1.GetControls<TextBox>().ForEach(p => p.Text = "hello");
this.Panel1.GetControls<TextBox>().ForEach(p => p.Text = string.Empty);

Applying several actions on each control:

In most cases, we would apply several actions to each control (not only one). In this case, it’s quite easy: The only thing we have to do is create a method that receives a parameter of this type, and call it passing the control as an argument:

this.GetControls<TextBox>().ForEach(p => ApplyFormat(p));

private void ApplyFormat(TextBox text)
{
    text.BackColor = Color.LightGray;
    text.ForeColor = Color.Red;
    text.Text = "hello";
    text.TextAlign = HorizontalAlignment.Center;
}

Or maybe, using an ‘action delegate’, both options are correct:

this.GetControls<TextBox>().ForEach(p =>
{
    p.BackColor = Color.LightGray;
    p.ForeColor = Color.Red;
    p.Text = "hello";
    p.TextAlign = HorizontalAlignment.Center;
});

Happy coding! :-)

November 2010