Monday, 26 April 2010

EPM 11.1.2 Installation

Well it finally arrived, nobody seemed to know the exact day it would appear and there was so much speculation around when it would.
Even though Oracle doesn’t make a fanfare about when it releases software, with all the activity on the various blogs and forums it didn’t take long for the word to spread.

For some reason I had this vision that many were constantly refreshing edelivery waiting anxiously for a glimmer of excitement in their lives. I am not putting myself in that bracket even though many would try.

Anyway I am not going to blurb on about meaningless nonsense, as I am sure that will not hold your attention for long. Today I want to go through the installation of 11.1.2 and highlight any issues I come across and point out any new items that are worth giving a mention.

It is just under 2 years since I blogged about installing the first major release of 11 and taking you throughthe new features and now the time has come to return to the challenge. Please note these are just my early observations of the new release, I apologize if any of the comments are inaccurate or you disagree with them.

The first document I checked out was the support matrix to pick up any new additions, you can download the Excel document here

There are a handful of new updates that interest me.

Operating System :- Windows 2008 x86 and Windows 2008 x64 are now officially supported, this is good news as I know the question has been asked many many times when it would be supported and the rumours were true.

On Windows x64 most products are now true 64bit with the exceptions of FDM, Financial Reporting Print Server, Interactive Reporting Services, and Strategic Finance.

Staying with Microsoft, SQL Server 2008 is supported as a repository and datasource though cannot be used as repository for ERP Integrator.

Finally Java 1.5 has bitten the dust and Java 1.6 is now the standard.

Microsoft Client Side :- Windows 7, Vista SP1+ and Windows 2008 SP1+ are all now supported for 32bit

The list of Microsoft 64bit supported clients has certainly grown, as I am sure it just used to be Windows 2003, the list is now XP Professional SP2+, Vista SP1+ and Windows 7.

Browsers :- Internet Explorer 8 at last becomes supported and good news that Firefox 3.5.x is on the list though not if you use FDM.

I am not going to go through individual new product features today, but in the EPM system readme there are some new additions and changes that are worth airing.

Goodbye Tomcat and Hello Weblogic– “WebLogic Server (replaces Apache Tomcat as the embedded Java container), Oracle HTTP Server (replaces Apache Web server as the embedded Web server)” – I know weblogic is far superior on a production system though I did enjoy using Tomcat as it is a lightweight installation and is very useful when prototyping or using a VM that can’t be given many GB of memory resources.

“Most EPM System products have adopted Oracle Diagnostic Logging (ODL) as the logging mechanism. The ODL framework provides uniform support for managing log files, including log file rotation, maximum log file size, and the maximum log directory size” – On quick inspection it looks like you now can update an logging configuration xml file to define how you want to maintain logs for each of the products, something I may cover in the future.

“Oracle Configuration Manager (OCM) integrates with My Oracle Support and provides configuration information for Oracle software. It assists in the troubleshooting, maintenance, and diagnostics of your EPM System deployment.” – This seems an interesting concept and it is the first time I have come across the process. I would like to see if it is actually worthwhile and how it interfaces with Oracle Support.

A little more information on the product

Oracle Configuration Manager (OCM), which EPM System installations include in the EPM Oracle home directory, collects information about your Oracle software installation and configuration and uploads the information to My Oracle Support. The information collected by OCM speeds resolution of problems and enables My Oracle Support to tailor content for your configuration.
Product documentation can be found here

“This release is intended for new deployments only. Upgrading or migrating from previous EPM System releases is not supported. In addition, products from this release are not compatible with products and applications from previous releases.” – Sorry you are going to have to wait for a future release to upgrade your current version 11 installation. Considering the amount of core changes there are in this release would you really want to trust an upgrade in future or is it best just go down the route of a clean install?

