OLAP: Decision Support, Pivot Table

The Pivot feature is only available in the Lite version, in True DataControl Lite.

Normally, you will use the Pivot feature of True DataControl with ComponentOne True DBGrid. True DBGrid supports Pivot in conjunction with TDataLite. You can use the Pivot feature with grids other than TDBGrid too, but they must be able to display hierarchical recordsets, and you will probably find them less convenient to use with TData than ComponentOne True DBGrid, for there won’t be such close integration between the two controls at design time as between TData and True DBGrid. For one thing, you must set the PivotFieldsAllLevels property to False using it with any control other than True DBGrid.

On-Line Analytical Processing (OLAP) functionality has many names, such as Data Analysis, Decision Support, Pivot Table, Decision Cube, etc. The essence of it is to allow end-users to explore data in different projections and hierarchies. The user can interactively choose and reorder the dimensions in which data are represented and summarized. An OLAP front-end, a pivot table browser/grid displays data in a hierarchical crosstab view.

With TData/TDataLite, True DBGrid becomes an OLAP front-end control. It gives you important advantages over other existing OLAP solutions:

You no longer have to rely on external tools adding OLAP functionality to your applications, you can use the same grid control that you love, with the same familiar object model.
True DBGrid is fully customizable and programmable in OLAP (Pivot) mode, as it is in all other modes. You can make your OLAP solutions look and feel consistently with other parts of your application, so your users won’t need to learn additional, external tools and user interface.

An example of the Pivot feature can be found in Tutorial 10 - OLAP - Decision Support, Pivot Table.

In Pivot mode, True DataControl functions in a very special way. Most of its other features become unavailable because they have no sense in that mode. You can think of a TData control in Pivot mode as of a separate control, different from normal TData, implemented in the same TData control only as a convenience, to spare the hassles of installing two different controls. Only pivot-related properties described below have effect on a TData control in Pivot mode, all other properties are ignored, including all properties specified on all property pages.

The following will give you instructions to set up a TDBGrid control displaying an OLAP cube. For step-by-step instructions and screenshots, consult Tutorial 10 - OLAP - Decision Support, Pivot Table.

To enable pivot mode, set the Pivot property to True.

TData Pivot mode uses ADO MD (Microsoft ActiveX Data Objects (Multi-Dimensional)) that works with an OLAP-enabled OLE DB provider, such as Microsoft OLAP Services. These components must be installed on the client computer. The easiest way to guarantee it is to install Microsoft Office 2000 (Microsoft Excel and MS Query).

First, you need to prepare an OLAP data source that you want to use with TData. It is specified in the PivotConnectionString property. PivotConnectionString accepts any OLAP data connection valid for ADOMD.Catalog object, see Microsoft ADO MD documentation for details. It can be a live on-line connection to an OLAP server, or a connection to an off-line cube file, as in the following example:

Example Title	Copy Code
“Provider=msolap;DATA SOURCE=C:\OLAPCubes\orders.cub”

For example, you can prepare a cube file in Microsoft Excel 2000, with PivotTable and PivotChart Wizard, see Tutorial 10 - OLAP - Decision Support, Pivot Table.

Set the PivotCubeName property to the name of the desired cube (there can be many cubes in a single OLAP data connection). The default cube name produced by Microsoft Excel is “OCWCube”.

Once PivotConnectionString and PivotCubeName are set, you are ready to bind a TDBGrid control to your TData control. Set TDBGrid.DataSource to TData.

If you use pivot feature with ComponentOne True DBGrid, make sure the PivotFieldsAllLevels is set to True (default). Using it with other, third-party controls, set this property to False.

Also, using ComponentOne True DBGrid, make sure you set DataView = 1 – Hierarchical. Otherwise, the grid will not display the data properly.

Retrieving fields in TDBGrid will show all dimension fields and measure field(s) of the cube. However, if you run the project, the grid will be empty. This is because you need to supply a query, specifying which dimension fields are used as row dimensions, which dimension field is used as the column dimension (TData/TDBGrid allows hierarchy in row dimensions, but not in column dimensions, it always uses a single dimension for columns, represented in a crosstab fashion), and which measure field is used for cell values. This query information must be specified by setting the corresponding properties: PivotRowDimensions (comma-separated list of chosen row dimension fields), PivotColumnDimension (the name of the chosen column dimension field) and PivotMeasure (the name of the chosen measure field).

You, as an application developer, write the code that specifies this query information (PivotRowDimensions, PivotColumnDimension, PivotMeasure) based on end-user selection made in some UI controls, such as list boxes, for example.

Having determined the query, you can apply it calling the PivotOpen method. This will show the result in the grid. As an advanced option, you can specify an arbitrary multidimensional query (an MDX statement) as an optional string parameter in the PivotOpen method, that will override PivotRowDimensions, PivotColumnDimension and PivotMeasure.

Row dimensions are presented in a hierarchical form, so the user can expand/collapse them to get more/less detailed view of the data.

Column dimension are presented as a crosstab: a column for each dimension value. For example, if you have column dimension Country, you will get as many columns as there are countries in the cube.

Grid cells show the measure field, usually a number, representing the measure value corresponding to certain dimension values. A special value of each dimension displays totals, measure values aggregated by this dimension.

All other fields, dimensions and measures, that don’t participate in the end-user selected query, are hidden.

All the powerful versatile features of True DBGrid are available in Pivot mode. You can specify property settings for grid columns representing dimension and measure fields, either at design or at run time. You can write code in grid events to further customize your OLAP front end application. For example, Tutorial 10 - OLAP - Decision Support, Pivot Table shows how to highlight cells meeting certain condition.

Every time you change end-user selection and re-apply PivotOpen, the grid is refreshed showing the updated query in the updated layout.