Dimension-Based MDX Calculations - Microsoft SQL Server 2008 Analysis Services - page 226

Follow these steps to assign different aggregation functions to different accounts:

1. Assign the ByAccount aggregation function to the Amount measure.
2. Map each member of an account dimension to a specific account type. Analysis
Services recognizes any dimension as an account dimension if the dimension type is
Account . To do this mapping, you follow these steps:

A. Add a column to the dimension table in the relational database. Each row of
the column corresponds to member of the Account dimension and has an
account type associated with a member. In general, the account type can be
any word or any string expression that makes sense to your organization; for
example, it could be Income , Expense , Asset , or Liability .

B. Create a new attribute AccountType of type AccountType , in the Account
dimension based on the new column. This attributed is related to the key
attribute of the Account dimension such that there is a one to one relationship
between members of the Account attribute and AccountType attribute.
3. In the Account collection property of the Database object, define the mapping
between account types (defined by the AccountType attribute) and aggregation func-
tions as described next.

Because different companies have different rules for aggregating different accounts, and
different companies also have their own naming systems for different account types,
Analysis Services enables you to define custom mapping between account types and aggre-
gation functions. You can create this mapping either by issuing a DDL request that alters
the database or by using BI Dev Studio. Listing 13.1 shows a portion of the DDL that
defines the mapping between account types and aggregation functions.

LISTING 13.1

Account Type Mapping

<Accounts>
<Account>
<AccountType>Balance</AccountType>
<AggregationFunction>LastNonEmpty</AggregationFunction>
<Aliases>
<Alias>Balance</Alias>
</Aliases>
</Account>
<Account>
<AccountType>Asset</AccountType>
<AggregationFunction>LastNonEmpty</AggregationFunction>
<Aliases>
<Alias>Asset</Alias>
</Aliases>
</Account>
</Accounts>