Oracle Essbase Connector

“Essbase” is short for “extended spreadsheet database”. It is a multidimensional database management system (MDBMS) owned by Oracle that provides a multidimensional database platform upon which to build analytic applications.

Essbase Prerequisites

Install Java and Java Web Start. To connect to Essbase, you need to have Java installed on your machine, as well as Java Web Start. For Open JDK, there is a Java Web Start implementation called “IcedTea”.
Add Oracle Essbase as a data source.
Define the Essbase connector parameters.

Essbase Parameters

To connect to an Essbase database, you need to know the following:

Parameter	Description
Server	IP Address
Username	Username for Oracle Essbase
Password	Password for Oracle Essbase
App
Database (Cube)

Prerequisites for Essbase

You can connect to Essbase by opening this URL in your browser:

http://:9001/easconsole/console.html

And then click Launch, which will launch the Essbase console, then login using the username and password.

At the time of this writing, there is an Essbase instance owned by Incorta that can be used for testing with the following details:

URL: http://70.38.54.74:9001/easconsole/console.html

Username: epm_admin

Password: Hyp3r10n

Essbase Parameters Server Username Password App Database (Cube)

Cubes and Dimensions

While data in a regular RDBMS is stored in tables, data in Essbase is stored in Cubes. A cube is similar to a table but it has multiple dimensions, and each dimension has multiple members. At the intersection of these dimension members there are data points (decimal values).

A typical Essbase cube has many dimensions. Users can execute queries to select specific dimensions and filter by specific members.

Hierarchy

Each cube dimension has multiple values, called “members”. These members are organized in a hierarchy, where each member has child members.

Generations and Levels

Generations and levels are numeric values that represent the depth of a member in a hierarchy.

Generation is a 1-based number representing the depth from the root (e.g. “Time” is generation 1, “Quarter 1” is generation 2, “Jan”, “Feb” and “Mar” are all generation 3).

Level is a zero-based number representing the depth from the leaf (e.g. “Jan” is 0, “Quarter 1” is 1, “Time” is 2).

Member Details

Each hierarchy member has a name and an alias. Name does not have to be globally unique. Alias is optional. The only identifier that is guaranteed to be globally unique is the full member path (e.g. [Year].[Qtr1].[Jan])

What is MDX?

MDX (short for “Multi-Dimensional eXpressions) is the query language used for selecting data from an Essbase cube. It is the Essbase equivalent of DML. The basic idea is that we select one or more dimensions in columns and one or more dimensions in rows. The intersection of rows and columns (cells) are the data values.

For example, below is the result of executing an MDX query on the Sample/Basic cube. We select all the leaves (level-0 members) of the Measures dimension on columns, and all the leaves of the Year dimension on rows.

It is possible to select multiple dimensions on rows or columns. In this case, each column or row header will contain a tuple of members instead of just a single member. Below is an example of selecting the cross-join of Year and Market dimensions in the rows axis (i.e. all possible combinations):

What is MaxL? MaxL is the query language used to obtain metadata from Essbase. It is the Essbase equivalent of DDL.

Incorta uses MaxL to discover dimensions cube.

Essbase Connector

Table Types There are two types of Essbase tables:

Dimension tables
MDX Query table

Dimension Tables extract all the members of a particular dimension, including hierarchy information (i.e. which member is child of which member).

The table columns are auto-generated by the connector:

FullName: The table key, containing the full name of the member (e.g. ”[Year].[Qtr1].[Jan]“) Name: The member name (e.g. “Jan”). Note that name is not necessarily unique. Alias: The member alias, if any. Parent: The full name of the parent member. There is a self join between this column and the FullName column. GenNumber: The member generation number, as explained above. LevelNumber: The member level number, as explained above.

MDX Query Tables

Executes an MDX query and extracts its results. Each cell in the Essbase result set is mapped to a row in Incorta. Columns are mapped as follows:

Each row dimension is mapped to a table column
Each column dimension is mapped to a table column.
One more column named “Value”, contains the cell value.

So basically, each row contains a combination of dimension members, plus the cell value.

Essbase and Schema Wizard

Create Essbase Data Set

In the schema wizard, Essbase data source can be used to discover dimension tables only.

Dimension Table Data Set

To create a dimension table, you must set the “Type” field to “Dimension”, then enter the dimension name. It is easier to do this using the schema wizard.

MDX Query Table Data Set

To create a dimension table, you must set the “Type” field to “MDX Query”, then enter the query text in the “Query” field.

There are some important things to note in MDX Query tables:

Discovery Query

During discovery, a flag named “dataLess” is set to true to indicate that we don’t need the server to return any data. However, even with this flag, discovery can be slow. This is an issue in Essbase itself.

As a workaround, we have an optional field named “Discovery Query”, where user can enter an MDX query with similar structure to the original query, but more restricted in the data it returns to allow faster discovery. If not set, the original query is used for discovery.

Dimension Properties

By default, only the member names are included in the result set. Extracted data look like this:

As discussed earlier, names are not necessarily unique (for example, there can be more than one member named “Sales”).

To avoid name conflicts, we need to instruct Essbase to return all the ancestors of each member, so we can use the full member name instead. This is done by adding “DIMENSION PROPERTIES [ANCESTOR_NAMES]” in the MDX queries for each dimension, as shown in the screenshot (the underlined part).

When we do this, Incorta will automatically detect this information and use full member names to identify members instead of simple names, as shown below:

Incremental Loading and Essbase

Essbase connector does not currently support incremental loading, because we can filter only by dimension members, not by arbitrary timestamps.