Binding to OLAP Data Sources

Jun 21, 2024
7 minutes to read

ASPxPivotGrid allows you to visualize data contained in a cube deployed on an OLAP server. In OLAP mode, the ASPxPivotGrid 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.

Requirements and Limitations
OLAP Data Providers
Binding to an OLAP cube at Design Time
Binding to an OLAP cube in Code

Requirements and Limitations

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

OLAP Data Providers

ASPxPivotGrid supports the following data providers used to establish a connection to OLAP cubes.

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

To specify the required data provider, use the ASPxPivotGrid.OLAPDataProvider property.

Binding to an OLAP cube at Design Time

You can bind the Pivot Grid to an OLAP cube at design time by clicking the button next to the Choose OLAP Data Source option…

… or by clicking the ellipsis button next to the ASPxPivotGrid.OLAPConnectionString property available in the standard Properties window. All these actions invoke the Connection String Editor dialog containing the following 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.
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.
Language - 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.
UserId - Specifies the user name used to authenticate an OLAP server.
Password - Specifies the password used to authenticate an OLAP server.
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).

Note

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

After connecting the Pivot Grid to a data source, create the required fields. You can do this using the ASPxPivotGrid Designer.

Tip

Demo: OLAP Browser

Binding to an OLAP cube in Code

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

Use the ASPxPivotGrid.OLAPDataProvider property to specify the required data provider.
Specify connection settings to the server using the ASPxPivotGrid.OLAPConnectionString property. A sample connection string is shown below.
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 ASPxPivotGrid.GetFieldList method to obtain a list of fields available in a bound data source, and the ASPxPivotGrid.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 PivotGridFieldBase.Area and PivotGridFieldBase.AreaIndex properties to position fields.

If you have a cube on the OLAP server (Microsoft Analysis Services), you can view its data using the ASPxPivotGrid control. In this example, you will see how to specify connection settings to the server and create fields that represent 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 ASPxPivotGrid.OLAPDataProvider property.
Specify connection settings to the server using the ASPxPivotGrid.OLAPConnectionStrings property. The connection string used in the example is shown below.
Create fields for all the measures and dimension in the bound OLAP cube, and moves these fields to the specified area, hiding them. To do it, use the ASPxPivotGrid.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.

