Oracle Essbase Integration Adapter

The Oracle Essbase Integration Adapter integrates with Essbase by exporting the data and meta-data from Essbase and loading it through a SQL Server database into a SQL Server Analysis Services (SSAS) database. After a data-export the resulting OLAP Cube can be used standalone without a live connection to Essbase.

Schematic overview

A schematic overview of the involved process is:

The Outline of a given Essbase database is put on the CXO Application Server (in case of Load-balancing the one where the Agent Service runs). The Outline can also be directly obtained from the Essbase server via a MaxL command;
Data extracts (column mode or non-column mode) are also put in a folder on the CXO Server;
VIa the CXO Source System Manager, a data-extraction can be triggered;
This trigger is being picked up by the Agent. The Agent reads and interprets the content of the outline and data-files and stores them in a SQL Server database;
The final results are stored (processed) into a SQL Server Analysis Services (SSAS) database.

Overview of the Data-extraction flow

The following schema shows more details of the flow (the blue arrows in above schema):

The ETL process starts with reading the outline file (.otl, which has a proprietary format) and transforming it into a generic format (.xml). This .xml file is an intermediate result which is stored in the temp-folder of the Windows user running the Agent Service;
Then this .xml file is read and interpreted and the result is stored in the tables xmlimport_dimension and xmlimport_member in the Staging database (usually named like cxo_essbasestaging_...);
These SQL Tables, together with the table dimension_mapping (which contains the information about how to map the Essbase dimensions to the CXO dimensions), are used to prepare and post-process the final dimension tables (with naming convention cxo_dim_...) in the Fact-database (usually named like cxo_fact_...);
The ETL process can be configured such that it only collects and processes the dimension information ('meta-data only'). Then this is the end-point;
Otherwise, as a next step the Data extracts will be parsed one by one;
As soon as 500,000 records are read, the data is stored in an intermediate table named dataimport_rawdata;
These raw data are then transformed to make it suitable for use in the OLAP cube and then stored in the table cxo_fact in the Fact-database;
Once all records are read (which can easily take > 10 iterations) then as a final step the OLAP database is being processed. This last step is optional. Sometimes - e.g., for troubleshooting - it's better to manually process the OLAP database using the SQL Server Management Studio;
Now the data is available to the end-users of the CXO Dashboard.

User Guide

To start an extraction, first open the CXO Source System Manager:

Then logon to the system:

You will get an overview of the Source Systems:

Each tab (pmtfds_qa in the above example - see green square) represents a CXO Source System. A Source System can be either an Integration Adapter or a direct connection to an existing OLAP database (like Essbase). In this case we will work with an Essbase Integration Adapter. The Staging-database and Fact-database used are indicated by the red square. The SSAS cube that will be populated with the extraction results is indicated with the blue square.

The Integration Adapter itself will popup if you press the button indicated with the arrow:

All Values in this table can be edited by clicking on the cell and pressing F2. Then press Enter and click Save to confirm.

Alias Table: enter here the Alias Table of the Outline that you want to use for the Descriptions in the CXO reports;
Essbase Server / Application / Database / Account / Password: the Essbase credentials. Also needed if extraction of meta-data and data is done through flat files;
ESSMH Location: File-location (path + .exe) where the MaxL tool essmsh.exe can be found on the CXO-application server;
Incremental: to be explained. Leave empty for now;
Location Extract: ';' separated references (paths + files) to the Essbase Extracts on the CXO application server (non-column based if Outline and Data come from the same Essbase database as indicated under 'Essbase Database', column based if data come from a BSO-cube);
Location Outline: path + .otl file where the Outline is stored on the CXO application server. If you leave this field empty then the outline will be retrieved from the Essbase Server;
Meta Data Only: 0 means do a full extract of data and meta-data. 1 means: only parse the outline and refresh the CXO dimensions. The latter is actually only meaningful for testing if the system is working properly;
Process Cube: 1 means: after the Extraction Process process the Microsoft SQL Server Analysis Services database ('the Cube'). Switching this to 0 could be convenient if you want to do the Cube-processing with the SQL Server Management Studio. This gives a bit more information about this process (e.g., the total number of records stored in the Cube).

Press the button Start Synchronization after having verified that all settings are OK. The footer of the dialog shows the progress of the extraction process. The process is handled by the CXO Agent Service. Hence, you can close the dialogs without interrupting the process. Note that the extraction process is finished only after the Check-symbol is shown (pointed by the green arrow).

Further, note that you should use the Close button (or the 'x') to exit this dialog. The Cancel button means 'Stop the extraction process'.

Dimension Mapping

The first time you must set the mapping of the Essbase dimensions to the CXO dimensions. This can be done by pressing the button Dimension Mapping:

The left column shows the available CXO dimensions:

Dimensions A01 ... A10 are hierarchical dimensions that can be used to store various financial or non-financial Essbase dimensions.
Dimension ACC should be used to store the Essbase Account dimension.
CAT should be used for Category or Scenario (Actual, Budget, Forecast, ...)
Dimension ENT is reserved for Entity
FLO is usually used for Balance movements, but can be used for any Essbase hierarchical dimension (similar to A01 ... A10)
Dimension ICP is used for the Intercompany transactions. In the example above it is not used, in spite of the fact that there is an Intercompany dimension in the underlying Essbase outline.
MEA can be used in case there is an Essbase dimension that is reserved for Measures like Amount or Count. It is often unused in this mapping. The MEA dimension in CXO can then be utilized to to store Cube-calculations.
PER should be used for the Essbase Period dimension.
RAT and VER are sometimes used to store Exchange Rates (like Actual rate, Budget rate, ...) and (Planning) Version. They can be used for any Essbase flat dimension (pick-list).
VAL is for Currencies
VIW is reserved for the View dimension. Often, the VIew dimension only contains one 'hard' (Input) member like Periodic. The other members are cube-calculations.
Dimension YER is for (Fiscal or Calendar) year.

There are a few dimensions that should always be mapped: ACC, CAT, ENT, PER, YER, VIW. The other ones are optional. If you do not use a CXO dimension, please put a Default value (usually None) in the 3rd column.

You can establish a mapping by clicking in the Essbase Dimension column and select an available Essbase Dimension.

Filtering dimension members

It is possible to filter certain members to prevent a data-overload. This is of course only meaningful for flat (non-attribute) dimensions like Years, Scenario, Currency, etc.

The filtering can be added or edited by changing the content of the table dataimport_filters:

In this example we only extract Actual numbers for 2018 and 2019 and only for the USD_ACT currency rate. Dimensions that are not listed in this table are not filtered.

Embedded Cube Calculations

After each full data refresh, in the database named cxo_essbasestaging_... (see screenshot of the Source System Manager: in the example the database is called cxo_essbasestaging_pmtfds) a table named ToBeStoredCalculations is being updated. If the outline that is being used in the extraction has changed since the last extraction - so if dimension hierarchies are changed or if members are added/deleted - then this table might contain new data. In that case, after the extraction is finished the following manual actions should be taken:

Start the SQL Server Management Studio and connect to the Database Engine containing the cxo_essbasestaging_... database;
Right-click on that database and select New Query:
In the query panel, enter:

Then press F5
In the result-set click on the column named StoredCalculation. The column will be selected. Then right-click and select Copy:

If this table is empty, no further action is required.
In the SQL Server Management Studio, now connect to Analysis Services:
Select the instance where the SSAS database exist:
Expand the database belonging to the data-source (see screenshot Source System Manager; there it is named pmtfds, in the below example we use hcefds) until the CXO cube, right-click that cube and choose Script Cube as / ALTER To / New Query Editor Window:
In the Query Panel you will see an XML like structure. Collapse the nodes Annotations, Dimensions and MeaureGroups (press the -):
Scroll down until:
Delete all CREATE MEMBER statements between the comment lines and put a few empty lines instead:
Paste the content of the Clipboard (^V) in the open space. See step 4. Ignore any spaces added or deleted by the system.
Then press F5. After a few seconds you will see in the result-panel:

Now the cube contains the latest version of the embedded cube-calculations

Troubleshooting

The extraction process does not start, even after waiting several minutes:

Please check if the Agent service is running (via the Configurator). If not: start it:

If it is already running then Restart the service.
The extraction process fails:

Usually, the message is very general. For more details, please open the Configurator, choose Logs at the left side and choose Agent in the header. Then select a log-file (usually Logging.log, but if the error occurred before today then select the one with the proper date).

The red message on the bottom of the document is the most recent error message:

In this example it is obvious that a typo has been made in the filename of one of the extracts.

If the message is less obvious either report this specific error or send the entire log-file to support@cxosoftware.com (if you press the folder icon in the above screen you will be navigated to the file-location of the logs)..
My numbers are incorrect and do not match the numbers in Essbase itself or certain dimension members are missing: There can be multiple reasons for that:
1. In case the extraction process uses a .otl file placed on the CXO Application Server, please make sure that the application contained in that file is exactly the same as the Live Essbase application; If you have to use an updated Outline, please follow the steps outlined in section Embedded Cube Calculations
2. Next to a couple of standard SQL Server Stored Procedures (which are equal for each and every implementation), there are also a number of Stored Procedures that are customer / implementation specific.
  
  These Stored Procedures are - among other things - responsible for:
  1. Translating the Essbase calculated members into MDX understood by the SSAS cube (see schema's above). There are general guidelines for this but the implementation is highly specific;
  2. Translating Essbase calculated members (especially those that part of a consolidation (sub-)tree) into ordinary fact-records;
  3. Avoiding (too many) shared members by creating MDX formulas that refer the shared members to their original definition. These MDX formulas can subsequently be embedded in the SSAS cube
    
    There is a chance that one of these custom stored procedures is not working properly.
    Contact the CXO Support person to find out whether this is the case.