Automating Databases Processes in Oracle Essbase

Automating Databases Processes in Oracle Essbase - ESSCMD and MaxLUp to this point in my blog, we have done almost everything in the various user interfaces. We modeled the database in the Essbase Studio console. We used the Administration Services console to view and create load rules, to create a drill- through report, and to create member formulas. We even generated a report (via MDX) using Smart View.

From a practical deployment perspective, however, most databases processes are automated. After you create the initial database using Essbase Studio (or custom load rules in the Data Prep Editor), you create automation scripts to use these objects in a larger batch process. This blog's article focuses on the command-line capabilities inherent in Essbase.


Table of contents[Show]


The two command-line languages available are ESSCMD and MaxL. ESSCMD is a supported and actively used scripting language for Essbase automations. However, the majority of recent development efforts from both a user and an Oracle product direction perspective - use MaxL. This section provides an overview of using ESSCMD and MaxL. For a complete listing of all MaxL and ESSCMD capabilities, see the Oracle Essbase Technical Reference.

 

Using ESSCMD

ESSCMD is a legacy scripting language that lets you create, use, and modify database objects. It was the first command-line language provided for Essbase. ESSCMD is primarily numeric-based in its command syntax. For example, to execute a load rule using ESSCMD, the syntax is as follows:

BUILDDIM 2 "GenRef" 2 "GenRef" 4 "GenRefError";

Decoding the syntax, this command does the following:

  • BUILDDIM executes a dimension build rule.
  • 2 "GenRef" leverages a server-based load rule named GenRef.
  • 2 "GenRef" leverages a server-based data file named GenRef.
  • 4 tells Essbase that the source is a text file.
  • "GenRefError" writes any errors out to an error file named GenRefError.

 

using MaxL

MaxL is the newer of the two command-line languages for Essbase. It provides commands for building, calculating, and managing Essbase processes. Unlike ESSCMD, MaxL's command structure is closer to common language. In many respects, it is similar to the Oracle SQL*Plus command-line language.

To perform the preceding data load example using MaxL, the command is as follows:

import database sample.basic dimensions from server text data_file 'GenRef.txt'

using server rules_file 'GenRef.rul' on error append to 'GenRefError.txt';

