Tip

When case is not the case in an Essbase ASO cube

Thomas Thuresson, TopDown Consulting

In one of our projects, we recently ran into an issue where an existing Year-To-Date member calculation in an Essbase ASO cube did not return the correct results for some accounts. After some

    Requires Free Membership to View

detailed investigations, we realized the issue had to do with the EndOfPeriod (EOP) members.

First, we realized that the issue only occurred when there were two or more EOP members with identical values, like in the example below.

Jan and Mar EOP values are identical, and as a result, the YTDEOP calculation didn't calculate correctly in the Essbase ASO cube. We changed one of the EOP values slightly so that all EOP values are unique, and the YTDEOP calculated correctly.

The Multidimensional Expressions (MDX) formula written to calculate YTDEOP was a simple CASE statement, like the example below.

CASE [Period].CurrentMember
     when[JAN] then ([JAN],[EOP])      when[FEB] then ([JAN],[EOP]) + ([FEB],[EOP])      when[MAR] then ([JAN],[EOP]) + ([FEB],[EOP]) + ([MAR],[EOP])      …

This looked like a perfectly fine calculation and it did calculate correctly in most circumstances. It was only when two or more EOP values were identical in the Essbase ASO cube that it didn't calculate as expected. To investigate how the calculation really behaved, we created a new calculated member (YTDEOP_CT) with a simple CASE statement like in the example below.

CASE [Period].CurrentMember

    	WHEN[JAN] then 1
    	WHEN[FEB] then 2
    	WHEN[MAR] then 3
    	…                   

And lo and behold, the returned number was 1, not 3, for Mar YTDEOP_CT.

This indicates that the simple CASE statement in the calculation does not compare with the member name, but with the value of the member. When it gets to the March member, the value is the same as what is in Jan, so it does the calculation at the January member, which in the example is just that value. Basically the value -6235.96 is the same as the value in Jan, so therefore it meets a "true" condition in the CASE statement and the result is Jan, EOP.

The correct CASE statement in this case, no pun intended, is to do a Searched Case statement that references the member name and not the value.

CASE

    	WHEN IS ([Period].CurrentMember, [JAN]) THEN ([JAN], [EOP])
    	WHEN IS ([Period].CurrentMember,  [FEB])  then ([JAN],[EOP]) + ([FEB],[EOP])
    	WHEN IS ([Period].CurrentMember,  [MAR]) then ([JAN],[EOP]) + ([FEB],[EOP]) + ([MAR],[EOP])
    	…

Or, better yet, don't use a CASE statement in the Essbase ASO cube at all. Instead, write an MDX calculation like one of the examples below. One line of MDX code, fully dynamic, is easier to maintain and has less of a chance for typos or copy-paste errors.

YTDEOP_S1:
    SUM([JAN]:[Period].CurrentMember, [EOP])
    OR  YTDEOP_S2:
    	SUM(PeriodsToDate(), [EOP])

Note that the PeriodsToDate function requires that the Period dimension is tagged as a Time dimension.

About the author:
Thomas Thuresson is a senior enterprise performance management and business intelligence consultant at TopDown Consulting. He has 15 years of experience as an EPM and BI consultant.

This was first published in February 2014

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.