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

Trying to describe how this technique works in purely theoretical terms is almost
impossible, so let's look at some practical examples of how it can be implemented.
First, we provide a simple example albeit flawed in several ways and later we
provide details of the best practice in implementation of calculation dimensions.
Implementing a simple calculation dimension
Here is what we need to do to create a simple calculation dimension:
• Add a new hierarchy to the Date dimension. We will call it
Date Calculations .
• Ensure that the hierarchy only has one member on it, called Real Value ,
and set it as the default member. The new hierarchy should also have its
IsAggregatable property set to False , so there is no All Member. Since the
Real Value member is the only real member on the hierarchy, at this point it
will always be selected either implicitly or explicitly for every query we run
and will have no impact on what data gets returned. We have not expanded
the virtual space of the cube at all at this point.
• Add a new calculated member to the Date Calculations hierarchy called
Year To Date .
• Define the Year To Date member so that it calculates the year-to-date
value of the current measure for the Real Value member on the Date
Calculations hierarchy. By referencing the Real Value member in our
calculation, we're simply referencing back to every value for every measure
and every other hierarchy in the cube - this is how our new calculation will
work for all the measures in the cube.
The first step is easily accomplished by adding a new named calculation to the Date
view in the Data Source View. The SQL expression in the named calculation will
return the name of the only member on the Date Calculations hierarchy; in our
case, the string Real Value . We will then need to use the new column to create a
new attribute called Date Calculations in our Time dimension.
The last two steps are the most important ones. We first define the new
calculated member as NULL , and then we use a scoped assignment to
perform the actual calculation:
CREATE MEMBER CURRENTCUBE.[Date Order].[Date Calculations].[All].[Year
To Date] AS NULL;
SCOPE ([Date Order].[Date].MEMBERS,
[Date Order].[Calendar Semester].[Calendar Semester].MEMBERS , [Date
Order].[Date Calculations].[Year To Date]);