Essbase: Loading Dates as Data

By default, only data values can be loaded into Essbase. However, sometimes it is helpful to load dates into an application (i.e. Product Inception Date). Follow these steps to enable an Essbase application to accept dates as data.

The first step is to edit the Outline in Essbase:

Navigate to the properties tab. “Typed measures enabled” will need to be set to True for dates to be activated:

Once typed measures are enabled, you will not be able to undo this setting:

Next, select the format that the date will display in Essbase:

Once the typed measures functionality has been enabled, select a member and set its properties to display date data. For this example, I created a member labeled “Inception Date” in the Measures dimension. Right click on the member and select Edit member properties:

Where the Type is set to Numeric, select Date from the dropdown menu:

Notice that the consolidation setting is set to “(^)Never”. This is the default setting for date members. Change this setting to “(~)Ignore” as Never Consolidate may cause some problems in an ASO outline if it is the first child in the hierarchy.

To load date data, make sure that the date is in the format selected from the properties tab. If mm/dd/yyyy is selected, represent that in the data file (i.e. 10/31/2011). In this example, there are 4 inception dates loaded for their respective products:

Notice that the inception dates are loaded to “Year N/A”. Rather than having to search for the relevant Year member for each Project, all Inception Date data is loaded to “Year N/A”. This keeps all of the Inception Date info in a more centralized location and facilitates quicker analysis.

Update (1/19/2015):

After loading the date data into Essbase, I was no longer able to export level 0 data. I would receive the following error:

• Error: 1270042 Aggregate storage data export failed

After working with the Oracle Development team, they were able to conclude that there was an available workaround. This required that the Accounts dimension be switched from “Compression: True” to “Compression: False”:

After updating this setting and saving the outline, the level 0 data exported successfully.

One thing to note, even though this setting allows the data to be exported, the .txt file is about 3 times larger than if the Accounts dimension still had compression enabled. This is something to keep in mind if you’re dealing with a larger database as there is a very real possibility that performance will be affected…