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

Apps for SharePoint : Map App (Bing Maps inside SharePoint) v1.0.0.2

Map App version 1.0.0.2 has been released. This version will contain the following new enhancements and new features in addition to previous version’s features.

Enhancements

• Updated UI – Inherits the site design

New Features

• Introducing Directions Module
• Get Directions
• Drag and Customize the Routes

Updated UI

Updated UI

Updated UI

Updated UI - Directions Module

Updated UI - Directions Module (Drag)

Enjoy the app in your SharePoint 2013 environment and appreciate your feedback.

Happy Coding.

Regards,
Jaliya

Asynchronous Operations in WCF

I have been writing many posts about asynchronous operations and thought to write about asynchronous operations in WCF. In WCF, asynchronous operations can be implemented by using one of the three following methods:

1. The event-based asynchronous pattern
2. The IAsyncResult asynchronous pattern
3. The task-based asynchronous pattern

Today I am going to explain how to implement asynchronous operations in WCF using each of these methods using some examples. In here I will not be covering the basics of WCF and I assume you all have some experience with programming WCF before. So I will start off with the event-based pattern.

1. The event-based asynchronous pattern

I have created a WCF Service Application and I have the default WCF service and it’s interface and I am modifying the service and it's contract as follows.

iService1.cs

[ServiceContract]

public interface IService1

{

    //event-based asynchronous pattern

    [OperationContract]

    string GetData(string message);

}

Service1.cs

public class Service1 : IService1

{

    public string GetData(string message)

    {

        Thread.Sleep(5000);

        return message;

    }

}

As you can see, In my service contract I have exposed a very simple method which will accept string and return a string. In my service I have it implemented and I have put a thread sleep, just for demonstration purposes.

Now I have my client console application and I am adding a Service Reference to this service. A thing to note here is I am enabling "Allow generation of asynchronous operations" and under that I am enabling "Generate asynchronous operations".

Add Service Reference

Generate Asynchronous Operations

When this is selected only, the proxy class will contain relevant methods for asynchronous operations for both event-based asynchronous pattern and IAsyncResult asynchronous pattern.

Now let’s see the client side code.

class Program

{

   static wsService1.Service1Client client1 = new wsService1.Service1Client();

   static void Main(string[] args)

   {

       client1.GetDataCompleted += client_GetDataCompleted;

       client1.GetDataAsync("event-based asynchronous pattern");

       Console.WriteLine("Waiting for async operation...");

       Console.ReadLine();

   }

   static void client_GetDataCompleted(object sender, wsService1.GetDataCompletedEventArgs e)

   {

       Console.WriteLine(e.Result.ToString());

   }

}

In here what happens is first I am subscribing to the GetDataCompleted method. When the GetDataAsync Method has completed, client_GetDataCompleted event will be triggered. If you notice the control flow here, the operations in the server are still done synchronously, but operations in the client are done asynchronously.

Finally the output will be as follows, first it will print the “Waiting for async operation” and then it will print the message returned from the service.

Output

2. The IAsyncResult asynchronous pattern

The second way of implementing WCF service operation in an asynchronous fashion is marking the <Begin> method with the AsyncPattern property set to true. In this case, the asynchronous operation is exposed with two additional methods;

• a method to start asynchronous operation (a method which has “Begin” pre fixed to original method name)
• a method to end asynchronous operation (a method which has “End” pre fixed to original method name).

We can further divide this IAsyncResult asynchronous pattern into two sub models. That is,

1. Client Side Asynchronous
2. Both Side Asynchronous (Client & Server Side Asynchronous)

2.1. Client Side Asynchronous

For this Client-Side Asynchronous model, I will take the same WCF Service I got for demonstrating event-based asynchronous pattern.

iService1.cs

[ServiceContract]

public interface IService1

{

    //event-based asynchronous pattern

    [OperationContract]

    string GetData(string message);

}

Service1.cs

public class Service1 : IService1
{

   public string GetData(string message)

   {

       Thread.Sleep(5000);

       return message;

   }

}

Now in my client console application I am using the same service reference.

class Program

{

    static wsService1.Service1Client client1 = new wsService1.Service1Client();

    static void Main(string[] args)

    {

        client1.BeginGetData("IAsyncResult asynchronous pattern (Client-Side)", new AsyncCallback(GetDataCallBack), null);

        Console.WriteLine("Waiting for async operation...");

        Console.ReadLine();

    }

    static void GetDataCallBack(IAsyncResult result)

    {

        Console.WriteLine(client1.EndGetData(result).ToString());

    }

}

In here, I am calling the BeginGetData method which is generated by the proxy. It will always have it’s first parameter as the same parametes in the method I am calling asynchronously, plus two additional parameters. The second parameter is an AsyncCallback delegate that references a method to be called when the BeginGetData completes.The third parameter is a object which contain state about the asynchronous operation. So when my asynchronous BeginGetData completed, my callback method will be called. In my callback method I am calling the EndGetDate method (again which is generated by the proxy) to end the asynchronous operation and to retrieve the results.

Again the output will be as follows: first it will print the “Waiting for async operation” and then will print the message returned from the service.

Output

2.2. Both Side Asynchronous

