Binding to an OLAP Server

• May 22, 2020
• 4 minutes to read

With Pivot Grid, you can view data from cubes deployed on an OLAP (online analytical processing) server. A cube represents a multidimensional structure defined by its measures and dimensions. You can think of a measure as a data field in the PivotGridControl, while a dimension can be represented by a column or row field.

In a typical binding mode, when the PivotGridControl is bound to a regular data source, the control requires all data to be downloaded from a data store to the client side. All data is processed locally. In an OLAP binding mode, when the control is bound to a cube on an OLAP server, the pivot grid does not calculate summaries itself. Instead, it delegates all data management and calculations to the server side. The raw data is not downloaded to the client side, but rather, only the results of calculations. So, the OLAP binding mode is appropriate for handling large amounts of data, especially when a cube is already defined on an OLAP server.

PivotGridControl uses the XMLA data access standard to connect to OLAP cubes. This connection does not require any data access libraries or data providers to be installed on the client system. Nevertheless, it supports all OLAP features available in OLE DB and ADOMD.NET data providers - drill-down, KPI, hierarchical filtering, etc.

The Silverlight Pivot Grid Control - XMLA Data Access video demonstrates how to bind a PivotGridControl to an existing OLAP cube using the XMLA data access standard.

Setting Up a Middleware IIS Server

Client applications that retrieve data from an Analysis Services server using the XMLA data access standard can do it in two ways:

• connect directly to the AS server over TCP/IP;
• use an IIS server as a proxy, while communicating with it over HTTP.

The PivotGridControl cannot directly send XMLA queries via TCP/IP. Therefore, you should set up an IIS server and configure a data pump (a special service component) on this server to access OLAP data via XMLA.

To learn how to prepare an IIS server with a data pump, see Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 7.0.

Cross-Domain Access

By default, Silverlight allows only site-of-origin communication for all requests other than images and media to prevent cross-site request forgery. Therefore, you cannot bind a PivotGridControl to a data pump hosted on a different domain if this domain does not explicitly allow cross-domain access.

For information on how to enable cross-domain access, see Making a Service Available Across Domain Boundaries.

Connecting the PivotGridControl to the IIS Server

After you have a data pump set up and properly configured, follow the steps below to connect the PivotGridControl to the pump.

1. Build a connection string - a String that encapsulates the parameters used to connect to the data pump.

   A sample connection string is shown below.

   Data Source=http://demos.devexpress.com/Services/OLAP/msmdpump.dll;Initial Catalog=Adventure Works DW Standard Edition;Cube Name=Adventure Works;Query Timeout=100;

   A valid connection string must provide the following parameters.

   • Data Source - the path to the data pump.
   • Initial Catalog - a data catalog that contains cubes.
   • Cube Name - the name of a cube that provides OLAP data.
   • Query Timeout (optional) - the number of seconds to wait until the query to SSAS is completed. If this parameter is set to 0, each query can last indefinitely.

2. Create fields in the PivotGridControl that represent the specific measures and dimension levels of the cube.

   Specify the field's PivotGridField.FieldName property, so that it refers to a specific measure or dimension level. The field name should represent 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.

3. Position created fields within corresponding PivotGridControl 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.

4. Call the PivotGridControl.SetOlapConnectionStringAsync method, passing the created connection string as its parameter.

At runtime, the PivotGridControl will automatically fetch the required data for the fields that have been created.

To learn about the requirements and limitations imposed by binding to an OLAP datasource, see Requirements and Limitations.

Example

• How to: Bind a Pivot Grid to an OLAP Cube