To review, this was identified as a bug by Oracle that was fixed in 11.1.2.x. For those of us that are not yet on that version, this is still an issue. As noted in my previous post, the date is stored correctly, but just being displayed one day less than the date stored. For example: 2/11/2013 displays as 2/10/2013. I had suggested loading the data to one member and then using formulas to display the correct dates.

So to expound upon my previous suggestion, here is how we are going to handle the date display issue.

Let’s say we have an effective date that needs to be stored. We are setting up 2 date measures:

1) Effective_Date – Date type member within account dimension where we will load and store the date

2) Effective Date – Date type member within account dimension where we will perform a calculation to roll the date forward by one day so that the date displayed is correct.

The formula in our Effective Date member utilizes the DateRoll MDX formula. The formula would be:

DateRoll([Hire_Date], DP_DAY, 1)

Now we have our data stored correctly in one member(Effective_Date) and have a member that can be used for reporting purposes (Effective Date). For end users, we will likely set up a filter that provides them no access to Effective_Date so there isn’t confusion as to which date is the correct date. In addition or as an alternative, you can create a small rollup within your account dimension that is for the stored dates. You can even label it as a do not use. It is really preference.

The beauty of this plan is that once we do upgraded to 11.1.2.x, we can remove our Effective Date formulated member and add “Effective Date” as an alias to our Effective_Date member. This will allow the process to be seamless to our users as they almost always use alias table when doing their ad hoc analysis. So having “Effective Date” on one of their retrieves, while pulling a different member, should still return the same results, eliminating the need to update any retrieves where this data was being utilized. Note: If you have applied a filter to limit access to these members then you will just need to remove that filter. If you decide to add a node to “hide” the stored members in prior to upgrade, just remember to move them back into the main hierarchy so users can easily find them.

As a note: The DateRoll function can be used to roll a date backwards or forwards by any part of the date (Year, Quarter, Month, Week, or Day).

About Me

I’m not an Essbase guru or consultant. I am an everyday Essbase user with over 5 years experience who develops as well as supports. I often find I run into situations where I find myself saying “I can’t be the only one trying to do this.” I have found several resources that are often times helpful, but have just as many instances where the answer is not readily available anywhere. For that reason, I decided to start a blog to chronicle these oddities in the hopes of being able to help others in the Essbase community as well.