Installation
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
InstallationThe 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
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:
$ARBORPATH pointing to the root directory of the Hyperion Essbase server installation.
C:\Hyperion\Essbase, then
%ARBORPATH% = C:\Hyperion\Essbase. In addition, %ARBORPATH%\Bin should be included in your path variable.
Before you install the Essbase.pm extension to Perl, ensure that:
$ARBORPATH pointing to the root directory the Hyperion Essbase installation.
home/hyperion/essbase, then
$ARBORPATH = /home/hyperion/essbase. In addition, $ARBORPATH/bin should be included in your path variable.
| 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.
essext.tar, and untar it as follows:tar -xvf essext.tar.
Functionsconnect (user, password, host);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);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}.
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.
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] } }
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]
disconnect();Terminates an Essbase session and destroys the session object.
Returns: Completion status.
Examples
# 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();
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;
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;
}