MDX - SQL Server Analysis Services 2012 Cube Development - page 215

Defining query level calculations and
named sets
In addition to listing the existing dimension hierarchy members, MDX queries often in-
clude calculations. If your application repeatedly references the same calculation, you
should define such calculations in the MDX script. However, ad hoc queries will often
necessitate calculations that are not defined within the cube. Query-level calculations
are defined using the WITH MEMBER clause. Calculations included within the Meas-
ures dimension are often called calculated measures, whereas within other dimen-
sions they're referred to as calculated members. A named set allows grouping of one
or more dimension members to perform additional calculations specific to the group or
to more elegantly reference the collection. Use the WITH SET keywords to define the
named set. Each query must specify the WITH keyword only once; if a query defines
a calculated measure using the WITH MEMBER construct, you don't need to repeat
WITH for the named set. In this case your query will have a notation similar to the fol-
lowing:
WITH MEMBER measures.my_calculated_measure AS
"calculation definition"
SET my_named_set AS "set definition"
This section will also teach you how to use named sets for two common requirements:
defining a date range and aggregating data for several dimension members.
How to do it...
Let's get started with defining query level calculations and named sets.
1. To define a calculated measure for deriving the average unit price, let's use
the [reseller unit price] and [reseller transaction count]
measures. The average unit price will be the unit price divided by the transac-
tion count. We will also include the FORMAT_STRING="Currency" option to
limit the number of digits to the right of the decimal points: