It’s only days before Telerik’s RadPivotGrid for ASP.NET Ajax will be officially out and about. Till now the control’s most prominent feature was its ability to provide reports of hard to dissect data into visually appealing and analytically meaningful summaries addressing real business-oriented requirements. Well, that’s excellent as far as it goes but does it go that far? For what is a pivot grid without support for Online Analytical Processing data sources. Frankly speaking: a one-winged bird. And that was the reason why the control in spite of its powerful data-engine, well-structured UI and carefully-thought-out API was still in its beta stage. So, in the case of RadPivotGrid, moving from beta to the level of an official stable release means only one thing – provide support for OLAP. And that’s exactly the main improvement that RadPivotGrid will be headlining in the Q1 2013 release.

Now, let’s delve a bit into the more technical side of the story. The OLAP data sources supported by RadPivotGrid are Microsoft Analysis Services cubes. With those, all calculations, data management and analytical processing are performed on the server whereas RadPivotGrid serves as a mediator that talks to the multi-dimensional data source in order to display the desired report. The communication of messages back and forth is done using web standards – HTTP, SOAP, and XML. The query language used is MDX - the most commonly used multi-dimensional expression language today.

RadPivotGrid supports a couple of data providers: Adomd and Xmla.

Binding Through XMLA

All settings RadPivotGrid needs to configure its data providers are assembled into a property collection called OlapSettings. The property most prominent in there is the ProviderType that, as its name implies, specifies whether the communication with the OLAP data source should be done through Xmla or Adomd. Then comes the XmlaConnectionSettings property collection that allows to set the name of the Cube, the exact name of the DataBase and any specific authentication details in an

XmlaNetworkCredential object. Here is quick example: the following configuration will give RadPivotGrid Xmla access to the Adventure Works cube at the OLAP server address given below:

Binding Through Adomd

ADOMD.NET is a Microsoft .NET Framework data provider that is designed to communicate with Microsoft SQL Server Analysis Services. ADOMD.NET uses the XML for Analysis protocol to communicate with analytical data sources. More information about ADOMD can be found here.

If you prefer binding RadPivotGrid to OLAP data sources using Adomd, the configuration of the OLAP settings is analogous to the Xmla one described above: the ProviderType should be Adomd whereas the AdomdConnectionSettings should have its Cube, DataBase and ConnectionString assigned. The latter expects an OLE DB connection string format. As expected the Credentials property is optional and serves the role stated for the Xmla configuration. The following mark-up will provide the information necessary for the ADOMD.NET provider to connect to the Adventure Works cube at the OLAP server address specified:

OLAP Browser 

RadPivotGrid gives you not only access to the data in an OLAP cube but to the structure of that data as well. By setting RadPivotGrid. EnableConfigurationPanel property to true you will find at your hands a nifty tool, name it an Olap Browser or Hierarchical Fields List - the technical term is not that important. What is of topical significance is its ability to present valuable information about the structure of the cube and allow for drag-and-drop of measures and dimensions so that your report is customized to the very last detail that suits your business analytical needs.

And last but not least, the Hierarchical Field List is optimized to load on demand the structure of  the Olap data source - for to deliver more than expected with an ASP.NET Ajax control is nothing else but to proficiently tip toe on that delicate line between richness in functionality and speed of performance.

So, OLAP data reports for quick analysis of business trends, visually pleasing graphic representations of KPIs, a browser tool for OLAP cubes and a pinch of Telerik’s commitment to its motto – that’s what RadPivotGrid will be shipping with in the Q1 2013 release. This, of course, is not the end of the story and we'd be glad as always to have your help in terms of feedback and feature suggestions.

For an online example, please, visit the address here.