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

Furthermore, in SQL the aggregations needed for the roll-up operations
must be explicitly stated through the GROUP BY and the aggregation
functions in the SELECT clause, while in MDX the aggregation functions
are stated in the cube definition and they are automatically performed upon
roll-up operations. Finally, in SQL the display format must be stated in the
query, while in MDX this is stated in the cube definition.
Consider now the comparison of measures of the current period with
respect to those of a previous period, such as the previous month or the same
monthinthepreviousyear.AnexampleisgiveninQuery6.3.InMDX,this
can be achieved with calculated members using the WITH MEMBER clause.
On the other hand, in SQL this can be achieved by defining a temporary
table in the WITH clause in which the aggregations needed for the roll-up
operation are performed for each period, and an outer join is needed in the
main query for obtaining the measure of the current period together with
that of a previous period. Nevertheless, as shown in Query 6.4, obtaining the
previous month in SQL is somehow complex since we must account for two
cases depending on whether the previous month is in the same year or in the
previous year.
Consider now top and bottom performance analysis, an example of which
is given in Query 6.5. In MDX this can be obtained with functions such as
TOPCOUNT , whereas in SQL, this can be achieved with the TOP function.
Nevertheless, there is a fundamental difference between the two approaches.
For example, in MDX, the function TOPPERCENT sorts a set in descending
order, and returns a set of tuples with the highest values whose cumulative
total is equal to or greater than a specified percentage. On the other hand,
in SQL, stating TOP(n) PERCENT will return the percentage of the total
number of tuples in the answer. We have seen in Query 6.7 how to achieve
cumulative top percentages in SQL.
Query 6.8 is an example of manipulating aggregates at several granular-
ities. In MDX this is achieved by starting at the coarser granularity and
obtained the finer granularity through the DESCENDANTS function. In SQL,
we computed the finer granularity in a temporary table and obtained the
coarser granularity by aggregating the temporary table in the main query.
Let us consider period-to-date calculations and moving averages, as exem-
plified in Queries 6.10 and 6.11. In MDX, the function PERIODSTODATE is
used for the former, and hierarchical navigation is used for the latter. On the
other hand, in SQL these are obtained by using the window functions.
Query 6.12 is an example of aggregation in parent-child hierarchies. As
can be seen, this is easily expressed in MDX, while a complex recursive query
is needed to obtain similar functionality in SQL.
Queries 6.13 and 6.14 show examples of manipulating fact dimensions.
Although this can be expressed quite succinctly in MDX, it is not immediate
to understand how to achieve such a result. The corresponding queries in
SQL are easier to write.