OLAP, shmOLAP: it slices, dices, and cubes

16 August 2007

In one of the screencasts I narrated recently, I talked about how using the data crunching capabilities of a server versus those of the client can paradoxically make the user experience of using a grid control with large datasets more pleasant.

I say "paradoxically" because it would seem that having the data on the client locally would result in a better experience, rather than having to scurry back and forth to the server fetching more and more data. In reality what happens is the initial download of all the data is the big time sink and problem (and then having to repeatedly do the same as you analyze the data). Replacing that huge engulfing of data with piecemeal sipping turns out to be more pleasant from the user's perspective: the delays the user experiences during data analysis are smeared out across all interactions and not clumped into some.

There's another place in our component suites where we make use of server processing to alleviate the clumpiness of this downloading of data, and that's with our XtraPivotGrid.

The XtraPivotGrid is a specialized grid that helps the user organize and analyze statistical, business, and financial data though its ability to summarize and present large amounts of information in a cross-tabular form. For example, the user can analyze revenue data during set periods (months, quarters, etc) by customer or product group, and so on.

By default the way the pivot grid works is to do the initial big swallowing of all the data. And because, in general, the pivot grid is going to be used on a lot of data (it's after all going to be used to analyze, summarize, organize data, so it needs it all), that's going to take some time, even if your database is local. A huge amount of memory is going to be allocated on the client to hold and process this data.

So a strategy for reducing these performance and memory issues at the client is to get the data server to do some of the work. After all, a good server would be able to share the results of queries amongst several clients. For an analysis tool like the pivot grid, the type of server that would be best at this work would be an OLAP server (Online Analytical Processing server).

An OLAP server serves up what are known as data cubes. Instead of a simple relational model as in the standard relational databases, an OLAP server uses a mix of hierarchical and navigational models in order to store and serve data in a multidimensional matrix. Terms used with OLAP servers are 'dimensions', which are the rows and columns of the matrix, and 'measures', which are the values or fields in the matrix. OLAP servers are optimized for financial type data and the principal dimensions used are time, locations, people, products and so on.

Back to the pivot grid. Ignoring the adornments of the grid itself — for example the borders, the column and row headers — in essence the pivot grid displays a matrix of similar data, exactly the output from an OLAP server. The XtraPivotGrid has a mode wherein it will construct and execute queries on a cube served up by the OLAP server. In this mode, it is the OLAP server that will aggregate data, sort it, group it, calculate summaries and so on, and then return the results to the pivot grid.

To use a cube on an OLAP server, there are a couple of steps you need to make:


1. Specify the connection settings for the server as a connection string using the PivotGridControl.OLAPConnectionString property. This connection string defines the names of the server, the data catalog, and the cube to use. At design time, you can build the connection string via the Connection String Editor available with the OLAPConnectionString property in the Properties grid.


2. Create fields in the XtraPivotGrid control that represent the specific measures and dimensions of the cube you wish to use. At design time, it's easy: after the connection string has been specified, you can open the Fields page of the pivot grid's designer and the Field List pane will contain all the available measures and dimensions of the cube. You can then add a specific measure or dimension to the XtraPivotGrid control by dragging it onto the Fields pane.

Of course, should you need to, you can do all this in code as well. See the help file for details.

After these properties have been set, the pivot grid will query and fetch the required data from the OLAP server. As discussed before, only the data needed for display in the grid is fetched, with the server doing the aggregation, sorting, and summarizing of the data.

At present we support Microsoft SQL Server Analysis Services (SSAS), both the 2000 and 2005 versions. The client requires the Analysis Services OLE DB provider to be installed as well (the latest release can be downloaded from the Microsoft web site — search for "Feature Pack for SQL Server").

So if you have a lot of financial or business data you want to crunch and you want to enable your users to analyze this information — after all, raw data becomes actionable information through analysis — you should be thinking about an OLAP server and then use XtraPivotGrid in the hands of your users.

1 comment(s)
Serkan Cankaya

It would be quite efficient to use OLAP of course but the problem is, we can not always find SSAS installed. So maybe DevExpress team need to build a component which makes Cubes local or implement this feature into PivotGrid. Because if we want to give OLAP functionality to our softwares PivotGrid is not enough by itself. Simply it can't handle large datasets. I was thinking to implement a DataReader which compatible with all IDataReader interfaces to fetch data per row and process it one by one. That way we won't have to store whole data in memory and avoid these OutOfMemory exceptions.

16 November, 2008

Please login or register to post comments.