Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 223

Finally, Query 6.15 is an example of manipulating many-to-many dimen-
sions. As can be seen, the SQL version needs to deal with the double-counting
problem while aggregating the measure.
To conclude, Table 6.1 summarizes some of the advantages and disadvan-
tages of both languages.

Table 6.1 Comparison of MDX and SQL
MDX

SQL

Advantages Advantages

• Data modeling: definition of di-

mensions, hierarchies, measure
groups, from various data sources

• Large user base

• Easy-to-understand semantics of

queries

• Simple navigation within time di-

mension and hierarchies

• Results are easy to visualize:

scalars or 2D tables

• Relatively simple expressions for

often used business requests

• Various ways of relating tables:

joins, derived tables, correlated
queries, common table expres-
sions, etc.

• Fast, due to the existence of agg-

regations

Disadvantages Disadvantages

• Extra effort for designing a cube

and setting up aggregations

• Tables must be joined explicitly

inside a query

• Steep learning curve: manipulat-

ing an n -dimensional space

• Sometimes not intuitive and com-

plex syntax for expressing analyt-
ical queries • Hard-to-grasp concepts: current

context, execution phases, etc.

• No concept of row ordering

and hierarchies: navigation di-
mensions may be complex

• Some operations are di cult to

express, such as ordering on mul-
tiple criteria • Not so performant for the types

of queries used in data analysis

6.5 Summary

In this chapter, we presented the MDX language, which is used for designing
and querying multidimensional databases. MDX can be used both as an
expression language for defining cubes and as a query language for extracting
data from cubes. We covered MDX as a query language and introduced its
main functionalities through examples. After this introduction, we addressed
a series of MDX queries to the Northwind cube. Then, we addressed the
same queries to the Northwind data warehouse using SQL. We concluded the
chapter comparing the expressiveness of MDX and SQL using both sets of
queries, highlighting advantages and disadvantages.