Dimensional Relational vs. OLAP: The Final Deployment Conundrum

Don’t expect to port an OLAP implementation to another vendor’s product. Discard everything including all your application development

There is no accepted, universally implemented access language for OLAP, although Microsoft’s MDX is the closest thing to a standard access language. Significantly, Oracle has not embraced MDX, preferring to rely on SQL for all forms of database access.

MDX in its full glory may be too complex for IT personnel to write by hand, or understand a complex application. But to be fair, I have looked at incomprehensible SQL many times!

Historically there has been much less industry expertise in MDX than in SQL although Microsoft has successfully sponsored a cottage industry of training organizations.

OLAP application development expertise is fragmented by vendor.

OLAP cubes can be catastrophically invalidated if you are not careful, i.e. a Type 1 SCD change to a dimension may cause all the cubes using that dimension to rebuild!

OLAP cubes are not considered stable enough for serious archiving and backup: this is a strong reason for creating a set of dimensional relational tables duplicating the content of the cube for those purposes.

OLAP vendors have certain size limits not present in relational implementations including possibly the number of members in a dimension, the number of distinct values at various levels in a hierarchy and the overall size of the cube.

When you must rebuild a cube, it may be a VERY time consuming process.

Equally Easy in Either Approach

The basic foundations of dimensional modeling, including slowly changing dimensions, the three fundamental grains of fact tables, and achieving integration through conformed dimensions, are equally easy to deliver with either approach, especially since these dimensional structures are carried to the very last step of the ETL pipeline.