my little epm

Essbase ASO reporting issue with SmartView

I haven't posted anything for almost a year because my projects shifted from Oracle EPM to Microsoft BI and so I had nothing to write about in this blog, but recently I came back to the world of Essbase and SmartView for the sake of a small but interesting project. To cut the long story short, I think I've bumped into a nasty Essbase bug.

You may have already known that SmartView uses MDX queries (when reports are assembled with Query Designer or Smart Query) on Essbase cubes that you can trace with the TRACE_MDX option since 11.1.2.2, if you haven't then see the details here.

Guess the difference between these traces:

===============================================================

Following MDX query executed at Sat Jul 27 15:32:41 2013

===============================================================

SELECT NON EMPTY

{ CROSSJOIN( { [Original Price] , [Price Paid] , [Returns] , [Items per Package] , [No. of Packages] } , CROSSJOIN( {(LEAVES([Geography]))} , CROSSJOIN( { [Under 20,000] } , CROSSJOIN( {(LEAVES([Payment Type]))} , {(LEAVES([Age]))} ) ) ) ) } PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME] ,[GEN_NUMBER],[LEVEL_NUMBER] ON ROWS,

{ {DESCENDANTS([MTD],[MTD].DIMENSION.LEVELS(0))} } PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME],[GEN_NUMBER],[LEVEL_NUMBER] ON COLUMNS

from [asosamp].sample

WHERE {( [Digital Cameras] , [No Promotion] , [004118] , [Sale] , [Current Year] )} PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME]

=== MDX Query Elapsed Time : [12.158] seconds ===================

===============================================================

Following MDX query executed at Sat Jul 27 15:33:09 2013

===============================================================

SELECT NON EMPTY

{ {DESCENDANTS([MTD],[MTD].DIMENSION.LEVELS(0))} } PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME],[GEN_NUMBER],[LEVEL_NUMBER] ON COLUMNS

from [asosamp].sample

WHERE {( [Digital Cameras] , [No Promotion] , [004118] , [Sale] , [Current Year] )} PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME]

=== MDX Query Elapsed Time : [0.001] seconds ===================

===============================================================

Following MDX query executed at Sat Jul 27 15:33:30 2013

===============================================================

SELECT NON EMPTY

{ {DESCENDANTS([MTD],[MTD].DIMENSION.LEVELS(0))} } PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME],[GEN_NUMBER],[LEVEL_NUMBER] ON COLUMNS

from [asosamp].sample

WHERE {( [Digital Cameras] , [No Promotion] , [004118] , [Sale] , [Current Year] )} PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME]

=== MDX Query Elapsed Time : [12.465] seconds ===================

===============================================================

Following MDX query executed at Sat Jul 27 15:33:36 2013

===============================================================

SELECT NON EMPTY

{ {DESCENDANTS([MTD],[MTD].DIMENSION.LEVELS(0))} } PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME],[GEN_NUMBER],[LEVEL_NUMBER] ON COLUMNS

from [asosamp].sample

WHERE {( [Digital Cameras] , [No Promotion] , [004118] , [Sale] , [Current Year] )} PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME]

=== MDX Query Elapsed Time : [0.001] seconds ===================

Let me point out that all of them are the same but it took 12 000 times longer to execute the 1st and the 3rd than the other 2.

Let me also point out that the queries contain stored leaf members only so there is no aggregation whose performance could be different using different formulas or cache settings or aggregate views. In fact the 4 query were fired in a short period of time and the result of them were an empty set since the built in ASOSample.Sample application contains no sample data for the particular part of the cube.

Then what could cause the 12 000 times longer execution?

Well the 1st and the 3rd was started from SmartView while the 2nd and the 4th were from EAS Console. It makes even weird that it does not matter how many times I run the test I always got similar results.

Ok you could say that the connection that SmartView and EAS Console use to Essbase server are different and so can be the reason for the discrepancy. Well SmartView and EAS console was running on the same client desktop and since the result set is empty (so the amount of data transferred back to the clients were almost nothing) it could not result such a big difference in my opinion.

If I include more leaf members in the query the difference gets even worse (while the result set remains empty).

I've tested it on 11.1.2.2 and 11.1.2.3. Both versions behaved the same way, while 11.1.2.1 crashed on the query...

I simply can't explain so raised an SR to Oracle...

