Automate Metadata load into ESSBASE cubes using Maxl scripts

Posted on Leave a commentPosted in Informative

 

What is MAXL:

MaxL is the multi-dimensional database definition language (DDL) for Essbase Server. Using MaxL, you can easily automate administrative and query operations on Essbase Server.

A MaxL script contains a login statement and a sequence of MaxL statements, each terminated by a semicolon. Using of MaxL Script Editor to execute a MaxL script, the login statement is optional. Most MaxL statements begin with a verb and consist of grammatical sequences of keywords and variables. MaxL Script Editor color-codes the elements of the MaxL syntax and provides an auto-complete feature that helps to build statements.

Sample MAXL:

maxl

Using MAXL Shell:

We can pass MaxL statements to Essbase Server using the MaxL Shell. The MaxL Shell command-line interface is installed with Administration Server in:EASPATH\server\bin\essmsh.exe (EASPATH/server/bin/essmsh on UNIX)

Where EASPATH is the directory to which Administration Services is installed.

Why MAXL:

After building essbase analytic model and importing the data, the next big thing is to keep essbase data refreshed with each coming day’s new data. And it might be necessary to add new dimension member to the outline sometimes (e.g. we might have to add today’s date to the date dimension before loading today’s sales data).

MAXL is such a script language that can use to automate essbase in daily maintenance job by using some batch script to do it automatically, instead of use the admin console every time to add new member or execute calculation on the database.

 

We can write MaxL scripts which are easy to customize and re-use. A MaxL script contains a login and a sequence of MaxL statements, each terminated by a semicolon.

MaxL statements begin with a verb, and consist of grammatical sequences of keywords and variables. A single MaxL statement looks similar to an English sentence; for example,

Create application Newsamp as Sample;

Maxl DDL statements:

2

Verbs in MAXL ddl are:

3

Automate essbase using these maxl statements:

The following is an example to import data using maxl script

________________________________________________________________________________

/*maxl example*/

Spool on to c:\output.txt;

Login admin password on localhost;

Import database ‘Basic’.’Sample’ dimensions from data_file ‘C:\essbase\LoadFile.Product.csv’ using local rules_file ‘C:\essbase\product.rul’ on error append to ‘C:\essbase\dataload.err’;

Logout;

Spool off;

Exit;

________________________________________________________________________________

In the above example spool on is used to Send output of MaxL statements to a file called output.txt, located in the pre-existing directory specified by a relative path.

And also we can direct errors to one file and output to another by placing the following lines in the script:

Spool stdout on to ‘output.txt’;

Spool stderr on to ‘error.txt’;

LOGIN is used to enter into the essbase without clicking on EAS.

Import data from text or spreadsheet data files, with or without a rules file.

LOGOUT s used to exit from essbase

The following is an example of a MaxL script, sent to Essbase via the MaxL Command Shell. This script creates a user, creates a filter, and then assigns the filter to the user. Note that all MaxL scripts must begin with a login to the Essbase system, which must be running.

________________________________________________________________________________

/* login admin identified by systempasswd on Esshost;

Create user Fiona identified by sunflower;

Create filter Sample.Basic.Diet read on ’@idesc(Diet)’;

Grant filter Sample.Basic.Diet to Fiona;

Logout;

Exit;  */

________________________________________________________________________________

Note: save the maxl file with a .msh extension

.Bat file to run maxl script automatically

________________________________________________________________________________

Essmshfilename with .msh extension (and) file path where the file exists

________________________________________________________________________________

AUTOMATING DRM EXPORTS AND LOADING INTO ESSBASE:

We can Automate DRM Exports using Batch Client

Following is an example for Batch Client Export for Market

maxl

If we run this VBScript Using Batch Client we can get Market.csv Export. We can import this file Directly into EAS using Maxl

5

 

This is a maxl script for Automate Market Dimension into EAS.

We can combine these two using a .bat file and run both at a time.

For Example

3

In the above examble first line is for DRM batch Client output and If Drm Export exists only then it calls Maxl Script If not exists it Writes file does not exist into out.txt log file.

 

 

Srujana Reddy

Oracle DRM Consultant

DataSprouts Technologies Pvt Ltd.