Date Dimension Display Problem

Last week I was working with one of the new features introduced to Essbase in version 11, date dimensions.

The goal is to store a start date and end date that can be utilized in calculations. I first started looking for where to enable dates and ended up stumbling upon a less intuitive step. To utilize dates you must Enable Typed Measures in the Outline Properties tab. I agree to the somewhat scary message that once enabled in an outline this cannot be disabled. After that a date format must be selected. I chose mm/dd/yyyy since it aligns with my source data. Now I was ready to add my two new dimensions, Start Date and End Date. I set them up to be a Date Type and to never aggregate and save my outline changes. I did a quick test of submitting data to the cube using Smart View. When I retrieved the results back everything looks perfect. I have dates in the cube and everything is looking good.

I then moved toward setting up my load rule to load my dates and my data. For right now I am using a flat text file. I set everything up and load data. After getting a success message, I went to validate my data in Smart View and that is where things started to get interesting. Every date is off by one day. My 02/01/2010 start date is showing 01/31/2010. I figure I must have set something up wrong and go back and verify my outline and load rule settings. I find everything in its place. I spent some time researching online and on the message boards and don’t find anyone having a similar issue. I went through the Knowledge section on My Oracle Support searching for an Essbase bug that might explain my bizarre results, no dice.
Not really sure what to do next, a level 0 export is pulled look at the underlying data. Low and behold, there is the 02/01/2010 start date. So the data is being loaded correctly, but not being reflected correctly in Smart View. A quickly built report in Financial Reporting Studio finds the data being displayed incorrectly there as well. Armed with this information, article 950835.1 is found on the Knowledge section of My Oracle Support. It’s a bug! And it is due to be fixed in version 11.1.2.

So now there are two immediate courses of action that come to mind, neither of which are ideal.
1) Load the data and have it display as one day off until the system is upgraded to 11.1.2. The up side to this option is that no data will need to be reloaded once the upgrade has taken place. The down side is that this may not be a viable option for the business.
2) Adjust the upload file to increase the date by one day so that when it is loaded, it will display the correct data. The upside to this is that the business is happy and dates are accurately reflected for reporting. The down side is that once an upgrade takes place, the data will have to be reloaded or else all are dates will be wrong. Remember the dates are correct in the database and are only displaying incorrectly.

So I take the weekend away for this and figure I may have to explain to the business the situation and find out how important the date is or if one day will make a difference or not. Then it hits me there is another solution. The dates can be loaded as they are to a member that is called Start_Date. Another member can be set up with a member formula that advances the date by one day. Access to Start_Date can be restricted or moved to an area in the Measures dimension that will be ignored by the end user. This is a win-win situation. The data is loaded correctly in the database now and can be accurately reflected for reporting. Once upgraded to the version with the fix, the calculated member can be removed and the stored member renamed and no data will need to be reloaded.

The only part that hasn’t been tested in this theory yet is the ease of doing calculations off of the calculated member. If that gets to be cumbersome though, adjustments can be made to calculations to simply add 1 to whatever the result is.