Database Reference
In-Depth Information
solve order from the default value. I strongly recommend reading Chapter 7 where Dan
Pressman speaks extensively on this subject.
6.7.4 ASO Member Formulas
member formulas are very common in Essbase applications. A member formula can be
something as simple as [Sales] - [CogS] or it can be extremely complicated with many
lines of code. Developers should remember that member formulas are dynamic and
they execute upon query. unlike BSo applications, you cannot calculate members with
formulas at the bottom of a hierarchy, and then roll the results up to the higher levels.
This must be done either prior to loading of the data or as of the latest release of Essbase
v11.1.2.1 using the ASo calculation script functionality. Developers need to keep this
in mind and weigh various optimization strategies when leveraging mDx member
formulas.
6.8 aDvanCeD mDx
6.8.1 Conditional Logic
A very common requirement when creating mDx calculations is to implement condi-
tional logic. Conditional logic is most commonly thought of as an “IF” statement (i.e.,
if “A” then “B” otherwise “C”). mDx has an IIF function to perform this type of logic.
note the IIF function has two “Is”. In addition to the IIF function, mDx also has a CASE
statement. CASE works similar to IIF, but can often be easier to work with, particularly
with multiple criteria logic.
Let's start off with a simple IIF and comparable CASE statement. going back to the
beginning of the chapter, the manager has asked to see a “What If ” analysis showing
Actuals times 10%. As shown in the beginning of the chapter, this was accomplished
with the following member formula ([Sales], [Actual]) * 1.1. As a query, it would look
like the following:
WITH MEMBER [Scenario].[What If] AS
'([Sales], [Actual]) * 1.1'
SELECT
{[Scenario].[Actual], [Scenario].[What If]} ON COLUMNS,
{[Qtr1].Children} ON ROWS
FROM Sample.Basic
WHERE ([Measures].[Sales])
Axis-1 (Actual) (What If)
+---------+---------+---------
(Jan)
32538.00 35791.80
(Feb)
32069.00 35275.90
(Mar)
32213.00 35434.30
now, let's assume the manager has changed the criteria a bit and says the “What If ”
analysis should show an increase of 10% in the first period, but then only 5% for the
other periods. using the IIF function we could do the following:
WITH MEMBER [Scenario].[What If] AS
'IIF([Year].CurrentMember IS [Jan], ([Sales], [Actual]) *
1.1, ([Sales], [Actual]) * 1.05)'
