Cubegeek

The Columnar Stadium

Q: What is the difference, in layman's terms, between a relational database and a columnar database?

A: I have a great analogy for you.

Imagine that you are at a football stadium. There are 100,000 fans in the seats. Obviously today is somebody's birthday. So if you asked everybody whose birthday is today, about 1/366 people will stand up.  Go through the calendar, and you'll eventually get everyone to stand up.

 

In a relational database, you use the entire stadium to 'store' the actual people by birthday. Everybody has their own seat. In a column database, you only have 366 actual people 'stored' and then you have 100,000 pointers to the other seats where those people would be stored.

 

In this analogy, you are the end user standing in the parking lot asking for all of the people born today. That is your query. When your answer for today is 273, you will see 273 people come out of the stadium with the birthday attribute of today. What you don't know is that the columnar database has only one person stored in the whole stadium that represents all of those 273, whereas the relational database is actually using 273 seats. So that makes the columnar database 273x as efficient as the relational database.

 

And guess what, the larger the number of total people 'stored' the greater the relative efficiency of the columnar store.

Related articles

Three Databases

Stepping Out of the Elephant's Shadow

Essbase Capacity Planning (1997)

Staging Essbase Data with Hand Built ETL

The best fun in being a hacker and a crank is that you have this internal notion of what you should do that makes perfect sense, but you can't explain it. My new habit is to use Audacity and explain it to myself for the times I get back to needing that code several years later.

For example. I know that I have some code that I put on a timer to go check and see if the Hyperion Reports server is up and running. I forget the guy who wrote it or what modifications I made to it. I just know it's in a big pile somewhere in my new All Customers archive. But I think of it every time I need historical fx data because I know it was hacked out of a general purpose web snarfer.  I had that written and carried over from another customer. And interestingly enough I just remembered that part as I wrote that sentence.

Well I just finished unit testing a new Essbase back-end. This one is actually fairly cool. One of my favorite things to do on backends are 'fabs' - little perl programs that fabricate other scripts and then execute them. So this fab generates a calling program, a wrapper around a stored procedure with a couple parameters that I craft.

The back end is like this. I've got a weird proprietary HR database with a wide view of about 25 columns, about a half-dozen demographics, 3 or four geos, 4 dates, some titles and grades, position numbers, and salary. It's small, only about 500 employees in the table, but enough master data to generate 17 dimensions. The HR Source is on super lockdown, and therefore I can't query directly into the transaction system and the specified view even goes through another database with separate security. So basically my account has access down to an absolute minimum. All of that is on Server A. Transacation database, and a 'tunnelvision' database.

I get a push of the tunnelvision view data into my ODS which is on a slightly different form of lockdown. Bascially just an administrative account on the server plus one more account which belongs to SQL Server. The source is pushed into a table. Now I have a workplace to create a bunch of status flags which give various date fields multidimensional meaning. Plus I calculate a tenure metric for calculation of vacation days. And of course I have the facility to incorporate any new business rules necessary.

The tunnelvision is stateless so I have to stamp it - and that's the fun part. I hacked one of my old code archiving programs which gives me incremental version numbers and a date stamp into a new cut field in a historical table. I just pile on that whenever I initiate a manual refresh into the cube.

So I've got one wide table that updates 17 dimension tables, one narrow minimum fact table (there's just one fk that ties to most of the attribute dims) and one historical table. I have a fab that creates the calling SQL for a five step stored procedure and takes parameters from the same date facility that updates the substitution variables.

The coolest thing is that I have the ability to either let the batch program run this whole thing or I can initiate it from the user desktop. That was done via some .NET programming by a guy on staff here. Just double-clich on an icon and a .NET program initiates my batch file from a browser. The only problem with that is that the path changes when it runs from IIS instead of my Essbase batch subdirectory, so I can't use any relative pathing internal to my scripts. But now end users can be on the phone with HR, know that they are about to commit a change to their system and can initiate a refresh of the whole shebang end to end in under 5 minutes. 

