Essbase data load and the rules files

We can load data in an Essbase database/cube in several ways using the Rules files.

• 1         MAXL script to load data.
• 2        ESSCMD script to load data.
• 3        EAS to load data.

In the examples that I have provided below to load data using Rules Files, some of the names assumed are as follows:

• Application Name: nirmal
• Database name: khoonks
• Username and password: admin and password
• Name of source file: sample_load.txt
• Rules file name: LdData

Loading data using MAXL script

We can run the MAXL script either in interactive mode or File Input mode. 

In this example, we will use the File Input mode in regards to both MAXL and ESSCMD scripts where we create a (MAXL or ESSCMD) file and run it each time we want to load data. 

These can be scheduled as well which makes it easier in the real-life environment.

In order to create a new MAXL file –

• Open a blank text editor, such as Notepad or TextPad or whatever you are used to.
• Make sure that the file has the following content (at least for this example).
• Save it as Load_sample.MXL or Load_Sample.msh.

##### Content of the file for reference #####

set LOGFILE=myscript$\\logs\\Sample_load.log;

set DATAFOLDER=datafolder$\\data_folder;

set ERRORFOLDER=myscripts$\\error_folder;

spool on to "\\$COMPUTERNAME\\$LOGFILE";

/* Login onto the Server  */

login admin password on $COMPUTERNAME;

import database "nirmal"."khoonks" data from local text data_file "\\\\$COMPUTERNAME\\$DATAFOLDER\\sample_load.txt" using server rules_file 'LdData' On Error write to "\\\\$COMPUTERNAME\\$ERRORFOLDER\\sample_load_error.err";

logout;

spool off;

exit;

Basically, what the script does is – Login to Essbase and load data from file “sample_load.txt” using the rules file “LdData” to the database “khoonks” which is in application “nirmal.” Moreover, there are other things such as creating log files and error files, etc.

Now, everytime we run the Load_Sample.msh or mxl file, we load the data in just one click.

In order to run the Load_Sample.msh/mxl file, right-click the file name -> Send To -> ESSMSH -> Click.

Loading data using ESSCMD script.

We can create an ESSCMD file and name it Load_Sample.txt and run it by right-clicking and Send To ESSCMD. It is the same way as we did the MAXL file, except for the file extension and content inside the file. 

We can also open a COMMAND PROMPT and type in –

ESSCMD E:\myscriptFolder\Load_Sample.txt

In case we are running MAXL script from the command prompt, we can type in 

ESSMSH E:\myscriptFolder\Load_Sample.msh

The content of the ESSCMD file can be as follows:

:Create an output log for the Esscmd

OUTPUT 1 "\\Servername\myscripts$\logs\sample_load.txt";

:Login into the Essbase Server

LOGIN "EssbaseServerName" "admin" "password" "nirmal" "khoonks";

:Loads data to khoonks database

IMPORT 3 "\\Servername\myscripts$\datafilefolder\sample_load.txt.txt" 4 "Y" 2 "LdData" "N" "\\Servername\myscripts$\errorfolder\load_error.err";

:Logs out of application

LOGOUT;

:Ends log recording

Output 3;

:Exits Esscmd

EXIT;

The admin console (EAS) to load data.

This is the graphical way of loading data in an Essbase database.

On the EAS enterprise view, expand the branch so that you can see the database where you are loading data.

Right-click the database -> Load Data

The Data Load screen pops up.

Click “Find Data File” to choose the source data file. Browse through either the “Essbase Server” or “File System” to find the data file. Click OK.

Click “Find Rules File” to choose the Rules file that you would like to use to load data. Click OK after you find the Rules file.

Now if you look at the screenshot below, there are other things that you can check, for example, “Abort on Error,” “Error File” etc. Check them as per your requirement.

Click OK.