Building a Custom Data Provider for xamPivotGrid in Silverlight using WCF and ADOMD.NET

The xamPivotGrid control supports custom data providers, but not every developer knows how to build one. In this article, I’ll go over the steps for building a custom data provider that uses WCF and ADOMD to access a SQL Analysis Server.

After following these steps, you should have a better understanding of what goes into a custom data provider, and should be able to extend this project to your own applications.

If you’re already comfortable with WCF and ADOMD, feel free to download the finished sample and dive right into the code yourself.

Before You Begin

Be sure to install the Microsoft ADOMD.NET Client, as well as the following components from the SQL Server 2008 R2 Feature Pack:

- MS SQL Server R2 ADOMD.NET

- MS Analysis Services OLE DB Provider for Microsoft SQL Server 2008 R2

This project will connect to the Adventure Works DW 2008 database, but you can change the parameters to connect to the database of your choice.

Configuring the Web Project

First, I’ll describe how to set up the web project. Here you must create data structures to store database information, as well a web service to retrieve that information.

Create a new Silverlight project. Remember to enable WCF RIA Services.

Once the application is open in the IDE, you can create a new folder named Framework in the CustomDataProvider.Web project. The purpose of this folder is to hold the custom classes (Cube, Dimension, Hierarchy, etc.) that store the OLAP cube information received via the web service.

To make life simpler, download the finished sample and use the provided class files.

Next, add a WCF service to the CustomDataProvider.Web project named AdomdConnector.svc.

In AdomdConnector.svc.cs, you must define several contract methods that employ the ADOMD client. The simplest is LoadSchema, which instantiates an AdomdConnection object, then returns the name of the database.

    public class AdomdConnector
    {
        [OperationContract]
        public string LoadSchema(string connectionString)
        {
            Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn =
                new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(connectionString);

            conn.Open();

            return conn.Database;
        }
        // ...
    }

You’ll need to define methods to load other parts of the data as well. See the finished sample for implementations of these methods.

Configuring the Silverlight Project

The next step is to configure the Silverlight portion of the solution. Here, I’ll describe how to build a custom data provider, using the WCF/ADOMD service from the previous section as a launch point.

Create the Data Provider

Start by adding the necessary references for xamPivotGrid.

Next, right-click on the Silverlight project (CustomDataProvider) and add a service reference to the WCF service we defined previously (AdomdConnector.svc).

Note: Click on the “Discover” button to locate available services in the project.

The next step is to create the custom data provider. Start by creating a new class named AdomdDataProvider. This class inherits from XmlaDataProvider.

There are several asynchronous “load” methods that you must implement for AdomdDataProvider, each corresponding to a “load” method of the AdomdConnector web service. Fundamentally, each of these methods must instantiate an AdomdConnectorClient object, then use that object to call the associated “load” method of AdomdConnector. Before doing so, you must assign logic to handle the web service’s corresponding “Completed” event.

Download the finished sample, and inspect the following four files carefully:

· AdomdCommand.cs

· AdomdConnection.cs

· AdomdConnectionSettings.cs

· AdomdDataProvider.cs.

Building the UI

Now you can add an instance of xamPivotGrid to your XAML code and hook it up to the brand new data provider.

First, add these namespace references to MainPage.xaml:

    xmlns:ig="http://schemas.infragistics.com/xaml"
    xmlns:olap="http://schemas.infragistics.com/olap"
    xmlns:CustomDataProvider="clr-namespace:CustomDataProvider"

Then, define an instance of XmlaDataSource in UserControl.Resources, using your custom data provider.

Note: You must modify the ConnectionString according to the parameters of your own SSAS server.

    <UserControl.Resources>
        <olap:XmlaDataSource x:Key="adomdDataSource" 
                             Database="Adventure Works DW 2008R2" 
                             Cube="Adventure Works"
                             Rows="[Date].[Calendar]"
                             Columns="[Product].[Product Categories]"
                             Measures="Internet Sales Amount"
                             >
            <olap:XmlaDataSource.ConnectionSettings>
                <CustomDataProvider:AdomdConnectionSettings
                    ConnectionString="Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Adventure Works DW 2008;Data Source=igbgsofsltest;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error"
                    />
            </olap:XmlaDataSource.ConnectionSettings>
        </olap:XmlaDataSource>
    </UserControl.Resources>

In the layout root, add an instance of xamPivotGrid.

    <Grid x:Name="LayoutRoot" Background="White">
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="*"/>
            <ColumnDefinition Width="Auto"/>
        </Grid.ColumnDefinitions>

        <ig:XamPivotGrid DataSource="{StaticResource adomdDataSource}" />

        <ig:XamPivotDataSelector Grid.Column="1" DataSource="{StaticResource adomdDataSource}"/>
    </Grid>

When you compile and run the application, you should see something like this:

Congratulations! You are now in possession of a working, custom data provider for xamPivotGrid.

Remember, this sample uses an instance of the Adventure Works DW 2008 database on a local server. You’ll need to change the ConnectionString’s “Data Source” and “Initial Catalog” parameters to match your own database and server, or it won’t work!