EPM 11.1.2.1 compact deployment update

In a previous post I went through the steps on deploying EPM 11.1.2.1 WebLogic based web applications in compact mode.

There were a couple of questions that were left unanswered questions in the post and I have been asked a few times what the answer is to them so I thought I would write up my findings.

Now if you are not intending of deploying in compact mode or have not read my previous post on the steps then this will probably be of no interest to you so you can go and do something far more interesting instead.

On to the first question, when deploying in compact mode there are a number of dependent products that need to be installed before running the deployment, in my case the deployment failed the first time due to not having HPS (Hyperion Performance Scorecard) installed and I didn’t really have any intention on using the product but at the time I just installed it to be able to proceed with the deployment.

I said that it may be possible to edit a few XML files to get around the need to have the dependent products installed, as it was niggling at me whether it could be done I went back and had a look.

The intention was to be able to deploy without having to install HPS and here are the steps that I carried out before running the compact deployment.




Details of the required librarie, dependent products and deployment details are stored within the following file
<MIDDLEWARE_HOME>/EPMSystem11R1/common/templates/compact/epm_system_11.1.2.1.jar



The file can be opened with a compression utility like 7zip so that all the XML files within the jar can be accessed and then edited, if you are planning on following these steps I would recommend taking a backup of epm_system_11.1.2.1.jar first.



The first file that was edited was template-info.xml, this file contains all the dependent libraries and deployment information the line containing “Oracle EPM HPS libraries” was deleted and then the file was saved.



The next file config-groups.xml contains all the products that will be deployed if they are already installed, two lines relating to HPS were removed.



Another line containing “hps_datasource” was removed from the same file.



The section highlighted above was also removed from the file and then the file was saved.



Within the config directory the file config.xml was edited and the above section relating to HPS was removed.



The datasource section relating to HPS shown above was also removed and the file was saved.



Finally the file hps_datasource-jdbc.xml was deleted from the jdbc directory within the jar file.

Now the compact deployment can be executed and it should not fail if HPS has not been installed, for full details on how to deploy in compact mode go back to my previous blog.

It is possible to remove other dependent products from the deployment by using this method to try and save some extra memory.

If you have products installed that are not on the dependent product list but are in the deployment list e.g. ERPi, Disclosure Management and you don’t want them deploying using the compact method then you will either to need to uninstall them and then install them after deploying in compact mode or use the method of editing the XML files as explained in this post.

I was going to go through my second question next but unfortunately I have not got any spare time left today so I will write it up in the next couple of days and then post it.

ODI Series – Essbase related bug fixes

There have been a couple of patches released lately for ODI which address issues with the essbase adaptor and I thought it was worth going through them as I have blogged about these specific problems in the past.

• Patch 10.1.3.6.7_01 contains the following fix - 9046176 - Wrong export order of members when using LKM Hyperion Essbase Metadata to SQL.A new column called sortid is added to the all the dimensions to order the records. This column is automatically populated using a counter.
  
  
• Patch 10.1.3.6.8 contains the following fix - 8826035 - Extract member's descriptions when we have more than one alias table in Hyperion.

Both patches contain updates to the knowledge module “LKM Hyperion Essbase (Metadata) to SQL” and updates to underlying Java code.

These bug fixes have not made it to ODI 11g yet but it might be possible to use the KM and Java files from the above patches (I have not tested this theory).

I will go through the wrong export order of members issue first, if you are not familiar with extracting essbase metadata using ODI then you can read an article I wrote on how to do it here.



Let’s use the measures dimension in Sample.Basic for the example, if a simple interface is created using the “LKM Hyperion Essbase (Metadata) to SQL” to extract the measures dimension then you would expect the output order of members to reflect the order in the outline.



As you can see the order is not correct, the dimension member “Measures” is the last in the order instead of being the first.

Now what I have done in the past is use a RDBMS target and add an ID column, in Oracle the ID column can be populated using a Sequence and for SQL Server an identity column property can be used.



This produces the above output.



If you reverse the output using SQL then “Measures” is outputted first though the order still is not correct.



If you were to build an outline based on that order you would get an outline like the one displayed on the left when what you want is the order of the outline on the right, basically all the levels are in the wrong order and not just the top level.

I was hoping that the patch release was going resolve the issue fully but let me go through it and see the output.

Once the patch has been applied make sure the “LKM Hyperion Essbase (Metadata) to SQL” is replaced using the import_replace option.



If the essbase database model is reversed again an extra numeric column called sortid is available within each dimension datastore.



A new interface was created to extract the measures dimension and sortid is mapped from the source to ID in the target.

To change the order of the output then a slight change to the IKM being used is required.



In my example I am using “IKM SQL to File Append” so the KM was edited and step “Insert new rows” was updated, the following code was added to the “Command on Source” -
ORDER BY <%=snpRef.getColList("","[EXPRESSION] ","","","UD1")%> DESC

This means the output will be ordered based on UD1 in the mapping of the interface.



UD1 was selected on the sortid column so the output will be ordered by this column and the interface was executed.



In my opinion the fix does not resolve the order issue fully, it has added a numeric counter to the output but the levels are still in the wrong order.

On to the second fix available in patch 10.1.3.6.8, this addresses the issue with being able to only extract the default alias table information.

I originally blogged about this issue here and went through a workaround by customizing the LKM and updating the Java API.



The patch release adds a new option in the LKM to enter which alias table to extract from; the idea is the same as what I used in my workaround so I had a quick look at the bug on “My Oracle Support”



I had a quick peak at the Java code in the new patch release and it is the same as what I wrote, so it looks my workaround has made it into official Oracle software, not that anybody in Oracle contacted me about it :)

There are a number of other essbase/planning/HFM related bug fixes in the latest patches so if you are experiencing any issues it is always worth have a look through the Readme.