MaxL Perl Module

The MaxL Perl Module, Essbase.pm, provides access to Hyperion Essbase multi-dimensional databases from Perl programs through MaxL, the multi-dimensional access language for Essbase. Communication from Perl to MaxL to Essbase provides the system-administrative functionality of MaxL with the rich programmatic control of Perl.

This section contains the following topics:

Installation Help
Functions
Examples

To get Perl and learn about it, go to the Comprehensive Perl Archive Network.

Installation Help

The MaxL Perl Module is available for all supported Essbase platforms.

Windows Prerequisites

We recommend that you download the Perl source from www.cpan.org and build it yourself. You may use ActivePerl, available from http://www.activestate.com

Before you install the Essbase.pm extension to Perl, ensure that:

You have Perl 5.005 (or higher) installed on your system.
You have Microsoft Visual C++ version 5 or 6 installed on your system. If you are using ActivePerl, you should use VC++ version 6.
The Hyperion Essbase Release 6.5 OLAP Server is either installed locally, or you have at least the Hyperion Essbase Runtime Client installed and your system's environment is set up to access a remote Essbase server.

Note: MaxL Perl Module Release 6.5 can only be used with Release 6.5 OLAP Server.
Your system should have an environment variable $ARBORPATH pointing to the root directory of the Hyperion Essbase server installation.
For example, if Essbase is installed on C:\Hyperion\Essbase, then %ARBORPATH% = C:\Hyperion\Essbase. In addition, %ARBORPATH%\Bin should be included in your path variable.

Windows Instructions

Install Essbase OLAP Server Release 6.5 from the Essbase CD-ROM or download site. The MaxL Perl Module files are included as part of that installation, and a Perlmod directory will be created under %ARBORPATH%.
Follow the instructions in README, included in the Perlmod directory.

UNIX Prerequisites

Before you install the Essbase.pm extension to Perl, ensure that:

You have Perl 5.005 (or higher) installed on your system.
You have a C compiler installed on your system. For example,
- on Solaris - Sun's Open Workshop 4.2
- on AIX - xlc version 3.6.6
The Hyperion Essbase Release 6.5 OLAP Server is installed. Your system should have an environment variable $ARBORPATH pointing to the root directory the Essbase installation.
For example, if Essbase is installed on home/hyperion/essbase, then $ARBORPATH = /home/hyperion/essbase. In addition, $ARBORPATH/bin should be included in your path variable.

The following MaxL and Essbase files exist in the appropriate directories.
If Essbase is installed correctly, this is already the case.

File name	Directory
`essmsh`	`$ARBORPATH/bin`
`maxl65.so` (`maxl65.a` on AIX)	`$ARBORPATH/bin`
`maxl.h`	`$ARBORPATH/api/include`
`maxldefs.h`	`$ARBORPATH/api/include`
`essapi.h`	`$ARBORPATH/api/include`
`essxlat.h`	`$ARBORPATH/api/include`
`esstypes.h`	`$ARBORPATH/api/include`
`esstsa.h`	`$ARBORPATH/api/include`
`libessutl.so`	`$ARBORPATH/bin`
`libessshr.so`	`$ARBORPATH/bin`
`libessotl.so`	`$ARBORPATH/bin`
`libesssd.so`	`$ARBORPATH/bin`
`libglobalc.so`	`$ARBORPATH/bin`

NOTE: You do not have to install the API to use MaxL. The necessary api/include and api/lib directories are created to contain the MaxL libraries and header files.

UNIX Instructions

If you have met the above prerequisites, change to the MaxL Perl Module directory, which is perlmod in the essbase directory.
Follow the instructions in README, included in the perlmod directory.

connect (user, password, host);

user- Required. The Essbase user name.
password- Required. A valid password for user.
host- Optional. The computer name hosting the Essbase instance.

Usage

my $dbh = Essbase->connect("user","password", "host");

Establishes a connection to Essbase using $dbh, the database handle in "my" namespace. Returns: A session object (for example, $dbh).

do (statement);

statement- Required. A MaxL statement to be passed to the Essbase server.

Usage

$dbh->do("display user");

Where display user is a valid MaxL statement.

Returns (and sets Essbase{STATUS} to):

$MAXL_STATUS {NOERR} if execution was successful. There are likely to be informational and feedback massages on the message stack, which may be obtained with pop_msg().

$MAXL_STATUS {ERROR} if there was a user error. Error numbers, levels, and texts may be obtained with the pop_msg method. Note: There are likely to be informational messages on the message stack even if execution was successful. These also may be obtained using pop_msg.

pop_msg();

Navigates through MaxL status messages one at a time.

Arguments: none.

Returns: a list of the form (<message_number>, <message_level>, <message_text>)

Each invocation of the "do" method results in a stack of status messages. This stack is unwound by repeatedly calling pop_msg until it returns nothing. It is acceptable for a Perl program to ignore the message stack, or to unwind it only partially. The next call to "do" will clear left-over messages.

