Connecting to SSAS Cube using Silverlight

Last week when I was watching a presentation of a third party reporting product, I saw some nice animated charts. I wanted to create something similar to those charts and when I checked on SSRS, unfortunately what I wanted isn't possible with SSRS. So I moved into Silverlight Charts and I was really surprised with what I can do with Silverlight chart control. But today I am not going to write about Silverlight Chart control, I am going to write about how to grab data from a SSAS Cube using Silverlight and then we can display on any Silverlight reporting control. And today I will be displaying data through a Silverlight chart control.

I will be using ADOMD.NET and sometimes back I wrote a post about ADOMD.NET. So I will not be covering the basics on ADOMD.NET in this post.

I will start off by creating a Silverlight application. Please make sure to install Silverlight toolkit. As I know Silverlight chart control was initially release with Silverlight 3 toolkit. The version I am using is Silverlight 5 with it's toolkit. After creating the Silverlight project, now I have a solution which has two projects, one is Silverlight project and the other is a web project to host my Silverlight application.

As you might already know I can’t use ADOMD.NET in the Silverlight project. Because I can’t add a reference to dlls which was not built against the Silverlight runtime and ADOMD.NET dlls are not build against Silverlight runtime. So for that what I can do is add needed ADOMD.NET references to my web project and somehow use them from my Silverlight project. Since I can only add reference to Silverlight projects from my Silverlight project, what I am going to do is I am going to create a WCF service in my web application and I am going to expose functions there, so my Silverlight project can consume them. Hope you all got a overall idea of what I am going to do here.

I am using ADOMD.NET Client Programming and I have added a reference to “Microsoft.AnalysisServices.AdomdClient.dll” which is located at,

C:\Program Files\Microsoft.NET\ADOMD.NET\100

Now I am creating a WCF service named “CubeConnector”.

ICubeConnector.cs

using System.Collections.Generic;

using System.ServiceModel;

namespace SilverlightApplication1.Web

{

    [ServiceContract]

    public interface ICubeConnector

    {

        [OperationContract]

        IEnumerable<Dictionary<string, object>> GetData(string query);

    }

}

CubeConnector.svc

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using Microsoft.AnalysisServices.AdomdClient;

namespace SilverlightApplication1.Web

{

    public class CubeConnector : ICubeConnector

    {

        public IEnumerable<Dictionary<string, object>> GetData(string query)

        {

            var table = GetDataTable(query);

            var columns = table.Columns.Cast<DataColumn>();

            return table.AsEnumerable().Select(r => columns.Select(c => new { Column = c.ColumnName, Value = r[c.ColumnName] })

                             .ToDictionary(i => i.Column, i => i.Value != DBNull.Value ? i.Value : null));

        }

        private DataTable GetDataTable(string query)

        {

            AdomdConnection conn = new AdomdConnection("Data Source=localhost; Catalog=MyCube");

            AdomdDataAdapter adapter = new AdomdDataAdapter();
            adapter.SelectCommand = new AdomdCommand(query, conn);
            var table = new DataTable();
            conn.Open();

            try

            {

                adapter.Fill(table);

            }

            finally

            {

                conn.Close();

            }

            return table;

        }

    }

}

I have exposed a operation contract which is GetData and it returns a “IEnumerable<Dictionary<string, object>>” and accepts a query of type “string”.

Now in my Silverlight application I have added a Service reference to the created WCF service. I have also added a Chart control to the MainPage.xaml. To my GetData operation contract in WCF Service, I need to pass a query. I am passing a MDX query to query the SSAS cube. In here my query will give me the following result and it’s what I wanted in the chart.

Query Result

In the chart Y axis, I need to show the amount as a currency. So I am adding some styling to the “Y axis” of my chart and to the tooltip (I am writing down the styling code, to make the example complete.).

<UserControl.Resources>

    <Style x:Name="CustomDataPoint" TargetType="toolkit:ColumnDataPoint">

        <Setter Property="DependentValueStringFormat" Value="{}{0:C}"/>

    </Style>

    <Style x:Name="CustomAxisLabel" TargetType="toolkit:AxisLabel">

        <Setter Property="StringFormat" Value="{}{0:C}"/>

    </Style>

</UserControl.Resources>

This is my chart with styles applied.

<toolkit:Chart Name="MyChart" Title="Amount by Account Types" HorizontalAlignment="Left" Margin="10,10,0,0" VerticalAlignment="Top" Height="479" Width="714" >

    <toolkit:ColumnSeries Title="Amount" DependentValueBinding="{Binding SeriesValue}" IndependentValueBinding="{Binding SeriesName}" AnimationSequence="FirstToLast" IsSelectionEnabled="True" DataPointStyle="{StaticResource CustomDataPoint}" RenderTransformOrigin="0.481,0.467"/>

    <toolkit:Chart.Axes>

        <toolkit:LinearAxis ShowGridLines="True" Orientation="Y" AxisLabelStyle="{StaticResource CustomAxisLabel}"></toolkit:LinearAxis>

    </toolkit:Chart.Axes>

</toolkit:Chart>

Now moving back to code behind, I am passing my MDX query and I am calling the WCF service.

using System;

using System.Collections.Generic;

using System.Windows.Controls;

using System.Windows.Controls.DataVisualization.Charting;

namespace SilverlightApplication1

{

    public partial class MainPage : UserControl

    {

        public MainPage()

        {

            InitializeComponent();

            svcCubeConnector.CubeConnectorClient client = new svcCubeConnector.CubeConnectorClient();

            client.GetDataCompleted += client_GetDataCompleted;

            client.GetDataAsync("SELECT NON EMPTY { [Measures].[AMOUNT] } ON COLUMNS, NON EMPTY { ([Account Type].[ACCOUNT DESC].[ACCOUNT DESC].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM [MyCube]");

        }

        void client_GetDataCompleted(object sender, svcCubeConnector.GetDataCompletedEventArgs e)

        {

            IEnumerable<Dictionary<string, Object>> result = e.Result;

            List<ChartClass> chartCollection = new List<ChartClass>();

            object seriesName;

            object seriesValue;

            foreach (Dictionary<String, Object> _item in result)

            {

                // I am querying Dictionary with the Key

                _item.TryGetValue("[Account Type].[ACCOUNT DESC].[ACCOUNT DESC].[MEMBER_CAPTION]", out seriesName);

                _item.TryGetValue("[Measures].[AMOUNT]", out seriesValue);

                chartCollection.Add(new ChartClass { SeriesName = seriesName.ToString(), SeriesValue = Convert.ToDouble(seriesValue) });

            }

            ((ColumnSeries)MyChart.Series[0]).ItemsSource = chartCollection;

        }

    }

    public class ChartClass

    {

        public string SeriesName { get; set; }

        public double SeriesValue { get; set; }

    }

}

Here I am calling the GetData method using event-based asynchronous pattern (For more information on  event-based asynchronous pattern and Asynchronous Operations in WCF, read Asynchronous Operations in WCF). In GetDataCompleted event, I am querying the result and I am binding the Silverlight Chart to a collection (here I am querying the result using hard coded keys which is not a good practise).

That’s all. Finally I am getting the following chart.

Chart

I have uploaded the full example to my SkyDrive. Appreciate your feedback.



Happy Coding.

Regards,
Jaliya