Managing ODI 11g standalone agents using OPMN with EPM 11.1.2

In ODI 11g there are number of different methods to manage agents, one of these is through the use of Oracle Process Manager and Notification server (OPMN). Unfortunately with the current release of ODI 11g the components that make up OPMN have not been included in the installation, I am sure this will be rectified in future releases of ODI.

Fortunately with EPM 11.1.2 and a selection of other middleware products OPMN comes packaged with the installation and if you have or plan to have a ODI standalone agent on the same machine then you can take advantage of using OPMN to manage the agent, if on the same machine as essbase and a windows OS you will have the added benefit of using the same windows service that OPMN is managing essbase with.



With Essbase 11.1.2 installed on windows there should be a service named something like “Oracle Process Manager (EPM_epmsystem1), which by default will start and stop the essbase server.

If you want to install OPMN in a non EPM environment then OPMN is included with most installations that use OAS but you can also install it by downloading Oracle Web Tier Utilities 11g, this can be downloaded from Oracle e-delivery under :-

Oracle Fusion Middleware > Oracle Fusion Middleware 11g Media Pack
Oracle Fusion Middleware Web Tier Utilities 11g (11.1.1.2.0) for Microsoft Windows (32-bit) - V18630-01 - 1.2G
Oracle Fusion Middleware Web Tier Utilities 11g Patch Set 2 (11.1.1.3.0) for Microsoft Windows (32-bit) - V20974-01 - 1.2G

As usual a pretty chunky install, you will need to install 11.1.1.2 first and then patch to 11.1.1.3.
When installing just tick options for Oracle HTTP server and Oracle Webcache as this will install the OPMN components.

Update 03/01/2011 - If you are intending to use OPMN on an environment which does not have OPMN preinstalled then there is an useful article available on "My Oracle Support" - "Configuring OPMN To Start, Stop And Monitor ODI 11g Standalone Agent [ID 1274484.1]"

Today I am going to stick with configuring an agent to use OPMN on an EPM environment as I don’t believe the current documentation is the best, I am going to install it on a Windows OS but the same principals should apply to Unix. The process and logic is pretty much the same if the agent was being configured on a non EPM environment.

The first step would be to create an agent using the studio; it may be that you have already created an agent and that could be used.



If you go to the ODI_HOME/oracledi/agent/bin/directory where the standalone agent has been installed you will see a number of files, in my case it is E:/Oracle/Middleware/ORAODI/oracledi/agent/bin



The files that I will be using in some form today will be agentcreate.properties, odi_opmn_addagent.bat, odi_opmn_deleteagent.bat and odiparams.bat
If you are using Unix then you would just use the .sh files instead of the .bat

The file agentcreate.properties contains all the information that is required to be able to add the agent to OPMN



By default the file is already populated but pretty much every property needs updating, the only one to keep is COMPONENT_TYPE=odiagent

It is worth pointing out to always use / as the path separator and not \



When the standalone agent installed if the configuration stage is not skipped then the file odiparams.bat/sh is populated with many of the properties required for agentcreate.properties


odiparams


I am not going to explain what the above properties mean as I believe they are self-explanatory, after updating agentcreate.properties with the above properties the file looks like



PORTNO = The port number that the agent is configured to listen on.

JMXPORTNO = The JMX port number by default is the agent port + 1000, so if the agent is listening on port 20910 the
JMX port number would be 21910



ORACLE_ODI_HOME = Installation path of ODI, in my case this is E:/
/Oracle/Middleware/ORAODI

COMPONENT_NAME = This is the name you want the agent to be added and used as identification in OPMN

INSTANCE_HOME = This is the location of the OPMN instance into which the agent should be added. In an EPM installation this refers to <EPM_HOME>/user_projects/epmsystem1, which in my case is E:/Oracle/Middleware/user_projects/epmsystem1

ORACLE_OPMN_HOME = OPMN installation directory, in an EPM installation this means the directory where Oracle HTTP server is installed e.g. <EPM_HOME>/ohs , in my case is E:/Oracle/Middleware/ohs

In <EPM_HOME>/user_projects/epmsystem1/bin there is a file named opmnctl.bat, in this file you will also find reference to INSTANCE_HOME and ORACLE_OPMN_HOME



opmnctl.bat is also used for managing OPMN components which I will go through shortly.



With agentcreate.properties complete the agent can be added to the OPMN instance