“Relational Database as Native DirectoryReplaces OpenLDAP as the central storage for native EPM System user accounts and provisioning information. Unlike previous releases, this release supports using only a relational database as Native Directory. Native Directory is created in the database that you configure as the Shared Services database while deploying EPM System components.” - So long Openldap you crashed far too many times and had to be recovered, not sure how much it will be missed, using a relational source is definitely the logical way to go.

“The EPM System directory structure has changed. The default directories are now Middleware Home and EPM Oracle Home – The default EPM Oracle home location is MIDDLEWARE_HOME/EPMSystem11R1”– Does this mean the mention of Hyperion in the directory structure is no more, well at least directories should have been dropped.

“The new Foundation Services Web application consolidates to one JVM and includes these Web applications: Shared Services and EPM Workspace.” – Interesting Shared Services is now being integrated with workspace but does this mean if workspace crashes all authentication across the EPM system is lost or do all the products now communicate directly with the relational directory.

Ok on to the installation, as I mentioned that Windows 2008 is now supported so I am going to give it a test drive to see if everything goes smoothly. I am going to use the 32bit OS as it will be easier for demo purposes. I hope to test out the Windows 2008 64bit version in the near future.

First of all if you are considering installing many of the products on one machine make sure it is of a high end spec and has plenty of memory, this version is much more resource hungry than previous versions.

The installation process is pretty much similar to previous version 11 installs.

First make sure you have downloaded all the required files, the best place to download the full set of files is Oracle edelivery.

The foundation parts 1-4 (V20799-01,V20800-01,V20801-01,V20802-01) and the installer (V20850-01) are vital, it is also worth downloading the “additional contents file” (V20843-01) as this includes the Oracle HTTP component. (this blog uses all of the above files)
Additionally you will need the files for products you want to install.

Extract all files to the same directory, this is very important. Also make sure you have plenty of room on the drive you are installing the EPM suite to it has to be about double the size of all the extracted files, if it is not the installation will fail.

Double click installTool.cmd and this should start up the installer, it will check if the machine you are using passes the prerequisites.

The installer looks pretty much the same as the previous V11 installer.

Drive was changed to F:\ as I didn’t want to install on the C:\ drive.

You can select to install the products individually or by tier, I kept the default which is selected by tier.

I have most of the product set selected.

The above screen just highlights all the components that are going to get installed.

Click next and off the installation goes.

Notice WebLogic Application Server and Oracle HTTP server are automatically installed in this release.

I would go off and do something more interesting because depending on the number of products you are installing it can take a while, just hope on your return that all the icons are green.

That is what you want to see all green icons and the code of EPMINS-1020

In the start Menu you should hopefully see “Oracle EPM System”, “Oracle Web Tier 11g – Home1” and “Oracle Weblogic”.

You should also have a file structure similar to the one above.

On to the configuration, click the configure button (ok I clicked it and nothing happened) so I went through the start menu instead > Oracle EPM System > Foundation Services > EPM System Configurator.

Think I must of hit the first bug :- 9402134 - On the EPM System Installer summary window, clicking the Configure button does not start EPM System Configurator.

The first screen will display all the product areas that can be configured depending on what products you have installed. I never go down the route of configuring everything at once.

I have seen installations go wrong when everything is selected at once so I unchecked everything and just left Foundation just to be sure. This is just the way I like to go about it and everybody has their own preference.

This is the minimum requirement for the first configuration, there are a couple of notable additions to the foundation configuration in this version, “Configure Logical Address for Web Applications” and “Configure Oracle Configuration Manager”

As this is the first configuration “Perform 1st-time configuration of Shared Services database” should be selected.
Database connection details are then entered. I had already set up a number of schemas to be used as the EPM repositories, I know you can probably use one schema but I like to separate them out by product.

The next screen is basically just used to enter Mail server information that will be used with the EPM system.

This is one of the new additions that I highlighted earlier OCM, it should integrate with My Oracle Support and assist with troubleshooting any issues with the configuration. I am not sure how it does work yet hopefully the documentation will make it clearer.

Another new addition to the configuration, maybe too many people were sticking with the default password with previous installs.

Clicking next will configure the foundation components.

Once again all green ticks are what you are expecting.

If you check your services you should see two new services that have not been seen in previous releases.
“Oracle Process Manager” – You will get to know more about this product if you install essbase (or Oracle HTTP Server) as it used to control starting, stopping and managing failover for essbase clustering.

The other service is for “Oracle Configuration Manager” which I highlighted earlier, Oracle have decided to give it the longest service name in history.

Next I deployed the Foundation Application Server.

The default settings were kept, I just changed the admin user and supplied a password.

Default settings were kept; notice workspace is now running on the same app server instance as Shared Services.

The application server deployment was successful.

You should have a new service “Hyperion Foundation Services – Managed Server” – the service will be set to automatic, I usually change this manual and use a start up script to start the services I require. You will also notice there is no longer an OpenLdap service.

Shared services can be tested just as you would in other version 11 releases e.g. http://<foundation_server>:28080/interop/index.jsp

If shared services is up and running you are in a good position with your configuration and can carry on.

Shared services is pretty much the same as previous releases, the look has slightly changed to fit in line with Oracle Middleware products.

Next, on to configuring essbase.

Essbase Custom Configuration was selected.

You will notice that there is an option to set an “Essbase Instance Name”, this is the name that Oracle Process Manager will use to interact with essbase.

The full path to essbase has changed in this release; products now sit under <EPM_ORACLE_HOME>\user_projects\epmsystem1\..

You also have the option of setting the ARBORPATH environment variable, this is not set by default.

In previous version 11 releases this variable was only set for the administrator who installed the product, if you logged in as another user the variable would not be set and you can encounter problems when using Maxl or esscmd.

Configuration was successful.

If you look at the windows services there will not be a service there that relates to Essbase, as I said earlier “Oracle Process Manager” controls this.

If you start the service you should find that the Essbase process starts.

You can then use command line to start, stop and restart essbase for example

opmnctl startproc ias-component=EssbaseInstanceName
opmnctl restartproc ias-component=EssbaseInstanceName
opmnctl stopproc ias-component=EssbaseInstanceName

You can also start essbase using :-


This method will still use OPMN, if OPMN is not running it will first start up the OPMN processes and then start up Essbase.

If you want to find more information on OPMN go here

You don’t have to use OPMN to start essbase, I can see OPMN being more beneficial if you are using Essbase clustering.

If you don’t want to use OPMN you can start essbase by using <EPM_ORACLE_HOME>\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin\startEssbase.bat

Now I am not going to through the configuration of the rest of the products as many of the screens are quite similar and if you have installed previous releases there is nothing outstandingly different.

What I will say is that I configured “Hyperion Foundation” > “Configure Web Server” last, this is because it configures Oracle HTTP Server for the products that will be accessible on port 19000 via workspace (that is if you are using Oracle HTTP Server as the web server and the default port).

You can run the web server configuration again at any point and it should pick up any new products that have been installed.

When I was configuring the web server it did seem to freeze at 50% for an age, so I cancelled it and ran the configuration again and it picked up the configuration where it left off at 50% and completed successfully.

The Oracle HTTP Server is also controlled by OPMN and a service should have been created with a friendly looking instance name.

If you do have any issues with configuration and are wondering where the logs have moved to have a look at :-

Well I am going to leave it there for today, there are many more areas to cover but they are for another day.

Thursday, 22 April 2010

ODI Series – Extracting data from HFM

In the last blog I went through the steps to load data into a HFM application, today I am going to reverse this process and extract data to a flat file using the Knowledge Module “LKM Hyperion Financial Management Data to SQL” and “IKM SQL to File Append”
Like the previous HFM examples I am going back to basics and keeping it all very simple.

In this example I am going to extract data shown above for Account :- Equipment, Building & Maintenance.

