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…



Getting large numbers in FR Reports?

When using Workspace to view reports, some users have seen excessively large numbers that don’t belong. If you are having this issue, it could very well be because the default Essbase query engine in 11.1.1.x is the MDX query engine, which can cause documented bug 9062413. Essentially, this bug will cause users to see the same astronomical number in every cell that sits on an intersection to which the user does not have security access. Understandably, this can cause some concern. This issue is expected to be fixed in a future release, but until then, the query engine will need to be manually changed.
The first option is to fix the issue at a report level. This is a relatively quick process and is a good idea if you only have a handful of reports.  To change the query engine setting for a particular report, follow the steps below:

1. Open the report.
2. For each grid, select the entire grid.
3. Right-click and select Data Query Optimization Settings.
4. Deselect the option “Essbase Queries Use MDX.”
5. Save the report.

Repeat the above process for each report.

For users that have a larger number of reports, a better option may be to change the query engine in the properties file on the server.  The benefit to fixing the issue in the properties file is that changes only need to be made once, and all reports will reflect this change.
The file that needs to be edited is located on the Financial Reporting (app) server, typically on the path D:\<Hyperion Home>\products\biplus\lib where <Hyperion Home> represents the root location of the Hyperion install. The file that needs adjusted is the fr_global.properties file.
Open the properties file and add these lines:

# MDX Query Engine has been set as the default in Essbase 11.1.1.x. This can cause bug 9062413 
# which may cause unauthorized users to see a long series of numbers in each cell when running  
# reports. To solve this issue, the below line was added, which switches the query engine.
EssbaseUseMDX=false

Any line preceded by “#” is commented out. Therefore, these can say whatever you prefer, but should give anyone that views this file a good indication why this text is in the file.

Once the properties file has been updated, the following services must all be stopped in the following order, then started in the same order for the changes to take effect.

1. Hyperion Financial Reporting – Print Server
2. Hyperion Financial Reporting – Report Server
3. Hyperion Financial Reporting – Scheduler Server
4. Hyperion Financial Reporting – Web Application (Note – This service may be on the FR (Web) server, not the FR(App) server like the other three services.)

Note – This modification will apply to everyone using the server on which they are made, so be careful when making changes to a shared server.