Working with Oracle Essbase data in Spotfire

If you have access to data in an Oracle Essbase system, you can use the Oracle Essbase connector in Spotfire to connect to your OLAP cube. On this page, you can find information about how to work with your Oracle Essbase data in Spotfire

Measures in Oracle Essbase

In an Oracle Essbase cube, you organize measures in one dimension, which you tag as the accounts dimension. If no dimension in the cube is tagged as accounts, you must specify the dimension where the measures in the cube are located, when you create a connection in Spotfire.

You define the dimension to use for measures in the Select Measure Dimension dialog, which is displayed automatically when you connect to an Oracle Essbase cube that does not have an accounts dimension.

Formula-based measures

Some measures in Oracle Essbase cubes are calculated based on non-dynamic Essbase formulas. By default, the possibility to aggregate such calculated measures with MDX is disabled, because aggregating them can yield unpredictable results.

For formula-based measures that you know have a definition that makes them able to be aggregated, you can enable MDX aggregation. When you select data to include in your connection, in the Data Selection in Connection dialog, click to select the formula-based measure you want to configure, and, in the Settings pane, select Allow aggregation with MDX.

Hierarchical measures

In Oracle Essbase, measures can be organized in hierarchies, in contrast to other cube data sources.

When the data source structure is shown in Spotfire, a measure that is also a parent is shown both as a folder and as a separate measure, which is slightly different compared to what the structure looks like in the Oracle Essbase Administration Services user interface.

Example of Structure

In the Data Selection in Connection dialog in Spotfire, only metadata is shown. This means that even if the Show both member and alias check box has been selected in the Oracle Essbase connection dialog, the member names will still be shown in the Data Selection in Connection dialog. The aliases will be shown in the data when the selection has been added to Spotfire.

External (in-database) data from Oracle Essbase

Each time you make a selection in a hierarchy filter or a check box filter, a request is sent to the Oracle Essbase database to provide the selected data. This means that if you select/clear several check boxes in a filter in Spotfire, many requests may be sent to the database instead of just the request for the final selection. You should try to avoid sending many requests since too many requests at the same time may overload the database and make it go down.

Spotfire uses a delay of about one second in order to see if there are any additional selections made in a filter before sending the request to the database, but you should also try to restrain the number of changes you do to the data selection in general, when working in-db.

Tip: If you filter to only see a few selections, the recommendation is to first right-click on the filter and select Deselect All Values, and then select the specific check-boxes you want.

Imported (in-memory) data from Oracle Essbase

When it comes to import of data from a cube, the most important thing to think about is to limit the data to import as much as possible by making sensible selections.

See Importing data from cube data sources for details.