As you can see, MaxL syntax is easier to interpret than the ESSCMD commands. The following are some key syntactical requirements of MaxL:

  • MaxL statements end in a semicolon (;).
  • Paths with spaces and special characters require single quotes (').
  • To insert comments into a script, open the comment block with /* and close it with */. Anything within these markers is ignored when the script is executed.

With MaxL, you can automate other tasks beyond building a database, such as the following:

  • Logging out users and disabling connections during batch processes (if desired)
  • Exporting data and objects for backup
  • Encrypting user names and passwords
  • Creating calculation and report scripts
  • Setting database caches

In short, the MaxL command-line language provides a full range of capabilities for managing all database processes.

Automating Essbase Databases - ESSCMD and MaxL

A Sample MaxL Script

You can automate all the processes modeled in this article in a single MaxL script, as presented in this section. This script assumes that the application name is Sample, the database name is Basic, and the calculation script created earlier is named BudInc. It also assumes the load rules generated by the Essbase Studio process are as follows:

  • ACCOUN builds the Account dimension.
  • MARKET builds the Market dimension.
  • PRODUC builds the Product dimension.
  • SCENAR builds the Scenario dimension.
  • YEAR builds the Year dimension.
  • BASIC loads numeric data into the model.

The following script contains commented sections that explain the purpose of each command or block of commands.

/* Create a log file of the process and log into the Essbase sever.*/ spool on to 'c:/MaxL_Logs/output.txt'; login 'admin' 'password';
/* Build the Essbase database using the rules created by Essbase Studio*/
import database sample.basic dimensions
connect as 'admin' identified by 'password' using server rules_file
'ACCOUN',
connect as 'admin' identified by 'password' using server rules_file
'MARKET',
connect as 'admin' identified by 'password' using server rules_file
'PRODUC',
connect as 'admin' identified by 'password' using server rules_file
'SCENAR',
connect as 'admin' identified by 'password' using server rules_file
'YEAR'
on error append to 'C:/MaxL_Error/dimbuild.txt';
/* Load data into the Essbase database using the rule created by
Essbase Studio*/
import database sample.basic data
connect as 'admin' identified by 'password' using server rules_file
'BASIC'
on error append to 'C:/MaxL_Error/data.txt';
/* Execute the BudInc calculation script */
Execute calculation sample.basic.budinc;
/* Execute the Top report script*/
export database sample.basic using server report_file 'top.rep' to
data_file
'c:/MaxL_Reps/top.txt';
/* Close log file and exit*/
spool off;
exit;


FIGURE 1. An Essbase MaxL Shell window

 

Executing a MaxL Script

MaxL is a command-line shell similar to a standard Windows command line. The Essbase MaxL Shell (ESSMSH), shown in Figure 1, inherently knows how to execute the MaxL commands.

Once you have created a script file, you execute the script in a batch process by invoking the MaxL shell and passing the name of the script object. This can be done, for example, with a Windows BAT file. For instance, if you named the previous script sample.mxl, the batch file to execute this script might contain a single command:

ESSMSH sample.mxl

In this case, the batch file starts ESSMSH and passes the sample.mxl file to the shell. The MaxL shell reads the commands in the file and executes them as specified.

Note also that parameters can be passed to the script, so you could pass the user name and password when calling the script. In addition, you can encrypt the script, so that the password is not visible. Furthermore, you can incorporate MaxL commands into the Perl language for greater integration into other business processes.

 

Creating a Script in the MaxL Script editor

This script creates an application and database named test, and then shuts down the running application.

Follow these steps to create a script using the Administration Services console: In the Administration Services console, select File | Editors | MaxL Script Editor.

  1. In the MaxL Script Editor window, type the following script:
create application 'test';

create database 'test'.'test';

alter system unload application 'test';
  1. From the menu bar, select MDX | Execute.

 

 

After you have the script created, you can save this as a text file and use a batch file to execute the script (as shown in previous section). While you could have created the script in any text editor, the MaxL editor provides valuable syntax­checking capabilities, as well as an autocomplete option.

 

Conclusion

There is not a single method or philosophy for designing and building an Essbase database. After so many years of doing something, you often take for granted how difficult or easy it is to do. A number of years ago, I was working with a friend on an Essbase design project. He remarked on how easy it is to build an Essbase database. From his perspective, it is no more difficult than counting to three. While I never thought building an Essbase database was difficult, I never really would have thought it as simple as 1-2-3. Rather, like most things in the world, it is somewhere in between. The steps outlined in our blogs provide a method for building an Essbase database. For sake of simplicity, the article did not include ETL tools such as Oracle Data Integrator or Informatica. You could just as easily build a database leveraging adapters in numerous ETL tools. The specific methodology you use to build an Essbase database will vary depending on the specifics of your data source(s), your analytical needs, and your comfort level with the product.

 

Вас заинтересует / Intresting for you:

Oracle Essbase Care and Mainte...
Oracle Essbase Care and Mainte... 613 views Franklin Wed, 22 Aug 2018, 05:48:19
Oracle Engineered Systems for ...
Oracle Engineered Systems for ... 445 views Александров Попков Fri, 15 Jun 2018, 14:05:15
Oracle OLAP and Essbase archit...
Oracle OLAP and Essbase archit... 2110 views Андрей Волков Thu, 19 Sep 2019, 14:35:37
OLAP concepts, history and imp...
OLAP concepts, history and imp... 1122 views Akmaral Wed, 03 Jan 2018, 06:21:53

Comments on Automating Databases Processes in Oracle Essbase

Be the first to comment
Please login to comment