Next the script odi_opmn_addagent.bat/sh needs to be edited.



By default the variables OPMN_HOME and INSTANCE_HOME and set to the parent directory where the file is executed from, if these are not updated then adding the agent to OPMN will fail.



The values for the variables are the same as the ones used in agentcreate.properties



An alternative would be to add the variables as environment variables, which would save having to update the scripts.

To add the agent to OPMN the syntax is :-
odi_opmn_addagent.bat/sh <agent create property filename>
and not addagent as the script specifies.

Just before the adding the agent I am going to check the status of OPMN, this can be achieved by from command line by executing :-
<EPM_HOME>/user_projects/epmsystem1/bin/opmnctl.bat status



As you can see an essbase agent is currently running on the instance of OPMN.



From command line the ODI agent is registered with OPMN, if no errors are received then the agent should have been added.



The agent has been added to the OPMN instance but is not started.



To start the agent you can either start by component or process_type, it does make a difference when you only have one agent but if you had multiple agents you could manage them all at once using the process-type.



The syntax is :-

opmnctl startproc ias-component=<COMPONENT_NAME>
opmnctl startproc process-type=<PROCESS_TYPE_NAME>



Once the agent process has started it can be tested to check if it is working in the ODI Studio.



To stop the agent the syntax is :-

opmnctl stopproc ias-component=<COMPONENT_NAME>
opmnctl stopproc process-type=<PROCESS_TYPE_NAME>



For a list of other available commands type :- opmnctl



Using the windows service will also start/stop any components added to the OPMN instance, so at present it will control the essbase agent and the ODI agent.

To edit the configuration of the OPMN instance use the following file
<EPM_HOME>/user_projects/epmsystem1/config/OPMN/opmn/opmn.xml



If you want the agent to automatically restart if the agent process crashes then you can use the “restart-on-death” parameter.



Update the following line from –


to



Once you have made the changes restart the ODI agent using restartproc command in OPMN or restart the windows service, if the agent crashes for any reason it will automatically start up a new agent process. This principal applies to any processes being managed by OPMN so you could apply the same command to the essbase agent.

If you want to remove the ODI agent from the OPMN instance then one method is to use the script odi_opmn_deleteagent.bat/sh located in the agent/bin directory.



The variable OPMN_HOME should be set to <EPM_HOME>/ohs, once again the value used in agentcreate.properties can be used.

The syntax to delete the agent from the OPMN instance is :-

odi_opmn_deleteagent <INSTANCE_HOME> <OPMN_ORACLE_HOME> <AGENT_NAME>



You will get a warning if the agent is not running like above but as long as you don’t get any errors messages the agent should be removed from the OPMN instance.



Check whether the agent has been removed by executing: - opmnctl status

ODI Series – Deleting essbase members part 2

In the last blog I went over a simple method of deleting essbase members, today I want to expand on that method and provide a different solution to accomplish the same task.

There are situations where you may not have the full dimension hierarchy to hand so the last method does not work for you, it can be painful going through a full dimension looking for the members to remove.

The end solution for today is to provide a flat file in parent/child format with only the members we want to remove from the outline. Once again I will be using the ASOSamp.Sample essbase database and using ODI 11G, I am using 11G because it provides extra functionality over 10G, which is not to say this cannot be done in 10G and I will still give an example of how to do it in both versions.

Basically I am going to use ODI to extract the members of a dimension into a temporary table in-memory and then minus the members in the flat file from the members in the temporary table, finally using the same method as last week load the members from the temporary table into essbase using the remove unspecified option, the members in the temporary table should at that point only contain the members I want leaving in the hierarchy.

Let’s waste no more time and go through the proposed solution in my usual style.



Highlighted are the members that are going to be deleted from the product dimension.



Above is the flat file that contains the members that are going to be deleted in parent/child format.



The first step is to reverse the flat file in ODI so it can be loaded at a later stage, the same Datastore that was created in the last blog can be used.



I want to be able to use this solution across other dimensions in the essbase database instead of having to create an interface per dimension, so the next step is to create a variable that can be used to define which dimension to use.



I have set the default value to Products though this does not matter as it can be set at runtime.



The products Datastore was duplicated, any of the standard dimensions could have been duplicated. I say standard dimensions as a measures type dimension contains additional columns.



The duplicated Datastore was renamed and the Resource Name set as the variable that was created earlier which was #DIMDELETE. This means I can pass the variable value to set which dimension I want to use in the extract interface.

Next step is to create an interface that will extract the dimension members from essbase.



I am using the memory engine as the staging area as the complexity and size of the output extract is not that large, if I was extracting a large dimension then I would consider using an rdbms as the staging area.

In the mapping area the StandardDimension Datastore was dragged on to the source, in the target I am using a temporary Datastore, as I am using the memory engine as the staging area this means that a temporary table will be created in memory when the interface is executed.



The temporary DataStore was named and two columns added, in the properties area for each column a name was given and Datatype was set to VARCHAR (80 length as this is the maximum length for essbase member names)



The target columns were mapped to the source, PARENT to ParentName and MEMBERNAME to MemberName.

So currently the interface will extract members from an essbase dimension, the dimension that is extracted from is defined the ODI variable DIMDELETE, the parent/child members are loaded into an in-memory temporary table.

This is fine but the aim is to remove the members in the source flat file that was created earlier from the members that have been extracted. This is where a new feature in ODI 11G comes into play; this is with the use of DataSets.

DataSets basically lets you have a group of source Datastores and these Datastores can be merged into the target Datastore using operators such as UNION,UNION ALL, MINUS and INTERSECT.

With this new functionality I can add a new DataSet that will be the source flat file and use the operator of MINUS to compare the source flat file from the dimension member extract.



A new Dataset can be added from the interface toolbar.



The existing DataSet was named as “ExtractProduct” and a new one created called “LoadMinusProds”, the operator was set to MINUS.



Once a new DataSet has been created you will notice that tabs are generated to distinguish between each set.



The flat file Datastore containing the members to delete was dragged on to the source area, the columns were then mapped to the temporary target table.



If you look at the flow diagram you get more of an understanding of what is happening, member information is extracted from essbase using the KM “Hyperion Essbase METADATA to SQL” and loaded into a temporary table in the staging area (memory engine). The flat file is loaded into a temporary table in memory.



These two temporary tables are compared using the MINUS operator and finally loaded into the temporary in-memory table “PROD_EXTRACT”. The PROD_EXTRACT tables should only contain the parent/child members that need to be kept in the dimension.



If you run the interface in simulation mode (another nice feature in 11G) and look at step 14 “… Insert new Rows” you can see the MINUS SQL in operation.



In step 5 members from the product dimension are extracted and loaded to temporary table C$_0PROD_EXTRACT, at step 10 records from the flat file source are loaded into temporary table C$_1PROD_EXTRACT.

Step 14 records from C$_1PROD_EXTRACT are subtracted from the records in table C$_0PROD_EXTRACT and the difference loaded into temporary table PROD_EXTRACT.



In the LKM options for extract the dimensional information the MEMBER_FILTER_CRITERIA was set to Descendants and MEMBER_FILTER_VALUE set to the variable value stored in #DIMDELETE (which is products), Descendants was chosen as I didn’t want to include the dimension name in the extract as the extract includes a parent of NULL and this causes an issue when running the dimension build later on.

Using the variable in the filter value option means the interface doesn’t need to be updated if it is ever used against a different dimension, all that is required is to set the variable.



When the interface loads the subtracted records into the temporary table PROD_EXTRACT the IKM options of CREATE_TARG_TABLE and DELETE_ALL were set to true, this is because the temporary table may not exist in memory so create it if it doesn’t and if it does exist delete all the records.

You may be saying well this all wonderful but unfortunately are not yet using ODI11G, in 10G you can still get to the same position just not in such an elegant manner, what you could do is have an interface that extracts the dimension into a temporary table, have another interface that loads the flat file records to another temporary table and then create a view using the MINUS operator to subtract one temporary table from the other, for example



The example given is for Oracle but is pretty much standard across the board for creating views. It is possible to create an interface in 10G to mimic the MINUS functionality but to be honest for this situation it is much simpler just to create a view.
Once the view has been created it could be reversed and used in ODI.

Right, so now an interface exists that will load only the records we want to keep in a dimension and stores this in a temporary in-memory table, the next step is just the same principal as in the last blog and an interface is required to load the records from the temporary table back into the essbase dimension using the “Remove Unspecified” load rule (if you don’t understand have a quick read of the last blog).



The interface that has just been created (EXTRACT_HIER) was dragged on to the source so the temporary table PROD_EXTRACT becomes the source.
If you are using 10G then you could drag the view on to the source.


The essbase dimension Datastore that was duplicated earlier is dragged to become the target and the target columns are simply straight mapped to the source columns.
The target dimension that is used in the interface is set by the value stored in the variable #DIMDELETE



The IKM uses the essbase rules file "REMOVE", I am not going to go over the creation of the load rule as the process was explained in detail in the last blog, basically it is a load rule that will remove any members from the dimension that do not exist in the source which in this case is the temporary table.

Finally an ODI package is required to put the steps together.



Step 1 sets the variable DIMDELETE to Products, step 2 populates the temporary table with only the dimension members that are needed to be kept, step 3 loads the members from the temporary table into the essbase dimension using the remove unspecified method.



After successfully executing the package a quick check of the outline and you can see the members from the flat file have been deleted from the hierarchy.

If I want to use the package to delete members from a different dimension it is as easy as setting the DIMDELETE variable to a different dimension name and updating the flat file to include the members that need to be deleted.

In the next blog I will delve into another method of using ODI to delete essbase members.

ODI Series – Deleting essbase members part 1

Recently I have had a few emails and have seen a few posts about deleting essbase members using ODI, unlike the planning adaptor there is no operation setting to define how to delete members.

So I have decided to go through a few different methods on how to achieve this, today I will go back to basics and look at the standard method, if you have prior experience in essbase then this is the same way as you will be used to using so today’s post may be of no benefit to you but anyway the main reason I am going to go over this is so in future if I get asked the question I can quickly point to this post instead of having to try to explain it yet again.

This first method requires you to have in your source the full list of parent/child members in the dimension excluding the ones you want to remove.

No time for unfounded waffle I will get on with the process.
I am using ODI 11g for the example but it is exactly the same process if you are using 10G.



I am using the sample ASO database ASOsamp.Sample, the objective is to remove from the Products dimension the member “Digital Cameras/Camcorders” and its children.

Only two columns are required in the source to carry out the deletion process, the required information is the parent member and the member itself.



Above is the source with a full list of parent/child members in the product dimension, highlighted in red are the members to be deleted from the dimension.



The members that were highlighted in red are deleted from the source.

My example uses a flat file but the source could just as easily be from another source such as a relational table.

I am not going to go over step by step in ODI on how to set up connections to essbase and flat files I am going to assume you already have done this, if you don’t know how to do it then have a look back at my earlier ODI series posts.



Reverse the Essbase model and there should be a datastore available for the dimension that the members are going to be deleted from.



Reverse the Essbase model and there should be a datastore available for the dimension that the members are going to be deleted from.



Create a new interface, set a staging area.
In the diagram drag the source datastore on to the source area and drag the essbase dimension datastore on to the target.

Make sure you set the target ParentName/MemberName columns to execute on the staging area as the essbase technology has no JDBC capabilities.




In the flow set the target KM to “IKM SQL to Hyperion Essbase (Metadata)”
In the KM options provide an essbase load rule name (it doesn’t exist at the moment)
Turn on logging and error logging and provide a full path and filename for the logs, the error log is required to make sure there were no failures in performing the dimension build.

Save the interface but don’t run it yet as it is time to switch over to EAS.

Once logged into EAS and have expanded the essbase server, right click the essbase database and create a new load rule, Select “Options” > “Dimension Build Settings”, click the “Dimension Build Settings”



Double click the dimension you are removing the members from, in my example this is Products



“Use parent/child references” has to be selected as the build method as this is the only way to perform hierarchy builds when using ODI.



Now for the important part in the member update section “Remove unspecified” must be specified.
Remove unspecified basically deletes the members in the selected dimension that do not exist in the source, click OK.

Next the columns in the rule need to be matched to the output from ODI. Select Field properties > Dimension Build Properties



Double click the dimension that is being built and for the Type select “Parent”, click next and repeat the process but select Child as the type.



You should end up with columns looking like the image above.
Save the rule with the same name that was provided in the KM options.

The interface can now be executed within ODI.



If the interface successfully completes it doesn’t mean the members were definitely removed, the best place to check is to see if the error log generated contains any records.



And there you have it “Digital Cameras/Camcorders” and its children have successfully been removed.

So what if you want to delete members and don’t have the full list members to start with, well in the next part I will go through another method that can be used.