Loading to EPMA planning applications using interface tables – Part 2

In the first part I went through an introduction to what this series is about and covered getting the core planning application created in EPMA.

In this part I want to look at interface tables and end up with them built ready to load metadata into.

Interface tables are basically just a set of relational staging area tables that are populated before pushing into EPMA and these can be populated from any designated source, the ultimate end game in this series of blogs is to use ODI to populate the tables and then execute the process of moving them into EPMA.

The first step that needs to be undertaken is to create the interface data source and this is done in the EPM system configurator, I am not sure why this functionality is not yet available through workspace though I am sure it will be there one day.

Setting up the interface data source is basically configuring the connection to the RDBMS where the tables will be held, you will need to have created a schema/database first.

Start up the EPM System Configurator, uncheck all, expand Foundation > Performance Management Architect and select “Configure Interface Data Source”

As this is the first time configuration “Create a New Datasource Link” is selected.

Select the RDBMS type that is going to be used for the interface tables.

A name for the data source is entered and this name that will be used in EPMA.
The connection details to database are entered.

Now there is the option to “Create interface tables”, I have selected this option and what it does is create a set of sample interface tables.

Once the interface tables have been created this is where the fear can set in.

As you can see a hell of a lot of tables have been created and you start to wonder whether this interface table route is going to be worth it, if you select one of the tables and view the amount of columns the fear gets worse but in the words of the great The Hitchhiker's Guide to the Galaxy “Don’t Panic!”

These are a set of sample tables and are not required, the columns in the tables relate to all the possible properties across Essbase, Planning and HFM.

The fact that I am initially only looking at planning and the approach I am taking is to only load metadata to dimensions which have been already created plus associations applied then honestly it is not as bad as it first looks.

The set of tables that are of more value are prefixed with IM_ which are not in the list above.

The rest of the tables that will be required are related to the metadata to be loaded for each dimension which I am going to manually create and will go through shortly.

IM_LOAD_INFO

This table basically allows grouping of metadata to be loaded, when importing dimensions in EPMA there is an option to select the interface load ID, the load ID is a numerical value.

Each dimension hierarchy table (not created yet) will have a column called LOADID that will relate back to the IM_LOAD_INFO table, it will become much clearer as we progress.

I have manually added a record to the table, it is not actually that important that the table keeps getting updated it could easily be set up once and then left alone.

I_LOAD_ID – I have started out with the value of 1

C_SOURCE_SYSTEM – Anything can be entered here but as I am initially going to be loading from files I have just set it to “FLAT_FILE”, it could be there are multiple metadata loads and you could have load ID 1 loading from flat files and Load ID 2 loading from a different source such as a warehouse.

C_USER_LAST_UPDATED – Once again anything can go here it all depends if you want to keep the user details.

D_DATE_LAST_UPDATED – Timestamp field.

C_LAST_UPDATE_LOGIN – Again anything can go here

IM_DIMENSION_ASSOCIATION – I am not going to be using this table as the dimension associations have already been applied, I may cover this at a later stage.

 
IM_DIMENSION

This table holds the information about the dimensions that are going to be loaded.

I_LOAD_ID – The load ID links back to the previous table IM_LOAD_INFO, so a corresponding value would be entered for the interface load.

C_DIMENSION_NAME – This is the name of the dimension in EPMA that is going to be loaded to.

C_DIMENSION_CLASS_NAME – This is the dimension type e.g. Account, Entity, Generic.

C_MEMBER_TABLE_NAME – This is the name of the table that will hold the member information. I am not going to be using this column as it can be all covered in the next column.

C_HIERARCHY_TABLE_NAME – This is the name of the table which will hold all the hierarchy and member information.

C_PROPERTY_ARRAY_TABLE_NAME – This is the name of the table which contains property information. I am not going to be using this column.

C_DIM_PROPERTY_TABLE_NAME – This is the name of the table containing all the dimension property information. I am not going to be using this column.

So out of all the columns I only need to populate four of them.

I have set the I_LOAD_ID to 1 to match what was set in the IM_LOAD_INFO table, so if a dimension import from interface tables is executed in EPMA against load ID 1 the required dimensions to be loaded and the tables holding the information are known.

For the records in column C_HIERARCHY_TABLE_NAME I have entered table names that will hold all the hierarchy/member properties, I am going to go through the process of creating them now.

It is possible to look at the HS_<DIMNAME>_HIERARCHY AND HS_<DIMNAME>_MEMBER sample tables and pick out the columns relating to Planning and then create a table based on them.

To make it easier I have gone through each dimension that I will be loading metadata to and generated the full list of columns that can be populated, many of them don’t have to be populated just like when using other methods to load metadata to a planning application

VERSION  

ENTITY

SCENARIO

ACCOUNT

SEGMENTS (custom dimension = Generic dimension type in EPMA terms)

The columns in each of the tables do not include any attribute related information yet, I will cover attributes and UDAs at a later stage.

In the next part I was going to look at introducing ODI to populate the interface tables but I think it is worth covering off import profiles first.

Loading to EPMA planning applications using interface tables - Part 1

In the mass of blogs I have written about using ODI with the Hyperion adaptors there is one area I have not touched upon and that is EPMA interface tables, it is true there are no knowledge modules available to make life easier when loading to EPMA type applications but by using interface tables it certainly is achievable.

In the past I have tried to steer clear of this area due to the numerous problems with EPMA but overtime many of the major flaws have been addressed and I thought it was about time to cover off ODI and interface tables, I will be perfectly honest and say using ODI with classic planning applications is still definitely my preferred option and it offers much more flexibility.

In this series of blogs I am going to basically try and replicate the sample planning application and use interface tables to populate metadata for a number of the dimensions, I am going to try and approach it from the angle of being used to loading to planning applications with the outline loader or ODI methods to make the transition over to EPMA a little bit smoother.  It is not going to be all about ODI in fact you could substitute the ODI part for another tool of your choice.

There are a few important points to highlight before I start.

•    It is going to be based on EPM 11.1.2.1, the concept should be similar for earlier versions but I can’t guarantee it will be exactly the same.

•    The version of ODI being used will be 11.1.1.5 but everything covered will be valid for earlier versions like 10g.

•    This is not a guide to EPMA and there will be an assumption you have used EPMA before or have a basic understanding of it but I will try and explain as much as possible as I go along.

•    There is going to be assumption that you are familiar with the basics of ODI.

My approach is going to be that the planning application is going to be built first and the dimensions and associations created, this is the same concept that would be undertaken if using the classic planning method as the core application would be available before loading to it. Using this approach I feel that it is much easier to grasp the concept of what is required to use interface tables.

I am not sure how many parts there will be to this blog as I am going to try and spread it out and not cram too much into each session, in this first part I am really just going to get the basic core application built in EPMA.

So to start with I am going to create the applications dimensions in the shared library, yes this is a manual process but it only needs to be done once and really does not take much time at all.

Remember it is going to be based on the sample planning application so the dimensions created will be Account, Year, Period, Entity, Currency, Version, Scenario, Segments, Alias.

I have suffixed each of the dimensions in the shared library with “_Shared”, just to point out that this is not a requirement it was just my way of highlighting they are shared dimensions. The dimensions have been simply created using File > New > Dimension and then entering the dimension name and type. 

Dimension        Type
Period_Shared        Period
Currency_Shared    Currency
Account_Shared    Account
Year_Shared        Year
Entity_Shared        Entity
Version_Shared        Version
Scenario_Shared    Scenario
Segments_Shared    Generic
Alias_Shared        Alias

I have cheated a little and manually populated the Period,Currency,Year and Alias dimensions. It is certainly possible to use interface tables to perform this step but due to the approach I am taking I need them to be available to be able to create the core sample application, also these dimensions are pretty much remain static.
The rest of the dimensions will be populated using interface tables.

The only additional steps was to create the associations (right click a dimension and select create association) for the currency dimension and between alias and all the dimensions

Hopefully this should all make sense as I did point out there was going to be an assumption of a basic understanding of EPMA.

On to creating the application, first of all make sure you have a relational schema/database created to hold the planning application.

File > New > Application

Basically I have replicated all the settings of the default sample application

All the dimensions from the Shared Library are associated with the dimensions in the application.

The properties of the sample application were replicated and then the application validated, any errors can easily to be resolved.

As “Deploy when finished” was enabled the Deploy window will display, if no Data Source has been created it can be generated at this point.

The application has been deployed successfully so in the next part I can move on to looking at interface tables.

Get up and running with Smart View 11.1.2.1.102

