Advanced Dimension Design - Professional SQL Server Analysis Services 2005 with MDX - page 322

2005 allows you to specify the calculations through MDX expressions as
a property to the hierarchy.
Here is an example. If there is an Account dimension that indicates the types of
accounts of your company, such as asset, liability, income, and expenditure, and
you have a measure called Amount, the rollup of the values to the parent member
is not a simple sum. In such a case, you need to specify a custom rollup. If the
hierarchy is a parent-child hierarchy, Analysis Services 2005 allows you to perform
a custom rollup using a feature called the unary operator if it is a simple operation
(addition, subtraction, do not rollup the values), which is discussed in the next sec-
tion. However, if the value of a member has not derived from its children or you
have a user hierarchy where you have to rollup the values to the parent using a
complex operation or custom formula, you specify the custom rollup using a prop-
erty called CustomRollupColumn for an attribute hierarchy.
The CustomRollupColumn property of an attribute should be set as a column in
the relational table that will contain the custom rollup calculation — which is an
MDX expression. For example, in the Account dimension in Adventure Works DW
sample, the value for Account Average Unit is calculated from the Accounts Net
Sales and Units, which are members in the Account dimension under different par-
ents. In order to specify the custom formula for a member, the column in the re-
lational table should contain an MDX expression that evaluates the value for the
member. You need to specify an MDX expression for each member a custom for-
mula needs to be applied to. In Analysis Services 2000 you had properties to spe-
cify custom formulas to members and Custom Rollup Formulas to levels within di-
mensions. You can consider the CustomRollupColumn property for a hierarchy in
Analysis Services 2005 as merging the two properties in Analysis Services 2000.
It would have probably been better to name this property CustomFormula instead
of CustomRollup. Follow the steps below to understand the behavior of a custom
rollup by using the sample Adventure Works DW relational database.
1. Create a new Analysis Services project called AnalysisServicesTutorial
using BIDS.
2. Create a data source to the AdventureWorksDW relational data-
base on your SQL Server instance.
3. Create a Data Source View that contains all the tables except Adven-
tureWorksDWBuildVersion, DatabaseLog, and ProspectiveBuyer within the
Adventure Works DW data source, as shown in Figure 8-3 .