Binding to OLAP Data Sources

• Mar 05, 2022
• 6 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. Thus, you can use OLAP binding mode to process large amounts of data.

Run Demo Watch Video

Requirements and Limitations

To learn more about requirements and limitations imposed by binding to OLAP data sources, see Requirements and Limitations.

Bind to an OLAP cube at Design Time

You can bind to an OLAP cube at design time using the Items Source Configuration Wizard. To do this, perform the following steps.

1. Click the PivotGridControl’s smart tag and select the Items Source Wizard command.

   

2. In the invoked Items Source Configuration Wizard, select OLAP Cube.

   

   Click Next.

3. Select the provider used to communicate with the Microsoft Analysis Services server.

   

   The following providers are available.

   • OLE DB for OLAP - A native provider for Analysis Services database connections.

   • ADOMD.NET - 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 by using either TCP/IP or HTTP connections to transmit and receive SOAP requests and responses that are compliant with the XML for Analysis specification.

     Note

     Note that the ADOMD.NET data provider provides a better performance than OLE DB for OLAP and provides additional capabilities. To learn more, see Data providers used for Analysis Services connections.

   • XMLA - Provides the capability to connect to an OLAP cube over HTTP protocol. This method does not require any data access libraries or data providers to be installed on the client system.

   Select ADOMD.NET and click Next.

4. On the final page, specify the required settings.

   

   • Provider - Allows you to specify the version of the Microsoft Analysis Services OLE DB Provider for the Microsoft SQL Server. Note that MSOLAP identifies the latest version of the OLE DB provider.

   • Server Name - Specifies the name of the OLAP server that runs an instance of Microsoft SQL Server Analysis Services (SSAS). You can also specify the full path to a local cube file.

     Note

     Click the Retrieve Schema button to obtain the available catalogs/cubes contained in the specified server.

   • Catalog Name - Specifies a data catalog that contains cubes.
   • Cube Name - Specifies the name of a cube that provides data.
   • Query Timeout - The maximum amount of time (in seconds) allowed for a query to SSAS to complete. If the parameter is set to 0, each query can last indefinitely.
   • Culture - Specifies the locale used to retrieve translations from an OLAP server.
   • Connection Timeout - Specifies the time (in seconds) to wait while trying to establish a connection before terminating the attempt and generating an error.
   • User Id - Specifies the user name used to authenticate an OLAP server.
   • Password - Specifies the password used to authenticate an OLAP server.
   • Connections String - Contains the connection string generated based on settings specified above.

   After you have specified all the required settings, click Finish and rebuild the solution.

5. After you have created an OLAP data source at design time, create pivot grid fields using commands from the control’s smart tag.

   

   • Add Data Area Field - Adds a new field to the Data Header Area. To bind the created data field and the measure from the data source, click the field’s smart tag, click the ellipsis button next to FieldName and select the required measure.

     

   • Add Column Area Field/Add Row Area Field/Add Filter Area Field - Adds a new field to the Column Header Area, Row Header Area or Filter Header Area. To bind the created data field and the dimension from the data source, click the field’s smart tag, click the ellipsis button next to FieldName and select the required dimension.

     

Bind to an OLAP cube in Code

To bind the PivotGridControl to an OLAP cube in code, do the following.

• Use the PivotGridControl.OlapDataProvider property to specify the required data provider.

• Specify connection settings to the server using 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 provide the following parameters: Provider, Data Source, Initial Catalog, Cube Name.

• If you create PivotGrid fields in code, you need to specify the field’s PivotGridField.FieldName property, so that it refers to a specific measure or dimension level. The field name should contain the full name of the measure/dimension level. See the PivotGridField.FieldName topic for more information.
• Use the PivotGridControl.GetFieldList method to obtain a list of fields available in a bound data source, and the PivotGridControl.RetrieveFields method to create PivotGridField objects for all available fields.
• Place the created fields within corresponding Pivot Grid Control areas: Data Header Area, Column Header Area, Row Header Area or Filter Header Area. Use the PivotGridField.Area and PivotGridField.AreaIndex properties to position fields.

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

For this, it is required to set the PivotGridControl.OlapConnectionString property value, and add the necessary fields to the PivotGridControl.Fields collection.

View Example

MainWindow.xaml

<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>

MainWindow.xaml.vb

Imports System.Windows
Imports DevExpress.Xpf.PivotGrid

Namespace HowToBindOLAP
    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 fields.
            Dim fieldMeasuresInternetSalesAmount As New  _
                PivotGridField("[Measures].[Internet Sales Amount]", FieldArea.DataArea)
            fieldMeasuresInternetSalesAmount.Caption = "Internet Sales Amount"

            Dim fieldCustomerCountryCountry As New  _
                PivotGridField("[Customer].[Country].[Country]", FieldArea.RowArea)
            fieldCustomerCountryCountry.Caption = "Country"

            Dim fieldDateFiscalYearFiscalYear As New  _
                PivotGridField("[Date].[Fiscal Year].[Fiscal Year]", FieldArea.ColumnArea)
            fieldDateFiscalYearFiscalYear.Caption = "Fiscal Year"

            ' Add fields to the PivotGridControl
            pivotGridControl1.Fields.AddRange(fieldMeasuresInternetSalesAmount, _
                                        fieldCustomerCountryCountry, fieldDateFiscalYearFiscalYear)
            pivotGridControl1.EndUpdate()
        End Sub
    End Class
End Namespace

MainWindow.xaml.cs

using System.Windows;
using DevExpress.Xpf.PivotGrid;

namespace HowToBindOLAP {
    public partial class MainWindow : Window {
        public MainWindow() {
            InitializeComponent();
        }
        private void Window_Loaded(object sender, RoutedEventArgs e) {
            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 fields.
            PivotGridField fieldMeasuresInternetSalesAmount = 
                new PivotGridField("[Measures].[Internet Sales Amount]", FieldArea.DataArea);
            fieldMeasuresInternetSalesAmount.Caption = "Internet Sales Amount";

            PivotGridField fieldCustomerCountryCountry = 
                new PivotGridField("[Customer].[Country].[Country]", FieldArea.RowArea);
            fieldCustomerCountryCountry.Caption = "Country";

            PivotGridField fieldDateFiscalYearFiscalYear = 
                new PivotGridField("[Date].[Fiscal Year].[Fiscal Year]", FieldArea.ColumnArea);
            fieldDateFiscalYearFiscalYear.Caption = "Fiscal Year";

            // Add fields to the PivotGridControl
            pivotGridControl1.Fields.AddRange(fieldMeasuresInternetSalesAmount, 
                fieldCustomerCountryCountry, fieldDateFiscalYearFiscalYear);

            pivotGridControl1.EndUpdate();
        }
    }
}