Adding Calculations to the Cube - Expert Cube Development with SSAS Multidimensional Models - page 174

Now, if we run this query:
SELECT Measures.[Sales Amount] ON COLUMNS,
Best10ProductsDynamic ON ROWS
FROM
Sales
WHERE
([Date Order].[Calendar].[Calendar Year].&[2001])
SELECT Measures.[Sales Amount] ON COLUMNS,
Best10ProductsDynamic ON ROWS
FROM
Sales
WHERE
([Date Order].[Calendar].[Calendar Year].&[2004])
We get the correct set of top-ten products for each year.
However, dynamic sets are still quite limited in their uses because they are not
completely aware of the context of the query they're being used in. For example, if
a user tried to put several years on the rows axis of a query they would not be able
to use a dynamic set to show the top-ten products for each year. Also, the way some
client tools generate MDX (once again, Excel 2007 is the main offender here) means
that dynamic sets will not work correctly with them. As a result, they should be used
with care and users must be made aware of what they can and can't be used for.
Summary
In this chapter, we have seen many ways to enhance our cube by adding calculations
to it. Let us briefly recall them:
• Calculated Measures create new measures whose value is calculated using
MDX expressions. The new measures will be available as if they are real
measures, although they do not support drillthrough.
• Calculation dimensions allow us to apply a single calculation to all
of the measures in our cube, avoiding the need to create multiple
calculated measures.
• Named sets allow us to predefine static or semi-static sets of members that in
turn make building reports easier for our users.
In the next chapter, we'll move on to look at a much more complex, but nonetheless
common type of calculation: currency conversion.