Data cubes are very popular because they allow multidimensional data to be collapsed to any two dimensions and shown as a "CrossTab" -- and human beings can comfortably visualise only two dimensions on a page or a screen. Unfortunately, creating CrossTabs is not very easy with normal SQL and that is why there exist a genre of specialist products -- Multidimensional OLAP or MOLAP -- that allow users to create CrossTabs by "rotating" the data cube as necessary.
Microsoft SQL-Server, a RDBMS product, has a proprietary construct called CUBE that allows this feature but this is not available in most RDBMS products and certainly not in MySQL, the free and open-source product that is the most widely used RDBMS on the planet.
The following slide deck shows how MySQL can be used to "rotate" an OLAP data cube and generate CrossTabs for any cube of dimension 3 or higher
(please view the slide deck in full screen mode)We also show how a "pivot" table, so beloved of Excel users can also be generated using MySQL and hence by extension in any RDBMS.
But why would anyone wish to use SQL or MySQL to build and work with data cubes when MOLAP tools are available?
- First, SQL is easily understood and widely used by a vast majority of IT professionals
- Second, MySQL is a free and open-source product that is used in almost every web application
- Third, SQL is supported in a multi-machine, clustered environments like Hadoop/Hive and Spark and so this technique can be used -- at least in principle -- to support data cubes built with ultra large data sets.
Unless one wants the bells and whistles that come along with most MOLAP products, MySQL is good enough for almost any OLAP activity and can be scaled up with Hive / Spark for very large data.
Acknowledgement : The technique demonstrated in this post has been adopted from information provided at http://www.artfulsoftware.com/infotree/qrytip.php?id=78