There will probably be a number of messages on the stack even after a successful execution. In most cases, a Perl program will only need to know if the execution of the last "do" was successful, which is indicated by the return value from "do".

When the message stack is empty, the return list elements are undfined and Essbase{STATUS} is set to $MAXL_STATUS{END_OF_DATA}.

Usage example

fetch_desc();

Returns a reference to list of column names in a MaxL output table.

A column is defined as { name, { val[0], val[1], ... , val[NUM_OF_ROWS-1] } }

Usage example

fetch_row();

Returns a reference to a row of query results in a MaxL output table, as a list.
Essbase->{STATUS} is set to one of the following:

$MAXL_STATUS{NOERR} on success.
$MAXL_STATUS{END_OF_DATA} if there were no rows to fetch.
$MAXL_STATUS{ERROR} if a user error has occured.

A row of record is defined as { val[0], val[1], ... , val[NUM_OF_FIELDS-1] } }
Row numbers are counted cardinally from 0:
[0, 1, 2, ... , NUM_OF_ROWS - 1]

Usage example

disconnect();

Terminates an Essbase session and destroys the session object.
Returns: Completion status.

Examples

Createuser.pl

The following is the simplest example of a Perl script using Essbase.pm. The script establishes a connection to the Essbase server, creates a user, and disconnects. Words that are passed to the MaxL parser or the essmsh shell interpreter are shown in bold color.

# Use the Essbase.pm module. This statement is required to use Essbase within a Perl script.
use Essbase;

# Create a handle to the Essbase server by connecting as admin, mypassword to the local machine.
my $dbh = Essbase->connect("admin", "mypassword", "localhost");

# Print a message about the success or failure of connect.
print defined $dbh ? "Essbase database handle defined\n" : "ERROR: Essbase database handle NOT defined\n";

# Use the do Perl function to pass the MaxL create user statement (enclosed in quotation marks) to the Essbase server.
$dbh->do("create user Essbase identified by mypassword");

# Disconnect from the Essbase server.
$dbh->disconnect();

Createusers.pl

The following Perl script tests whether Perl is able to use the MaxL Perl extension. If Essbase.pm is loaded, the program establishes a connection to Essbase, creates three users with different passwords, and disconnects.

######################### print on failure.

BEGIN { $| = 1; }
END {print "ERROR: System NOT Loaded\n" unless $loaded;}
use Essbase;
$loaded = 1;

######################### 

sub create_user
{

    my $dbh = Essbase->connect("admin", "pass1", "localhost");

    print  defined $dbh ? "Essbase database handle defined\n" : "ERROR: Essbase database handle NOT defined\n";

# Create array of users    
    @user = (
	"Fred",
	"George",
	"Mary",
     );

# Create array of passwords
    @password = (
	"password1",
	"password2",
	"password3",
    );

    $i = 0;

    while ($i le 2) {

    $username = $user[$i];
    $newpassword = $password[$i];
    $j = $i + 1;

    print $dbh->do("create user $username identified by $newpassword") == 0 ? "user$j created\n" : "ERROR: user user$j NOT created\n";

    $i = $i + 1;

    }

    print $dbh->disconnect() == 0 ? "Essbase database handle released\n" : "ERROR: Essbase database handle NOT released\n";
}


# 
# create user test
#
&create_user;

Maketable.pl

The following subroutines from a Perl script return a message list that resulted from executing a MaxL statement, and build a table from a result set.

use Essbase;
 
#
# Returns a message list that resulted from executing
# a MaxL statement.
#        
sub msgs
{
    my $dbh = shift(@_);
    my $msglist;

    # dump all messages one thread at a time
    while (1)
    {
        my ($msgno, $level, $msg);

        ($msgno, $level, $msg) = $dbh->pop_msg();
        # gets us out of the loop if a $msg comes back as undef
        last if ! $msg;
        $msgstr = sprintf " %-8d", $msgno;
        $msglist .= "$msgstr - $msg\n";
    }

    return $msglist;
}
 
#
# Returns a result set in the form of a table.
#
sub tab
{
    my $dbh = shift;
    my ($colnum, $rec, $name, $tab, $line);

    # build an output table

    # setup the header
    $name = $dbh->fetch_desc();
    for ($col = 0; $col < $dbh->{NUM_OF_FIELDS}; $col++)
    {
        $str = sprintf " %-14.14s", $name->[$col];
        $tab .=  $str;
        $line .= "+--------------";
    }

    $tab .= "\n$line\n";

    # now populate the table with data
    $rec = $dbh->fetch_row();
    while(defined($rec))
    {
        for ($col = 0; $col < $dbh->{NUM_OF_FIELDS}; $col++)
        {
            $str = sprintf " %-14.14s", $rec->[$col];
            $tab .= $str;
        }
        $tab .= "\n";
        $rec = $dbh->fetch_row();
    }
    $tab .= "\n";

    return $tab;
}