Stored procedures have generally been a mystery to me precisely because DBAs are jealous of their positions. So being the multidimensional guy I never get access to do with SQL what I would generally want to do - I just provide the specs for the interface, they disappear for weeks and come back grumbling. But it turns out that they are super simple - at least they are in SQL 2K5 Express which is the freebie I use for my ODS.

The most difficult thing to do here was pass the security review. It's a huge help that MaXL now has the security hashing that encrypts your IDs and pws, but my server was closed enough to not require that. I really dig dealing with streams of data like this, mixing perl, SQL and Essbase calcs to each optimize that part of the final picture the end user is presented. It was a great discipline to learn how to manage those kinds of streams independently and asynchronously as they dropped into my world's inbucket. Now being able to do this on a standard batch schedule and on demand using the same code is the new cool thing at my customer.

Binary Aggregation

(From the archives July 1997)

I finally came up with the solution. As we looked at the numbers on the paper reports for headcounts, I recognized that none of the numbers were tying. Every cell contained a possible double count depending on the rollup. Only zero level numbers were reasonable. I reasoned that at every level of aggregation, what we wanted was a yes or no by individual.

Binary Aggregation

The solution is to create a member formula for Heads which performs the equivalent of a binary aggregation at every level of aggregation of every dimensions but the top level of the Individual dimension.

In order to get unique heads as a measure do the following.

First, you must have a dimension for individuals and input records by individual.

Input a dummy measures field called Hits by adding a "1" as a column in your input file by modifying your load rule.

 Calc your database normally.

 When you report against such a database you'll get a problem with double counting, unless you drill down to the individual level.

 For example,

                                                       

Individual	Dim X	Dim Y	Hits
A	1	0	1
A	1	1	1
A	1	0	1
A	0	1	1
B	1	0	1
B	0	0	1

 

 

Notice that if you query Hits for XY = {1,0} you will get 3 despite the fact that there are only 2 distinct individuals. (A hits twice), Unless you break out the individual dimension, you can't know that A hit twice.

 

The solution is to aggregate Hits at the descendants of the Individual dimension according to the following logic:

hits = (if <>#missing) 1, #missing

Fix on the descendants of Individual, then aggregate the Individual dimension across all the other dimensions, then on a third pass, aggregate the Individual dimension by itself. I forget the actual syntax i wrote in the calc script but it worked like a charm. Thus reporting at the top level of Individual, you get distinct heads. Now you can rename the Hits measure to Heads.

At this point you can export the database and get rid of the Individual dimension, if you like.

The Wiki Problem

I have agreed with Seth Grimes to contribute to a BI wiki. I'm not sure exactly how and when, but it's a good idea and I intend to get a lot of material out there.

The problem is that the current Wikipedia entries around OLAP surround the idea that OLAP is 'a projection of a relational database'. If you check out this entry, I am rather at a loss at how to deal with it. What I think I'll do is to start with the particulars of Essbase and move backwards to the general and then make the kinds of corrections at the high level that make sense with regard to the way this industry actually matured.

In addition I'll keep a rolling commentary going on here under this new category MDBMS. Since Essbase is evolving into something which is truly unique I think it's important to keep things open.

You see Essbase is implemented both at IBM DB2 OLAP and as Essbase. It has completely abstracted its storage model, so even in Essbase 7X as available from Hyperion, you can use a block storage or an 'aggregate storage' option. In addition there is Hybrid Analysis which, from what I understand is actually a projection onto relational (which if Essbase puts onto MySQL could be a huge hit). IBM DB2 OLAP server has bolted the Essbase system onto DB2 relational (by the guys at the Santa Theresa Labs). As far as I know this is fairly unique in the whole of the database world, much less the 'OLAP' pigeonhole.

My point is to show that Essbase is an organic fully-featured database platform, not simply an OLAP engine, nor a projection of relational tech.