Online Analytical Processing with .NET and DevExpress PivotGrid

Now and then I hit situation where some kind of Online Analytical Processing (OLAP) is needed.

In short OLAP is just a way to provide convenient slicing and dicing of some data. You can call it fluent reporting, where the the data-source for the report is fact table and report itself is some kind of aggregation.

There are two simple implementation scenarios of OLAP .NET world that I have encountered so far. In both cases facts table could be simply represented as enumeration of strongly-typed objects (residing DB, memory, flat file etc). And the aggregation is performed via

• Linq queries against facts data that generate some output. Sorting, grouping, filtering, projecting – all this is done in the code. This output is in fact the aggregation/projection/report (whatever you call it).
• Pivot Grid controls that are fed with the facts table and are used to let end-users define filtering, sorting etc rules via the UI. The process is less flexible than in the previous scenario, but it does not require any development skills.

I've assembled quick-n-dirty tool for the second type of approach. You can download it here and use to slice-n-dice your raw data (see below for the details).

To start playing:

• Download OLAP analysis tool sample and unzip
• Run, hit “Open” and load “SampleOrderFacts.xml” that comes with the tool.

To create “Total Sales per Product Category” report:

• Drag-n-drop “CategoryName” to “Drop Row”
• Drag-n-drop “Total” to “Drop Data”

To create “Employee Sales per Product Category” report:

• Drag-n-drop “CategoryName” to “Drop Column”
• Drag-n-drop “EmployeeName” to “Drop Row”
• Drag-n-drop “Total” to “Drop Data”

To add “Include only orders that ship to USA”:

• Hit “Prefilter”
• Hit “+”, then compose “ShipCountry Equals 'USA'”

To load your own facts table:

• Check out the Sample project.
• Use XmlSerializer to save array of your facts into file
• Copy assembly with fact object into the working directory of OLAP tool and reference the type in the OLAP.exe.config
• Run, hit “Open” and load your file with serialized facts.
• Have fun

Comments

[Client123]

Hi,

Nice work,
I am actually using these features in my application but having difficulty customizing the styles of the PivotGrid PreFilter window, in particular the Red, Green & Blue text that comes as standard within.

Could you please tell how you managed to change these.

Thanks.

[Rinat Abdullin]

AFAIK this was just a theme selection.