Essbase 11.1.2.4.002+ changes to unloading applications using Maxl

I was recently sat in the “What’s New in Essbase 11.1.2.4” session at Kscope15 where one of the questions from the audience lead on to a discussion on terminating applications, Steve Liebermensch from Oracle spoke of an upcoming update in patch 002 for Essbase 11.1.2.4 which will change  the behaviour of unloading applications using Maxl.

Fast forward a few days and the Essbase 002 PSUs are released.

In the patch readme it contains the following:

Changes in Server Shutdown (21137674)

With this release, issuing a MaxL statement to unload an application cancels all active requests and database connections, and stops the application, unless you explicitly specify otherwise using the no_force option.
Updated MaxL Syntax

alter system unload application {all|APP-NAME} [no_force];

no_force Optional keyword. The no_force option causes Essbase to return an error if active requests are running on the application.

In the past it has sometimes been a bit of an issue shutting down applications when there are active requests, there are ways around it using a few different MaxL statements like kill requests and forcibly log out sessions but these can have varying success depending on the active requests and the Essbase version, I must admit from 11.1.2.3 it has been much easily to terminate requests like exports.

I will just go through the behaviour of using the unload application MaxL statement when there are active requests using the base install of 11.1.2.4

First of all with an active data load in progress then issuing the MaxL unload application statement.

MAXL> alter system unload application MORE2LIFE;
 OK/INFO - 1054005 - Shutting down application MORE2LIFE. 
ERROR - 1013018 - Cannot unload database [BLOG] while user [admin@Native Directory] is performing database operation.  Wait for the user to complete the operation, or ask the user to abort it.  Log out all users and then unload the database..

Ok so I added the following line to the same unload script:

alter system kill request on application MORE2LIFE;

Even though the request was killed the application was not unloaded

MAXL> alter system kill request on application MORE2LIFE;

 OK/INFO - 1056090 - System altered.

MAXL> alter system unload application MORE2LIFE;

 OK/INFO - 1054005 - Shutting down application MORE2LIFE.
   ERROR - 1013018 - Cannot unload database [BLOG] while user [admin@Native Directory] is performing database operation.  Wait for the user to complete the operation, or ask the user to abort it.  Log out all users and then unload the database..

To get around this I added the logout session with force statement though I am sure it can be done with other statements.

MAXL> alter system logout session on application MORE2LIFE force;

 OK/INFO - 1013220 - Supervisor [admin@Native Directory] has forced user [admin@Native Directory] to logout.
 OK/INFO - 1051037 - Logging out user [admin@Native Directory], active for 11 minutes.
 OK/INFO - 1056092 - Sessions logged out [1].
 OK/INFO - 1056090 - System altered.

MAXL> alter system unload application MORE2LIFE;

 OK/INFO - 1054005 - Shutting down application MORE2LIFE.
 OK/INFO - 1056090 - System altered.

It was exactly the same behaviour when running a calculation, unload was no use without killing the request and the user, running a data export only required the request killing first before successfully unloading like the following:

MAXL> alter system kill request on application MORE2LIFE;

 OK/INFO - 1056090 - System altered.

MAXL> alter system unload application MORE2LIFE;

 OK/INFO - 1054005 - Shutting down application MORE2LIFE.
 OK/INFO - 1056090 - System altered.

The essbase application log gives more of an insight to what is going on.

Received Command [ParExport] from user [admin@Native Directory]

Request [ParExport] from user [admin@Native Directory] was terminated by an admin user

Transaction [ 0x4000f( 0x55913c7a.0x6aef0 ) ] aborted due to status [1014030].

Parallel export failed due to status [1014030]

Transaction [ 0x4000e( 0x55913c7a.0x6aef0 ) ] aborted due to status [1005032].

Received Command [Unload Database]

RECEIVED SHUTDOWN COMMAND - SERVER TERMINATING

Right so moving on to the difference with the 002 patch applied.

It is worth taking note of the information in the essbase patch readme:

Oracle recommends using the same version of all Essbase portfolio products (Essbase, Essbase Administration Services, Hyperion Provider Services, and Essbase Studio) and components (server, client, runtime client, API, and JAPI). When only some Essbase portfolio products are included in a patch release, the last released versions of the products that are not included in the patch are supported.

