Binding to OLAP Data Sources

• Oct 12, 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.

Requirements and Limitations

For information about requirements and limitations imposed by binding to OLAP data sources, see OLAP Requirements and Limitations.

Bind to an OLAP cube at Design Time

You can bind the Pivot Grid to an OLAP cube at design time in two ways:

• Use the Data Source Configuration Wizard.
• Specify the connection string (the PivotGridControl.OLAPConnectionString property) in the standard Properties window.

Data Source Configuration Wizard

To bind to an OLAP cube in the Data Source Configuration Wizard, do the following:

1. Click the Data Source Button.

   

2. In the invoked Data 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. This provider uses TCP/IP or HTTP connections to transmit and receive SOAP requests and responses that are compliant with the XML for Analysis specification.

     Note

     The ADOMD.NET data provider supplies better performance than OLE DB for OLAP. Refer to the following article for more information: Data providers used for Analysis Services connections.

   • XMLA - Allows you 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 supplies 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. When a connection timeout expires, the attempt is aborted and an error occurs.
   • 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 the settings specified above.

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

5. Rebuild the solution, click the control’s smart tag, and make sure that the Pivot Grid is bound to the OLAP cube.

   

   You can click the ellipsis button next to Choose OLAP Data Source to modify the connection string in the Connection String Editor. To learn more, see the Connection String Editor paragraph below.

Connection String Editor

The Connection String Editor dialog allows you to modify the OLAP connection string at design time.

You can invoke this dialog from the control’s smart tag menu or by clicking the ellipsis button next to the PivotGridControl.OLAPConnectionString property in the standard Properties window.

Note

To display all settings available in the Connection String Editor, enable the Show Advanced Properties checkbox.

This dialog contains the same settings as the Data Source Configuration Wizard. Moreover, it contains the following settings:

• Roles - Specifies a comma-delimited list of predefined roles to connect to a server or database using permissions allowed by this role. To learn more, see Connection String Properties (Analysis Services).
• CustomData - Specifies a function that can be used to pass a configuration setting to be used by Multidimensional Expressions (MDX) functions and statements. To learn more, see CustomData (MDX).

After you create an OLAP data source at design time, you can create Pivot Grid fields. To do this, open the Fields Page of the control’s Designer. The Field List pane contains all available measures and dimension levels of the cube. To add a specific measure/dimension level to the Pivot Grid, drag the measure or dimension onto the PivotGrid Fields pane.

Tip

Demo: OLAP Browser module in the XtraPivotGrid MainDemo

Requires installation of WinForms Subscription. Download.

Bind to an OLAP cube in Code

This example demonstrates how to specify connection settings to the Adventure Works cube on the OLAP server.

Follow the steps below to bind the Pivot Grid control to an OLAP cube in code.

1. Set the PivotGridControl.OLAPDataProvider property to ADOMD.

2. Specify connection settings in the PivotGridControl.OLAPConnectionString property. The following connection string is used in this example:

   Provider=MSOLAP;Data Source=http://demos.devexpress.com/Services/OLAP/msmdpump.dll;Initial catalog=Adventure Works DW Standard Edition;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.

View Example: How to Connect a Pivot Grid to an OLAP Data Source

C#

using DevExpress.XtraPivotGrid;
using DevExpress.XtraPivotGrid.Customization;

namespace WinOlapRetrieveFieldsExample {
    public partial class Form1 : DevExpress.XtraEditors.XtraForm {
        public Form1() {
            InitializeComponent();
            // Specify the OLAP connection settings.
            pivotGridControl1.OLAPDataProvider = OLAPDataProvider.Adomd;
            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;
                Query Timeout=100;";
                // ...
        }
        // ...
    }
}

VB.NET

Imports DevExpress.XtraPivotGrid
Imports DevExpress.XtraPivotGrid.Customization

Namespace WinOlapRetrieveFieldsExample

    Public Partial Class Form1
        Inherits DevExpress.XtraEditors.XtraForm

        Public Sub New()
            InitializeComponent()
            ' Specify the OLAP connection settings.
            pivotGridControl1.OLAPDataProvider = OLAPDataProvider.Adomd
            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;
                Query Timeout=100;"
                ' ...
        End Sub
        ' ...
    End Class
End Namespace

Examples

View Example: How to Bind to an OLAP Cube with the ADOMD.NET Data Provider View Example: How to Bind to an OLAP Cube with the XMLA Data Provider