There has been a flurry of activity on various blogs around the new patch release of Smart View 11.1.2.1.102 as at last it is starting to mirror the essbase add-in and apparently provide much more, seeing as this is the hot topic at the moment I thought I would jump  on the bangwagon and go through the steps to get you up and running with the latest version.

It is not just a case of updating Smart View as the essbase server and APS need to be patched to 11.1.2.1.102 be able to use this new functionality.

The readme for the patches do explain the process to follow but there are a few errors  and seeing as I am going to be carrying out the process I thought I would share it, whether it is any use to anybody who knows at least you get some nice screenshots to look at.

The prerequistite to following these steps are that you are lucky enough to be currently running 11.1.2.1 and you do want to sample the new Smart View functionality.

Log into My Oracle Support

First to download the essbase server patch.
Type 11823697 in the search box and then select the patch

From the Platform dropdown select the OS you will be installing the patch on and then click download.

The zip file will be in the following format  p<patch_id>_<coreversion>_<Plattform>.zip
e.g. p11823697_111210_MSWIN-x86-64.zip

Repeat the process for the APS patch - 11823281

Finally repeat the process for the SmartView patch – 12968304

There is a newer version of Smart View available - 11.1.1.2.103 - patch - 13502314

I am going to be patching on a windows OS but the process is pretty much the same for unix/linux just a slight variation in the command line syntax.

Patching is done by using Oracles patching utility OPatch, this is installed by default in <MIDDLEWARE_HOME>/EPMSystem11R1/OPatch

I will be patching the essbase server first.

Extract the downloaded patch to the above directory on the essbase server

You should end up with a directory created to match the patch ID.

Before starting the patch update make sure you stop the essbase server and then it is worthwhile backing up the following essbase directory -
<MIDDLEWARE_HOME>\EPMSystem11R1\products\Essbase\EssbaseServer

OPatch will basically copy a new set of files from within the patch directory and replace the existing ones in the essbase bin, java and localized directories.

OPatch is initiated from command line and the format for windows is

opatch.bat apply <MIDDLEWARE_HOME>\EPMSystem11R1\Opatch\<PATCH ID> -oh <MIDDLEWARE_HOME>\EPMSystem11R1 -jre <MIDDLEWARE_HOME>\jdk160_21

Open a command prompt, change directory to the OPatch directory and run the above command (update <MIDDLEWARE_HOME> to match the environment and <PATCH ID> to 11823697).

For example
E:
cd E:\Oracle\Middleware\EPMSystem11R1\OPatch
opatch.bat apply E:\Oracle\Middleware\EPMSystem11R1\OPatch\11823697 -oh E:\Oracle\Middleware\EPMSystem11R1 -jre E:\Oracle\Middleware\jdk160_21

Executing the command should output something like the following

And then list all the files that are being copied, I am not going to screenshot all the lines.

The important information is at the end of the output

If you see the above in the command window then all is good and the patching is done.

To view which EPM patches have been applied on the server through OPatch you can use the following command.

opatch.bat lsinventory -oh <MIDDLEWARE_HOME>  -jdk <MIDDLEWARE_HOME>\ jdk160_21

Once the patching is complete start up essbase.

Open a Maxl session and you should the patch .102 in ESB11.1.2.1.102B147

Login to make sure all is good.

Now that essbase is done you can move on to APS, the process for patching is pretty much the same so I won’t go into as much detail.

It is worth backing up <MIDDLEWARE_HOME>\EPMSystem11R1\products\Essbase\aps

Stop the APS service, you may also need to stop other services depending on what other EPM products are deployed on the APS server.

Extract the downloaded patch to the Opatch directory on the server hosting APS

The patch id for the extracted directory is incorrect as it should be 11823281 and not 194, rename the directory to 11823281.

Once again the OPatch utility is executed again following the same syntax logic as with the essbase patch.

The patching is complete and the APS service can be started up again.

Now I am not going to go through the step to install SmartView directly on a client as it involves extracting and running an executable and then following on the screen information.

If you are running on windows 7 please make sure you run the SmartView.exe as an administrator.

If you want the new version of Smart View to be available from workspace then extract SmartView.exe and version.xml from to the patch to <MIDDLEWARE_HOME>\EPMSystem11R1\common\epmstatic\wspace\SmartView on the server hosting workspace

Extract the online documents from smartview-user.zip to <MIDDLEWARE_HOME>\EPMSystem11R1\common\epmstatic\smartview\docs\<language>
e.g. c:\Oracle\Middleware\EPMSystem11R1\common\epmstatic\smartview\docs\en