The Both-Side Asynchronous model of IAsyncResult asynchronous pattern is little bit different. In here we have both client side and server side operations asynchronous. If you have been reading the post carefully, when I am introducing the IAsyncResult asynchronous pattern, I have mentioned about setting AsyncPattern property to true to the begin method. And to above Client Side Asynchronous model of IAsyncResult asynchronous pattern, I didn’t do that. Now let’s take a look at the following model.

I am creating a new WCF service.

iService2.cs

[ServiceContract]

public interface IService2

{

    //IAsyncResult asynchronous pattern

    [OperationContractAttribute(AsyncPattern = true)]

    IAsyncResult BeginWorkerMethod(string message, AsyncCallback callback, object asyncState);

    string EndWorkerMethod(IAsyncResult result);

}

Service2.cs

public class Service2 : IService2

{

    public IAsyncResult BeginWorkerMethod(string message, AsyncCallback callback, object asyncState)

    {

        var task = Task<string>.Factory.StartNew((res) => MyMethod(asyncState,message), asyncState);

        return task.ContinueWith(res => callback(task));

    }

    public string EndWorkerMethod(IAsyncResult result)

    {

        return ((Task<string>)result).Result;

    }

    private string MyMethod(object asyncState,string message)

    {

        Thread.Sleep(5000);

        return message;

    }

}

In my Service contract, I have defined two methods using the pattern BeginOperation and EndOperation. Please note that here I have applied AsyncPattern property to true only to the BeginWorkerMethod.

In the service, I have implemented these two methods. In here I have used Tasks for asynchronous operation. I have a callback method which will be called when the task is completed.

And in the client side, I have the following code.

class Program

{

    static wsService2.Service2Client client2 = new wsService2.Service2Client();

    static void Main(string[] args)

    {

        client2.BeginWorkerMethod("IAsyncResult asynchronous pattern (Server-Side)", new AsyncCallback(GetDataCallBack), null);

        Console.WriteLine("Waiting for async operation...");

        Console.ReadLine();

    }

    static void GetDataCallBack(IAsyncResult result)

    {

        Console.WriteLine(client2.EndWorkerMethod(result).ToString());

    }

}

The output is the same: first it will print the “Waiting for async operation” and then it will print the message returned from the service.

Output

3. The task-based asynchronous pattern

The task-based asynchronous pattern is the preferred way to implement asynchronous operations because it is the easiest and most straight forward. But the only thing is async/await was introduced with .NET framework 4.5. To demonstrate task-based asynchronous pattern, I am creating new WCF service.

iService3.cs

[ServiceContract]

public interface IService3

{

    //task-based asynchronous pattern

    [OperationContract]

    Task<string> MyWorkerMethodAsync(string message);

}

Service3.cs

public class Service3 : IService3

{

    public async Task<string> MyWorkerMethodAsync(string message)

    {

        return await Task.Factory.StartNew(() =>; MyMethod(message));

    }

    private string MyMethod(string message)

    {

        Thread.Sleep(5000);

        return message;

    }

}

Now in my client console application when I am adding a Service Reference I am enabling "Allow generation of asynchronous operations" and under that I am enabling "Generate task-based operations".

Generate Task-Based Operations

Now let’s see the client side code. Please make sure to change the Clients’ targeted framework to .NET Framework 4.5. In the client I have a async method and inside the async method, the await operator is applied to a task to suspend execution of the method until the task is completed.

class Program

{

    static wsService3.Service3Client client3 = new wsService3.Service3Client();

    static void Main(string[] args)

    {

        InvokeAsyncMethod("task-based asynchronous pattern");

        Console.WriteLine("Waiting for async operation...");

        Console.ReadLine();

    }

    static async void InvokeAsyncMethod(string message)

    {

        Console.WriteLine(await client3.MyWorkerMethodAsync(message));

    }

}

The output is the same: first it will print the “Waiting for async operation” and then will print the message returned from the service.

Output

And that's basics of asynchronous operations in WCF. I am attaching the sample code to my sky drive, you can download and play around.

Hope you got something out of this post. As always appreciate your feedback.

Happy Coding.

Regards,
Jaliya

Using async/await without .NET Framework 4.5

Sometime back I wrote a post about What's new in C# 5.0 and in there I wrote about async modifier and await operator. Though async and await was introduced with .NET 4.5, haven’t you ever wished to use them projects targeted .NET Framework 4.0, Silverlight 4 or Windows Phone 7.5. Well there were many times I wanted use async and await, but since I was on .NET 4.0, I had to stick to legacy asynchronous programming patterns.

But now we will not have to go to other choices anymore. If your project is targeted are on .NET Framework 4.0, Silverlight 4, Windows Phone 7.5 or higher versions of any of these and if you want you async/await, you can go for it using this Async Targeting Pack Nuget Package which was released by .NET Base Class Library Team.

To add a reference to this NuGet Package, right click on your project, select “Manage Nuget Packages” and search for Microsoft.Bcl.Async.

Add Nuget Package

Please make sure to select "Include Prerelease" on top left drop down and since this package is marked as prerelease software, there are some known issues and issues are expected.

You can also install this package via the Package Manager Console by running the following command:

install-package Microsoft.Bcl.Async –pre

Important thing to note is, you need to open your project using Visual Studio 2012 and set the target framework. Because this package is not supported in Visual Studio 2010 and if you open the project using Visual Studio 2010 and install the package, you will still be not able to use async/await.

Happy Coding.

Regards,
Jaliya