WebForm1.aspx.cs
WebForm1.aspx (C#)
WebForm1.aspx (VB.NET)
WebForm1.aspx.vb

csharp

using System;
using DevExpress.XtraPivotGrid;
using DevExpress.XtraPivotGrid.Customization;
using DevExpress.Web.ASPxPivotGrid;

namespace ASPxOlapRetrieveFieldsExample
{
    public partial class WebForm1 : System.Web.UI.Page {
        protected void Page_Load(object sender, EventArgs e)
        {

            // Specifies the OLAP connection settings.
            ASPxPivotGrid1.OLAPDataProvider = OLAPDataProvider.Adomd;
            ASPxPivotGrid1.OLAPConnectionString =
                @"Provider=MSOLAP;
                Data Source=https://demos.devexpress.com/Services/OLAP/msmdpump.dll; 
                Initial catalog=Adventure Works DW Standard Edition;
                Cube name=Adventure Works;
                Query Timeout=100;";

            // Retrieves fields.
            ASPxPivotGrid1.RetrieveFields(PivotArea.ColumnArea, false);

            // Adds some fields from the Field List to the specified area to create a report.
            ASPxPivotGrid1.Fields["[Customer].[Country].[Country]"].Area = PivotArea.RowArea;
            ASPxPivotGrid1.Fields["[Customer].[Country].[Country]"].Visible = true;
            ASPxPivotGrid1.Fields["[Customer].[City].[City]"].Area = PivotArea.RowArea;
            ASPxPivotGrid1.Fields["[Customer].[City].[City]"].Visible = true;
            ASPxPivotGrid1.Fields["[Date].[Fiscal].[Fiscal Year]"].Area = PivotArea.ColumnArea;
            ASPxPivotGrid1.Fields["[Date].[Fiscal].[Fiscal Year]"].Visible = true;
            ASPxPivotGrid1.Fields["[Measures].[Internet Sales Amount]"].Visible = true;

            // Sets the Customization Forms style to Excel2007 with additional capabilities.
            ASPxPivotGrid1.OptionsCustomization.CustomizationFormStyle = CustomizationFormStyle.Excel2007;

        }
    }
}

aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="ASPxOlapRetrieveFieldsExample.WebForm1" %>

<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v15.2, Version=15.2.5.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.Web.ASPxPivotGrid" TagPrefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" ClientIDMode="AutoID" 
            EnableTheming="True"  
            Theme="Metropolis">
        </dx:ASPxPivotGrid>
        

        <dx:ASPxPivotCustomizationControl ID="ASPxPivotCustomizationControl1" 
            runat="server" ASPxPivotGridID="ASPxPivotGrid1" Height="388px" Width="250px">
        </dx:ASPxPivotCustomizationControl>
    </div>
    </form>
</body>
</html>

aspx

<%@ Page Language="vb" AutoEventWireup="true" CodeBehind="WebForm1.aspx.vb" Inherits="ASPxOlapRetrieveFieldsExample.WebForm1" %>

<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v15.2, Version=15.2.5.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.Web.ASPxPivotGrid" TagPrefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" ClientIDMode="AutoID" 
            EnableTheming="True"  
            Theme="Metropolis">
        </dx:ASPxPivotGrid>
        

        <dx:ASPxPivotCustomizationControl ID="ASPxPivotCustomizationControl1" 
            runat="server" ASPxPivotGridID="ASPxPivotGrid1" Height="388px" Width="250px">
        </dx:ASPxPivotCustomizationControl>
    </div>
    </form>
</body>
</html>

Imports System
Imports DevExpress.XtraPivotGrid
Imports DevExpress.XtraPivotGrid.Customization
Imports DevExpress.Web.ASPxPivotGrid

Namespace ASPxOlapRetrieveFieldsExample
    Partial Public Class WebForm1
        Inherits System.Web.UI.Page

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

            ' Specifies the OLAP connection settings.
            ASPxPivotGrid1.OLAPDataProvider = OLAPDataProvider.Adomd
            ASPxPivotGrid1.OLAPConnectionString = "Provider=MSOLAP;" & ControlChars.CrLf & _
" Data Source=https://demos.devexpress.com/Services/OLAP/msmdpump.dll; " & ControlChars.CrLf & _
" Initial catalog=Adventure Works DW Standard Edition;" & ControlChars.CrLf & _
" Cube name=Adventure Works;" & ControlChars.CrLf & _
" Query Timeout=100;"

            ' Retrieves fields.
            ASPxPivotGrid1.RetrieveFields(PivotArea.ColumnArea, False)

            ' Adds some fields from the Field List to the specified area to create a report.
            ASPxPivotGrid1.Fields("[Customer].[Country].[Country]").Area = PivotArea.RowArea
            ASPxPivotGrid1.Fields("[Customer].[Country].[Country]").Visible = True
            ASPxPivotGrid1.Fields("[Customer].[City].[City]").Area = PivotArea.RowArea
            ASPxPivotGrid1.Fields("[Customer].[City].[City]").Visible = True
            ASPxPivotGrid1.Fields("[Date].[Fiscal].[Fiscal Year]").Area = PivotArea.ColumnArea
            ASPxPivotGrid1.Fields("[Date].[Fiscal].[Fiscal Year]").Visible = True
            ASPxPivotGrid1.Fields("[Measures].[Internet Sales Amount]").Visible = True

            ' Sets the Customization Forms style to Excel2007 with additional capabilities.
            ASPxPivotGrid1.OptionsCustomization.CustomizationFormStyle = CustomizationFormStyle.Excel2007

        End Sub
    End Class
End Namespace