The Five Types of OLAP Calcs

From the old Arbor graveyard, I found some interesting docs that may be of use to people new to the arena.

In OLAP we think about 5 types of calculations:

Aggregations
Matrix Calculations
Cross Dimensional Calculations
OLAP Aware Functions
Procedural Calculations

Aggregations are simply addition. Adding days into weeks, weeks into months, and so on. Or individual customers into customer groups, customer families, customer categories, etc.

Matrix calculations are like what you would do in a spreadsheet, with arbitrarilly complex relationships (+,-,*,/,sum, count, and more) both across the rows and down the columns. Calculations like variance, variance %, total, count, inventory balances, etc for example.

Cross dimensional calculations are like what you would do in linked spreadsheets, or in a multidimensional spreadsheet. In these, calculations can refer to numbers elsewhere in the cube, across difference dimensions or different hierarchies, not just on the same spreadsheet you are sitting on. Calculation examples include product share, market share, etc.

OLAP Aware Functions are like spreadsheet formulas that have been entended to understand OLAP. These include statistics, forecasting functions, financial functions, time calculations. Just like a spreadsheet, most OLAP servers have several hundred OLAP aware formulas provided. For example with a single OLAP Aware function I could calculate a standard deviation, a variance, an interest payment, an NPV, etc across all dimensions. Beyond what a spreadsheet does these functions work across dimensions and understand OLAP concepts like parents, children, ancestors, financial intelligence, time intelligence, etc.

Procedural calculations require a multidimensional calculation language. We call this our calculation script language. For any applications that requires procedural calculations, implementation of business rules within the calculation, arbitrarily complex calculations etc you need this. This is the equivalent of stored prodedure languages in RDBMSs, extending the capabilities of SQL, except in OLAP these languages are all highly specialized and OLAP Aware. Applications include allocations, activity based costing, forecasting, goal seeking, modeling, etc. A relatively large percentage of Arbor's customers use our calculation scripts for procedural calculations in addition to the other types of calculations.

MDX has arisen as the new procedural OLAP-aware language. I've avoided it like the plague, but I'm sure it does what it's supposed to do.

Comments

[Shahid]

Hi,

I just browsed this blog and it sounds quite interesting details on BI, Cubes and DWH. I would like to add you to my site (www.bi-dwh.com) blogroll. If interested revert back with mail and a brief about your blog and its URL.

Regards,
Shahid.