As I created a file DataStore that matches all the columns for a HFM application in the last blog I am going to re-use it.

If you want to know how I created the DataStore just have a quick read of my previous blog.

First I right clicked and duplicated the original flat file based DataStore I used for loading data.

The DataStore just required renaming (DATAEXTRACT) and giving a new resource name, in this case ExtractData.csv

If you don’t mind your extract file template being the same this is a quick and easy way to keep reusing DataStores, if you have to have a specific output template then still just duplicate a full existing DataStore and delete the columns that you don’t require.
With the DataStore in place an interface can be created to extract to the data.

I am using the memory engine as the staging area because the complexity and size of the output is small, in any other circumstances I would use the power of RDBMS engine as the staging area technology.

The HFMData DataStore was dragged on to the source, the newly created file DataStore was dragged on to the target. As all the source and target columns matched and there are no transformations required everything was auto mapped.

In the flow area make sure “LKM Hyperion Financial Management Data to SQL” is selected, as it may not have been select automatically, now there are a number of options available for the KM.

ENTITY_FILTER—The Entity dimension members for which you are exporting data
You can specify comma-delimited entities or one entity. To specify the parent and child separate them with a period; for example, Regional.Canada. If you do not specify any entities then data for all entities will be exported.

PERIOD_FILTER—The set of Period dimension members for which you are exporting data
Specify a range of members using the ~ character between start and end period numbers for example, 1~12. If you do not specify periods, the system exports data for only the first period defined in the HFM application

SCENARIO_FILTER—The Scenario dimension members for which you are exporting data
You can specify comma-delimited Scenario members or one scenario. If you do not specify any scenarios then data for all scenarios will be exported.

ACCOUNT_FILTER—The Account dimension members for which you are exporting data
You can specify comma-delimited accounts or one account. If you do not specify any accounts the data export will be for all accounts.

VIEW_FILTER—The View dimension member for which you are exporting data
Examples are Periodic, YTD, or (default)

YEAR_FILTER—The Year dimension members for which you are exporting data
You can specify comma-delimited years or one year. If you do not specify any years then data for all years will be exported.

LOG_ENABLED - If set to Yes, logging is done during the extract process to the file specified in LOG_FILE_NAME.

LOG_FILE_NAME - The name of the file where logs are saved.

I just updated the options to match the POV of the data I want to extract. The period filter was set to 7 as the start period for the HFM application is July and I want to export data for January.

As the target is going to be a flat file I used the IKM “IKM SQL to File Append”, TRUNCATE was set to Yes so the file is generated each time the interface is executed, if I wanted to append then I would just set the option to No.

The process creates a temporary table using the memory engine, the data is exported into the temporary table based on the options in the KM, and the data is then moved from the temporary table into the flat file.

The required data from the form at the beginning of this blog has been extracted.

As there are no options in the KM to filter such areas as custom dimensions then if you have any data in these dimensions then it all will be extracted.

You can get around these by using filters on your staging area.

By dragging the “Products” column on to the source a filter is created, once it has been created you can create logic; in this example data will be filtered to only return products that equal ‘GolfClubs’.

If you look in the operator at the step where data is inserted into the file from the staging area you will see the filter in the generated SQL.

You could take this one step further and use a variable instead of hard coding a value in the filter.

An ODI variable was created to hold the value for the product filter.

You can use the variable directly in the filter, this is achieved by using #VARIABLENAME

You can also use the same concept with IKM options, say that the “Scenario” data to extract was not always constant you could create a variable to set the member(s).

You can then add this variable to the IKM options

When you use variables in a KM option you cannot just run the interface as it will fail.

To get around this you need to declare the variable first before using it and this can be done in a package.

Once you create a new package you can just drag the variable on to the diagram and declare it, then drag the interface and join the steps together, using this method will not cause the interface to fail.

Well that concludes exporting data from a HFM application, yes it is basic but it should give you a head start and soon you will be on your way to creating much more complex solutions.