Skip to main content

Binding to OLAP Data Sources

  • 4 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.

Run Demo Watch Video

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

  1. Use the PivotGridControl.OlapDataProvider property to specify the required data provider.
  2. 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

  1. Create a PivotGridField object and add it to the PivotGridControl.Fields collection.
  2. Specify the field’s area and position within this area. For this, use the PivotGridFieldBase.Area and PivotGridField.AreaIndex properties. AreaIndex can 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

  1. Create a DataSourceColumnBinding instance.
  2. Specify the DataSourceColumnBinding.ColumnName property. ColumnName must 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]”.

  3. Assign the DataSourceColumnBinding object to the PivotGridField.DataBinding property.

The following example demonstrates how to bind a PivotGridControl to an MS OLAP cube.

View Example

Imports System.Windows
Imports DevExpress.Xpf.PivotGrid

Namespace HowToBindOLAP

    Public Partial Class MainWindow
        Inherits Window

        Public Sub New()
            Me.InitializeComponent()
        End Sub

        Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
            Me.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;"
            Me.pivotGridControl1.BeginUpdate()
            ' Create fields.
            Dim fieldMeasuresInternetSalesAmount As PivotGridField = New PivotGridField()
            fieldMeasuresInternetSalesAmount.Caption = "Internet Sales Amount"
            fieldMeasuresInternetSalesAmount.Area = FieldArea.DataArea
            Dim fieldCustomerCountryCountry As PivotGridField = New PivotGridField()
            fieldCustomerCountryCountry.Caption = "Country"
            fieldCustomerCountryCountry.Area = FieldArea.RowArea
            Dim fieldDateFiscalYearFiscalYear As PivotGridField = New PivotGridField()
            fieldDateFiscalYearFiscalYear.Caption = "Fiscal Year"
            fieldDateFiscalYearFiscalYear.Area = FieldArea.ColumnArea
            Dim fieldSales As PivotGridField = New PivotGridField()
            fieldSales.Caption = "Cleared Amount"
            fieldSales.Area = FieldArea.DataArea
            fieldSales.CellFormat = "c"
            ' Populate fields with data.
            fieldMeasuresInternetSalesAmount.DataBinding = New DataSourceColumnBinding("[Measures].[Internet Sales Amount]")
            fieldCustomerCountryCountry.DataBinding = New DataSourceColumnBinding("[Customer].[Country].[Country]")
            fieldDateFiscalYearFiscalYear.DataBinding = New DataSourceColumnBinding("[Date].[Fiscal Year].[Fiscal Year]")
            fieldSales.DataBinding = New OlapExpressionBinding("[Measures].[Internet Sales Amount] * 0.87")
            ' Add fields to the PivotGridControl.
            Me.pivotGridControl1.Fields.AddRange(fieldMeasuresInternetSalesAmount, fieldCustomerCountryCountry, fieldDateFiscalYearFiscalYear, fieldSales)
            Me.pivotGridControl1.EndUpdate()
        End Sub
    End Class
End Namespace
<Window xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid" 
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        x:Class="HowToBindOLAP.MainWindow"
        Title="MainWindow" Loaded="Window_Loaded"
        Height="350" Width="525">
    <Grid>
        <dxpg:PivotGridControl HorizontalAlignment="Left" VerticalAlignment="Top" 
                               Name="pivotGridControl1" />
    </Grid>
</Window>
See Also