Essbase Perl Extension

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.

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

Installation Functions Examples

Installation

The Essbase Perl extension is available for the following platforms:

Windows NT 4.0, Windows 2000, Windows 95, Windows 98
Solaris 2.6 and Solaris 7
AIX 4.3.3
HP-UX 11.0

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 6.1 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. 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 the Essbase Perl extension from the Hyperion Essbase 6.1 CD-ROM.
Follow the instructions in README, included in the Essbase Perl extension download.

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 6.1 OLAP Server is installed. Your system should have an environment variable $ARBORPATH pointing to the root directory the Hyperion 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`
`libmaxl.so` (`libmaxl.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`
`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, download the Essbase Perl extension, essext.tar, and untar it as follows:
tar -xvf essext.tar.
Follow the instructions in README, included in the Essbase Perl extension download.

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_col();

Returns columns by number from a MaxL output table.

Arguments: Column number. If the number is out of bounds an error message is returnd on the message stack.

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

The output of a query results in a table of information. This method returns a column of that table identified by the column number. Column numbers vary from 0 to Essbase{NUM_OF_FIELDS} -1.

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 Essbase 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;
}
 
#
# returns a col by a name
#
sub col_by_title
{
    my $dbh = shift;
    my $colname = shift;
    *mycol = undef;

    for($i = 0; $i < $dbh->{NUM_OF_FIELDS}; $i++)
    {
        ($name, $col) = $dbh->fetch_col($i);
        if($name eq $colname)
        {
            print "Found ", $name, "!!!\n";
            *mycol = $col;
            last;
        }
    }

    return @mycol;
}