Export Essbase Substitution variables to SQL

When I started working on Hyperion, most my projects were relatively small compared to the projects I have been doing the past 3 years. Now projects are over a year long with 2, 3, 4 phases and most of the times include Essbase, Planning, DRM, ODI to name a few of those tools. The bigger the project, the more complex the requirements around automations are, and one thing that I always find myself needing is the ability to leverage the values of Essbase substitution variables in SQL.

SQL Essbase load rules can of course leverage Essbase substitution variables but sometimes  you still need those subvars anyway. One example would be loading data in Planning using the outline load utility. Let’s say the value for the Scenario is part of the POV column of the data to be imported and the Scenario value is defined through an Essbase variable.

For those reasons, I had to find a way to get those Essbase subvars into SQL. I’m sure it’s been done already but when i first started looking at this a couple years ago, I couldn’t find much on this.

Before you start, you will need ODI and know how to import data from a delimited file into a SQL Table.

EXPORT ESSBASE SUBSTITUTION VARIABLES

First things first, we need to export the subvars. To do that, as there is no export command, the best option I could find was the below maxl script:

set column_width 250;
spool on to ‘/Path/ESSBASE_VARIABLES_EXPORT.txt’;
display variable all;

The third statement will display all existing Essbase Substitution variables in 4 columns.
The second statement defines the location of the file where the output of the MaxL session will be written into.
The first statement defines the width in characters of each column in the export . This can be increased or lowered. I set it pretty high just in case some of the subvars or values of these subvars end up being really long to prevent the export from being incomplete. Each of the 4 columns (Application, Database, Variable, Value) will have a width of 250 characters in this case.

Once you run that maxl script, open the generated ESSBASE_VARIABLES_EXPORT.txt. If you used the same width setting, you will need to scroll right to start seeing the subvar names and values. The file with also contain some rows that can be ignored like:

MAXL> display variable all;
OK/INFO – 1241044 – Records returned: [2].
MAXL> MAXL> spool off;

CREATE A SQL TABLE TO STORE THESE SUBVARS

Now that we have our variables exported.

Create a SQL table named ESSBASE_VARIABLES with the following columns and settings to replicate the view from EAS:

• “APPLICATION” (VARCHAR2 250 CHAR)
• “DATABASE” (VARCHAR2 250 CHAR)
• “VARIABLE” (VARCHAR2 250 CHAR)
• “VALUE” (VARCHAR2 250 CHAR)

The number of characters for each will vary if you use a different column_width setting in your maxl script.

IMPORT SUBVARS INTO SQL

With ODI, you will need to:

1) Create a new Datastore for ESSBASE_VARIABLES_EXPORT.txt. When setting it up, make sure to:

• set the file format to Fixed
• set the Heading (Number of Lines) to 4 (this will exlude any lines within ESSBASE_VARIABLES_EXPORT that do not hold actual variables information)
• Set the columns appropriately based on the column_width settings used

2) Create a new Datastore for the SQL table created ESSBASE_VARIABLES.

3) Create a new ODI interface ESB_VARIABLES_TO_TABLE and define:

• the ESSBASE_VARIABLES_EXPORT.txt datastore as the source
• the ESSBASE_VARIABLES table datastore as the target
• map each columns appropriately
• in the Flow tab:
  • pick the IKM SQL Incremental Update IKM on the Target Area
  • set INSERT option to True
  • set COMMIT option to True
  • set FLOW_CONTROL to False
  • set DELETE_ALL to True

Run the interface and check the ESSBASE_VARIABLES table. Make sure rows from the txt file have been inserted.

If it worked, the last step consists of cleaning up that table, and all that is needed is to create a SQL View:

CREATE OR REPLACE VIEW “XXXXX”.”ESSBASE_VARIABLES_V” (“APPLICATION”, “DATABASE”, “VARIABLE”, “VALUE”) AS
SELECT
TRIM(APPLICATION) APPLICATION,
TRIM(DATABASE) DATABASE,
TRIM(VARIABLE) VARIABLE,
TRIM(VALUE) VALUE
FROM ESSBASE_VARIABLES
WHERE VARIABLE IS NOT NULL AND VALUE IS NOT NULL;

What can you do now?

• compile all the above steps in one easy, simple ODI package
• reference essbase subvars and its values in SQL