MDX and SQL, the kissing cousins!

Quite interesting, yeah! Here we go,

If SQL is meant for create manage and query Relational Databases in OLTP(Online Transaction Processing) Systems, MDX, The Multi Dimensional Extension, is to perform calculations in OLAP(Online Analytical Processing) Databases.

Why MDX, and how important it is in Business Intelligence?

It is to query, navigate, and perform calculations in OLAP databases. OLAP databases are usually called as CUBES. Cubes are so called as the OLAP Databases comprises of multi Dimensional data. Though the cubes can have dozens of dimensions but rarely goes more than 15 to 20 to make them easier/understandable to work with. Cube consists of dimensions, hierarchies, levels, attributes and measures. MDX has been the strength of reporting and analysis in Business Intelligence (BI) as it supported virtually by many of the BI Server giants like Microsoft, IBM, Hyperion and SAP.

OLAP cubes are multidimensional database model which consists of measures and dimensions joined though a central fact table. As the arrangement of dimensions with a central fact table resembles a ‘star’, multidimensional database are called as ‘Star schemas’. At the same time we can compare OLTP database with ‘Spider Web’ as plenty of tables joined together through a complex set of primary and foreign key relationships.

Although you can theoretically query an OLAP cube using SQL, SQL has no inherent knowledge of dimension hierarchies and cannot easily navigate to the parent or children of a particular dimension member. Here comes the advantage of MDX over SQL. It can be used to navigate, query and perform calculations against multidimensional structures. Examples of Multidimensional structures are OLAP Cubes, dimensions, hierarchies, levels, attributes, members and measures.

MDX can recognize all kind of dimension relationship from ancestors to descendants f, from members to siblings, from root to leaf with very little coding. PeriodsToDate, LastPeriods, ParallelPeriod, YTD, QTD are few of the interesting 200 functions MDX have. (Interestingly, ParallelPeriod picks the data from a current period to the same period a year ago.) The queries for bringing these results in SQL would consume huge time and lines of code.

MDX was born on top of SQL to provide code consumption in dealing OLAP Server. OLAP Server was the invention of a third party called Panorama, and lately it was acquired by Microsoft.