Connectors → Oracle Essbase

About Oracle Essbase

Oracle Essbase is a multidimensional database management system and platform upon which analytic applications can be built. Essbase stands for Extended Spreadsheet Database.

About the Oracle Essbase Connector

The Oracle Essbase connector supports the following Incorta specific functionality:

Feature	Supported
Chunking
Data Agent
Encryption at Ingest
Incremental Load
Multi-Source
OAuth
Performance Optimized	✔
Remote
Single-Source	✔
Spark Extraction
Webhook Callbacks	✔

While data in a regular Relational Database Management System (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).

In the example below, there are three dimensions:

Region (with members North and South)
Time (with members Jan, Feb and Mar)
Account (with members Quantity, Sales, Expenses and Profit)

A typical Essbase cube has many dimensions. You 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. For example, the Time dimension in the example above can be represented as follows:

Time
- Qtr1
  - Jan
  - Feb
  - Mar
- Qtr2
  - Apr
  - May
  - Jun
- Qtr3
  - Jul
  - Aug
  - Sep
- Qtr4
  - Oct
  - Nov
  - Dec

If you filter by Qtr1, data from Jan, Feb and Mar will be selected. If you filter by Time, all data is selected.

The following is an example hierarchy.

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, Qtr1 is generation 2, Jan, Feb and Mar are generation 3).

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

Member Details

Each hierarchy member has a name and an alias. The 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]).

Multi-Dimensional eXPressions (MDX)

Multidimensional eXpressions (MDX) is the query language used for selecting data from an Essbase cube. It is the Essbase equivalent of data manipulation language (DML). The basic idea is to 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.

The following is the result of executing an example MDX query. The query selects 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. The following is an example of selecting the cross-join of Year and Market dimensions in the rows axis (i.e. all possible combinations).

MaxL

MaxL is the query language used to obtain metadata from Essbase. It is the Essbase equivalent of dynamic definition language (DDL).

Incorta uses MaxL to discover cube dimensions. The following is a sample MaxL query for listing cube dimensions.

Table Types

There are two types of Essbase tables:

Dimension tables
MDX Query table

Dimension Tables

Dimension tables extract all the members of a particular dimension, including hierarchy information (i.e. which member is the 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). The 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.
LevelNumber: The member level number.

MDX Query Tables

MDX query tables execute an MDX query and extract 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.
The Value column contains the cell value.

Each row contains a combination of dimension members and the cell value.

To avoid name conflicts, you can use the full member name to instruct Essbase to return all the ancestors of each member. Add DIMENSION PROPERTIES [ANCESTOR_NAMES] to the MDX query for each dimension. Incorta will automatically detect the dimension properties and use full member names to identify members instead of simple names. The following is an example query with dimension properties.

SELECT
{[Product].levels(0).members} DIMENSION PROPERTIES [ANCESTOR_NAMES] ON COLUMNS,
{[Year].levels(0).members} DIMENSION PROPERTIES [ANCESTOR_NAMES] ON ROWS
FROM [Sample].[Basic]

Steps to connect Oracle Essbase and Incorta

To connect Oracle Essbase and Incorta, here are the high level steps, tools, and procedures:

Create an external data source
Create a schema with the Schema Wizard
or, Create a schema with the Schema Designer
Load the schema
Explore the schema

Create an external data source

Here are the steps to create a external data source with the Oracle Essbase connector:

Sign in to the Incorta Direct Data Platform.
In the Navigation bar, select Data.
In the Action bar, select + New → Add Data Source.
In the Choose a Data Source dialog, in Application, select Oracle Essbase.
In the New Data Source dialog, specify the applicable connector properties.
To test, select Test Connection.
Select Ok to save your changes.

Oracle Essbase connector properties

Here are the properties for the Oracle Essbase connector:

Property	Control	Description
Data Source Name	text box	Enter the name of the data source
Server	text box	Enter the name of the Essbase server. The format is: `<IP ADDRESS>:<PORT>`
Username	text box	Enter the database username
Password	text box	Enter the database password
App	text box	Enter the name of the Essbase application.
Database (Cube)	text box	Enter the name of the Essbase database associated with the application.

Create a schema with the Schema Wizard

Here are the steps to create an Oracle Essbase schema with the Schema Wizard:

Sign in to the Incorta Direct Data Platform.
In the Navigation bar, select Schema.
In the Action bar, select + New → Schema Wizard.
In (1) Choose a Source, specify the following:
- For Enter a name, enter the schema name.
- For Select a Datasource, select the Oracle Essbase external data source.
- Optionally create a description.
In the Schema Wizard footer, select Next.
In (2) Manage Tables, in the Data Panel, first select the name of the Data Source, and then check the Select All checkbox.
In the Schema Wizard footer, select Next.
In (3) Finalize, in the Schema Wizard footer, select Create Schema.

Create a schema with the Schema Designer

Here are the steps to create an Oracle Essbase schema using the Schema Designer:

Sign in to the Incorta Direct Data Platform.
In the Navigation bar, select Schema.
In the Action bar, select + New → Create Schema.
In Name, specify the schema name, and select Save.
In Start adding tables to your schema, select Essbase.
In the Data Source dialog, specify the Oracle Essbase table data source properties.
Select Add.
In the Table Editor, in the Table Summary section, enter the table name.
To save your changes, select Done in the Action bar.

Oracle Essbase table data source properties

For a schema table in Incorta, you can define the following Oracle Essbase specific data source properties as follows:

Property	Control	Description
Type	drop down list	Default is Essbase
Data Source	drop down list	Select the Oracle Essbase external data source
Type	drop down list	Select the Essbase table type. The options are: Dimension MDX Query
Dimension Name	text box	Select Dimension as Type to configure this property. Enter the name of the Essbase dimension.
Query	text box	Select >MDX Query as Type to configure this property. Enter the MDX query to retrieve data from Oracle Essbase.
Discovery Query (Optional)	text box	Select MDX Query as Type to configure this property. Optionally enter an MDX query with a structure similar to the original query, but more restricted in the data it returns to allow faster discovery.
Callback	toggle	Enable this option to call back on the source data set
Callback URL	text box	This property appears when the Callback toggle is enabled. Specify the URL.

View the schema diagram with the Schema Diagram Viewer

Here are the steps to view the schema diagram using the Schema Diagram Viewer:

Sign in to the Incorta Direct Data Platform.
In the Navigation bar, select Schema.
In the list of schemas, select the Oracle Essbase schema.
In the Schema Designer, in the Action bar, select Diagram.

Load the schema

Here are the steps to perform a Full Load of the Oracle Essbase schema using the Schema Designer:

Sign in to the Incorta Direct Data Platform.
In the Navigation bar, select Schema.
In the list of schemas, select the Oracle Essbase schema.
In the Schema Designer, in the Action bar, select Load → Load Now → Full.
To review the load status, in Last Load Status, select the date.

Explore the schema

With the full load of the Oracle Essbase schema complete, you can use the Analyzer to explore the schema, create your first insight, and save the insight to a new dashboard.

To open the Analyzer from the schema, follow these steps: