ODI - Question on loading planning Smart List data directly into essbase

I am writing this blog as there is a unresolved question on otn planning forum, it is one of those questions where it is probably easier to go through the steps than try to explain in the forum. It might also be also be useful for anybody else that has the same sort of requirement in the future.

(I would just like to point out it is possible to load smart list data through the planning IKM though this does require updating the data load driver information in planning, you can read about loading data through the planning layer here in a previous blog)

The question relates to loading data directly into essbase where the source data is a mixture of numerical values and smart list names, not something I encounter very often and I wouldn’t thought you would mix that sort of data in a warehouse but sometimes it is best not to question the logic.

Now as you are probably aware that smart lists are loaded into essbase in numerical format, when you create a smart list it displays the ID that will be used, this means the data will need to be transformed from text to numerical when it is loaded into essbase.



I am not sure what the data looks like from the original question but I just going to base it around the planning sample application again. The aim is to populate the above composite form with data that contains a mixture of numerical and smart lists values.



What you need to be aware of is the data column is non-numerical and the destination essbase data column is numerical so there will be a requirement in the integration to change data types.

The first steps are to reverse the data and essbase database, no need to cover that off as I am sure you are very familiar with it by now.

Now we need to be able to map the data with the smart list values and to do this you need to access the planning applications database tables. Smart List information is stored in two tables

HSP_ENUMERATION


This table stores the information for the smart list properties.



HSP_ENUMERATION_ENTRY


This table stores the details of the smart list entries


The column ENUMERATION_ID links the tables; both the tables will need to be reversed unless you always know the enumeration id and in that case you will just need to reverse the HSP_ENUMERATION_ENTRY table.



In this example I am loading directly into essbase but before I do this I usually create a text file or db table to load the data into, this is just because I find it easier to sense check the data that is going to be loaded first. In this case I just created a near duplicate of the source data table with the exception of having a few extra columns that are not in the source table but are in the essbase target database.



The additional columns are HSP_Rates and Currency; I also created the Data field as numeric to mirror that is of the essbase data column, this table was then reversed in ODI.

Time to create the interface, now this can be done a number of different ways. There are three source DataStores we need to use though all three can not just be dragged on to the source, this is because the join that will be generated will not give the required results. I did try to use all 3 tables joined at the source but even with changing the ordered join numbers I couldn’t get the correct SQL generated, it probably can be done but I didn’t have the time to spend on it and want to keep this as simple as possible.

Ok, I will go through one of the ways it can be done, the Data was dragged on to the source and the HSP_ENUMERATION_ENTRY DataStore



A join was created between the SL_DATA DataStore and the HSP_ENUMERATION_ENTRY DataStore.



The join in its current state is not enough because it will only return the records where the data matches against the smart list name, in the source data there is the numerical values we want to return as well.



By selecting “Left Outer Join” the join is updated to return records in the source datastore where there is no match, which is what we want to achieve.

Though this will look at all Smart List Entry records and we only require a subset, this is where the HSP_ENUMERATION DataStore comes into play, as I stated before if you just drag the DataStore on to the source and join HSP_ENUMERATION_ENTRY and HSP_ENUMERATION then the SQL generated when the interface is executed does not give the desired results.

What I did was update the expression in the join between SL_DATA and HSP_ENUMERATION



So now it will only return the ID of the records where the name of the Smart List is defined as ‘Make’.



I also updated the expression to use an ODI substitution method that returns the full table name instead of hard coding it.

<%=odiRef.getObjectName("L", "HSP_ENUMERATION", "PLANSAMPSQLSCHEMA", "D")%>

The statement is saying return the object name for HSP_ENUMERATION in the logical schema PLANSAMPSQLSCHEMA, which will return PLANSAMP.dbo.HSP_ENUMERATION.

The data check table was dragged on to the target.



The two columns that are not mapped I need to put against members “Local” for currency and “HSP_InputValue” this is the member where planning stores its inputs against for currency applications.
If it is a non-currency planning application then you don’t need to worry about these columns.



The Data mapping has to be updated because it has defaulted to retrieving all the records from the source data table and what is required is a mixture, an expression has to be created to achieve this.



This is basically saying when there is no entry id i.e. there is no match between the source data and the smart list data then just return the source data otherwise return the smart list entry id.



I used the “IKM SQL Control Append”, set “DELETE_ALL” to yes as I want to clear out the source table each time I execute the interface and turned off “Flow Control” as I am not trying to control or check the data.



Executing the interface generates the desired results as the numerical data has been loaded and the smart list data has been converted to the correct numerical id.

As the results were correct the interface was updated by dragging the essbase data Datastore on to the target, the staging area had to be updated so it was done on the SQL database side, as essbase does not have the capabilities to transform the data.



If the interface is run as it currently stands it will generate an error.



I mentioned earlier that a conversion of the data type would be required; this is because the source data column is non-numeric and the target column is numeric.



Depending on what technology you are using will depend on how you update the expression, I was using SQL server so used the CAST function; if you are using Oracle then you could use the TO_NUMBER function.



Once the interface has been executed and loaded the data into essbase you can see the desired results are available in the planning form.

In the next blog I am going to go through exactly the same example but load data through the planning layer using "IKM SQL to Planning" then it is up to you which method you decide on using.

ODI Series - Web Services Part 2

Last time I went through setting up axis2 web services with ODI and how to execute services using a SOAP utility, if you are ever considering developing your own application and there is a requirement to execute ODI scenarios then you could either do this directly by using the Java invocation API but this relies on there being availability of a tcp/ip connectivity to the ODI agent, this is not always going a viable solution so using a web service can be a more appealing option.

You can obviously handle SOAP requests with one of the multitude of programming languages out there but I am going to show how this can be done with Java, mainly because I am a convert to Java, I am trying to improve my skills in this area and it fits in well with the Oracle EPM APIs.

Firstly the standard distribution of Axis 2 is required, in the previous blog I downloaded the war file but this doesn’t contain all the required Java packages.



Once the distribution files have been downloaded and extracted then what we need is some method in which a Java API can interact with the ODIinvoke web service, luckily included with axis2 is a useful utility that converts a WSDL to Java, aptly named WSDL2Java.

The utility will create what is known as a client stub, which is basically a Java class that contains all the information to communicate with the web service and its operations.

The syntax to create the stub is :- WSDL2Java -uri <wsdl location> -p <name-of-package-to-create-stub-in> -d adb –s –o <location-of-output>

The –d adb parameter specifies to use the ADB data binding method to create the client (if you want to understand more about the different methods information is available in the axis2 documentation, it certainly goes into far more depth than I can handle today)

The –s parameter stands for synchronous method so the client will wait for a response from the web service before continuing.



Running the utility outputs three files to the directory specified and creates the package structure.



Each file represents a different type of protocol that can be used; I am going to use the SOAP1.2 just because it is the latest and probably more robust, I wouldn’t be too concerned about what is in the java files unless you are a Java guru and wish to reach enlightenment.

Now we can move on to writing some simple Java, depending on what Java IDE you are using you will need to add the axis2 jar files and include the include the generated stub.

If you followed the blog where I used the Java ODI invocation API then using this client follows pretty much the same concept.



What I am going to do first is to retrieve a list of available scenarios that can be executed.

When creating a new instance of the OdiInvokeOdiInvokeSOAP12Port_httpStub class it requires one String variable in the constructor and that is the URL of the web service that is going to be invoked.



Once you have created the instance, you can view the methods that are available.



listScenario is the method to retrieve the available scenarios though this requires an instance of ListScenarioRequest to be passed into it.

ListScenarioRequest has a method call setListScenarioRequest that requires an instance of RepositoryConnectionType3 to be passed into it.

I am not sure why it is done like this or it a fault of the conversion of the WSDL but there are three different classes of RepositoryConnectionType, to use the ListScenarioRequest you need to use 2 and 3.



RepositoryConnectionType2 has a number of methods to set items such as the ODI username/password, JDBC connection details.



RepositoryConnectionType3 you can set the work repository.



Basically what we are doing the same details as when I set up the SOAP request in the previous blog.



The only difference is that we are using the various methods of the class to populate this information.



Once all the information has been passed into the methods, the web service can be invoked and the response dealt with.



