Binding to OLAP Data Sources
- 5 minutes to read
The PivotGridControl allows you to visualize data contained in a cube deployed on an OLAP server. In OLAP mode, the PivotGridControl delegates data management operations (such as summarization, grouping, etc.) to the server side. You can use OLAP binding mode to process large amounts of data. The following article describes recommendations related to OLAP data sources: Pivot Grid Performance - OLAP.
Requirements and Limitations
Refer to the following topic for information about requirements and limitations in OLAP mode: Requirements and Limitations.
Bind to an OLAP cube at Design Time
Important
You cannot bind the Pivot Grid to data at design time in .NET 5+ projects. Refer to the “Bind Pivot Grid to an OLAP cube in Code” section for information on how to populate the Pivot Grid with data in code.
Refer to the following tutorial for information on how to bind the Pivot Grid to an OLAP cube at design time in .NET Framework WPF applications: Lesson 2 - Bind a Pivot Grid to an OLAP Cube.
Bind Pivot Grid to an OLAP cube in Code
Bind Pivot Grid to an OLAP Cube
- Use the PivotGridControl.OlapDataProvider property to specify the required data provider.
Specify connection settings to the server in the PivotGridControl.OlapConnectionString property. A sample connection string is shown below.
Provider=msolap;Data Source=localhost;Initial Catalog=Adventure Works DW;Cube Name=Adventure Works;Query Timeout=100;Note that a valid connection string should contain the following parameters: Provider, Data Source, Initial Catalog, and Cube Name.
Create Pivot Grid Fields
- Create a PivotGridField object and add it to the PivotGridControl.Fields collection.
- Specify the field’s area and position within this area. For this, use the PivotGridFieldBase.Area and PivotGridField.AreaIndex properties.
AreaIndexcan be set only after the field is added to the control’s field collection.
Use the PivotGridControl.GetFieldList method to obtain a list of fields that are available in a bound data source, and the PivotGridControl.RetrieveFields method to create PivotGridField objects for all available fields.
Populate Pivot Grid Fields with Data
- Create a
DataSourceColumnBindinginstance. Specify the DataSourceColumnBinding.ColumnName property.
ColumnNamemust specify the full name of the bound measure or dimension.For dimensions, the full name is composed of a dimension name, followed by a hierarchy name, followed by the name of a level(s). All names should be wrapped within square brackets and separated from one another with the dot symbol. Example: “[Customer].[Customer Geography].[Country]”.
For measures, the full name is composed of the “[Measures].” string followed by the measure name. Example: “[Measures].[Sales Amount]”.
Assign the
DataSourceColumnBindingobject to the PivotGridField.DataBinding property.
The following example demonstrates how to bind a PivotGridControl to an MS OLAP cube.
Imports System.Windows
Imports DevExpress.Xpf.PivotGrid
Namespace HowToBindOLAP
''' <summary>
''' Interaction logic for MainWindow.xaml
''' </summary>
Partial Public Class MainWindow
Inherits Window
Public Sub New()
InitializeComponent()
End Sub
Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
pivotGridControl1.OlapConnectionString = "Provider=msolap;" & "Data Source=http://demos.devexpress.com/Services/OLAP/msmdpump.dll;" & "Initial Catalog=Adventure Works DW Standard Edition;" & "Cube Name=Adventure Works;"
pivotGridControl1.BeginUpdate()
' Create Pivot Grid fields.
Dim fieldMeasuresInternetSalesAmount As New PivotGridField()
fieldMeasuresInternetSalesAmount.Caption = "Internet Sales Amount"
fieldMeasuresInternetSalesAmount.Area = FieldArea.DataArea
pivotGridControl1.Fields.Add(fieldMeasuresInternetSalesAmount)
Dim fieldSales As New PivotGridField()
fieldSales.Caption = "Cleared Amount"
fieldSales.Area = FieldArea.DataArea
fieldSales.CellFormat = "c"
pivotGridControl1.Fields.Add(fieldSales)
' Populate fields with data.
fieldMeasuresInternetSalesAmount.DataBinding = New DataSourceColumnBinding("[Measures].[Internet Sales Amount]")
fieldSales.DataBinding = New OlapExpressionBinding("[Measures].[Internet Sales Amount] * 0.87")
AddField("Country", FieldArea.RowArea, "[Customer].[Country].[Country]", 0)
AddField("Fiscal Year", FieldArea.ColumnArea, "[Date].[Fiscal Year].[Fiscal Year]", 0)
pivotGridControl1.EndUpdate()
End Sub
Private Function AddField(ByVal caption As String, ByVal area As FieldArea, ByVal fieldName As String, ByVal index As Integer) As PivotGridField
Dim field As PivotGridField = pivotGridControl1.Fields.Add()
field.Caption = caption
field.Area = area
If fieldName <> String.Empty Then
field.DataBinding = New DataSourceColumnBinding(fieldName)
End If
field.AreaIndex = index
Return field
End Function
End Class
End Namespace
<Window
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:local="clr-namespace:HowToBindOLAP"
xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid" x:Class="HowToBindOLAP.MainWindow"
mc:Ignorable="d"
Title="MainWindow" Height="450" Width="800" Loaded="Window_Loaded">
<Grid>
<dxpg:PivotGridControl Name="pivotGridControl1"/>
</Grid>
</Window>