Learning OLAP and Analysis

As a consultant I find myself working on different Customer sites doing a wide variety of different types of IT work. This might include Asp.Net, WPF, WCF, you name it, from the MS Technology Stack.

One technology I'll be working with over the next few months is OLAP. It's a technology that I've always seen as slightly Voodoo-ish, Black Magic like, akin to walking into a mysterious smoky room with a pointy bearded Wizard lurking over a Crystal Ball!

Over my next few blog posts I'll aim to break down some of the mystery and offer some laymans descriptions to OLAP - what it is, how it works, and how to work with it. For my first post I'll describe some of the key terms, along with some links that I've found useful on understanding it better.

Measure
This one is pretty simple. Whenever we analyse some data we are analysing a particular type of information. It's usually a numerical piece of data and some examples might be Sales Amount or margin, GST, VAT, etc.

Dimension
Starting with an example, lets I have some Sales Data against time. The total amount taken be a store per day. In this case my dimension is time! Simple huh? I might ask my OLAP database to show me the sales total against Time.

As another example my sales data might also be broken down against location. I can ask my OLAP database to show me sales data for 2004 in Australia. We've defined another dimension, location.

Cube
No more black magic. Think of a cube - it's a simple 3-dimensional object. Lets say I have a cube where the X-dimension are my 'measures' - sales amount, margin, units sold, etc, my Y-dimension is time, and my Z-dimension is location.

You should start to see how we can form a query to the cube along the lines of "Show me the total margin, in February 2008, in Western Australia". The Performance Point video I'll link to shows this beautifully, but take the cube and take the 'Slice' of the cube representing Margin. Then cut another slice of the same cube and get the WA slice. Finally cut a 3rd slice along the time dimension for 'February 2008'. What you'll end up with is one piece of the cube where these 3 slices intersect which happens to contain the data we're after. Simple huh?!

Enough for now. Next time we'll either look into more detail around the Cube, or take a look at some of the query language (MDX) we use to write the queries to it.

Nice MS video on cube basics

Wikipedia on OLAP

Nice introduction linked from Wikipedia

Updated 4:55pm removed the Slicing and Dicing quote. Not sure if that's what slicing and dicing is!!