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

Calculation dimension best practices
Now that we are confident with both the basic concept of a calculation dimension
and how it should be built, let's now go through the process of building one in more
detail, following the best practices we've mentioned.
The first step is to create a new physical dimension, with real members for each of
the calculations we're going to need. We don't actually need to create a table in our
data warehouse for this purpose, we can do this with an SQL view like this:
CREATE VIEW DateTool AS
SELECT ID_Calc = 1, Calc = 'Real Value'
UNION ALL
SELECT ID_Calc = 2, Calc = 'Year To Date'
UNION ALL
SELECT ID_Calc = 3, Calc = 'Previous Period'
This view contains three rows, one for each member we need.
Next, we need to add this view to our DSV and create a dimension based on
it. The dimension must have one hierarchy and this hierarchy must have its
IsAggregatable property set to False : it makes no sense to have an All Member
on it because the members on it should never be aggregated. The DefaultMember
property of this hierarchy should then be set to the Real Value member. Giving this
dimension a name can be quite difficult, as it should be something that helps the
users understand what it does - here we've called it Date Tool, but other possibilities
could be Time Calculations or Date Comparisons.
After it has been processed, the single hierarchy should look like this: