Using XmlaDataProvider
In this article we will show how to set XmlaDataProvider to RadPivotGrid and RadPivotFieldList.
XMLA description
Extensible Markup Language for Analysis (XMLA) is a standard that allows client applications to talk to multi-dimensional or Online Analytical Processing (OLAP) data sources. The communication of messages back and forth is done using web standards – HTTP, SOAP, and XML. The query language used is MDX, which is the most commonly used multi-dimensional expression language today. Oracle's Essbase, Microsoft's Analysis Services, and SAP's NetWeaver all support the MDX language and the XMLA spec.
RadPivotGrid supports both multidimensional and tabular OLAP Cubes (except the DirectQuery mode of the Tabular cubes).
XmlaDataProvider Properties
RadPivotGrid provides XMLA access to OLAP data sources. You can use the XmlaDataProvider in your application to connect to your OLAP data source.
You have to add reference to the following Telerik assembly to be able to use XmlaDataProvider in your application: Telerik.Pivot.DataProviders.Xmla
XmlaDataProvider has several important properties that have to be defined:
-
ConnectionSettings - this property is of type XmlaConnectionSettings. It is used to define all needed connection settings through the XmlaConnectionSettings properties:
Cube - string property defining the exact name of the Cube.
Database - string property defining the exact name of the Database.
ServerAddress - string property defining the exact address(with protocol used) of the server.
Credentials - this property is of type XmlaNetworkCredential and it is used to define the authentication details for the server(if it has any kind of authentication).
You can extend the parameters passed to the OLAP cube by using QueryProperties property of XmlaConnectionSettings. QueryProperties is a collection of XmlaQueryProperty items. Each XmlaQueryProperty must have Name and Value set. Name is the parameter which you want to pass to the cube, while value is its current value. This can be used if you want to take localized data from your cube, for which you have to pass LocalIdentifier parameter and as value you have to set the value of the language:
<pivot:XmlaConnectionSettings Cube="LocalizedCube" Database="OrdersCube" ServerAddress="http://localhost/olap/msmdpump.dll">
<pivot:XmlaConnectionSettings.QueryProperties>
<pivot:XmlaQueryProperty Name="LocaleIdentifier" Value="1036"/>
</pivot:XmlaConnectionSettings.QueryProperties>
</pivot:XmlaConnectionSettings>
XmlaConnectionSettings cubeConnectionDetails = new XmlaConnectionSettings();
cubeConnectionDetails.Cube = "LocalizedCube";
cubeConnectionDetails.Database = "OrdersCube";
cubeConnectionDetails.ServerAddress = "http://localhost/olap/msmdpump.dll";
XmlaQueryProperty localization = new XmlaQueryProperty();
localization.Name = "LocaleIdentifier";
localization.Value = "1036";
cubeConnectionDetails.QueryProperties.Add(localization);
Dim cubeConnectionDetails As New XmlaConnectionSettings()
cubeConnectionDetails.Cube = "LocalizedCube"
cubeConnectionDetails.Database = "OrdersCube"
cubeConnectionDetails.ServerAddress = "http://localhost/olap/msmdpump.dll"
Dim localization As New XmlaQueryProperty()
localization.Name = "LocaleIdentifier"
localization.Value = "1036"
cubeConnectionDetails.QueryProperties.Add(localization)
AggregatesLevel - set the position where groups for the aggregates should be placed.
AggregatesPosition - defines whether the positon of the Aggregates will be Columns or Rows.
XmlaDataProvider uses four different collections to design the view of RadPivotGrid report and RadPivotFieldList: RowGroupDescriptions, ColumnGroupDescriptions, AggregateDescriptions, FilterDesciptions.
Defining XmlaDataProvider
XmlaDataProvider can be defined in the XAML as a StaticResource and used by both RadPivotGrid and RadPivotFieldList.
The pivot namespace is URI namespace: xmlns:pivot="http://schemas.telerik.com/2008/xaml/presentation/pivot". It is mandatory to define it if you are using the XmlaDataProvider in your XAML.
<Grid>
<Grid.ColumnDefinitions>
<ColumnDefinition Width="3" />
<ColumnDefinition Width="" />
</Grid.ColumnDefinitions>
<Grid.Resources>
<pivot:XmlaDataProvider x:Key="XMLADataProvider"/>
</Grid.Resources>
<pivot:RadGridView x:Name="radPivotGrid" DataProvider="{StaticResource XMLADataProvider}" />
<pivot:RadGridView x:Name="radPivotFieldList" Grid.Column="1" DataProvider="{StaticResource XMLADataProvider}" />
</Grid>
You can also create an object of type XmlaDataProvider in the code behind and set it for your controls:
XmlaDataProvider xmlaDataProvider = new XmlaDataProvider();
this.radPivotGrid.DataProvider = xmlaDataProvider;
this.radPivotFieldList.DataProvider = xmlaDataProvider;
Dim xmlaDataProvider As New XmlaDataProvider()
Me.radPivotGrid.DataProvider = xmlaDataProvider
Me.radPivotFieldList.DataProvider = xmlaDataProvider
Connection to OLAP Cube
To show data in RadPivotGrid and RadPivotFieldList we have to connect to OLAP Cube. For this purpose we will set the ConnectionSettings property of the XmlaDataProvider. This can be done directly in the XAML or in the code behind:
<pivot:XmlaDataProvider.ConnectionSettings>
<pivot:XmlaConnectionSettings
Cube="Adventure Works"
Database="Adventure Works DW 2008R2"
ServerAddress="https://demos.telerik.com/olap/msmdpump.dll">
</pivot:XmlaConnectionSettings>
</pivot:XmlaDataProvider.ConnectionSettings>
XmlaConnectionSettings cubeConnectionDetails = new XmlaConnectionSettings();
cubeConnectionDetails.Cube = "Adventure Works";
cubeConnectionDetails.Database = "Adventure Works DW 2008R2";
cubeConnectionDetails.ServerAddress = "https://demos.telerik.com/olap/msmdpump.dll";
XmlaDataProvider xmlaDataProvider = new XmlaDataProvider();
xmlaDataProvider.ConnectionSettings = cubeConnectionDetails;
Dim cubeConnectionDetails As New XmlaConnectionSettings()
cubeConnectionDetails.Cube = "Adventure Works"
cubeConnectionDetails.Database = "Adventure Works DW 2008R2"
cubeConnectionDetails.ServerAddress = "https://demos.telerik.com/olap/msmdpump.dll"
Dim xmlaDataProvider As New XmlaDataProvider()
xmlaDataProvider.ConnectionSettings = cubeConnectionDetails
You can set credentials if your connection requires username and password. The Credentials property of XmlaDataProvider expects object of type XmlaNetworkCredential which gives you properties for UserName, Password, SecurePassword and Domain.
Defining Group Descriptions
When initializing the XmlaDataProvider in the code behind it is a good idea to wrap all modifications in BeginInit() - EndInit() section. This will cause only one refresh of the DataProvider and it will be when the EndInit() is reached. If you are applying only modifications (more than one) on already initialized XmlaDataProvider you should use the DeferRefresh() method which will cause delay of the Refresh and this way all your changes will be applied simultaneously. More information for these methods is available in our Populating with Data - Overview article.
The XmlaDataProvider is using four collections that are reflecting the visual representation of RadPivotGrid and RadPivotFieldList:
RowGroupDescriptions - the data added to this collection will show as Row Headers in RadPivotGrid. There is only one type of description that can be added to this collection - XmlaGroupDescription.
ColumnGroupDescriptions - the data added to this collection will show as Column Headers in RadPivotGrid. There is only one type of description that can be added to this collection - XmlaGroupDescription.
AggregateDescriptions - the data added to this description will be aggregated and included in RadPivotGrid as Cells. There is only one type of description that can be added to this collection - XmlaAggregateDescription.
FilterDescriptions - the data added to this collection will be filtered based on a particular condition. RadPivotGrid will show only items that pass the filter condition. There is only one type that can be added to this collection - XmlaFilterDescription. You can define a single filter condition or set up filtering for different levels of the selected member.
The XmlaGroupDescription is used to define the data that will show as Rows and Columns in RadPivotGrid. When defining XmlaGroupDescription you must set the MemberName property. The MemberName property is of type string and you should define it based on your Cube. But it should have specific syntax to access Cube's data.
The XmlaAggregateDescription is used to define data that has to be aggregated and that will show as Cells in RadPivotGrid. You should set the MemberName property.
The MemberName property must have the following syntax:
- For XmlaAggregateDescription: "[Measures].[MEASURE_NAME]".
- For XmlaGroupDescription there are two ways (based on the data that will be used): "[DIMENSION].[HIERARCHY]" or "[SET_NAME]".Note that the brackets are mandatory.
Here is how to define row, column group descriptions and aggregate descriptions:
<pivot:XmlaDataProvider.RowGroupDescriptions>
<pivot:XmlaGroupDescription MemberName="[Date].[Calendar Year]"/>
</pivot:XmlaDataProvider.RowGroupDescriptions>
<pivot:XmlaDataProvider.ColumnGroupDescriptions>
<pivot:XmlaGroupDescription MemberName="[Promotion].[Promotion Category]"/>
</pivot:XmlaDataProvider.ColumnGroupDescriptions>
<pivot:XmlaDataProvider.AggregateDescriptions>
<pivot:XmlaAggregateDescription MemberName="[Measures].[Internet Order Quantity]" />
</pivot:XmlaDataProvider.AggregateDescriptions>
XmlaGroupDescription rowGroupDescription1 = new XmlaGroupDescription();
rowGroupDescription1.MemberName = "[Date].[Calendar Year]";
XmlaGroupDescription columnGroupDescription1 = new XmlaGroupDescription();
columnGroupDescription1.MemberName = "[Promotion].[Promotion Category]";
XmlaAggregateDescription aggregateDescription1 = new XmlaAggregateDescription();
aggregateDescription1.MemberName = "[Measures].[Internet Order Quantity]";
XmlaDataProvider xmlaDataProvider = new XmlaDataProvider();
xmlaDataProvider.BeginInit();
xmlaDataProvider.RowGroupDescriptions.Add(rowGroupDescription1);
xmlaDataProvider.ColumnGroupDescriptions.Add(columnGroupDescription1);
xmlaDataProvider.AggregateDescriptions.Add(aggregateDescription1);
xmlaDataProvider.EndInit();
Dim rowGroupDescription1 As New XmlaGroupDescription()
rowGroupDescription1.MemberName = "[Date].[Calendar Year]"
Dim columnGroupDescription1 As New XmlaGroupDescription()
columnGroupDescription1.MemberName = "[Promotion].[Promotion Category]"
Dim aggregateDescription1 As New XmlaAggregateDescription()
aggregateDescription1.MemberName = "[Measures].[Internet Order Quantity]"
Dim xmlaDataProvider As New XmlaDataProvider()
xmlaDataProvider.BeginInit()
xmlaDataProvider.RowGroupDescriptions.Add(rowGroupDescription1)
xmlaDataProvider.ColumnGroupDescriptions.Add(columnGroupDescription1)
xmlaDataProvider.AggregateDescriptions.Add(aggregateDescription1)
xmlaDataProvider.EndInit()
The XmlaFilterDescription is used to filter the data that will be included in the report. Filtering is applied for a specific member defined in MemberName property. Condition property defines the way to filter the items. Only the ones that pass the filter will be shown in RadPivotGrid. The filtering is based on the Unique Name of the members. For example, if you want to show only data for year 2005, you can define it like this:
<pivot:XmlaDataProvider.FilterDescriptions>
<pivot:XmlaFilterDescription MemberName="[Date].[Calendar Year]">
<pivot:XmlaFilterDescription.Condition>
<pivot:OlapSetCondition Comparison="Includes">
<pivot:OlapSetCondition.Items>
<sys:String>[Date].[Calendar Year].&[2005]</sys:String>
</pivot:OlapSetCondition.Items>
</pivot:OlapSetCondition>
</pivot:XmlaFilterDescription.Condition>
</pivot:XmlaFilterDescription>
</pivot:XmlaDataProvider.FilterDescriptions>
XmlaFilterDescription filterDescription = new XmlaFilterDescription();
filterDescription.MemberName = "[Date].[Calendar Year]";
OlapSetCondition condition = new OlapSetCondition();
condition.Comparison = SetComparison.Includes;
condition.Items.Add("[Date].[Calendar Year].&[2005]");
filterDescription.Condition = condition;
Dim filterDescription As New XmlaFilterDescription()
filterDescription.MemberName = "[Date].[Calendar Year]"
Dim condition As New OlapSetCondition()
condition.Comparison = SetComparison.Includes
condition.Items.Add("[Date].[Calendar Year].&[2005]")
filterDescription.Condition = condition
As you can see, XmlaFilterDescription Condition expects object of type OlapSetCondition. Items property of the OlapSetCondition object is a collection of all objects that each item must match. In our case we are using string objects as the member names in OLAP Cubes are strings.
As OLAP dimensions are hierarchical, you can use filters for each of the levels by using "Levels" property of XmlaFilterDescription. Levels is a collection of XmlaLevelFilterDescriptions which are members of the user-defined hierarchy of the dimension:
In the blue rectangle are all attribute hierarchies - you can use them as a filter by adding them as a new FilterGroupDescription. In the red rectangle you can see the user-defined hierarchy of Date.Calendar dimension. You can filter your data by each of the members by adding it to the Levels property of XmlaFilterDescription:
<pivot:XmlaFilterDescription MemberName="[Date].[Calendar]">
<pivot:XmlaFilterDescription.Levels>
<pivot:XmlaLevelFilterDescription MemberName="[Date].[Calendar].[Calendar Year]">
<pivot:XmlaLevelFilterDescription.Condition>
<pivot:OlapSetCondition Comparison="Includes">
<pivot:OlapSetCondition.Items>
<sys:String>[Date].[Calendar].[Calendar Year].&[2007]</sys:String>
</pivot:OlapSetCondition.Items>
</pivot:OlapSetCondition>
</pivot:XmlaLevelFilterDescription.Condition>
</pivot:XmlaLevelFilterDescription>
<pivot:XmlaLevelFilterDescription MemberName="[Date].[Calendar].[Calendar Semester]">
<pivot:XmlaLevelFilterDescription.Condition>
<pivot:OlapSetCondition Comparison="Includes">
<pivot:OlapSetCondition.Items>
<sys:String>[Date].[Calendar].[Calendar Semester].&[2007]&[2]</sys:String>
</pivot:OlapSetCondition.Items>
</pivot:OlapSetCondition>
</pivot:XmlaLevelFilterDescription.Condition>
</pivot:XmlaLevelFilterDescription>
</pivot:XmlaFilterDescription.Levels>
</pivot:XmlaFilterDescription>
XmlaFilterDescription topFilterDescription = new XmlaFilterDescription();
topFilterDescription.MemberName = "[Date].[Calendar]";
OlapSetCondition condition1 = new OlapSetCondition();
condition1.Comparison = SetComparison.Includes;
condition1.Items.Add("[Date].[Calendar].[Calendar Year].&[2007]");
XmlaLevelFilterDescription levelFilterDescription1 = new XmlaLevelFilterDescription();
levelFilterDescription1.MemberName = "[Date].[Calendar].[Calendar Year]";
levelFilterDescription1.Condition = condition1;
OlapSetCondition condition2 = new OlapSetCondition();
condition2.Comparison = SetComparison.Includes;
condition2.Items.Add("[Date].[Calendar].[Calendar Semester].&[2007]&[2]");
XmlaLevelFilterDescription levelFilterDescription2 = new XmlaLevelFilterDescription();
levelFilterDescription2.MemberName = "[Date].[Calendar].[Calendar Semester]";
levelFilterDescription2.Condition = condition2;
topFilterDescription.Levels.Add(levelFilterDescription1);
topFilterDescription.Levels.Add(levelFilterDescription2);
Dim topFilterDescription As New XmlaFilterDescription()
topFilterDescription.MemberName = "[Date].[Calendar]"
Dim condition1 As New OlapSetCondition()
condition1.Comparison = SetComparison.Includes
condition1.Items.Add("[Date].[Calendar].[Calendar Year].&[2007]")
Dim levelFilterDescription1 As New XmlaLevelFilterDescription()
levelFilterDescription1.MemberName = "[Date].[Calendar].[Calendar Year]"
levelFilterDescription1.Condition = condition1
Dim condition2 As New OlapSetCondition()
condition2.Comparison = SetComparison.Includes
condition2.Items.Add("[Date].[Calendar].[Calendar Semester].&[2007]&[2]")
Dim levelFilterDescription2 As New XmlaLevelFilterDescription()
levelFilterDescription2.MemberName = "[Date].[Calendar].[Calendar Semester]"
levelFilterDescription2.Condition = condition2
topFilterDescription.Levels.Add(levelFilterDescription1)
topFilterDescription.Levels.Add(levelFilterDescription2)