This topic covers many details involved in implementing a simple Essbase API application, including a number of hints and tips that might not be apparent to a new API user.
This tutorial refers to sample programs that actually work. The sample programs are delivered along with the API documentation. To find the sample programs look in /Essbase/Docs/Api/Samples. The C code programs are in /Samples/Cexecs. The Visual Basic programs are in /Samples/VBexecs. Both the compilable source and the compiled executables are included.
For a quick look at the samples click one of the links below.
Essbase API functions are prefixed with "Ess" for the C API or "Esb" for the Visual Basic API. This discussion uses the function prefic "Esx" when discussing the operation of an API function that is available in both languages. For example, EsxLogin() refers to either EssLogin(), EsbLogin(), or both. Similarly, the prefix "ESX" indicates "ESS" or "ESB" as a prefix for a data type or constant, for example, ESX_NULL.
Design Environment Setup Issues
Before you can build Hyperion Essbase API programs you must set a few configuration options in your design environment. This discussion focuses on Microsoft Visual C++ version 6 and Visual Basic version 6. The configuration settings within a specific development environment are set in different ways, but here are a couple of hints to assist in building an API program:
#pragma pack(push,localid,1) #include <essapi.h> #pragma pack(pop,localid)
Basic Requirements
All API application programs are required to perform specific basic operations that are common to all API programs. This section describes in detail the process of writing the shell of an application, and is aimed at programmers who are new to the Hyperion Essbase API.
The Nested Program Model
When programming using the API, your code should adopt the nested programming model. In the nested programming model the code has calls to an initial function and a corresponding final function. The calls are arranged as a sandwich, with the code to perform some action in between as the filling. Consider the following example:
begin action 1 begin action 2 begin action 3 perform action 3 end action 3 begin action 4 perform action 4 end action 4 end action 2 end action 1
The implication of this arrangement is that you should ensure that you end every action or operation that you begin. Here is a more concrete example that uses real API actions:
Initialize the API Login to a server Connect to a database Open a database outline Browse the outline Close the outline Open a report Modify & save the report Close the report Disconnect from a database Logout from the server Terminate the API
The example above illustrates the basic structure of any code that accesses the Hyperion Essbase API.
Using Function Return Codes
One of the first things you need to know as an API developer is how to handle the status codes returned by API functions. In general, a zero return code indicates successful completion and a non-zero return code indicates an error. In the latter case, the program should abort the operation in progress and return to the default state, only calling those API functions that are needed to clean up. It is essential that every time a program makes a call to the API that it checks the return code and handles it properly.
The API provides a type declaration for status return codes (ESS_STS_T) and a constant declaration (ESS_STS_NOERR). The constant declaration can be used to test the status return codes from API functions in an implementation-independent way.
/* C Example of checking return value from an API function */
ESS_STS_T sts;
if ((sts = EssSomeFunction (.....)) == ESS_STS_NOERR)
{
do something else;
}
else
{
process error;
}
' VB Example of checking return value from an API function */
Dim sts as ESB_STS_T
if ((sts = EsbSomeFunction (.....)) == ESB_STS_NOERR)
do something else
else
process error
endif
The nested programming model is good for releasing resources if a Hyperion Essbase function fails and returns an error return value. Consider the following example:
allocate resource 1 begin action 1 allocate resource 2 begin action 2 action 2 end action 2 free resource 2 end action 1 free resource 1
Calling API Functions
Each API function has the prefix Ess (for C) or Esb (for Visual Basic) followed by a verb-object naming convention, for example, EssGetDatabaseInfo(). Some functions that relate to a specific area of the product have an additional prefix to indicate that relationship. For example, all the Outline API functions have EssOtl or EsbOtl prefixes, for example, EssOtlOpenOutline() and EsbOtlGetNextSibling().
All API functions take a series of arguments. The arguments are different for every function. But the arguments follow a logical sequence. The first argument to most functions is typically a handle, either an instance handle, a context handle, an outline handle, or a member handle. The term "handle" refers to an identifier that is used by the API to keep track of different objects in the system (just like a file handle). Different handles are returned by certain functions. Handles should then be stored in your program and passed to other API functions when required.
Handles are different in C and Visual Basic. For more information on the different types of API handles and their uses, refer to Using the C Main API and Using the Visual Basic Main API.
If there are any arguments to be passed in to a function, they typically come next in the sequence. Finally, if the function returns any values, the variables to store those returned values are passed in at the end of the argument list.
In the following examples, the first argument is a context handle (hCtx). The next two arguments (the application and database names, Sample and Basic), are passed in and the argument to be returned (the database information structure, ESX_DBINFO_T) is passed in at the end:
/* C Example of passing arguments to an API function */
ESS_STS_T sts;
ESS_HCTX_T hCtx;
ESS_PDBINFO_T pDbInfo;
sts = EssGetDatabaseInfo (hCtx, "Sample", "Basic", &pDbInfo);
if (sts == ESS_STS_NOERR)
{
do something;
}
' VB Example of passing arguments to an API function
Dim sts as ESB_STS_T
Dim hCtx as ESB_HCTX_T
Dim DbInfo as ESB_DBINFO_T
sts = EsbGetDatabaseInfo (hCtx, "Sample", "Basic", DbInfo)
if (sts = ESB_STS_NOERR)
do something
endif
Note that in the C example, the returned argument (pDbInfo) is passed to the function as a double indirection (a pointer to a pointer) by passing the address of a declared structure pointer variable (using the & operator). This variable is then assigned the address of a database information structure that is allocated internally by the API function.
In the Visual Basic example, the caller first allocates the structure (DbInfo) and passes it to the API function (implicitly by reference).
Initializing the API
All application programs must initialize the API (with EsxInit()) before using any other Hyperion Essbase functions. The program should perform the initialization only once, preferably during the program's startup sequence.
/* C Example of initializing the Essbase API */ ESS_STS_T sts; ESS_INIT_T InitStruct; ESS_HINST_T hInst; /* first clear the init structure (use API defaults) */ memset (&InitStruct, 0, sizeof (ESS_INIT_T)); sts = EssInit (&InitStruct, &hInst); ' VB Example of initializing the Essbase API Dim sts as ESB_STS_T Dim InitStruct as ESB_INIT_T Dim hInst as ESB_HINST_T sts = EsbInit (InitStruct, hInst)
The API default settings are appropriate for most application programs. If you need to change the settings, refer to the EssInit() and/or EsbInit() for more information on setting the individual fields of the API initialization structure (ESS_INIT_T and ESB_INIT_T) in your program.
The instance handle (hInst) that is returned from EsxInit() should be saved within your program for subsequent API calls. This instance handle uniquely identifies your program and its associated resources to the API.
Would you like to see an actual Sample C API Program?
Would you like to see an actual Sample Visual Basic API Program?
Logging On to a Server
After the API is initialized, a program must log in to a Hyperion Essbase server in order to perform any Essbase-related actions on that server. Generally, a login only needs to be performed when a specific action is requested by the user (typically a database connect operation). Note that a login to a server does not necessarily imply a connection to a specific Essbase application or database on that server; some administration operations do not require a connection to a particular database, and some do not even require connection to a server.
A login can be performed using EsxLogin(). For Microsoft Windows only, an encapsulated login dialog function, EsxAutoLogin(), is available. The dialog box displayed by this function is the same as the one used by the Hyperion Essbase Application Manager and Spreadsheet Add-in interfaces. Optionally, the user can use the dialog box to select an application and a database to connect to (see Connecting to a Database, below). The user can also perform other operations, such as changing a password.
/* C Example of a login using the EssLogin function */ ESS_STS_T sts; ESS_HINST_T hInst; ESS_SVRNAME_T Server = "Larch"; ESS_USERNAME_T Username = "Joe User"; ESS_PASSWORD_T Password = "secret"; ESS_ACCESS_T Access; ESS_HCTX_T hCtx = ESS_INVALID_HCTX; sts = EssLogin (hInst, Server, Username, Password, &Access, &hCtx); ' VB Example of a login using the EsbLogin function Dim sts as ESB_STS_T Dim hInst as ESB_HINST_T Dim Server as ESB_SVRNAME_T Dim Username as ESB_USERNAME_T Dim Password as ESB_PASSWORD_T Dim Access as ESB_ACCESS_T Dim hCtx as ESB_HCTX_T Server = "Larch" Username = "Joe User" Password = "secret" hCtx = ESB_INVALID_HCTX sts = EsbLogin (hInst, Server, Username, Password, Access, hCtx)
The following is a similar example of logging in, this time using EsxAutoLogin(). When using this function, the user supplies all the relevant information (server name, user name, password, application, and database names) by entering the information into the appropriate fields of the dialog box:
/* C Example of a login using the EssAutoLogin function */ ESS_STS_T sts; ESS_HINST_T hInst; ESS_ACCESS_T Access; ESS_HCTX_T hCtx = ESS_INVALID_HCTX; sts = EssAutoLogin (hInst, ESS_NULL, ESS_NULL, ESS_NULL, ESS_NULL, ESS_NULL, AUTO_DEFAULT, &Access, &hCtx); ' VB Example of a login using the EsbAutoLogin function Dim sts as ESB_STS_T Dim hInst as ESB_HINST_T Dim Access as ESB_ACCESS_T Dim hCtx as ESB_HCTX_T hCtx = ESB_INVALID_HCTX sts = EsbAutoLogin (hInst, ESB_NULL, ESB_NULL, ESB_NULL, ESB_NULL, ESB_NULL, ESB_AUTO_DEFAULT, Access, hCtx)
See EssLogin(), EsbLogin(), EssAutoLogin(), and EsbAutoLogin().
Note that, if string variables, instead of ESX_NULL, are passed to the function as the user-entered parameters, on return from the function those variables contain the values entered into the login dialog box by the user.
Your program should normally login once (at the start of a user session). However, if tying up unused server ports is a big issue, consider logging in at the start of each operation, and logging out at the end of each operation (see Logging Out). Note, however, that this process can slow down user response time significantly.
When using either EsxLogin() or EsxAutoLogin(), the returned login context handle (hCtx) should be saved within your program for subsequent API calls. The login context handle uniquely identifies that particular login to the API.
Using Local Context HandlesIf you are performing API administrative operations (such as file operations) on the client machine, you can use a dummy login context handle to represent a local login to the API. The dummy handle can be used like a server context handle, except that most server-specific and database-specific operations cannot be performed. Use EsxCreateLocalContext() to create a local context handle. Consider the following example:
/* C Example of creating a local context handle */ ESS_STS_T sts; ESS_HINST_T hInst; ESS_HCTX_T hLocalCtx = ESS_INVALID_HCTX; sts = EssCreateLocalContext (hInst, ESS_NULL, ESS_NULL, &hLocalCtx); ' VB Example of creating a local context handle Dim sts as ESB_STS_T Dim hInst as ESB_HINST_T Dim hLocalCtx as ESB_HCTX_T hLocalCtx = ESB_INVALID_HCTX sts = EsbCreateLocalContext (hInst, ESB_NULL, ESB_NULL, hLocalCtx)
Connecting to a Database
Many Hyperion Essbase API functions (such as server administration, security, and outline maintenance) can be performed after the program has logged in. However, many database-related functions (for example, reporting or performing calculations) require that the program connect to a specific application and database. Use EsxSetActive() to identify a specific Hyperion Essbase database. Logging in with EsxAutoLogin() also allows the identification of a specific database.
Note that the user must have sufficient privileges to access the database. A list of all applications and databases to which a particular user has access is returned by EsxLogin(), and can be obtained using EsxListDatabases().
If you connect to a database that is not running, Hyperion Essbase automatically starts the database. It is not necessary to disconnect from a database. However, using the same login context handle to connect to another database will disconnect you from the original database. If you really need to be connected to two or more databases at once, your program needs to login multiple times (and manage each context handle independently).
/* C Example of connecting to a database */ ESS_STS_T sts; ESS_HCTX_T hCtx; ESS_APPNAME_T AppName = "Sample"; ESS_DBNAME_T DbName = "Basic"; ESS_ACCESS_T Access; sts = EssSetActive (hCtx, AppName, DbName, &Access); ' VB Example of connecting to a database Dim sts as ESB_STS_T Dim hCtx as ESB_HCTX_T Dim AppName as ESB_APPNAME_T Dim DbName as ESB_DBNAME_T Dim Access as ESB_ACCESS_T AppName = "Sample" DbName = "Basic" sts = EsbSetActive (hCtx, AppName, DbName, Access)
The user's access level to the selected database is returned by EssSetActive (and by EsxAutoLogin()). This access level can be checked by using the security constant definitions that allow the application program to alter user options, by graying out menus, and so on.
Logging Out
After the user completes one or more database operations and finishes with Hyperion Essbase, your program should log out from the server. Logging out can be done either as a result of an explicit user request or automatically (for example, after a specific sequence of actions is complete). All active connections should also be logged out before the program terminates and exits.
It is not always necessary for the program to log out after each data access operation. Whether to log out (and so release Hyperion Essbase server ports) or remain logged in (giving faster response to successive user requests) is a design judgment call.
/* C Example of logging a user out */ ESS_STS_T sts; ESS_HCTX_T hCtx; sts = EssLogout (hCtx); hCtx = ESS_INVALID_HCTX; ' VB Example of logging a user out Dim sts as ESB_STS_T Dim hCtx as ESB_HCTX_T sts = EsbLogout (hCtx) hCtx = ESB_INVALID_HCTX
After logging out, do not use that same context handle. That will probably crash your program.
If you want to dispose of a local context handle, use EsxDeleteLocalContext():
/* C Example of deleting a local context handle */ ESS_STS_T sts; ESS_HCTX_T hLocalCtx; sts = EssDeleteLocalContext (&hLocalCtx); ' VB Example of deleting a local context handle Dim sts as ESB_STS_T Dim hLocalCtx as ESB_HCTX_T sts = EsbDeleteLocalContext (hLocalCtx)
Terminating the API
At the very end of its execution, your program should terminate the Essbase API by calling EsxTerm(), to ensure the proper release of all API resources. This function also logs out all active server connections (if they are not already explicitly logged out by your program).
/* C Example of terminating the API */ ESS_STS_T sts; ESS_HINST_T hInst; sts = EssTerm (hInst); hInst = ESS_INVALID_HINST; ' VB Example of terminating the API Dim sts as ESB_STS_T Dim hInst as ESB_HCTX_T sts = EsbTerm (hInst) hInst = ESB_INVALID_HINST
After terminating the API, do not attempt to make any more calls to API functions. If you make more calls your program will probably crash.
Assembling a Program
So far in this discussion we have addressed those aspects of the API that are common to all programs. We have not addressed the operations that the program will be designed to accomplish. All programs require that you understand the nested programming model, pass arguments to and from the API functions in a consistent way, interpret the function's return codes, initialize the API, log in to a server, connect to a database, log out, and terminate. Now we need to address the real point of the program; the program needs to perform an operation of some kind.
This discussion covers the main functional groups of the C Main API. Some sections have references to the sample programs, but the sample programs do not include all areas of the API. The sample program loads data, reports the contents of the database, performs an update and a calculation, and then reports the new status of the data. Comments in the code show places where functions could be added in the future to perform additional operations.
To get some idea of the types of operations that the API can perform, take a look at the C Main API Function Groups and/or the Visual Basic Main API Function Groups. There are almost 200 functions in the C Main API divided into 20 functional groups. That means there is a wide variety of operations that the API can perform. The C Outline API (78 functions) and the Grid API (59 functions) represent additional possible complexity for an API program. The sample programs need to stay as simple as possible, so they only use a small number of functions from the C Main API, and they do not use the Outline API or the Grid API at all.
The sample programs use the Hyperion Essbase Sample Basic database that is supplied with the Hyperion Essbase server. The database is delivered empty and needs to be loaded with data. The data is delivered in a text file named CALCDAT.TXT. The sample program uses a prebuilt calc script and a prebuilt report script. The login information used by these programs (server name, application name, database name, user name, and password) are hardcoded into the program. The program displays the Login dialog box, but all the fields are filled in. The user needs only to click Okay in response to the dialog box. The server name is "LocalHost". The application name is "Sample". The database name is "Basic". The user name is "admin" and the password is "password".
Building Dimensions
Dimensions are the building blocks of the database. They define the database's structure (commonly referred to as the outline or metadata. Build the database by first assembling the necessary dimensions and each dimension's associated members. Then add the data. The outline can be developed from scratch or an existing database can be altered by adding and subtracting dimensions and members. The Sample Basic application/database is delivered with a complete outline, so it is not necessary to build the outline to run the sample programs. But it is necessary to load the data either through the Essbase Application Manager or by running the sample program.
The API can automate the process of rebuilding dimensions dynamically from a data file or SQL source. To automate the process you must first create rules files by using the Hyperion Essbase Application Manager and then use the rules files to build the dimensions by calling EssBuildDimension() or EsbBuildDimension().
These functions take the rules and data file object definitions as arguments and dynamically modify the outline on the server according to the parameters set in the rules file. They also cause any data in the database to be restructured to correspond to the new dimension structures in the outline.
The API can alter an existing database by adding and subtracting dimensions and members (using the Outline API) until the needed structure is in place. After the outline is finished load the data into the database using EssImport() or EsbImport().
Editing the Outline
The database outline can be navigated and modified, using the outline API functions. These functions allow movement through the outline hierarchy, modification of member information and properties, addition and deletion of members, and so on.
To begin using an outline, call EsxOtlOpenOutline(). If you intend to edit the outline, you should set both of the fLock and fKeepTrans arguments passed to EsxOtlOpenOutline() to TRUE. The fLock flag locks the outline to prevent anyone else from updating it (but not from viewing it). The fKeepTrans flag saves all transactions performed during the edit of the outline, for when the outline is subsequently restructured.
To start navigating the outline from the first dimension member, call EsxOtlGetFirstMember(). Alternately, you can locate a member by name by using EsxOtlFindMember() or EsxOtlFindAlias(). In either case, the function returns a member handle that can then be used to get or set information about that member or to get the member handles of adjacent members in the outline hierarchy.
To get information about the current member, use EsxOtlGetMemberInfo(), EsxOtlGetMemberAlias() and EsxOtlGetMemberFormula(). To set information for the current member, use the corresponding Set functions.
To get the parent of a member, call EsxOtlGetParent(). To get the first child of a member, call EsxOtlGetChild(). To get the siblings of a member, call EsxOtlGetNextSibling() or the EsxOtlGetPrevSibling(). To locate the next shared occurrence of a member, call EsxOtlGetNextSharedMember().
To add or delete dimensions in an outline, use EsxOtlAddDimension() or EsxOtlDeleteDimension().
To modify members in the outline hierarchy, use EsxOtlAddMember(), EsxOtlDeleteMember(), or EsxOtlMoveMember().
After an outline is modified, it can be verified using EsxOtlVerifyOutline(), saved using EsxOtlWriteOutline(), and then closed using EsxOtlCloseOutline().
Before any changes made to a server outline can take effect, the database must be restructured by calling EsxOtlRestructure(). This function applies the edits made to the outline against the old version of the outline and restructures both the outline and the associated data.
For detailed descriptions of these functions see EssOtlOpenOutline(), EssOtlGetMemberInfo(), EsbOtlOpenOutline(), EsbOtlGetMemberInfo() and each function's associated See Also lists.
Loading Data
After the outline dimensions are built, data can be loaded into the database through the API. The data load can be done by using a data file or a SQL source together with a rules file, by loading a free-form data file, or by loading free-form data a record at a time.
To load by using a rule with either a data file or an SQL source, use EsxImport(). Pass valid rules and data file object definitions as arguments. To load a free-form data file without a rules file, simply pass a NULL rules file object definition.
To load data a record at a time, call EsxBeginUpdate() with the Unlock argument set to FALSE, and then call EsxSendString() with each record of data to be loaded. This method avoids the need to lock the blocks being updated. This mechanism should be used only for batch data loading. Do not use this mechanism in multi-user situations. The lack of locking can compromise data integrity.
Note also that each record sent to the server by this method must have a terminating newline character at the end of each row.
For detailed descriptions of all these functions, see EssImport(), EssBeginUpdate(), EsbImport(), and EsbBeginUpdate().
Would you like to see an actual Sample C API Program?
Would you like to see an actual Sample Visual Basic API Program?
Reporting
Reporting in the Hyperion Essbase API requires the use of a report script. The report script is sent through the API to the Hyperion Essbase server and is executed. The results are sent back through the API to the caller. The resulting output data can be displayed, printed, sent to a file, and so on. It can also be parsed and stored in an array data structure within your program.
A report script is a text string that contains the data extraction and data formatting commands required to generate output from the Hyperion Essbase server. See the Essbase Technical Reference for a full description of the Essbase report script language. The following principal elements generally need to be included in a report script for an API application:
Many of these elements are typical user-configurable parameters that are set up in advance by the user, either globally or per-report (or both). The elements are described in detail below.
The {TABDELIMIT} Command
This command should be included at the beginning of any report script sent to the API. It causes the output data to be returned in a format useful for parsing within a program. This command suppresses all unnecessary formatting (for example the commas used as thousand separators in numbers) and returns each member name or data value as a tab-separated token, that can be parsed and divided into cells.
The {DECIMALS n} Command
This command specifies the decimal precision of the returned numeric data (all numbers in Essbase are stored internally as floating point numbers with 15 digits of precision). For example, {DECIMALS 2} gives two digits of decimal precision.
The {INDENTGEN n} Command
This element allows a program the option of indenting either parent members or child members in the rows of the report output. A negative value of n indents parent members by n spaces relative to their children. A positive value of n indents the child members by n spaces relative to their parents. A zero value of n turns off all indenting. For example, {INDENTGEN -2} indents parent members by two spaces per level (the default):
100-10 47 41 50 138 100-20 44 38 49 131 100-30 21 14 20 55 100 112 93 119 324 200-10 25 19 23 67 200-20 18 14 18 50 200-30 17 9 14 40 200 60 42 55 157 Product 287 217 290 794
The {SUPMISSING} and {SUPZERO} Commands
To eliminate unnecessary rows in the report output, use the {SUPMISSING} and {SUPZERO} commands. The {SUPMISSING} command suppresses the output of all data rows that contain only #Missing values (that is, no actual data), and the {SUPZERO} command suppresses the output of rows that contain only zero values.
Also useful are the {SUPBLANK} command, that suppresses both zero and #Missing values, and the {SUPALL} command, that suppresses a range of report output parameters.
The {MISSINGTEXT string} Command
If the output data includes a #Missing value, the #Missing value can be automatically converted to a string specified by the program. For example {MISSINGTEXT "N/A"} converts any #Missing values to the string "N/A".
The {OUTALTNAMES} or {OUTMBRNAMES} Command
If you need to use alias names instead of member names in the output, include the {OUTALTNAMES} command in your report script. To revert to member names, use the {OUTMBRNAMES} command (the default).
The <PAGE, <COL, and <ROW Commands
These commands specify how the different dimensions are oriented in a report. The <PAGE command specifies which dimensions are in the page header (at the top of the report), and the <COL and <ROW commands specify that dimensions are in the columns and rows, respectively. For example, <ROW(Market, Product) forces the members of the Market and Product dimensions to be displayed in that order in the rows of the report.
Any member from any dimension can be specified in the <PAGE, <COL, and <ROW commands. Each dimension should appear in only one of these commands, otherwise the last command takes precedence, and all dimensions should be specified (or the report layout will be unpredictable).
The List of Member Names
To extract the data required in the report by the simplest method, list the members concerned. For example, "Actual Sales Ohio Jan Feb Mar Product" produces the following report output:
Actual Sales Ohio Jan Feb Mar Product 287 217 290
Alternately, you can use macro commands to specify a range of members from a dimension. Consider the following example:
Note: All the above macro commands can be abbreviated, for example, <DESC, <ICHILD, and <PAR.
The most commonly used of the above macro commands are <CHILD (or <ICHILD) to perform a single level drill-down; <DESC (or <IDESC) to perform multilevel drill-downs, and <DIMBOTTOM to drill down to the lowest level members of a dimension.
For example, "Actual Sales Ohio <ICHILD Qtr1 <DESC Product" produces the following report output:
Actual Sales Ohio Jan Feb Mar Qtr1 100-10 47 41 50 138 100-20 44 38 49 131 100-30 21 14 20 55 100 112 93 119 324 200-10 25 19 23 67 200-20 18 14 18 50 200-30 17 9 14 40 200 60 42 55 157 300-10 30 19 32 81 300-20 24 16 25 65 300-30 12 7 11 30 300 66 42 68 176 400-10 30 27 32 89 400-20 14 10 12 36 400-30 5 3 4 12 400 49 40 48 137 100-20 44 38 49 131 200-20 18 14 18 50 300-30 12 7 11 30 Diet 74 59 78 211
Because member names can be numbers (for example, "100") and can contain embedded spaces (for example, "New York"), it is always a good practice to surround member names with double quotation marks when sending a report script to the API. In Hyperion Essbase Release 4.0 and above, you can force member names to be output in this format by using the {QUOTEMBRNAMES} command.
The Bang (!)
The final element of a report script must always be a bang (!), the exclamation point character. Each script must have one (at least one) bang to cause data to be generated. If a report script appears to be executing correctly but no data is output, check to make sure that you are appending a bang to the report script.
Would you like to see an actual Sample C API Program?
Would you like to see an actual Sample Visual Basic API Program?
A report script can be executed in one of three ways:
To execute a report, you can call EsxReport() and pass the report script as a single string. Set the Output argument to TRUE and the Lock argument to FALSE unless you are performing a lock and send operation.
Alternately, call EsxBeginReport() (setting the Output and Lock arguments as above), and then call EsxSendString() to send the report script a string at a time. Finally, terminate the report sequence with a call to EsxEndReport().
To execute a report script from a file, call EsxReportFile().
To get the report output, call EsxGetString() repeatedly to read the returned strings, until a null value is returned (in C, this means a null pointer value, in Visual Basic, an empty buffer is returned).
For more detailed descriptions of all these functions, see EssReport(), EssReportFile(), EssBeginReport(), EsbReport(), EsbReportFile(), and EsbBeginReport()
Would you like to see an actual Sample C API Program?
Would you like to see an actual Sample Visual Basic API Program?
To parse the data returned from a report, you first need to understand the report's format. If you included the {TABDELIMIT} command in the report script, the data comes back in the following format:
<token><tab><token><tab><token><tab>..........<token><newline> <token><tab><token><tab><token><tab>..........<token><newline> ..... <token><tab><token><tab><token><tab>..........<token><null>
For example, consider the following report script:
{SSFORMAT}{DECIMAL 0} <COL(Year) <ROW(Market) Budget Sales Cola <CHILD Qtr1 <ICHILD Market !
This report script would normally output data that looks like the following:
Budget Sales Cola Jan Feb Mar East 5200 5000 5300 West 5600 5350 5700 Central 4250 4050 4400 South 3800 3450 3800 Market 18850 17850 19200
When you include the {TABDELIMIT} command, the report script outputs the data as follows:
<tab>Budget<tab>Sales<tab>Cola<newline> <tab>Jan<tab>Feb<tab>Mar<newline> East<tab>5200<tab>5000<tab>5300<newline> West<tab>5600<tab>5350<tab>5700<newline> Central<tab>4250<tab>4050<tab>4400<newline> South<tab>3800<tab>3450<tab>3800<newline> Market <tab>18850<tab>17850<tab>19200<null>
To parse data in this format, scan the returned string for a tab, a newline, or a null, each of which define the end of a token. The token can be one of four types:
If the report is stored in an internal data structure, such as a grid or array, and the report shrinks in the number of rows or the number of columns (for example, after a zoom out operation), you might need to adjust the bounds of the new report.
The possible conflict between numeric values and numeric member names can usually be resolved by scanning any tokens that begin with a number and validating that they conform to the parameters (for example, decimal precision) of a number value. Any token that does not conform should be treated as a member name.
A more reliable method is to use the positioning of the token in the report to determine whether it is a member name or a data value. The first x rows of the report can be only member names (where x is the number of column dimensions + 1 row for the page header), and the first y columns can only be member names (where y is the number of row dimensions). If the coordinates of the token are greater than both x and y, then the token is either a special value (begins with a # character), or it is a number value.
It is possible to force double quotation marks around all member names (and so avoid the identification issue) by using the <QUOTEMBRNAMES command. When you use this command, you can recognize member names by the leading double quotation marks.
It is often useful to parse the returned report output tokens into Page, Column, Row and Data areas, so they can be easily re-used in subsequent reports (see Using Report Output as a Script, below).
The output from an Hyperion Essbase report can be used as the input to another report. The report output contains only member names and data, so you need to preface the new report with the header commands (as described above). Then append the member names output by the previous report onto the report header (not including the returned data, to avoid sending unnecessary information to the server), and execute that as a script. For example, if you first execute the following:
<COL("Year") <ROW("Market")
"Actual" "Sales" "Cola" <CHILD "Qtr1" <CHILD "East"
!
The resulting report output might look something like the following:
Actual Sales Cola
Jan Feb Mar
New York 36 32 39
Massachusetts 24 09 14
Florida 37 29 37
Connecticut 0 5 11
New Hampshire 12 10 11
Now if you send the header from the previous report (that is, the first two lines of format commands), strip out all data from the report output, surround all member names with double quotation marks, and append a bang (!) character, you should get the following report script:
{TABDELIMIT}{DECIMALS 0} <PAGE("Scenario", "Measures", "Product") <COL("Year") <ROW("Market")
"Actual" "Sales" "Cola" "Jan" "Feb" "Mar" "New York" "Massachusetts" "Florida" "Connecticut" "New Hampshire"
!
This script now generates the same report that the first script generated. This method is useful when performing a series of ad-hoc operations, such as drill-downs, on a view.
Hyperion Essbase inserts spaces before certain member names. What is inserted depends on the <INDENTGEN report setting. Leading spaces must be removed if the members are subsequently used as part of a report script.
To perform a simple (one-level) zoom in on a member in a view, send the output from the report that created the view as a script with the <CHILD (or <ICHILD) command before the member to be zoomed on. To perform a multilevel zoom in, use the <DESC or <IDESC commands. To perform a zoom out, use the <PARENT (or possibly the <ANCESTORS) command.
For example, consider the following report output:
Actual Sales Cola
Jan Feb Mar
East 109 85 112
If the user chooses to drill down on East, the report script might be as follows:
{SSFORMAT}{DECIMALS 0} <PAGE(Scenario, Measures, Product) <COL(Year) <ROW(Market)
Actual Sales Cola Jan Feb Mar <ICHILD East
!
This script generates the following report output:
Actual Sales Cola
Jan Feb Mar
New York 36 32 39
Massachusetts 24 09 14
Florida 37 29 37
Connecticut 0 5 11
New Hampshire 12 10 11
East 109 85 112
It is possible to force the output of a report to be in a tabular format that resembles a relational database query. The report writer commands to achieve this format are as follows:
Also, all of the dimensions (or all but one) need to be included in the <ROW command in the report, to ensure that the data is returned in a fully normalized form.
The {ROWREPEAT} Command
This command causes the full list of member names to be output on each row of the report, even when there are nested groups. In the following example, Ohio is repeated on each row:
Actual Sales Jan Feb Mar Ohio 100-10 130 121 134 Ohio 100-20 118 104 123 Ohio 100-30 77 65 81
The {SUPCOLHEADING} Command
Adding this command to the report suppresses the column headings in the report output.
Actual Sales Ohio 100-10 130 121 134 Ohio 100-20 118 104 123 Ohio 100-30 77 65 81
The {SUPHEADING} Command
Adding this command also suppresses the page headings in the report output. As shown in the following example:
Ohio 100-10 130 121 134 Ohio 100-20 118 104 123 Ohio 100-30 77 65 81
Updating Data
Updating data is the process of changing data in a view, and sending the data back to the server. When the update is in progress the user must lock the blocks that relate to the view. This ensures that no other user can change the data between the time the program retrieves that data and the time the data is written back to the database.
The sequence of actions for an update is as follows:
Lock the blocks with EsxReport() or EsxBeginReport(). Make sure to set the Lock argument passed to these functions to TRUE, locking all the blocks relating to the retrieved data. These functions can either lock the blocks and retrieve the data or just lock the blocks (if the data is either new or already current). The functions lock the blocks without retrieval by changing the value of the Output argument passed to them to TRUE or FALSE, as appropriate.
Next, allow the user to edit the data cells in the view (using whatever mechanism your product provides).
Finally, call EsxUpdate() and pass it the entire contents of the view (including the updated data values), or call EsxBeginUpdate(), and send the entire view to the server a string at a time by calling EsxSendString().
Each string sent to the server must have a newline terminating each line of the update specification.
To execute an update from a file, first lock the blocks as described above and then call EsxUpdateFile().
For more detailed descriptions see EssUpdate(), EssSendString(), EssBeginUpdate(), EsbUpdate(), EsbSendString(), and EsbBeginUpdate().
Would you like to see an actual Sample C API Program?
Would you like to see an actual Sample Visual Basic API Program?
Calculating Data
To calculate data in Hyperion Essbase means to consolidate part or all of the database by using either the hierarchies and formulas defined in the database outline (the default calculation), or the formulas contained in a calc script.
The default calculation is stored in the database and is executed by calling EsxDefaultCalc(). To get and set the script used for a default calculation, use EsxGetDefaultCalc() and either EsxSetDefaultCalc() or EsxSetDefaultCalcFile().
Like reports, calculations can be executed in one of three ways:
Calculations in Hyperion Essbase are asynchronous operations, meaning that when the appropriate calc function is called, the API returns to the caller immediately without waiting for the calc to finish (unlike executing a report, for example). Hyperion Essbase uses asynchronous calculations because a calculation can take a significant amount of time to complete (several hours is not uncommon). So, after the calculation starts, the program must check (by calling EsxGetProcessState()) at intervals to see if the calculation is complete.
The simplest way to check is to set up a system timer to wake up a process at short intervals (say 5-10 seconds), checking the status of the calculation. While the calculation is running you can perform any other operations within your program, but you can not make function calls to the Hyperion Essbase API using the same context handle.
For detailed descriptions of all these functions see EssCalc(), EssBeginCalc(), EssCalcFile(), EsbCalc(), EsbBeginCalc(), and EsbCalcFile().
Would you like to see an actual Sample C API Program?
Would you like to see an actual Sample Visual Basic API Program?
Using Security
The security system in Hyperion Essbase is quite sophisticated. All the capabilities provided by the Hyperion Essbase Application Manager for administering security are available through the Hyperion Essbase API. To fully understand the workings of the security system, refer to the Hyperion Essbase Database Administrator's Guide.
Many of the functions that use the security system require certain privileges to be available to the logged in user and return errors if an attempt is made to change security information without the correct authority. Typically, the logged in user should have Supervisor or Application or Database Designer privileges, but you should be aware of possible problems if you are using the security functions and should plan for such errors, particularly during your initial testing.
To create or delete users or groups in Hyperion Essbase, use EsxCreateUser() and EsxDeleteUser(). To set a user's password, use EsxSetPassword(). To get a list of users on a server, use EsxListUsers().
To get and set a user's or a group's security information, call EsxGetUser() and EsxSetUser().
To get and set the list of users that are members of a group (or the list of groups to which a member belongs), call EsxGetGroupList() and EsxSetGroupList().
To get user access privileges to an application, call EsxGetApplicationAccess().
The security functions can return the names of all the users who have access to a named application, all the applications to which a named user has access, or the access level of a specific application-user combination. A similar function exists for databases, and corresponding Set functions exist for setting application and database access.
To get the contents of a named security filter, first call EsxGetFilter() then repeat calls to EsxGetFilterRow() (to get each row description in the filter) until a NULL string is returned. To set the contents of a filter, first call EsxSetFilter(), and then repeat calls to EsxSetFilterRow() until all rows have been sent (send a NULL row pointer to terminate the sequence).
To get a list of the named filters in a database, call EsxListFilters(). To get a list of users who are assigned a named filter, use EsxGetFilterList().
For detailed descriptions of the security-related functions, see the C Security Filter Functions and the VB Security Filter Functions.
Maintaining Applications and Databases
Apart from maintaining database outlines, there are some other administrative functions that can be performed with the API.
To get information about an application, use EsxGetApplicationInfo(). To get modifiable application state parameters, call EsxGetApplicationState() (a corresponding Set function also exists to update these parameters). Similar administrative functions exist for databases.
When using any of the application or database Set functions, call the corresponding Get function first to initialize the structure fields.
To get an application log file, call EsxGetLogFile().
To get a selection of database run-time statistics, call EsxGetDatabaseStats(). To get or set a database note (a text string that can be viewed from the default Hyperion Essbase login dialog box), use EsxGetDatabaseNote() and EsxSetDatabaseNote().
To export part or all of a database into a text file format that can be loaded into Hyperion Essbase, use EsxExport().
To move Hyperion Essbase file objects (outlines, calc scripts, rules files, and so on) between applications or databases, use EsxCopyObject(). To move objects between the client and server for editing, use EsxGetObject() and EsxPutObject().
To create an object, call EsxCreateObject(). To rename an object, call EsxRenameObject(). To delete an object, call EsxDeleteObject(). To list all objects of a particular type within an application or database, call EsxListObjects().
For detailed descriptions of using the administration functions for database and application, see C Database Functions, C Application Functions, VB Database Functions, and VB Application Functions.
Would you like to see an actual Sample C API Program?
Would you like to see an actual Sample Visual Basic API Program?
Handling Messages
The API includes a mechanism for intercepting error messages and other messages generated at the server and for displaying the appropriate messages automatically on the client program's screen. This mechanism, although generally useful, can be turned off if desired. The API allows your program to prevent those messages from appearing and to trap them for processing within your program. You can choose which messages to display and then display the choses messages in a way that is consistent with your program's internal message and error handling. This mechanism provides seamless integration of Hyperion Essbase with your program.
The default message processing in Hyperion Essbase is platform-dependent, but typically generates a dialog box with the log information (application and database name, username, timestamp, and so on) and the message text. Consider the following example:
Every Hyperion Essbase message has a unique identification number, a message level number, and an associated text string (that is not necessarily unique). By default, Hyperion Essbase displays error messages only for serious errors, not for warnings and not for information messages.
Message Handling in C
In the C API, you can define a Custom Message Handling function and pass a pointer to that function during the initialization call, EssInit(). This custom function is then called when the API receives a message from the server. The custom function can examine the function return code either to process the message internally or to pass the message back to the API for default message processing. For more details see, Message Handling in the C Main API.
An example of a message handling function for Windows and C is given below:
/* C Example of a message handling function */
ESS_FUNC_M ErrorHandler (ESS_PVOID_T myCtx,
ESS_LONG_T MsgNum,
ESS_USHORT_T Level,
ESS_STR_T LogStr,
ESS_STR_T MsgStr)
{
ESS_STS_T sts = 0;
ESS_STR_T ErrorStr;
ESS_USHORT_T len;
HANDLE hMem;
/* Only display messages of level ERROR or above */
if (Level >= ESS_LEVEL_ERROR)
{
/* Calculate combined length of Log and Message strings */
len = 3; /* allow for end of line characters + null */
if (LogStr != NULL)
len += strlen (LogStr);
if (MsgStr != NULL)
len += strlen (MsgStr);
/* Concatenate the strings */
if ((hMem = GlobalAlloc (GPTR, len)) != 0)
{
ErrorStr = GlobalLock (hMem);
sprintf (ErrorStr, "%s\n%s", LogStr, MsgStr);
/* Display message in a Windows message box */
MessageBox ((HWND)NULL, ErrorStr, "Essbase Error",
MB_OK);
GlobalUnlock (hMem);
GlobalFree (hMem);
}
}
return (sts);
}
Message Handling in Visual Basic
In the Visual Basic API the message handling mechanism is slightly different. Again, you pass a parameter to the API during the initialization call, EsbInit(). The call initiates custom message processing (suppressing the Hyperion Essbase default processing) and sets up a message stack. Then, when an error occurs in your program (indicated by a non-zero return value from an API function call), you should call an internal error handling function. That function should in turn call EsbGetMessage() to retrieve any messages from the stack and then display the messages in whichever way you choose. For more details, see Message Handling in the Visual Basic API.
An example of a message handling function in Visual Basic is given below:
' VB Example of message handler Dim hInst As Long Dim hCtx As Long Dim sts As Long Dim Server As String * ESB_SVRNAMELEN Dim User As String * ESB_USERNAMELEN Dim Password As String * ESB_PASSWORDLEN Dim Appname As String * ESB_APPNAMELEN Dim Dbname As String * ESB_DBNAMELEN Dim Access As Integer Dim Init As ESB_INIT_T ' GetMessage Variables Dim Count As Integer Dim TestApp As String Dim TestDb As String Dim TestFtrName As String Dim ErrMsg As String * 256 Dim ErrNum As Long Dim ErrLev As Integer ESB_TRUE = Chr$(1) ESB_FALSE = Chr$(0) Init.Maxhandles = 10 Init.ClientError = ESB_TRUE Init.ErrorStack = 100 sts = EsbInit(Init, hInst) sts = EsbAutoLogin(hInst, Server, User, Password, Appname, Dbname, ESB_AUTO_NOSELECT, Access, hCtx) If sts <> 0 Then sts = EsbGetMessage(hInst, ErrLev, ErrNum, ErrMsg, 256) MsgBox ErrMsg & Chr(13) & "Program Ending" End If TestApp = "Sample" TestDb = "Basic" TestFtrName = "Anything" 'This function call should return an error and then be picked up by EsbGetMessage sts = EsbGetFilterList(hCtx, TestApp, TestDb, TestFtrName, Count) If sts <> 0 Then sts = EsbGetMessage(hInst, ErrLev, ErrNum, ErrMsg, 256) MsgBox "Program Ending" & Chr(13) & Chr(13) & ErrMsg End If sts = EsbLogout(hCtx) sts = EsbTerm(hInst) End
Managing Memory
In the C API only, it is possible to define custom memory management functions for use within the API itself, so that you do not have any conflict between your internal memory management scheme and the memory management scheme of the API. Again, custom functions provide integration of the API into your program.
First, you need to write three functions within your code:
Next, you need to pass pointers to these three functions to the API during the initialization call, EssInit(). The functions are then used within the API whenever the API needs to allocate, free, or reallocate a memory buffer. Any items that are allocated within the API and returned to your program are guaranteed to have used these functions, so you can reallocate or free them without any possibility of a memory corruption or violation.
For more information on using custom memory management with the API, see Using Memory in C Programs.