MDX - Layering Formulas

As most Essbase users know, ASO has become the future of Essbase. There are some great advantages of ASO over BSO, but it also means having to learn MDX to create member formulas. MDX can be daunting at first, but with practice and persistence, formulas can be created to reflect almost anything that needs to be calculated. You can create a more complex formula by layering formulas together, but to do this you need to know what formulas are available. Fortunately, Oracle does a pretty good job of listing the available formulas and their syntax in their Technical Reference Guide. The 11.1.1 version can be found at: http://download.oracle.com/docs/cd/E12825_01/epm.111/esb_techref/frameset.htm?mdx_grammar_rules.htm.

Below is an example of a fairly complex formula that was created using MDX.

For this example, the Sample Basic database has been converted to an ASO database. Dimension changes include the addition of weeks for each month, using a 4-4-5 calendar, Work Days was added as a member within the Measures dimension, and Fiscal Year was added as a dimension. This is what the new outline looks like:

Let’s say for budgeting or trending purposes you need to need to look at the 4 week average of Sales, but Sales is impacted by the number of work days in the week. You want to exclude any weeks that were not full 5 day work weeks for your calculation. Add a member to the Measures dimension called 4 Wk Avg Sales with the following formula to get the correct calculation.

Case
  When IsEmpty(([Year].CurrentMember,[Measures].[Profit],[Product].[Product],[Market].[Market]))
    Then 0
  Else
  (
  Case
    When (IS([Year].CurrentMember, WK1) OR
               IS([Year].CurrentMember, WK2) OR
               IS([Year].CurrentMember, WK3) OR
               IS([Year].CurrentMember, WK4) OR
               IS([Year].CurrentMember, WK5) )
      Then Avg (Tail(Filter(Crossjoin (Union (Crossjoin (MemberRange([Year].[WK48],
                     [Year].[WK52]),
                     {[Fiscal Year].CurrentMember.Lag(-1)}), Crossjoin (MemberRange([Year].Wk1,
                       [Year].CurrentMember),{[Fiscal Year].CurrentMember})),{[Measures].[Sales]}),
                      ([Market].[No Market],[Product].[No Product],[Work Days]) = 5),4))
    Else Avg (Tail(Filter(Crossjoin (MemberRange([Year].Wk1,[Year].CurrentMember),
                {[Measures].[Sales]}), ([Market].[No Market],[Product].[No Product],[Work Days]) = 5),4))
    End
    )
End

So what does this formula do exactly?  Let's break it down into pieces.

The first part of the formula is a test to see if there is Profit at the very highest levels. If Profit is zero, then it will bring back zero as the 4 Wk Avg. This will prevent the formula for calculating for future weeks or prevent it from not calculating at a lower level, when there is no Profit at that lower level.

    Case
      When IsEmpty(([Year].CurrentMember,[Measures].[Profit],[Product].[Product],
         [Market].[Market]))
        Then 0

The next part is the calculation that will be performed whenever there is Profit at the highest level. The first part of this piece looks at the first 5 weeks of the year and the second part looks at the remaining weeks in the year.

Although it is a little backwards let’s look at the second part of the Else statement first. So for all weeks that not WK1 through WK5 we have the following formula.

    Else Avg (Tail(Filter(Crossjoin(MemberRange([Year].Wk1,[Year].CurrentMember),
                  {[Measures].[Sales]}),
                  ([Market].[No Market],[Product].[No Product],[Work Days]) = 5),4))

This statement is filtering WK1 through the current week Sales based upon where there are 5 Work Days and averaging the last 4 weeks.

The Crossjoin gives us a set of all the weeks Sales.

   Crossjoin(MemberRange([Year].Wk1,[Year].CurrentMember), {[Measures].[Sales]}

The Filter gives us a set where there Work Days is set to 5 for the week.

    Filter(Crossjoin(MemberRange([Year].Wk1,[Year].CurrentMember),
          {[Measures].[Sales]}),
          ([Market].[No Market],[Product].[No Product],[Work Days]) = 5),

The Tail gives us the last 4 members of the set where there Work Days is set to 5 for the week.

    Tail(Filter(Crossjoin(MemberRange([Year].Wk1,[Year].CurrentMember),
          {[Measures].[Sales]}),
          ([Market].[No Market],[Product].[No Product],[Work Days]) = 5),4)

The logic is similar for the first 5 weeks of the year, except we have to look at both current year and prior year to determine our 4 weeks.

    Case
      When (IS([Year].CurrentMember, WK1) OR
                 IS([Year].CurrentMember, WK2) OR
                 IS([Year].CurrentMember, WK3) OR
                 IS([Year].CurrentMember, WK4) OR
                 IS([Year].CurrentMember, WK5) ) 
        Then Avg (Tail(Filter(Crossjoin (Union (Crossjoin (MemberRange([Year].[WK48],
                  [Year].[WK52]), {[Fiscal Year].CurrentMember.Lag(-1)}),
                  Crossjoin (MemberRange([Year].Wk1,[Year].CurrentMember),
                  {[Fiscal Year].CurrentMember})), {[Measures].[Sales]}),
                  ([Market].[No Market],[Product].[No Product],[Work Days]) = 5),4))

This time we first want to Crossjoin the weeks to the Fiscal Year for both current year and prior year and since we want to look at both, we are going to use Union to join these two sets together.

    Union(Crossjoin (MemberRange([Year].[WK48],[Year].[WK52]),
             {[Fiscal Year].CurrentMember.Lag(-1)}),
             Crossjoin (MemberRange([Year].Wk1,[Year].CurrentMember),{[Fiscal Year].CurrentMember}))

Now we can Crossjoin the Unioned sets to Sales.

    Crossjoin (Union (Crossjoin (MemberRange([Year].[WK48],[Year].[WK52]),
             {[Fiscal Year].CurrentMember.Lag(-1)}),
             Crossjoin (MemberRange([Year].Wk1,[Year].CurrentMember),
             {[Fiscal Year].CurrentMember})),
             {[Measures].[Sales]})

From here we apply the same Filter, Tail, and Average logic to finish the statement.

Here is sample output showing the formula results:

Note:  A similar formula can be used in the time dimension Year, but it would only be for the current week, with no historical values stored.  The formula would also have to be modified for the current year to be from WK1 to a substation variable that is set up to be the current week.