The new version should be available from workspace.


If you want to be able auto-deploy the latest version of Smart View when connecting from an existing version of Smart View then

edit essbase.properties on the APS server in <MIDDLEWARE_HOME>\user_projects\epmsystem1\aps\bin

Update
smartview.webservice.autodeploy.force=false
smartview.webservice.autodeploy.warn=false

To
smartview.webservice.autodeploy.force=true
smartview.webservice.autodeploy.warn=true

Save the file and then restart the APS web application.

Connect in excel  and the new client version available window should pop up, click “Click here to Install”, choose run, close all MS Office software and then follow the on screen instructions.

You should then be ready to use the new functionality; the details on the new functionality can be found within the patch in the document sv_new_features.pdf or have a read of Glenn’s excellent article or the Oracle by Example on the new features.

Go forth and enjoy!!!!

ODI Series – Issues with 11.1.1.5 and the Hyperion knowledge modules

Recently on the ODI forum an issue was brought to my attention when using 11.1.1.5, Hyperion planning and the staging area set to the memory engine. Now I have used ODI 11.1.1.3 with the Hyperion knowledge modules and not really experienced any major issues but I have not had the opportunity to test them out on 11.1.1.5

In the support matrix for 11.1.1.5 it says the modules are certified for 11.1.1.3+

Considering most of the technologies say 11.1.1.3+ I assuming that means 11.1.1.5 should be supported for the Hyperion set.

The issue with loading planning metadata using the memory engine as the staging area can easily be replicated.

A simple interface is created loading one record from a flat file source a dimension in planning, it doesn’t really matter which dimension it is as issue occurs across them all.
The equivalent interface definitely works on 11.1.1.3

The staging area is set to use the Memory Engine.

The error generated is

com.hyperion.odi.planning.ODIPlanningException: com.hyperion.odi.planning.ODIPlanningException: The source result set contains column [C1_ACCOUNT] which has no corresponding column on the planning side.

For some reason "C1_ACCOUNT" is being passed to planning instead of "Account"

The code generated is same as on previous versions and querying the memory engine seems to return the correct results.

I think the issue does lie with the memory engine as changing the staging area to another rdbms technology such as Oracle allows for the successful execution of the interface.

So there is a workaround to the issue but if you are considering moving to 11.1.1.5 and currently use the memory engine in planning interfaces it is one to watch out for until a fix arrives.

While I had the chance I thought I would just test out a metadata load to essbase to see if there were any issues.

Once again a simple interface was created to load one record of metadata to an essbase database; the interface was tested on 11.1.1.3 and then recreated using 11.1.1.5

The only difference with 11.1.1.5 is it looks to includes patches that I wrote about here for 10g which provide an extra column for sortid.

Executing the interface on 11.1.1.5 generates and a Java related error and causes the interface to fail.

I thought maybe it was down the ODI installation so I tested out on a different 11.1.1.5 install and the same problem exists, so either I am really unlucky which could easily be the case or there is definitely a problem (if anybody else has tried this and doesn’t see the issue then let me know)

It is also worth pointing out this issue is only with loading metadata, loading data and extracting metadata still works without any issues.

As the error points towards to an issue with java files that the modules use I thought I would look into replacing them with ones from another version.

I decided to go with ones from 10.1.3.6.8 as these contain the fixes that include the sort id and are newer than ones in the 11.1.1.3 version.

The files replaced were odihapp_common.jar and odihapp_essbase.jar though it should be possible just to replace odihapp_essbase.jar.

If you look at the file size the 10.1.3.6.8 ones are bigger so I assuming contains additional code.

After replacing the jar files the same interface executes without any issues so it does look like a problem with the java files released with 11.1.1.5

Also make sure you import the KM - "LKM Hyperion Essbase METADATA to SQL" from the 10.1.3.6.8 release as it contains required extra code otherwise you will receive an error while extracting metadata from essbase.

I know this is not the official way of going about things but at least the essbase interfaces successfully complete and is a workaround if you are also experiencing the same issue until an official fix from Oracle comes along.

Update: If you experience the above issues then have a read of the following in Oracle support:

Unable to load Essbase Metadata using Oracle Data Integrator 11.1.1.5 (Doc ID 1437498.1)

Patch 12905298: INTERFACE FAILS TO INTEGRATE ON STEP "LOAD DATA INTO PLANNING"