What is also interesting is that Oracle have now started to include the following recommendation after applying essabse server patches.

After you apply a patch within the same release codeline, Oracle recommends as a best practice that you export the data from your databases, clear the data from the databases, and then reload the data.

I tested the same script with only an unload application statement and this time the application was shutdown successfully while running active requests like data loads, calculations and exports.

Here is an example of unloading when an export was running

MAXL> alter system unload application MORE2LIFE;

 OK/INFO - 1054005 - Shutting down application MORE2LIFE.
 OK/INFO - 1013432 - Unloading application [MORE2LIFE]: waiting for active requests to finish..
 OK/INFO - 1056090 - System altered.

As you can see now the application is unloaded without the need for any additional MaxL statements

If you look in the application log it looks similar to running a MaxL kill request and then an unload.

Received Command [ParExport] from user [admin@Native Directory]

Canceling current requests.

Unloading application [MORE2LIFE]: waiting for active requests to finish.

Request [ParExport] from user [admin@Native Directory] was terminated by an admin user

Transaction [ 0x20002( 0x55914ccf.0xe2ce8 ) ] aborted due to status [1014030].

Parallel export failed due to status [1014030]

Transaction [ 0x30001( 0x55914ccf.0xe2ce8 ) ] aborted due to status [1005032].

RECEIVED SHUTDOWN COMMAND - SERVER TERMINATING

If you don’t want to use force which is the default to unload the application then you can add no_force to the statement and the the unload should behave in the way it used to.

MAXL> alter system unload application MORE2LIFE no_force;

 OK/INFO - 1054005 - Shutting down application MORE2LIFE.
   ERROR - 1013018 - Cannot unload database [BLOG] while user [admin@Native Directory] is performing database operation.  Wait for the user to complete the operation, or ask the user to abort it.  Log out all users and then unload the database..

So finally it is much simpler to unload applications which will certainly should help eliminate orphaned ESSSVR processes when shutting down Essbase.

HFM 11.1.2.4 as a data source in OBIEE

Continuing on the theme of EPM data sources in OBIEE I thought I would just put together a post of about using the HFM thin ADM driver, recently I have asked a few times about the correct process for getting up and running with a HFM 11.1.2.4 as a data source in OBIEE.

The OBIEE documentation has not yet been updated to include HFM 11.1.2.4 even in the recent 11.1.1.9 release and to be honest it what exists in the documentation is a little confusing anyway.

Prior to 11.1.2.3 you would use the ADM native driver to connect OBIEE to HFM but from 11.1.2.3 there was the option to use thin drivers allowing the configuration to be also run on Linux.

With all the changes to HFM in 11.1.2.4 support for the native ADM driver has gone and the only way to connect is using the thin driver.

Component	Supported Version
Financial Management ADM Driver	Release 11.1.2.1.xxx (native drivers) Release 11.1.2.2.xxx (native drivers) Release 11.1.2.3.xxx (This release supports native and thin drivers) Release 11.1.2.4.000 (This release supports only thin drivers)

I am going to concentrate on the process to get running with HFM 11.1.2.4 as this is the first version where the thin driver has to be used and will be in the future, I will be using OBIEE 11.1.1.9 though the process should still be relatively similar when using the thin driver in HFM 11.1.2.3 or OBIEE 11.1.1.7

The first thing to do is to get the required HFM drivers (basically a set of jar files) installed on the OBIEE server, I am not quite sure why they have not been automatically deployed in OBIEE 11.1.1.9 for HFM when they are for Essbase and Planning.

There is no separate installer just to drop the ADM related files so you will need to use the full EPM system installer which really does seem like an overkill for what is actually required.

After firing up the EPM installer keep the middleware home separate to OBIEE unless you want to destroy your OBIEE deployment.

In 11.1.2.4 there is no option to install the FM ADM driver like in 11.1.2.3 so to get the required files laid down select the SDK.

You will notice that by default WebLogic application server will be installed and there is no way of getting out of this, it can be easily be uninstalled at a later stage though.