The request is sent by using the listScenario method and a response is returned with an object type of ListScenarioResponse, this contains a method to retrieve an array of Scenario Types, then all I do is iterate through each scenario type and output the Scenario name and version.



You can easily convert this to say a jsp script that displays the scenarios in a drop down box ready to be executed from a web page.



Running a scenario follows the same concept and is exactly the same as using the invocation API, to run a scenario you could put something like this together.



Combining the listing of scenarios and executing a scenario you could update the jsp script to execute a scenario based on the selection from the dropdown, I am not going to show that because I feel I am done for today and hopefully it has given you an insight into how you can work with Java and the ODI web services.

Next time I am going to take you through Data Services in ODI and go through step by step on setting it up.

ODI Series - Web Services

Today I am going to embark on going through the Web Services functionality that is available in ODI; I have seen a few posts recently on problems setting up and configuring them so I thought I would go through my usual methodology and break it into detailed bite size chunks. Yes, I know there are other explanations out there on the web but from what I have seen I don’t feel they provide enough substance and anyway I find it useful to write it all down so I can refer back to it when required.

There are three main areas in which you can use Web Services in ODI, invoking ODI scenarios/sessions over the web, invoking web services using the ODI tool – ODIInvokeWebServices and finally Data Services. Data Services enable to create a web services against existing DataStores, this is mainly going to be of a RDMS source technology, I wouldn’t of thought you could use any different type of technology but maybe I am wrong? The Data Service provides the ability to retrieve, update and delete data governed by the DataStore.

First of all it is important to install the necessary components to use Web Services. To use Web Services with ODI you will need a Java JDK, I recommended downloading and installing the latest 1.5 version. If you go down the 1.6 route you may encounter problems, I know I did and it is a known issue.

