WinPivotGrid - it's not just for OLAP anymore...

What exactly does 'OLAP' mean anyway?

OnLine Analytical Processing.

Well, that's what each letter in the acronym stands for, anyway. I'll defer the formal definition to the folks over at OLAP.com, who can presumably explain it better than I:

" OLAP is the technology behind many Business Intelligence (BI) applications. OLAP is a powerful technology for data discovery, including capabilities for limitless report viewing, complex analytical calculations, and predictive 'what if' scenario (budget, forecast) planning. "

Okay...sounds cool. Data discovery, limitless report viewing, complex calculations, etc. Yes, all these things sound like stuff I'd like to have in my application. How do I get that?

OLAP please - and hold the server.

So one way to go about incorporating business intelligence into your applications is to familiarize yourself with Microsoft's SQL Server Analysis Services (SSAS) model. You might need to make a few stops along the way for things like server configurations, user permissions, integrating with SQL Server, and then of course on to the actual business of data mining, dimensions, cubes, roles, KPIs...and probably a few acronyms I've neglected to mention.

Getting the point? None of this is terribly difficult, but can be time consuming - more so if you don't already have access to these things. Wouldn't it be great if you could bypass all this SSAS business and go straight to the part where your users are slicing and dicing data just like they would on a fancy OLAP server?

Introducing the FlatDataSource.

Designed specifically so that you can get all that neat multi-dimensional data presentation (and all the slicing, dicing, and filtering that goes along with it), without an OLAP server, the FlatDataSource class was introduced with the 14.2 release. All you need is a standard, garden variety .NET class, and an IEnumerable implementation that contains the instances of this class which comprise your data set. You create a FlatDataSource instance with this IEnumerable implementation, assign it to the WinPivotGrid's DataSource property, and that's really it...

What's that? You don't believe me? Alright, let's go through it step-by-step with a quick example.

1. The .NET class

   Let's assume you have a .NET class named CustomerOrder, with all the data required to describe an order for a given product, made by a given customer on a given date. Oh, yes - with the price of that product...finance people get all bent out of shape when you don't include that kind of stuff.

   A C# implementation of such a class would look something like this:

   

2. The IEnumerable implementation

   Now we need an IEnumerable implementation which consists of instances of this CustomerOrder class, with each instance representing an actual order. We'll assume this part is self-evident, and continue on to the creation of the FlatDataSource instance which we use to serve this data to the WinPivotGrid control. That code looks something like this:

   

3. The End Result

   As you can see, the data which we generated from a "non-OLAP" data source appears in the WinPivotGrid exactly as if it were produced by an OLAP query. Users can slice, dice and filter this data any which way they like:

   

4. The Missing Piece

   The astute reader will have noticed that in section 2, The IEnumerable Implementation, there is a call to a method named GetData at line 44...and the GetData method implementation is nowhere to be found...huh?

   Now I could give you one of the usual excuses like, "beyond the scope of this article" or my personal favorite, "omitted for the sake of brevity" (as if the internet were running out of space and my saving a couple of kilobytes here is going to stave off impending doom). The fact is, there's nothing fancy going on in there, but it does represent several hundred lines of code, mostly involving database querying and table joining and all that good stuff, which may very well be off the point of this article, but is still of interest to anyone who wants to use the WinPivotGrid to display data from "regular" databases.

   Wait a minute - did you say several hundred lines of code??? Gulp. Yes, that's what I said. You've got to connect to the database, query it across a couple of different tables, create instances of a proxy class to hold the flattened data, and get the resulting recordset into an IEnumerable implementation containing a list of those proxy class instances. Certainly not rocket science, just tedious, manual labor. There's got to be a better way...right?

Adapt and Overcome.

In order to address this shortcoming, a new namespace was added in the Infragistics.Olap.DataSource.Flat assembly, Infragistics.Olap.FlatData.Adapters. This namespace contains two new classes, SqlAdapter and OleDbAdapter, each representing a liason between the WinPivotGrid control and a database table or query.

These adapters greatly simplify the act of querying a 'standard', i.e., non-OLAP database, and presenting the result in the WinPivotGrid control. In the simplest case, only two properties need to be set - one for the connection string which establishes the connection to the database, and one for the SQL statement that defines the data to be returned.

The following code sample gives you an idea of how these adapters work:

In closing.

Hopefully you'll agree that these data adapters greatly simplify the problem of presenting standard database data in the WinPivotGrid control. If any of the material presented here was unclear, or you have further questions, post in the WinPivotGrid Forum, and we'll be happy to help you out.