Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
This chapter describes MaxL DDL, the data definition language for Analytic Services. MaxL is a flexible way to automate Analytic Services administration and maintenance tasks. MaxL DDL improves upon ESSCMD in that it is a multi-dimensional access language for Analytic Services. Using MaxL, you execute Analytic Services operations using a language-like interface, rather than a series of commands with complicated arguments.
MaxL scripts can be developed flexibly to accommodate many uses, and the language is easy to learn. MaxL includes a Perl module that enables you to embed its statements in Perl programs.
This chapter contains the following sections:
For comprehensive coverage of MaxL syntax, see the MaxL DDL section of the Technical Reference.
The MaxL data definition language is an interface for making administrative requests to Analytic Services using statements.
Using MaxL, you can easily automate administrative operations on Analytic Services databases. You can write MaxL scripts with variables to make them customizable and re-usable.
In order for Analytic Services to receive and parse MaxL statements, you must "pass" them to the Analytic Server using either the MaxL Shell (essmsh
), Administration Services, or a customized Perl program that uses the MaxL Perl Module.
Use this section to learn more about the language:
All MaxL DDL scripts and interactive sessions consist of a login and a sequence of statements. Each statement is terminated by a semicolon.
Statements consist of grammatical sequences of keywords and variables. A statement looks similar to an English sentence; for example,
create or replace user <user-name> identified by <password>;
MaxL statements always begin with a verb, such as create or alter. Verbs indicate what type of operation you want to perform. After the verb, you specify an object. Objects, such as database or user, indicate the Analytic Services elements you are working with. After the object specification, the rest of the statement is for giving more details about the action you wish to perform. This is done using a grammatically correct sequence of statement-parts, or tokens.
To get an overall picture of the grammar requirements and the verb-object structure of MaxL statements, see the MaxL DDL section of the Technical Reference.
The MaxL parser recognizes and requires an ordered presentation of tokens, and these are the components of statements. A token is a space-delimited sequence of valid characters that is recognized by MaxL as a single readable unit. Tokens can be any of the following:
Keywords are the reserved words that make up the MaxL vocabulary. These include verbs, objects, and other words needed to construct statements. Keywords in MaxL are independent of your data: conversely, all other MaxL tokens (names, for example) must be defined by you.
The MaxL parser expects to see MaxL keywords and other tokens in their correct grammatical order, as diagrammed in the MaxL DDL section of the Technical Reference.
In the following sample syntax diagram from the Technical Reference, only the lower-cased words in the diagram represent keywords. The other elements are placeholders for names or values that you provide.
Figure 234: Example of MaxL Syntax Diagram: Alter Filter
Note: Keywords are not case-sensitive; the use of lower case for keywords is a documentation convention. For more information about how to interpret the diagrams, see "How to Read MaxL Railroad Diagrams" in the online Technical Reference.
Names in MaxL are used to uniquely identify databases and database objects, such as users, applications, or filters.
Unless you enclose a MaxL name within single quotation marks, a MaxL name is a string that must begin with an alphabetic character or the underscore. Names that are not enclosed in quotation marks may contain only alphabetic characters, numbers, and the underscore.
When enclosed in single quotation marks, a name may contain white space and any of the following special characters:
. , ; : % $ " ' * + - = < > [ ] {} ( ) ? ! / \ | ~ ` # & @ ^
Note: Any name that is also a MaxL keyword must be enclosed in single quotation marks. For a list of keywords, see the "Reserved Words List in the MaxL DDL" section of the Technical Reference.
The following application names do not require single quotation marks:
The following application names do require single quotation marks:
Orange
County
(contains a space)22Orange
(begins with a number)variable
(is a MaxL keyword)
Some Analytic Services objects have single names, and some require compound names known as doubles and triples, which express the nesting of namespaces. The three possible ways to name Analytic Services objects are with a singleton name, a double, or a triple.
A singleton name is a name that can be meaningful in a system-wide context: the object to which it refers may be global to Analytic Services, or, for whatever reason, it needs no application or database context to be specified. For example, an application has a singleton name because it does not need to be considered in the context of another application or a database.
A double is two names connected by a period, and a triple is three names connected by two periods. Doubles and triples show the inherited namespace of the named entity. For example, a database is usually identified using two names. The first name identifies the application in which the database resides. The second name is the database's own name. Therefore, a database name could look like this:
Sample.Basic
Database objects, such as filters, are usually identified using triple names: the first two names identify the application and database, and the third name is the object's own name. Therefore, a filter name could look like this:
sample.basic.filter3
.
The following table shows what type of name is required for the most common objects, and provides an example of the name used in a statement.
Strings are used in MaxL statements to represent the text of comments, member expressions, calculation scripts, and file references. Strings can begin with any valid character. As with names, strings containing whitespace or special characters must be enclosed in single quotation marks.
See Rules for Names for a list of valid special characters.
The following table shows examples of statement elements that are strings:
You use numbers in MaxL statements to change certain database settings in Analytic Services. For example, you can change cache and buffer sizes, or set system-wide intervals such as the number of days elapsing before users are required to change their passwords. To change numeric settings, you can use positive integers, positive real numbers, and zero. Decimals and scientific notation are permitted.
For size settings, units must follow the number. Spaces in between numbers and units are optional. Units are case-insensitive, and may include the following: B/b (bytes), KB/kb (kilobytes), MB/mb (megabytes), GB/gb (gigabytes), and TB/tb (terabytes). If no units are given, bytes are assumed.
1000 b
5.5GB
645e-2 mb
145 KB
2,000e-3TB
This section helps you review what you have learned about statements by illustrating some MaxL statements and their components, in template form. In the diagrams, lower-cased words are keywords, and words in upper-case are intended to be replaced with the appropriate values, as shown in the example following each illustration.
Figure 235: MaxL statement to change data file cache size
alter database Sample.Basic set data_file_cache_size 32768KB;
Figure 236: MaxL statement to grant application permissions to a user
grant designer on application Sample to Fiona;
Figure 237: MaxL statement to create a stored calculation
create calculation sample.basic.varcalc '"Variance"=@VAR(Actual, Budget); "Variance %"=@VARPER(Actual, Budget);' ;
This section shows you how to get started using the most of the features of the MaxL Shell. For more complete information and examples, see the MaxL DDL > MaxL Shell section of the Technical Reference. This section contains the following topics:
This section does not discuss the Administration Services MaxL Script Editor. If you are using MaxL Script Editor, you can skip the rest of this chapter and refer instead to the Essbase Administration Services Online Help.
The MaxL Shell can be invoked to take input in any of the following ways:
The MaxL Shell also accepts any number of command-line arguments at invocation time. These can be used with positional parameters to represent any name, or a password.
This topic contains the following sections:
To enter MaxL statements interactively at the command line, simply invoke the shell at your operating-system prompt. Text you type is indicated by bold text.
essmsh Analytic Services MaxL Shell Release 7.0.0 (c) Copyright 2000-2003 Hyperion Solutions Corporation. All rights reserved.
To enter MaxL statements interactively after logging in at invocation time, use the -l flag. For example,
essmsh -l Admin password Analytic Services MaxL Shell Release 7.0.0 (c) Copyright 2000-2003 Hyperion Solutions Corporation. All rights reserved. 49 - User logged in: [Admin].
To enter MaxL statements interactively and also supply command-line arguments to represent variables you will use in your interactive session, use the -a flag. For example,
essmsh -a Admin password Sample Basic Analytic Services MaxL Shell Version 7.0.0 (c) Copyright 2000-2003 Hyperion Solutions Corporation. All rights reserved. login $1 $2; 49 - User logged in: [admin]. alter database $3.$4 enable aggregate_missing; 72 - Database altered: ['sample'.'basic'].
In the above example, $1, $2, $3, and $4 are positional parameter variables: they represent whatever arguments were entered after essmsh at invocation time, in the order they were entered.
To invoke the MaxL Shell to take input from a MaxL script file, type essmsh followed by the name of a MaxL script in the current directory, or, the full path and file name of a MaxL script in another directory.
If you provide only a file name, the MaxL Shell assumes that the file is in the current directory (the directory the operating-system command prompt was in when essmsh was invoked). In the following invocation example, the file maxlscript.msh
must be in C:\ .
C:\>
essmsh maxlscript.msh
If the MaxL script is not in the current directory, provide a path to the MaxL script. You can use absolute paths or relative paths.
$
essmsh ../hyperion/essbase/test.msh
Note: MaxL scripts are not required to have any particular file extension, or any file extension at all. This document uses.msh
.
In UNIX shells, you should place single quotation marks around the path to avoid file-reading errors.
In a Windows command prompt, if the path to the script contains a space, you may have to use double quotation marks around the entire path and file name to avoid file-reading errors.
To invoke the MaxL Shell to take input from the standard output of another program or process, use the -i flag. For example,
program.sh | essmsh -i
The shell script program.sh
may generate MaxL statements as output. The shell script's output is piped to essmsh
-i
, which uses that output as its input. This allows for efficient co-execution of scripts.
The following Windows batch script generates a login statement and a MaxL display statement as its output. The -i flag enables that output to be used by essmsh
, the MaxL Shell, as input.
echo login admin password on localhost; display privilege user;|essmsh -i
User Admin is logged in, all user privileges are displayed, and the MaxL session is terminated.
The following portion of a shell script ensures that there are no applications on the system, by testing whether the display application statement returns zero applications.
if [ $(echo "display application;" | essmsh -l admin password -i 2>&1 | awk '/Records returned/ {print $7}' ) != "[0]." ] then print "This test requires that there be no applications." print "Quitting." exit 2 fi
Here is how the above example works:
echo
command.awk
for pattern-matching with the MaxL status message you would get if you entered display application on an empty system: Records
returned:
[0]
.Awk
matches the string 'Records
returned:
'
, and then it checks to see if that is equal to '[0].'
$7
(a variable representing the fifth token awk
finds in the status string) is equal to '[0].'
, then there are no applications on the system; otherwise, $7
would equal '[1].'
or whatever number of applications exist on the system.
For more information and examples on invocation options, see the MaxL DDL > MaxL Shell section of the Technical Reference. Invocation information is also contained in the essmsh "man page." To view the man page, enter essmsh
-h
|
more
at the operating-system command prompt.
The MaxL language interpreter requires a connection to an Analytic Services session before it can begin parsing MaxL statements. Use the MaxL Shell to establish the connection to Analytic Services.
To log in to Analytic Services after the command shell has been started, use the shell's login grammar. Text you type is indicated by bold text.
essmsh Analytic Services MaxL Shell Release 7.0.0 (c) Copyright 2000-2003 Hyperion Solutions Corporation. All rights reserved. MAXL>login Fiona identified by sunflower on hostname;
If a host name is not specified, localhost is assumed.
To log in when you invoke the shell, use the -l option. To log in to a server besides
localhost
at invocation time, use the -s option. To set a message level, use -m. For example,
essmsh -l fiona sunflower -s myHost -m error
Note: You can log out and change users without quitting the shell.
For more information about MaxL Shell invocation options, see the MaxL DDL > MaxL Shell section of the Technical Reference.
The MaxL Shell includes command-line argument processing, environment variable processing, nesting of MaxL scripts, and shell escapes. These features offer the flexibility needed to create a highly automated Analytic Services production environment.
For complete information on the syntax and usage of all MaxL Shell features, see the MaxL DDL > MaxL Shell section of the Technical Reference.
As a database administrator, you may wish to save your separate automated tasks in several MaxL scripts, rather than executing many operations from a single script. Putting the pieces together is a simple task if you know how to reference one MaxL script from another.
To reference or include other MaxL scripts within the current MaxL session, use the following MaxL Shell syntax:
msh <scriptfile>;
You can create a log file of all or part of a MaxL session and its associated messages by spooling output to a file.
To record a MaxL session, complete the following tasks:
login fiona sunflower;
MaxL statements and their output are logged to the output file when you issue the spool command, either in an interactive session or in a script. However, MaxL Shell commands and output are logged only if you spool during an interactive session. The MaxL Shell commands and output are ignored in log files created from script sessions. Additionally, output from any operating-system commands you may have included is ignored in the log files of both interactive and script sessions.
You can issue operating-system commands directly from a MaxL session. The operating-system output becomes part of the MaxL Shell output. When the operating system finishes executing commands, it returns control to essmsh
.
To escape to the operating system from within a MaxL session, use shell. For example, try running the UNIX date command from a MaxL script.
shell date;
To escape to ESSCMD from within a MaxL session:
shell esscmd '../scripts/test.scr';
In the MaxL Shell, you can use variables as placeholders for any data that is subject to change or that you refer to often; for example, the name of a computer, user names, or passwords. You can use variables in MaxL scripts and during interactive sessions. Using variables in MaxL scripts eliminates the need to create customized scripts for each user, database, or host. Variables can be environment variables (for example, $ARBORPATH
, which references the Analytic Services installation directory), positional parameters (for example, $1, $2, and so on), and locally defined shell variables. A variable always begins with a $ (dollar sign) when you reference it.
For more information about using variables in the MaxL Shell, see the MaxL DDL > MaxL Shell section of the Technical Reference.
You can log out of a MaxL session, or log in as another user, without quitting the shell. You should include a logout statement at the end of MaxL scripts. It is not necessary to exit at the end of MaxL script files, or after a session using stream-oriented input from another program's output.
To log out without exiting the MaxL Shell, enter
logout;
To exit from the MaxL Shell after using interactive mode, enter
exit;
With the aid of the MaxL Perl Module (Essbase.pm), you can embed the MaxL language within Perl programs, offering more programmatic control than is available in the shell.
Essbase.pm, located in the Perlmod
directory, enables beginning or advanced Perl programmers to wrap MaxL statements in Perl scripts. In this way, database administration with MaxL becomes as efficient and flexible as your Perl programs are.
Using Perl with MaxL enables you to take advantage of these and other programmatic features while you administer Analytic Services databases:
The Perl Module (Essbase.pm
), contains methods that enable you to pass MaxL statements by means of Perl. These methods are:
To make the Perl methods available to Analytic Services, you must include a reference to Essbase.pm
in your Perl program. Place the following line at the top of each Perl script:
use Essbase;
Perl is not difficult to learn, especially if you have prior knowledge of UNIX shells or other programming languages. To download Perl and learn more about Perl, visit the Comprehensive Perl Archive Network Web site at http://www.cpan.org/.
For information and examples about installing and using the MaxL Perl Module, see the MaxL DDL section of the Technical Reference, and also the README file located in the PERLMOD directory of your Analytic Services installation.
![]() |