There are a whole raft of components that will get installed as they get laid down no matter what EPM products you are installing.

In the end there are around 4GB of files that have been installed which unfortunately if you are sticking with the supported route then it is something you have to live with at the moment.

Now the next step in the process I do have questions whether it is required which I will get on to later but for now let’s stick with it.

Run the EPM configurator and set a new EPM instance name.

Configure to point to the EPM Shared Services / Registry database.

There is the option to configure FM but I don’t believe this is actually required and it is your choice to whether you feel it is needed.

Next we need to configure OBIEE to be able to pick up the EPM related jar files so we can start to use HFM as a data source.

This requires updates to the OPMN configuration file located in:

<OBIEE_INSTANCE>/config/OPMN/opmn/opmn.xml

Two additional variables are required under the Java Host component section which will be based on where the EPM components were installed too and the EPM instance location which was configured.

For example:

<variable id="EPM_ORACLE_HOME" value="E:/Oracle/Middleware/EPMSystem11R1"/>
<variable id="EPM_ORACLE_INSTANCE" value="E:/Oracle/Middleware/user_projects/obiee"/>

Now the location of the required EPM jar files need adding to the classpath parameter

Instead of going through adding lots of jars I have found it is best to add the following:

$EPM_ORACLE_HOME/common/jlib/11.1.2.0/epm_hfm_web.jar$:$EPM_ORACLE_HOME/common/jlib/11.1.2.0/epm_j2se.jar$:

The above jar files contain a manifest which includes an additional classpath to all the jar files which will be required.

Once the two additional jars have been added to the classpath then OBIEE can be restarted and should be good to start importing the metadata within the administration tool.

When you import the metadata and select Hyperion ADM connection type the URL for FM will be based on the native driver which is not supported.

The format to use the thin driver is:

adm:thin:com.oracle.hfm.HsvADMDriver:CLUSTERNAME:APPNAME

If everything has been configured correctly then OBIEE should be able to connect to the HFM application so the metadata can be imported.

If there is anything wrong with the configuration then you will probably get errors relating to java classes not being found or ADM errors, for example:

If you do get errors then double check the OPMN file, also make sure the ADM files jar files adm.jar and ap.jar are correctly being picked up.

Also problems can occur if there is an overlap of jar files which I have seen in 11.1.1.7 so it is worth deleting all files except admimport.jar and admintegration.jar in:

<MIDDLEWARE_HOME>/Oracle_BI1/bifoundation/javahost/lib/obisintegration/adm

If there are no problem then HFM dimensions and associated properties should be imported.

Once everything has been done in the physical, BMM and presentation layers and OBIEE restarted you should be good start developing reports.

If you are using the administration tool on a client machine then you may receive the following error:

If so then update the OBIEE NQSConfig.ini file on the client machine to include the server name and port of the OBIEE server running the JavaHost.

So everything is working now so all good but I just want to return to the step where the EPM configurator is run as I am questioning whether it is required or not.

Running the EPM config utility will add information into the EPM System registry about the OBIEE instance which probably does not need to be there.

The important part is that running config creates a reg.properties file which contains connection information to EPM registry database.

Adding the EPM_ORACLE_INSTANCE variable to the OPMN xml file means the reg.properties file and the EPM environment can be accessed.


To test this theory I did the following on an OBIEE instance which had not had the EPM configurator run, please note I am not suggesting you do this.

Created a folder which will be used as the EPM_ORACLE_INSTANCE variable value.

Created the folder structure and copied the reg.properties file from the EPM environment.

Set the EPM_ORACLE_INSTANCE variable to path I created.

Did it work? Yes it did and I didn’t see any issues, so is the EPM configuration actually required?

I also wanted to know if all that EPM installation was actually required so I removed the EPM installation and copied two directories from an EPM environment to the OBIEE one.

The combined total for these directories is around 16MB.

Updated EPM_ORACLE_HOME variable to point to the OBIEE home location.

As far as I could work out everything seemed to be working which meant a saving of the EPM installation process and 4GB of additional files.

I am stressing though do not try this as I just wanted to prove that Oracle can overcomplicate things sometimes and surely it would be easily if they had included the files in the 11.1.1.9 installation like the other EPM related files.

