Inserting TIMESTAMP while exporting data from Essbase to relational table during DATAEXPORT

In this blog, I would be talking about inserting a timestamp entry to each record that is exported out of Essbase to the relational table while running the DATAEXPORT command. So, here are some assumptions. I assume that the target table is on Oracle RDBMS. Also, that you are familiar with some database administration skills.

 
So, first thing I do is run an export which is as shown in the below script. The output will be to a file. It will fetch data for Quarter 1 (Jan, Feb, Mar) and BegBalance. 

BegBalance is a placeholder member that we will be using for storing the timestamp information. This will be a NULL value for data in the Essbase application.

Sample output is as shown in the below file.

Observe that BegBalance comes as the sixth column in the data output and is represented as #Mi although it is mentioned after descendants of Q1. This is because the descendants are processed top down as they appear in the outline. I fix this by moving the BegBalance to be the last entry in the Period dimension as shown in the below snapshot.   

The output of the script after updating the outline is as shown in the below snapshot. 

Observe that the value of BegBalance comes last now when I run the output.

I now create a table in the format of the above export to push data from Essbase to the relational table. This is shown in the next snapshot. Observe that BegBalance has a datatype of TIMESTAMP tagged to it.

 The contents of the table after running the script which pushes data from Essbase to relational table in Oracle is as shown in the next snapshot. 

Observe that the last column for BegBalance is populated with NULL values. We are now close to what we want to achieve.

I now define a trigger in the Oracle database to update the value of BegBalance column for ESSDEMO_TARGET table before INSERT as shown in the next couple of snapshots.  

A trigger is procedural code that is automatically executed in response to certain events on a table or view in the database.

Now, let us insert a dummy record into the table to see if the trigger works or not. The query that I will fire is as shown in the below snapshot.

The output of the table after executing a SELECT query is as shown below. Observe the BEGABALANCE column has the value of the current timestamp added to it for the last record. 

 Now, on running a data export from Essbase to relational table, observe that the BegBalance is populated with timestamp of the data load run. The trigger holds and its acting as a SCD now. 

Running the calculation script once again will populate the data once again but with a new current timestamp. Check the timestamp of the last three records. It is different from the first three records. 

Assuming you run a data load every day, you can use the BegBalance column to identify which is the latest data pushed from Essbase to the relational table.