OLAP Mode

• Jan 17, 2024
• 5 minutes to read

Pivot Grid connects to data contained in a cube deployed on an OLAP server and delegates data-aware operations (summarization, grouping, and so on) to the server side. The OLAP data source does not reload/recalculate data on each request. The data source caches data and calculation results, and returns only the requested slice. Use OLAP binding mode to process large amounts of data.

Refer to the following article for more information about Pivot Grid’s performance recommendations: Pivot Grid Performance.

Note

OLAP is not supported when the Pivot Grid uses the Optimized calculation engine because data is calculated on the client side.

OLAP Data Providers

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

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

To specify the required data provider, use one of the following properties:

WinForms	WPF	ASP.NET Web Forms
PivotGridControl.OlapDataProvider	PivotGridControl.OLAPDataProvider	ASPxPivotGrid.OLAPDataProvider

Data Provider Requirements and Limitations

To use the OLE DB and ADOMD.NET providers, Pivot Grid requires the Microsoft Analysis Services OLE DB and Microsoft ADOMD.NET providers on the web server. Refer to the following topic for more information: Data providers used for Analysis Services connections.

The following limitations are in effect when you use OLE DB and ADOMD.NET providers:

• The application should run in Single-Threaded Apartment (STA) mode if the OLE DB data provider is used to establish a connection to the Microsoft SQL Server 2000 Analysis Services.
• When you connect to local cubes , use an OLE DB or ADOMD.NET provider. When one provider establishes a connection, the other provider becomes unavailable in the scope of the current process. The following exception might occur: an attempt to connect to a local cube using OLE DB provider followed by disconnection and another attempt to connect to the same cube (or another cube file in the same process) using ADOMD.NET provider.

Supported OLAP Servers

The following OLAP servers are supported:

• Microsoft SQL Server 2000 Analysis Services
• Microsoft SQL Server 2005 Analysis Services
• Microsoft SQL Server 2008 Analysis Services
• Microsoft SQL Server 2008 R2 Analysis Services
• Microsoft SQL Server 2012 Analysis Services (Multi-dimensional mode)
• Microsoft SQL Server 2014 Analysis Services (Multi-dimensional mode)
• Microsoft SQL Server 2016 Analysis Services (Multi-dimensional mode)

Note

• Microsoft SQL Server Analysis Services 2008 requires Microsoft SQL Server 2008 Service Pack 1.
• Microsoft SQL Server Analysis Services 2005 requires Microsoft SQL Server 2005 Service Pack 2.
• Microsoft SQL Server Analysis Services 2000 requires Service Pack 4.
• In Microsoft SQL Server Analysis Services 2000, the ASPxPivotGrid does not support a cube’s dimension members with DBNull values.

OLAP Mode Limitations

The following features have limitations in OLAP mode:

You should handle one of the following events to apply custom sorting:

WinForms	WPF	ASP.NET Web Forms
PivotGridControl.CustomServerModeSort	PivotGridControl.CustomServerModeSort	ASPxPivotGrid.CustomServerModeSort

The PivotGridFieldBase.DisplayFolder property used to manage User Folders does not allow you to edit the dimension root folders. Specify the following properties for the newly created field to place it in the OLAP field tree displayed in the Customization form:

• OLAPExpressionBindingBase.Expression
• PivotGridFieldBase.OLAPDimensionCaption
• PivotGridFieldBase.DisplayFolder

The following Pivot Grid features are not supported in OLAP mode:

• Custom Summaries.
• Custom Totals whose summary type is set to Custom.
• Grouping Intervals.
• Filter Editor.
• Filter by Summaries.
• Top N - “Others” field value (see PivotGridFieldBase.TopValueShowOthers).
• PivotGridFieldBase.SummaryType cannot be changed.
• Sort operations by summaries are not supported if custom totals are displayed.
• You cannot create two or more fields bound to the same data source field (except for data fields bound to the same measure if they have different PivotGridFieldBase.SummaryDisplayType values).
• You can add fields bound to cube measures only to the data area.
• You cannot add fields bound to cube dimensions to the data area.
• You cannot use Excel-style filters in OLAP mode.

The following table contains platform-specific help topics that illustrate the features listed above:

WinForms	WPF	ASP.NET Web Forms
Custom Summaries	Custom Summaries	Custom Summaries
Custom Totals	Custom Totals	Custom Totals
Grouping Intervals	Grouping Intervals	Grouping Intervals
Filter by Summaries	Filter by Summaries	Filter by Summaries
Display Top N Values	Display Top N Values	Display Top N Values
Sort by Summaries	Sort by Summaries	Sort by Summaries
Data Area	Data Area	Data Area
Data Fields	Data Fields	Data Fields

OLAP Culture Settings

When the ADOMD.NET data provider (specified by the OLAPDataProvider property) connects to an OLAP data source, the provider requests the value and formatted text from the OLAP server’s cube. This information includes a format string and the culture information.

Note

If the OLAP server does not include culture information, the client’s culture settings are used to format the value (except currency settings). The client’s culture settings are also used if you use the PivotGridFieldBase.CellFormat property to specify the format. To use different culture settings to format values, handle one of the following events:

WinForms	WPF	ASP.NET Web Forms
PivotGridControl.CustomCellDisplayText	PivotGridControl.CustomCellDisplayText	ASPxPivotGrid.CustomCellDisplayText

Bind Pivot Grid to OLAP

Refer to the following platform-specific topics for more information on how to connect Pivot Grid to an OLAP database:

• WinForms: Binding to OLAP Data Sources
• WPF: Binding to OLAP Data Sources
• ASP.NET Web Forms: Binding to OLAP Data Sources