This method would only really work with 11.1.2.4 and OBIEE 11.1.1.9 because most of the EPM files in OBIEE 11.1.1.9 are the 11.1.2.4 version, once patches arrive this all changes though and you have to take into consideration that OBIEE and EPM will be running on different middleware versions.

Planning data source in OBIEE 11.1.1.9

In the recent release of OBIEE 11.1.1.9 there was one interesting new feature from an EPM perspective.

OBIEE at last supports planning as a data source, it has been a long time coming as other EPM data sources such as Essbase and HFM have been supported in OBIEE as a data source for a long time.

The ability to use a planning data source compared to an essbase source should give the additional reporting features such as Smart Lists, Text, Date type measures and cell comments.

So I thought I would go through having a look at getting up and running with a planning data source as to be honest the documentation seems a little light.

The documentation currently does suggest the following:

The Hyperion Planning Server must be version 11.1.2.4 or above.

This is actually incorrect as if you look in the issues and workarounds documentation it states:

The section "Importing Metadata From Hyperion Planning Data Sources" contains the following requirements:

• Hyperion Planning Server must be version 11.1.2.4 or above.
• The servers that run Hyperion Planning 11.1.2.4 or above must be licensed for Oracle Enterprise Performance Management Financial Planning Analytics.

These statements are incorrect and must be ignored.

I am sure the documentation will be updated in the future to remove these statements but unfortunately it doesn't say which versions of planning are actually supported, I did check the OBIEE support matrix and it does not yet contain any reference to the planning data source.

I will be using Planning 11.1.2.4 so there should be no problem there so let’s get on with it.

The first step is to import the Planning metadata from within the OBIEE Admin tool.

The connection type to select is “Hyperion ADM”, ADM stands for Analytic Data Model and has been around in the hyperion world for an age and is mainly used by Financial Reporting, it is basically a set a Java classes that allow a standardised way of querying of data sources such as Essbase, Planning and HFM.

HFM also takes on board the ADM functionality when added as a data source in OBIEE.

The URL format to connect to planning is supposedly:

adm:thin:com.hyperion.ap.hsp.HspAdmDriver:<Server>:<Port>:<Application>

I say supposedly as not everything is as it seems which you will find out shortly.

I am going to be using the Vision sample application and going via the web server to connect to planning though it is certainly possible to go direct to planning on the default 8300 port.

Using the suggested URL format is where the fun and games begin.

Not a great start to be hit with an error, if you look closer at the error it is generating a http URL to connect to planning but is ignoring the port which wouldn't be a problem if the web server is running on port 80.

I tested connecting by removing the port and the application name.

This generated a different error but provides further information to the format required to connect. 

The syntax should be:

adm:[thin|native|corba]:<driver>:<server>:<application>

So you can clearly see there is no mention of port which I admit did through me for a while but I then thought what happens if I just encode the colon between the server name and port.

The encoded format for a colon is %3A so this means the URL would be:

adm:thin:com.hyperion.ap.hsp.HspAdmDriver:<SERVERNAME>%3A<PORT>:<APPLICATION>

The new format brings much more success and connects to the planning server which then allows to select which plan types to be used when importing the metadata.

After stumbling with this issue I subsequently found that in the OBIEE issues and workarounds documentation there is reference to this problem which means at least the main documentation will be updated in the future:

In the section "Importing Metadata from Multidimensional Data Sources," Table 5-1 Multidimensional Connection Options, the following additional information is available for the Hyperion ADM connection type:

For Hyperion Planning using the ADM thin client driver, include the driver and the application name (cube name), in the following format:

adm:thin:com.hyperion.ap.hsp.HspAdmDriver:[server]%3A[port]:[application_name]

On a side note if you do have technical issues with the planning ADM drivers they can be found in the following locations in the OBIEE deployment.

Currently the ADM version in OBIEE 11.1.1.9 matches EPM 11.1.2.4

Anyway, once the metadata has been imported into the physical layer it is broken down by both measures and dimensions.

There are multiple value columns to support different account member data types such as Text and Date.

All Hyperion Planning dimensions are modelled as parent-child hierarchies in the Physical layer. Shared members, alternate hierarchies, and unbalanced hierarchies are supported.

