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

This article is an updated version of the one by Douglas Hohensee. You will understand how to build a custom data provider for the Silverlight xamPivotGrid in versions 11.2 and 12.1. Here the same approach for connecting to the SSAS server is used; however there are some structural changes in the provided solution in order to achieve clearer separation of logical components.

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.

Solution Projects

The attached sample solution contains four projects:

· Infragistics.Olap.Adomd.Service – a class library project containing the AdomdConnector class which is responsible for connecting to the SSAS server and retrieving the data.

· CustomDataProvider.Web – a web site project that hosts the service and also the Silverlight application where the xamPivotGrid is used.

· Infragistics.Olap.Adomd – a Silverlight class library that contains implementation of the custom data provider itself (AdomdDataProvider). This project has a service reference to the Infragistics.Olap.Adomd.Service.AdomdConnector service.

· CustomDataProvider – a Silverlight application where the AdomdDataProvider is used with a xamPivotGrid control.

If you keep on reading you will see what the exact role of each of these projects is.

The Service

The AdomdConnector class from the Infragistics.Olap.Adomd.Service project is responsible for executing queries against the server on behalf of the data provider. It is marked with the ServiceContract attribute and its methods with the OperationContract attribute. In each one of these methods a connection to the analysis server is opened and the requested information is retrieved. However this information is contained in classes defined in the Microsoft.AnalysisServices.AdomdClient assembly, so some simple classes where this information will be copied and transferred over the network are needed. These can be found in the Framework folder. 

As an example we can take a look at the LoadSchemaAsync() method which initializes a connection to the database:

 1: [OperationContract]

 2: public string LoadSchema(string connectionString)

 3: {

 4:  

 5:     Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn =

 6:         new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(connectionString);

 7:  

 8:     conn.Open();

 9:  

 10:     return conn.Database;

 11: }

The Web Project

This project hosts the service so it has to reference its project or output assembly. It is also used for hosting the Silverlight application.

In order to host the service inside the web project, add a new WCF service to the project and name it AdomdConnector.svc. Delete all generated files without the .svc file and replace its content with the following:

<%@ ServiceHost Language="C#" Debug="true" 

Service="Infragistics.Olap.Adomd.Service.AdomdConnector" %> 

The Data Provider

This Infragistics.Olap.Adomd project has a service reference to the AdomdConnector service hosted in the web project through which data will be downloaded from the server. It contains the AdomdDataProvider class, which is an XmlaDataProvider derived class that overrides all the Load…Async() methods. In each of these methods an AdomdConnectorClient is instantiated and the corresponding operation on the service is invoked. Additionally an event handler is attached to the completed event of the operation. It performs the necessary actions when the data is returned. For most of the methods this is first to create the necessary element for example dimension, hierarchy, level etc. These are classes found in the Infragistics.Olap.Data.Xmla.SqlAnalysisServices2008 namespace. For Level and Member though, you need to create your own classes that inherit from the ones in the namespace mentioned above. This is needed for the correct operation of the data provider. For the Level class you need to add a constructor that initializes the MembersCount property and for Member you need to implement the IsLoaded property.

After the necessary data structures are created in the completed event for an operation, you need to call the method on the XmlaDataProvider class that will add the data to the provider. For example in the LoadSchemaCompleted event handler you need to call the AddDataBase() method. Finally call the respective OnLoad…Completed() method:

 1: public override void LoadSchemaAsync()

 2: {

 3:     AdomdConnectorClient client = new AdomdConnectorClient();

 4:     client.LoadSchemaCompleted += (s, args) =>

 5:     {

 6:         Infragistics.Olap.Data.Xmla.SqlAnalysisServices2008.OlapSchema schema =

 7:         new Infragistics.Olap.Data.Xmla.SqlAnalysisServices2008.OlapSchema();

 8:  

 9:         if (args.Error == null && !args.Cancelled)

 10:         {

 11:             string databaseName = args.Result;

 12:             AddDatabase(schema, new Database(databaseName, databaseName, databaseName, databaseName));

 13:         }

 14:  

 15:         this.OnLoadSchemaCompleted(new LoadSchemaCompletedEventArgs(schema, args.Error, args.Cancelled, schema));

 16:     };

 17:  

 18:     client.LoadSchemaAsync(this.ConnectionString);

 19: }

Apart from the AdomdDataProvider class, this project contains three more key classes. The AdomdCommand class and its ExecuteCommandAsync method in particular is responsible for executing a query on the server and returning a result (column and row axes and data cells). The other two classes are the AdomdConnectionSettings and the AdomdConnection whose main purpose is to provide a connection string to the data provider.

The Silverlight Application

The Silverlight client is where a xamPivotGrid is used with the custom data provider. Note that you should set the Data Source and the Initial Catalog of the connection string to match your database name and server. After you do this, you should be ready to run the project and see the data loaded in the pivot grid.