OdiInvokeWebService tool is not working properly when using Designer with Java 1.6
It is not possible to invoke a Web Service with the OdiInvokeWebService tool when using
Designer with Java 1.6.
Invoking an operation throws the following exception :
java.lang.AbstractMethodError:
org.apache.crimson.tree.XmlDocument.getXmlStandalone()Z at
com.sun.org.apache.xalan.internal.xsltc.trax.DOM2TO.setDocumentInfo(DOM2TO.jav a:373)
at com.sun.org.apache.xalan.internal.xsltc.trax.DOM2TO.parse(DOM2TO.java:127)
at com.sun.org.apache.xalan.internal.xsltc.trax.DOM2TO.parse(DOM2TO.java:94....

I actually encountered the issue when trying to create a web service from a DataStore. The JDK version I installed was 1_5_0_18.



The environment variable ODI_JAVA_HOME was updated to point to the JDK installation, if you are using an agent service you will need to restart it to pick up the JDK.

To run web services an application server is required plus Axis2 which is Apaches web services engine.
The documentation recommends “J2EE 1.4 compliant application server, such as OC4J 10.1.3 and above.”
I already had Apache Tomcat 6.0.16 installed so will go ahead with using that as the app server.

It is important that you download the correct version of Axis2 or you will encounter issues, Version 1.2 seems to be the best option anything later and you will hit errors.



If you are just going to use the web services standard functionality then the WAR file will suffice and can be deployed from the application server.

If you are using Tomcat you can just place the axis2.war file in the webapps directory of the Tomcat installation, restart the server and axis2 will be auto-deployed.



After axis2 has been deployed you should be able access the administration console from http://:/axis2/axis2-admin/



The default username password for axis is admin/axis2.
Once logged in select “Upload Service”, the ODI public web service file is available at :- OraHome_1\oracledi\tools\web_services\odi-public-ws.aar

Once uploaded if you select “Available Services” you should see the service OdiInvoke and the operations that are available.



If you have read through my previous blogs I covered the Java invocation API that does pretty much the same thing except that uses TCP/IP to communicate directly with the ODI agent.

Clicking on OdiInvoke will display the WSDL, this information is required for testing.

If you intend on using the public web service you will need a source application that will generate and handle SOAP requests which is the protocol for communicating with Web Services. For testing purposes, I highly recommend soapUI and that is what I will be using to demonstrate any SOAP related material, you could use the ODI tool ODIInvokeWebServices but I am going to cover that off later.

If you are using soapUI then you can just create a new project and put in the url to the ODIinvoke WSDL.



This will generate all the operations that are available.



If you want to get a list of the available Scenarios that can be executed then the listScenario operation can be used, in soapUI it is simple as opening up the request editor and filling in the details and then executing the request.



Executing a scenario is just as simple though you will require an agent running, the format for the SOAP request look like this >



Many of the parameters are optional so once you have stripped out all unneeded elements an example would be :-



A successful request will produce a result of :-



The session number can be traced back to the operator where you can see the results of the execution.
If there are any problems with the request the response would follow the format of :-



Well that is all fine running SOAP requests from a testing GUI but what if you want to build them into a functional application, there are a number of different programming options out there and in the next blog I will use my preferred choice Java to try and accomplish this.

Applying EPM maintenance release 11.1.1.2

As many will be aware the next release of the Oracle EPM system is available, the main reason for this release is to add localization, if it was solely for that reason then I probably wouldn't be applying the release. I am going to be applying the release due to the amount of defects that have been addressed and I am sure some others will be also considering applying it so I thought I would give a heads up to see if any issues are encountered. It is getting to a stage though if you have deployed version 11 then it is worth checking through the list of defects addressed and judging whether it worth going through the whole upgrade process and not just jumping into upgrading because a newer version is available.

Now there may be many defects fixed but what is quite startling is the ever growing size of the known issues, I don't know if Oracle are now being more open about the issues than Hyperion were but you could be spending a long time reading through them if you use the full suite of products. If you are a user of EPMA then you will have a fair few known issues to read through, I dread to think what the list will grow like when the take up of the product increases.

First of all be ready to download an even bigger set of files, this is because Oracle is going down the route of one-size fits all, so it doesn't matter if you are downloading 32/64bit or from Windows to linux it is all grouped together. I am sure it is filling you with joy if you want to just install the essbase client.

If you only intend on updating partially then you will still need to download the installer and the foundation components plus the products you want to upgrade.

Once you have taken up the bandwidth for a small community and downloaded the entire product set extract all the files into the same base directory.



I would make sure that no EPM services are running and you have backed up everything first and then execute the InstallTool file and all being well the prerequisites will have been met and a new window should appear allowing you to select a language, this is a new feature which is part of localization being added.



Once the welcome screen has appeared do check that hostname has resolved correctly and not to an ip address, if you do then check your network configuration before proceeding.



At the “Installation Type” section choose “Apply maintenance release”



The next stage you can define which products you want to upgrade, I kept the default selected, as I want to upgrade everything. It is a good time to check that everything you are planning on upgrading appears in the list, if it doesn’t then you have a missed a download or not extracted all the components into the correct directory.



The list of products to upgrade should be displayed as “Maintenance”



Now go and do something far more interesting as it can take a while to upgrade depending on what has been selected and the performance of the machine.



All being well everything should complete without any issues and you can move on to configuring the products.



All the products that need configuring will be highlighted; in most cases it will be just the application server that needs redeploying with exception of EAS where the database also needs reconfiguring.



After everything was configured I went through and checked each product was operating as expected, I couldn’t spot any obvious problems though that is not saying there might not be. I had to go through each planning application and migrate from 11.1.1.1 to 11.1.1.2



Update 11th April 2009 :- I did experience a problem after upgrading to the latest maintenance release, it does look like a known issue but I thought I would point it out because I know it will affect anybody who uses business rules.

After upgrading no users could access business rules, after looking in Shared Services I did notice there was a new element under the Business Rules Application groups (HBRAPP)



Any provisioning against the original server was no longer valid and had to be applied to the new HBRAPP, so if you are planning on upgrading and have a large numbers of users/groups provisioned for business rule roles then you will need to use LCM or the CSSImportExport utility to migrate the provisioning.

Here is documented known issue in the Business Rules 11.1.1.2 Readme :-

If you install this release of Administration Services on top of a prior release, after you complete the upgrade configuration and log on to Shared Services, there may be two instances of the relational database host in the Business Rules project. In addition, there may be provisioned roles for administrators for the previous release only, not the current release. To work around this, delete the instance for the previous release and provision the users against the current release instance (7185251).