UPDATE: Oracle has accepted this issue as a bug.

How to get HPCM 11.1.2.2 WebServices API to work? How can wsclient be configured to automate HPCM tasks? This post would like to add some details to the EPM 11.1.2.2 documentation to make configuration of wsclient a bit easier.

First of all, lets try to follow the docs and see what happens...

Take a look on the Prerequisites of HPCM 11.1.2.2 External Automation Processes Guide, it says that three things needs to be done before we can use HPCM WebServices. All of them can be found in the Profitability and Cost Management Postconfiguration Tasks, in fact only the last point is relevant here which is about Configuring Oracle Web Services Manager for EPM System Products.

Basically the steps are:

We need to set up a schema for Metadata Services using RCU as it is described in Creating Infrastructure Schemas Using Repository Creation Utility
Configure Oracle Web Services Manager, which includes steps extending our EPM domain with Oracle WSM Policy Manager and setting up JDBC Data Sources pointing to the previously created schema. Details can be found in Configuring Oracle Web Services Manager.
Set up a keystore, what is to say that we need to cerate a keystore and set up our domain to use it. As it is shown in Setting Up the Keystore for Message Protection

Let's do these steps quick, and follow with wsclient configuration that can be found in Using the Profitability and Cost Management Sample Client File.

Setting up a schema for Metadata Services

RCU can be download from Oracle Software Delivery Cloud, I'm using the 11.1.1.6.0 version:

Start with rcuHome/BIN/rcu.bat and follow these steps:

Configuring Oracle Web Services Manager

The Weblogic domain configuration wizard can be started with %MIDDLEWARE_HOME%\Middleware\wlserver_10.3\common\bin\config.exe or from the Start menu:

The steps are the following:

I believe it is not necessary to deploy the wsm-pm component or the mds-owsm datasource to the AdminServer (because HPCM WebServices will run on EPMServer) so we can carry on without selecting Deployments and Services:

Creating a new keystore

Creating a Java keystore that stores a geneated keypair is easy just run the following command:

keytool -genkeypair -keyalg RSA -alias EPMKey -keypass KeyPassword1 -keystore EPMKeystore.jks -validity 3600 -storepass StorePassword1

Basically this command creates an EPMKeystore.jks file that stores an RSA keypair and a self-signed certificate which can be refered to by the EPMKey alias. The certificate contains the public key of the keypair, valid for 3600 days. The private key of the keypair is protected by the KeyPassword1 password. And finally, the entire keystore can be used only if you know the password for it, which is in this case StorePassword1.

The keystore should be placed to %WEBLOGIC_DOMAIN_HOME%/domains/<EPM domain>/config/fmwconfig folder, so the command to use is:

Setting up EPM domain to use the keystore

