Monday, April 15, 2013

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.
QueryResult
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
Chart
I have uploaded the full example to my SkyDrive. Appreciate your feedback.



Happy Coding.

Regards,
Jaliya

No comments:

Post a Comment