In this blog, I would be talking about data export from an
Essbase application using the DATAEXPORT command. The data would be exported to
a flat file as well as a relational system. So let us deep dive into it.
This is a snippet of data that I am interested in exporting
out an Essbase application. I have done the export using a report script.
The first thing to remember about DATAEXPORT is that it is a
calculation script command. This means that I can use it within a FIX statement
to do, what is called as a targeted export. For example, suppose that I would
be doing some changes to level0 data for a particular version and entity. I can
choose these in a fix and export the data before I do any updates in my
application. Targeted data export can also be done using a report script but
you have to remember that report script will be slightly slower than DATAEXPORT
since the formatting part is skipped.
The next snapshot shows me creating a calculation script for
running a data export of descendants of Q1(Jan, Feb, Mar) and Account level-0
members for a specific entity, scenario, version and year.
The export will be a file that is
comma-delimited and named “EssDemo_Q1_Export.txt” WITH Missing values
represented using #Mi.
On running the data export, the file is saved to the Essbase
app folder as shown in the below snapshot.
Contents
of the file is as shown in the next snapshot. Although the data is exported
out, it is not in a very clean columnar format as such.
To
export the data in a bit more cleaner format, I will use the SET
DATAEXPORTOPTIONS command and specify to export the data in column format as
shown in the below snapshot.
When
I save and ran the above script, the file did not change!!!Output did not come
in columnar format. This is because there is a flag called as
“DATAEXPORTOVERWRITEFILE” which tells Essbase calculation engine if it should
overwrite a file or not that already exists. By default, this flag is OFF. I
set it to ON as shown in the next snapshot.
On
running the calculation script, the cleaner output in column format is as shown
in the below snapshot.
Now, let us try to export the data to a relational schema
from Essbase.
The first thing that you need to do is create a
DSN for the RDBMS that you want to hit on the Essbase server. This is shown in
the below couple of snapshots. 
The
next thing that I do is create a table in the columnar export format that was
present in the file as shown in the below snapshot.
I next updated the calculation script to export to a DSN as
shown in the below snapshot to the database table that I created above.
After
running the export, the data is updated in the table as shown in the below
snapshot.
ReplyDeletehow can we format members using this script?
Account|FY15|Jan|secnario|Final|Europe|Income Statement|3680.50
If you want a timestamp to this record, please use a simple batch or shell script...
Deletebatch use for /f command
shell script make use of awk or sed...
Pushing timestamp in relational system is challenging...doing it in flat files is relatively simple.
Is this achieved?
ReplyDeleteI am looking same requirement, can any one advise how to overwrite the data to Relational table if we ran the Calc Script multiple times?
It does a bulk export. If you are using Oracle, you need to export to a staging table and then update the target table using the MERGE statement.
ReplyDelete