Sunday 18 September 2011

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!!!!

Friday 16 September 2011

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"