Each dimension has a set of properties which are available as columns, if you are not clear on what the properties represent then select view data on the dimension.

Among the many properties there is also sort order which should allow the dimension members to be displayed in the same order in a report as set within the planning application.

By default the connection pool to planning will be using a Shared Logon which means all reports will be connecting to planning using this account.

I will get on to using SSO later on in this post.

I am not going to go into more detail with the admin tool and simply drag the plan type over to the BMM and Presentation layers.

Once saved and OBIEE restarted the plan type will be available in the subject areas ready to start creating reports.

One thing that drove me crazy when attempting to build some simple reports against planning was the caching as the BI Server cache was enabled, once this was disabled it made life a little less frustrating.

I am far from ever being a report developer but even with my simplistic mind it didn't take too long to knock up a basic report.

One thing to watch out for when using a planning ADM data source say over say an essbase data source is the speed of retrieval, while it seemed pretty snappy for simple reports I am not sure how it will handle with complex reports.

Another area which is not the best with ADM is the diagnostic logging, if you do get hit issues then it will be difficult to find some meaningful information in the logs on both the EPM and OBIEE sides.

In the essbase application log you won’t get much more than the time it took to execute the retrieval, unlike when using essbase as a data source where you can enable tracing to view the MDX that is being run.

Local/Vision/Plan1/admin@Native Directory/5272/Info(1020055)
Spreadsheet Extractor Elapsed Time : [0.828] seconds

When designing reports and selecting members in OBIEE a flat list is returned which is defined by the sort order, if you have a large hierarchy then the search is your friend, below is an example of Financial Reporting using a planning data source and then on the right the equivalent in OBIEE.

I was interested in whether attribute dimensions would be available because with Financial Reporting and planning this has never been possible, I didn’t expect it to work with OBIEE as it also using the same ADM driver and to be honest I hit problems when the planning application contained attribute dimensions.

The attribute dimensions were imported as metadata but when viewing members for many of the dimensions all that was returned was the attribute members and not the actual members, no reports could be created or run because of this problem and once I deleted the attribute dimension from planning everything started to work again in OBIEE.

I have not had a chance to look at it in more detail but it felt like a bug.

I also wondered whether it would be possible to use substitution variables in a report as it is certainly possible in Financial Reporting which is uses the ADM drivers, I tested using a substitution variable directly in a filter.

Unfortunately it doesn't look it is possible to directly use the variable and I tried a number of different combinations but they all generated ADM query errors.

Not to worry as there is an alternative method which can be used by adding a connection to essbase in the RPD.

Once added if you look at the variable manager in the Admin tool a new initialisation block is automatically added.

If you view the property of the initialization block you will be able to see all the substitution variables that have been brought through from essbase.

I am going to add the “CurVersion” substitution variable which has a value of “Working” to the analysis I created earlier.

The filter for version was edited and a new repository variable added.

The format for the variable is:
<SERVER>:<APPLICATION>:<VARIABLE>

Now the analysis is using the substitution variable to return the version member “Working”

Earlier I mentioned that in the connection pool Shared logon is selected by default, this is fine if you are not interested in the reports taking on the users access permissions that are defined within the planning application.

If you are then you make use of the single sign on property.

In order for this to work then there are some configuration changes that are required which I covered in previous posts.

It is possible to have single sign back to planning for a user when they log directly into OBIEE or you can have the option of integrating OBIEE into workspace and sharing a single SSO token.

Once SSO enabled is enabled then users should only see members which they have access to, in the below example a user is running the same example report I created but does not have access to the “Return of Equity” member.

This also demonstrates that the ADM functionality is honouring the permissions applied in the planning application.

If the integration between OBIEE and Workspace has been configured then a provisioned user will see addition OBIEE menu options available in workspace.

The user will then be able to access OBIEE without the requirement of having to log in again.

In the above example you can also see that Smart Lists, Text Members and Cell Text can easily be displayed, I saw a slight issue where the cell text would also return html code for a line break which might be a bug.

Well I am going to leave it there for now as putting these posts together takes a large amount of my own time, hopefully this overview has been useful.