Start up your AdminServer and log into Enterprise Manager (http://<AdminServer>:7001/em) and follow these steps:

Use the StorePassword1 for Password, KeyPassword1 for Signiture and Crypt Password. Finally, logout and restart AdminServer to take the changes into account.

Testing HPCM WebServices

Start up your EPM system and test the vailability of HPCM Web Services by navigating to the

http://<EPMServer>:19000/profitability/ProfitabilityService?WSDL address. You should see the WSDL file like this:

Setting up wsclient

Let's carry on with the client configuration steps. HPCM wsclient is a simple Java program that uses HPCM WebServices to carry out different tasks in HPCM. An unconfigured copy can be found here: %EPM_ORACLE_HOME%/products/Profitability/samples/wsclient. Our task is now to copy it to a new location and follow the guide to make it work.

I'm using the %EPM_ORACLE_HOME%/products/Profitability/wsclient folder.
We need to copy jps-config.xml, our keystore (EPMKeystore.jks) and cwallet.sso from the %WEBLOGIC_DOMAIN_HOME%/domains/<EPM domain>/config/fmwconfig folder to the new location:

Some modification of hpm_ws_client.properties is necessary. The jps.config.file key should point to the new copy of your keystore file (D:/Oracle/Middleware/EPMSystem11R1/products/Profitability/wsclient/jps-config.xml). The wss.recipient.key.alias key should contain the alias of the our keypair in the keystore (EPMKey). The hpcm.wsdl.url should point to the WSDL URL that we previously tested (http://<EPMServer>:19000/profitability/ProfitabilityService?WSDL). In my case the file looks like this:

If you don't have JAVA_HOME or MIDDLEWARE_HOME environment variables configured then you have to modify hpm_ws_client.bat (or hpm_ws_client.sh) like this:

What's more, if you are running wsclient on different machine than the EPM server then you may not have MIDDLEWARE_HOME at all, in this case you can obtain JDeveloper from Oracle Software Delivery Cloud which creates one for you.

Let's do some tests

Now that we are ready with configuration, let's try to test wsclient. All of the available functions can be listed by issuing the hpm_ws_client.bat -help command:

Now we should start with something simple like listing the HPCM applications, so enter hpcm_ws_client.bat getApplications

Obviously something went wrong, is says that some kind of keystore service instance reference is missing from the JPS context. Strange error message without any details. What's more, if you tried to google for it I bet you wouldn't find any useful piece of information about what can cause it. Or just I'm too fool to find it... :) But, if you search for the word "context" in all files that reside in wsclient folder then you can find it in 2 files:

One of them is the jps-config.xml that we copied here. If you take a look on the contents of the file you can see that there are <serviceInstanceRef> tags inside <jpsContext>. Looks promising. Is something missing from there?

At first sight, it seems Ok to me, because there a <serviceInstanceRef ref="keystore.inst.0"/> tag that refers to a <serviceInstance name="keystore.inst.0" provider="keystore.provider" location="./EPMKeyStore.jks"> tag.

Let's search for other occurrences of jps-config.xml under MIDLEWARE_HOME, it looks like there is a template in the %MIDLEWARE_HOME%/oracle_common/modules/oracle.jps_11.1.1/domain_config folder.

Let's compare them. There are a few differences, for example, the name of the keystore service instance differs, there is no idstore.loginmodule service instance in the template and the order of the service instances are different:

Let's try to change the order of service instances and move keystore.inst.0 to the second position.

Issue hpcm_ws_client.bat getApplications again. It seems we have a new error message.

It says wsclient can't find the epmpcm.credentials key in the credential store. This key looks familiar, right? We have it in hpm_ws_client.properties:

It also says that it found some other keys in the credential store like sign-csf-key (its associated value is EPMKey), and enc-csf-key (its value is EPMKey too) and keystore-csf-key (with keystore-csf-key value). These are also look familiar, we have set these in Enterprise Manager.

So the next step is that to add a new key (epmpcm.credentials) to our credential store (in fact it is the cwallet.sso file). Open Enterprise Manager and select Security/Credentials on your domain:

And add a new key called epmpcm.credentials to the oracle.wsm.security map. The username and password should be the name and password of an EPM user you want to use via wsclient.

If you prefer command line to Enterprise Manager, you can use the following WLST command:

connect()

createCred(map="oracle.wsm.security", key="epmpcm.credentials", user="wsclient", password="<wsclient EPM password>", desc="")

exit()

Certainly, you can use admin if you want, but I prefer creating a new user (called wsclient) and assign the necessary roles only. (Please note that EPMA Administrator role is necessary to query the available HPCM applications.):

Do not forget to recopy cwallet.sso from %WEBLOGIC_DOMAIN_HOME%/domains/<EPM domain>/config/fmwconfig to wsclient folder before retesting wsclient:

We have a new error message that refers to authentication failure. That is because WebService (Weblogic) and EPM authentication are different, we have to create a wsclient (or admin if you use admin) user in Weblogic Admin Console. Log in, select Security Realms and myrealm:

Click on Users and Groups and then new:

Fill the Name and Password fields and click Ok:

Nothing else left than retesting the client, and voila it works:

Let's try something else like getting the POV of the sample application:

Seems to be working fine.

Next time I will try to use HPCM WebServices via ODI.

my little epm

Sunday, July 28, 2013

Essbase ASO reporting issue with SmartView

Sunday, August 5, 2012

Automating HPCM 11.1.2.2 tasks via wsclient

How to get HPCM 11.1.2.2 WebServices API to work? How can wsclient be configured to automate HPCM tasks? This post would like to add some details to the EPM 11.1.2.2 documentation to make configuration of wsclient a bit easier.

First of all, lets try to follow the docs and see what happens...

Setting up a schema for Metadata Services

Configuring Oracle Web Services Manager

Creating a new keystore

Setting up EPM domain to use the keystore

Testing HPCM WebServices

Setting up wsclient

Let's do some tests