ROLAP smolap 2

Years ago at ungodly hours BBC2 used to broadcast TV lectures on behalf of the UK's Open University. Many of the films date from the 60's and 70's and are usually quite funny to watch because of the dubious haircuts and fashions prevalent in academia at that time.

I remember one such program caught my attention because it focussed on the SQL language. The lesson extolled the virtues of putting an english like query language in the workplace, implying that your average office worker would be comfortable using it. I suppose compared to the pointer driven databases that were prevalent in the 70's I can understand why they might have thought this way. However in practice companies never let their users loose with SQL and it remains very much an IT development tool. There are a number of reasons for this (cartesian products, anyone?) but one of them is NOT because SQL is an inherently complex language - it isn't - but rather that the data that we work with often is.

This led to the development of simpler dimensional data models which would format the data in a more user friendly manner - mostly with the Star Schema and to a lesser extent the snowflake schema. Fundamentally all OLAP and ROLAP technology is built to utilise these dimensional models.

So what's the problem? Well in a couple of projects recently I've had end users who have rejected the Star Schema as being too complex. In both cases they wanted a single denormalised view of the world - kind of a denormalised fact and dimension all-in-one superset.

Why did they reject the star schema? Well the reasons varied from not wanting to undertsand the complexities of the Star schema (i.e. surrogate join keys, current flags, effective dates, etc.) to a misguided belief that querying a single 'all-in-one' table would outperform a star query.

It gets worse as on one of the BI Managers had come up with the construct of a daily snapshot, such that every day the full denormalised snapshot dataset would be inserted even if only there were no changes to the source data.

In both cases I fundamentally disagreed with the customer and argued on behalf of the star schema. One battle I won and one I lost. Funnily enough though the battle I lost wasn't because of any inherent objection to the star schema - it was lost because the users had built a Visual Basic front end application through which to query and report their data. What they were able to achieve in VB had my Business Objects developers heads spinning. For those reasons I state again. ROLAP is old hat. If you want to keep your BI consumer happy you'll need to start building them some nice apps.