Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Collapse]Pivot Grid
   [Expand]Getting Started
   [Expand]Fundamentals
   [Collapse]Binding to Data
     Binding to Data Overview
    [Expand]Data Source Configuration Wizard
    [Collapse]OLAP Data Source
      Binding to OLAP Data Sources
      Requirements and Limitations
    [Expand]Database Server Mode
     Unbound Fields
     Local Data Stores
     Asynchronous Mode
   [Expand]Data Shaping
   [Expand]Data Analysis
   [Expand]Layout
   [Expand]Focus and Navigation
   [Expand]Printing and Exporting
   [Expand]Appearance
   [Expand]Design-time Features
   [Expand]Miscellaneous
   [Expand]UI Elements
   [Expand]End-User Capabilities
   [Expand]Examples
   [Expand]Member Tables
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Expand]Spreadsheet
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Binding to OLAP Data Sources

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. The following KB article describes recommendations related to using OLAP data sources: How to improve the PivotGrid performance.

Expanded Requirements and Limitations

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

Expanded Binding to an OLAP cube at Design Time

You can bind to an OLAP cube at design time in two ways.

Data Source Configuration Wizard

To bind to an OLAP cube using the Data Source Configuration Wizard, perform the following steps.

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

  5. Rebuild the solution, click the control's smart tag and make sure that the PivotGridControl 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 using from the control's smart tag 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 created 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 will contain all available measures and dimension levels of the cube. You can add a specific measure/dimension level to the PivotGridControl by dragging it onto the PivotGrid Fields pane.

Expanded Binding to an OLAP cube in Code

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

Show Me

A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=T344546.

If you have a cube on the OLAP server (Microsoft Analysis Services), you can view its data using the Pivot Grid. In this example, you will see how to specify connection settings to the server and create fields that represents specific measures and dimensions of the cube.

To bind the Pivot Grid control to an OLAP cube, follow the steps below.

  • Set ADOMD as a data provider using the PivotGridControl.OLAPDataProvider property.

  • Specify connection settings to the server using the PivotGridControl.OLAPConnectionString property. The connection string used in the example is shown below.

    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;
  • Create fields for all the measures and dimension in the bound OLAP cube, and moves these fields to the specified area, making them hidden. To do it, use the PivotGridControl.RetrieveFields method overload and set the field's visibility to false.

  • Place some of the created fields within corresponding Pivot Grid Control areas and set the visibility of the fields to true using the PivotGridFieldBase.Visible property.

Use the invoked Customization Form to specify the Pivot Grid control's layout.

To learn more about OLAP Datasources, see OLAP Datasources.

Expanded See Also

How would you rate this topic?​​​​​​​