Writing Formulas on Aggregate Storage Outlines

Write MDX formulas on Essbase aggregate storage (ASO) outlines using the formula editor. Formulas enable you to apply basic mathematical equations, calculate members across dimensions, perform conditional tests, and filter based on user defined attributes (UDAs).

Composing Formulas on Aggregate Storage Outlines

Formulas are plain text. You can type the formulas directly into the formula editor, or you can create a formula in any text editor and paste the text into the formula editor.

MDX-based syntax checking tells you about syntax errors in formulas; for example, a mistyped function name or a nonexistent member. Unknown member names can be validated against a list of function names. If you are not connected to the service instance or to the application associated with the outline, Essbase may connect you to validate unknown names.

Syntax checking occurs when validate or save a formula. Errors are displayed in the user interface. If an error occurs, you choose to save or not save the formula. If you save a formula with errors, you are warned when you save the outline. When you calculate a formula with errors, the formula is ignored and the member is given a value of $MISSING.

A syntax checker cannot warn you of semantic errors in a formula. Semantic errors occur when a formula does not work as you expect. One way to find semantic errors in a formula is to place the numeric value expression that defines the formula into an MDX query and run it, to verify that the results are as you expect.

You can include formulas in a dimension build data source. See Set Dimension Build Field Type Information.

Basic Equations for Aggregate Storage Outlines

You can apply a mathematical operation to a formula to create a basic equation. For example, the following formula is applied to the Avg Units/Transaction member in the ASOsamp.Sample cube:

[Units]/[Transactions]

The formula in Avg Units/Transaction divides the number of units by the number of transactions to arrive at the average number of units per transaction.

In aggregate storage outlines, members cannot be tagged as expense items. Therefore, functions in Calc, such as @VAR and @VARPER, which determine the difference between two members by considering expense tags, are not relevant in aggregate storage outlines.

The MDX subtraction operator can be used to calculate the difference between two members. For example, the following formula can be applied to a new member, called Price Diff, in ASOsamp.Sample, to calculate the difference between the price paid and the original price:

[Price Paid]-[Original Price]

Members Across Dimensions in Aggregate Storage Outlines

ASOsamp.Sample provides a formula on a member called % of Total. This member formula identifies the percentage of the Measures total that is produced by Transactions. The formula for % of Total:

Transactions/
(Transactions,Years,Months,[Transaction Type],[Payment Type],
Promotions,Age,[Income Level],Products,Stores,Geography)

The formula specifies a member (Transactions) divided by a tuple (Transactions, Years, ...). The formula lists a top member from every dimension to account for all Transaction data in the cube; that is, not Transaction data for the Curr Year member but Transaction data for all members of the Years dimension, not Transaction data for months in the first two quarters but Transaction for all months, and so on. In this way, the value of % of Total represents the percentage of the Measures total that are produced by Transactions.

Conditional Tests in Formulas for Aggregate Storage Outlines

You can define a formula that uses a conditional test or a series of conditional tests to determine the value for a member. Use the IIF function to perform a test with one else condition. You can nest IIF functions to create a more complex query.

The example specifies a formula for a member that represents the price the company must pay for credit card transactions, which includes a 5% charge. The following example assumes that the Credit Price member has been added to the Measures dimension of the ASOsamp.Sample cube. Credit Price has the following formula, which adds 5% to Price Paid when the payment type is a credit card.

IIF (
   [Payment Type].CurrentMember=[Credit Card],
   [Price Paid] * 1.05, [Price Paid]
)

Use the CASE, WHEN, THEN construct to create formulas with multiple tests and else conditions.

The Filter function returns the tuples of the input set that meet the criteria of the specified search condition. For example, to establish a baseline (100) for all products, you can add a Baseline member and create a formula for it, as follows:

Count(Filter(Descendants([PersonalElectronics],
[Products].Levels(0)),[Qtr1] > 100.00))

Specifying UDAs in Formulas in Aggregate Storage Outlines

UDAs are words or phrases that you create for a member. For example, in Sample.Basic, top-level members of the Market dimension have the UDA Small Market or the UDA Major Market.

The Major Market example used in this topic shows how to create a formula for a member that shows the sum of sales for all major market members. The example assumes that a new member (Major Market Total) has been added to Sample.Basic.

MDX provides a Boolean function, IsUDA, which Returns TRUE if a member has the associated UDA tag. The following syntax returns TRUE if the current member of the Market dimension has the UDA “Major Market”:
```
IsUda([Market].CurrentMember, "Major Market")
```
A Filter function, when used with IsUDA (as shown in the following syntax), cycles through each member of the Market dimension and returns a value for each member that has the Major Market UDA:
```
Filter([Market].Members, IsUda([Market].CurrentMember, "Major Market"))
```
The Sum function adds the values returned by the Filter function; for the Major Market example, the following formula is produced:
```
Sum (Filter([Market].Members, IsUda([Market].CurrentMember, "Major Market")))
```
This formula is attached to the Major Market Total member.