More to life than this...

Loading to EPMA planning applications using interface tables – Part 8

2012-01-22T10:55:00.000-08:00

Just when you thought the end of the interface series had come about I return with another instalment. Today I thought I would quickly go over loading data from interface tables as it is an area I have not covered and I want the series to be complete.

Within EPMA there is built in functionality known as data synchronization which allows the synchronizing of data between EPM products, flat files and interface tables which I will be covering.

The objective of today is as usual to go back to basics and load numeric, date and text data from an interface table into a planning application.

A simple form was created with three account members, TextMember1 which is a text data type, DateMember1 which is a date member type and a standard account member Acc1.

A relational interface table was created to hold the data, there are not really any special requirements on the format of the table and the one created basically has column for each dimension and one for the data.

The table was populated with three different data type records, I am not going to be covering populating the table as the data could have come from practically any source and it could have gone through numerous transformations using the tool or method of your choice before being loaded to the interface table.

To be able to load the data there are a few steps that need to be carried out within the Data Synchronization area accessed through workspace.

The first step is to create the Interface Area definition which basically means which relational table and columns will be used to load the data from.

Select Navigate > Administer > Data Synchronization in workspace.

In the Data Synchronization area select New > Data Interface Table Definition.

The interface area that is going to be is selected, if you have followed this blog series you should already know or have set up an interface area, if you are unclear then have a read here.

The table which holds the data and the data column are selected from the drop down boxes.

The dimension definition section allows you to add the columns that are going to be used in the synchronization and provide a friendly display name for them.

Finally a name is provided for the Interface area.

The interface area now appears in the data synchronizer, you are not confined to just one interface area and can create as many are required.

The next step is to create the Synchronization which defines which interface area to associate with, the application to load data to and the dimension mappings between the source interface table and the target application.

To create the Synchronization select New > Synchronization.

The source type is then defined which can either be a Hyperion application, external source such as a flat file or in my case an interface area.

A list of available interface areas will then be displayed.

A list of available target applications are then displayed.

As the destination is a planning application a list of available plan types to load the data to are displayed and one can be selected.

The next screen defines the dimension mapping from the source interface columns to the target planning application dimensions, to create a link between source and target then it is as simple as dragging the source to the target.

There is not always match between source and target and in the example above you can see there is no mapping for the dimension HSP_Rates in the source, in this case you can force the target dimension to a member.

If you right click the dimension there is an option “Assign Default Member”

A member can then selected and the data will be loaded to this member each time the synchronization is run.

It is also possible to filter the data from the source, so say for example you only wanted to load data for one account then a filter can be applied.

If you right click the source dimension the option to “Create Filter” will be available.

As the source is an interface table then there two types of filter available EQUAL or LIKE, if the source was an application there are also a number of functions available.

Wildcards such as * and ? are allowed when using the LIKE filter, to be honest it is pretty basic functionality and if you are using interface tables then it is probably best to transform the data into a format ready to be loaded before using the data synchronizer.

There is also the option to create mapping tables if the source is a Hyperion application.

If you create a filter an icon will be displayed against the source.

Once all the mappings are complete the synchronization can be saved.

The synchronization should now appear in the Data Synchronizer window and then right click “Execute Synchronization” to load the data, check the Job Console to see if the synchronization was successful.

The synchronization was successful and the web form displayed the desired results.

If you don’t want to have to run the synchronization each time from workspace and want to automate the process then you can use the EPMA batch client, I covered the client in part 5 of the series so if have not used it then it might be worth having a read.

The syntax to execute synchronization using the batch client is -

Execute DataSynchronization Parameters(DataSynchronizationName, DataTransformationOperator,DataTransformationValue, FileName, ValidateOnly, WaitForCompletion, dataSyncLoadOptionHpMode) Values('', '', '', '', '', ‘',’’);

DataSynchronizationName—Name of the Data Synchronization profile to execute.

DataTransformationOperator—Valid values are:

•    None
•    '*' (Multiply)
•    '/' (Divide)
•    '+' (Add)
•    '-' (Subtract)

DataTransformationValue—Value to use in conjunction with the DataTransformationOperator to modify the data values.

FileName—If the synchronization uses an external source file for the source of the synchronization, the location of the external source file.

ValidateOnly—Validates the data synchronization without executing it.

WaitForCompletion—If set to true, the Batch Client waits for the job to finish. If set to false, the Batch Client submits the job and continues. Allowed values: True or False

dataSyncLoadOptionHpMode. Allowed values: ADD, SUBTRACT, OVERWRITE (which is the default)

So in the examples I have been using the syntax would be.

Execute DataSynchronization Parameters(DataSynchronizationName, DataTransformationOperator,DataTransformationValue, FileName, ValidateOnly, WaitForCompletion, dataSyncLoadOptionHpMode) Values('INT_2_EPMASAMP', 'None', '', '', 'false', ‘true',OVERWRITE’);

This can then be added to a script that can be called from command line, an example of the command line would be

epma-batch-client.bat -C"F:\Scripts\ExcDataSync.txt" -R"F:\Scripts\ExcDataSync.log" -Uadmin –Ppassword

The output is written to the command window and if specified to a log file, the output includes a link to the job console to view a summary of the synchronization.

If you are interested in using ODI to automate the process of using the batch client then have a read of part 6 of the series.

If you are experiencing any issues with the synchronization then it is possible to enable additional logging.

There is a file called dme.properties located at
<MIDDLEWARE_HOME>\user_projects\<instancename>\config\EPMA\DataSync

If you edit the file and remove the # from the beginning of the following lines.

preTranslationProcessingClass=com.hyperion.awb.datasync.custom.FileBasedRowLogger
preTranslationProcessingClass.outputFile=preTransOut.txt

postTranslationProcessingClass=com.hyperion.awb.datasync.custom.FileBasedRowLogger
postTranslationProcessingClass.outputFile=postTransOut.txt

createDebugFiles=true
debugSampleSize=1000

Save the file and then restart Data Synchronization web application service web application

The additional logs will be available from
<MIDDLEWARE_HOME>\user_projects\epmsystem1\tmp\oracle\temp

There is a log available displaying the data before it is loaded to planning, the filename is prefixed with a unique ID.

and another log with the data has the filters and mappings applied which is the format that is then loaded to the planning application.

There is also an additional folder created each time a synchronization is executed with the same unique ID and contains further diagnostic logging information to investigate.

To turn off the addition logging edit dme.properties again and place a hash in front of the lines that were originally edited and restart Data Synchronization web application.

Loading to EPMA planning applications using interface tables – Part 7

2012-01-08T05:39:00.000-08:00

Back once again with another instalment of the EPMA interface series even though I thought the last one would have been the final part, today’s blog is about loading attributes, Smart Lists and UDAs and is mainly due to the number of requests I have had on how to go about it.

I am going to start off with attributes and run through the steps to load an attribute hierarchy and apply an attribute to a base member using interface tables. Once again I am not going to overcomplicate the matter so I am going manually create the dimension and associations first, it only takes a couple of minutes to do and is probably quicker than trying to achieve it through the interface route and considering the creation of an attribute dimensions don’t happen that often it makes sense to use this method.

I will also assume you have been following the series and that you are up to speed on the concept of how interface tables work.

Anyway on to the steps, once in the dimension library select File > New Dimension

I am going to create an unimaginative attribute dimension called ProductType which will be used to analyze products in the Segments dimension of the sample planning application.

Once the dimension has been created the association between the segments and the ProductType dimension has to be created, this is achieved by right clicking the Segments dimension in the dimension library and choosing “Create Association”. If I didn’t create this association then I would not be able to apply attribute members against base level members in the Segments dimension.

I also created an association between the Alias and ProductType dimension to allow aliases to be added to the attribute member names.

The ProductType dimension was added to the sample planning application and all the associations activated.

If a member in the Segments dimension is selected then the ProdTypAtt property is now available and members from the ProductType dimension can be assigned to it.

This is where the interface tables can perform the rest of the work such as loading the attribute dimension hierarchy and assign attribute members to base level members in the Segments dimension.

The first step is to return the interface table IM_DIMENSION, I went through the details of this table in part 2 of the series so if you are unsure about the table have another read of that part of the series.

A new record was added to the table with the following details

C_DIMENSION_NAME – this defines the name of the dimension so ProductType is used.
C_DIMENSION_CLASS_NAME – this defines the type of dimension which is Attribute.
C_HIERARCHY_TABLE_NAME – this provides the table name which holds the attribute dimension hierarchy, the table name is going to be PLAN_PRODUCTTYPE_HIER

I created the table PLAN_PRODUCTTYPE_HIER which will hold the hierarchy metadata for the ProductType attribute dimension, the ISPRIMARY shouldn’t really be necessary but if you don’t include it then warnings are generated in EPMA when an import from the interface tables takes place.

The table was populated with a simple one level attribute hierarchy.

As attribute members are going to be assigned to base level members in the Segments dimension a column was added to the Segments hierarchy table “PLAN_SEGMENTS_HIER”, the column was named the same as the attribute property “PRODTYPEATT” defined in EPMA.

The import profile requires editing to take account of the new attribute dimension otherwise if an import was run the new information would not be picked up and loaded to EPMA.

In the Map Dimensions section the Interface table was mapped to the ProductType dimension in the shared dimension library.

In the Dimension mapping section the ProductType dimensionAlias column was mapped to the Alias property in EPMA, an attribute member onlyhas the Alias property to map.

The Segments dimension was updated to map the attribute column from the source interface table to the property in EPMA, usually I keep the source/target with the same naming convention so then it is much easier to map.

The import profile was then executed to load the metadata from the interface tables to EPMA.

The ProductType attribute dimension hierarchy was successfully created and the Segment members were assigned with an attribute member.

Now say you didn’t create the associations manually as I did in EPMA and you wanted to achieve this using the interface tables, I still think it is just as quick to manually do it but here is how you would go about it.

When the sample interface tables are first created there is a table available called IM_DIMENSION_ASSOCIATION, the table name basically explains what it is about and it provides the ability to map dimensions and properties.

IM_DIMENSION_ASSOCIATION

There are four columns to populate

I_LOAD_ID which is the same as the load ID used in pretty much all the interface tables
C_BASE_DIMESION - Name of the base dimension whose member property will be associated with another dimension
C_PROPERTY - The name of the associated property.
C_TARGET_DIMENSION - Name of the dimension with which the associated property is associated.

The table has been populated to associate the Segments dimension with the attribute dimension and its property, also the alias property is associated between the attribute and alias dimensions.
If the import profile is run again and the associations did not exist they would be created replicating what I originally did manually.

If you want to also created the dimension in EPMA from the information in the interface tables then you need to populate an extra column in the IM_DIMENSION table

IM_DIMENSION

The C_DIM_PROPERTY_TABLE_NAME column holds a table name to define the properties of the dimension, so in this case the properties for the ProductType attribute dimension will be held in table PLAN_PRODUCTTYPE_PROPERTY.

PLAN_PRODUCTTYPE_PROPERTY

For an attribute dimension there are only a few property columns.
DIMENIONALIAS – Alias of the dimension.
ATTRIBUTEDATATYPE – Defines the attribute dimension type which can be Text, Boolean, Date, Numeric

By populating this information the dimension and its properties will be created when the import profile is executed.

Let’s move on to UDAs.

Once again I am going to create the dimension first in the EPMA dimension library and set the Type to UDA.

When a UDA dimension is created two members are added by default, these are specific to planning and if you are unclear what HSP_NOLINK and HSP_UDF do then have a read of the documentation here

I am going to assign UDAs to the Segments dimension so an association between the two dimensions was created using the existing UDA property.

Once again the IM_DIMENSION table requires an addition row with the UDA information.

Dimension name = UDA_Shared
Dimension class = UDA

Hierarchy table name will be PLAN_UDA_HIER

PLAN_UDA_HIER

The table was created and populated with UDA members in the child column.

As the objective is to apply UDAs to members in the Segments dimension the Segments hierarchy table requires updating.

PLAN_SEGMENTS_HIER

I am going to apply multiple UDAs to one member and to do this you need one column per UDA, so this case an extra UDA column was added.

The import profile required editing to map the newly added UDA dimension.

The Segments dimension mapping was also updated to map to the new UDA columns from source to target.

The import profile was executed and the UDA dimension was built and multiple UDAs were assigned to Segment dimension members.

On to the final topic and that is Smart Lists, this time the objective is to create and build the Smart List dimension and then assign a Smart List to a member in the accounts dimension, the logic is pretty much the same to that of the attribute and UDA dimensions.

IM_DIMENSION

A Smart List dimension is going to be created called GradeSL which will basically just define different employee grades.

A new record is added to the IM_DIMENSION table, the Smart List members will be in table PLAN_GRADESL_HIER and the properties for the Smart List dimension will be in table PLAN_GRADESL_PROPERTY.

If you created a Smart List dimension in EPMA then there are a number of properties than can be set, most of these can be applied from an interface table.

PLAN_GRADESL_PROPERTY

The table to hold the Smart List dimension property definitions was created and populated, if you don’t understand what the properties mean then it is worth consulting either the EPMA or Planning administrator documentation as they contain detailed information, the above screenshots should provide enough detail to map the EPMA property to the interface table property.

PLAN_GRADESL_HIER

The table to hold the Smart List member information was created and populated.

ITEMVALUE – when a Smart List member is added it requires an integer value
SMARTLISTENTRYLABEL – each Smart List member requires a label which will be used to populate a drop down menu in planning.

As the Smart List is going to be applied to an account member the account hierarchy interface table will require updating.

PLAN_ACCOUNT_HIER

The Smart List GradeSL is applied to account member GradeType and the Data Type is set as a Smart List.

No associations are required between the Smart List dimension and the dimension the Smart List will be applied to.

The import profile is edited again to define the new Smart List dimension.

The Smart List dimension does not exist yet in the Shared Library so “Create dimensions for the non-mapped dimensions with the source dimension name” is enabled, this creates the dimension in EPMA with the same name as defined in the C_DIMENSION_NAME column in the IM_DIMENSION table.

The interface property columns for the Smart List members are mapped to their corresponding properties in EPMA.

The import profile was then executed to create and populate the Smart List dimension and assign the Smart List to an account member.

The Smart List dimension has been created and properties set.

The Smart List GradeSL has been assigned to account member GradeType.

Hopefully you should now have a grasp on how to handle Attributes, UDAs and Smart Lists dimensions using EPMA tables.

Planning - loading text member data update

2011-12-31T10:47:00.001-08:00

A while back there was a post on the planning forum about loading text member data to a planning application using the Outline load utility, there was a reply on the post saying it was not possible in 11.1.2.x and an SR was raised and it had been confirmed as a bug. I knew it was possible in previous versions so I was going to test it out but never got round to it.

Then recently I received an email asking if it was possible to load text data using ODI to planning 11.1.2.1 as once again an SR had been raised and it had been confirmed as a bug. I knew I had definitely loaded text data when working on a previous project but that was with planning 11.1.1.3

I did write a blog on using an alternative method to loading text data because that was before it was possible directly with the ODI planning adaptor, I think it wasn’t possible in the first releases of 11 but it certainly was possible in 11.1.1.3

I thought I had best test out both the Outline load utility and ODI to see if it is possible to load text member data to a 11.1.2.1 planning application.

I first created an account member called TextMember1 with a data type of text.

I constructed a simple form so that I could view the results of load text data to the selected POV.

I set up the Data Load Settings in planning to match how I had set up the form with the Data Load Dimension as Period, Driver Dimension as Account with the member TextMember1

I made sure the Data Load Settings had definitely been correctly saved by running a SQL query against the planning applications relational tables.

I created a CSV file in the format required for the Outline load utility and populated it with the same POV details as the planning web form.

OutlineLoad /A:PLANSAMP /U:admin /M /I:textload.csv /D:Period /L:dataload.log /X:dataload.exc

The Outline load utility was run from command line using the load dimension set as Period to match the Data Load settings and CSV file.

The output log from the utility showed that 1 record was successfully processed and loaded.

The planning web form was run again and the text data was displayed successfully.

I know that you can also use the Outline utility to load data without having to set the Data Load setting in planning and specify the driver information directly in the source file so I thought I would give that a try as well.

I created and populated a new file using the Driver Member and Value column headings.

OutlineLoad /A:PLANSAMP /U:admin /M /I:textload2.csv /TR /L:dataload.log /X:dataload.exc

I successfully ran the utility again but this time removing the load dimension /D and used /TR which means the driver information is specified in the file.

The form displayed the newly loaded text data.

So no problems loading text data using the Outline load utility so time to move on to ODI, the email I received specified ODI 10.1.3.6.x with planning 11.1.2.1 so that is what my first test would be with.

I reversed the planning application and checked the columns “Data Load Cube Name”, “TextMember1” and “Point-of-View” had been added to the Period Datastore.

I created a file Datastore against the CSV file I used with the Outline load utility.

I created a new interface with the CSV file as the source and the Period dimension as the target, the target columns were then mapped to the source.

The flow was set as LKM File to SQL, the memory engine as the staging area and then “IKM SQL to Hyperion Planning”

The interface executed successfully and the web form was reloaded to show the correct text data.

So no problems using ODI 10g how about ODI 11.1.1.5

I replicated everything I had created in ODI 10g but this time used Oracle as the staging area due a bug using the memory engine with planning.

The interface ran with no problems and once again the correct text data was shown in the planning web form.

All testing was successful so at least I can put my mind at rest on this subject.

Loading to EPMA planning applications using interface tables – Part 6

2011-12-11T02:04:00.001-08:00

In the last part of the series I went through the steps using the command line driven EPMA batch client to import from interface tables and then deploy to planning.

Today I am going to look at using ODI to control running of the batch client and add in some error handling functionality. Unfortunately there are no adaptors or API available to use with ODI and EPMA so I am going to use the OS command tool to run the EPMA batch client.

If you look back to part 4 of this series I had created a number of interfaces that loaded the planning metadata into the EPMA interface tables.

I ended up with the above package which I will expand on to bring in the batch client functionality.

Usually when designing packages or using tool like the OS command one I would add in control using variables and not really hardcode but for today’s exercise I will just keep it simple which should make it easier to read.

I must also point out that my methods are not the only way of implementing and you may think to yourself there are better ways of achieving the end result and that is the beauty of using ODI as there are not always a right and wrong method. If you do feel you have come up with a better solution then feel free to get in touch to discuss.

If you are going to use the OS command with the EPMA batch client then the ODI agent will require access to the client, if you look back at the last part I gave advice on how to install the client on a supported OS so you have the option of either installing the client on a machine with an agent or install an agent on a machine hosting the client.

If you drag the OS command tool on the diagram in a package you will see there are a number of parameters to set, the most important parameter is “Command to execute” which is basically what you want ODI to execute at command line.

In the last part I executed the following from command line to import metadata from Interface tables to EPMA.

E:\Oracle\Middleware\EPMSystem11R1\products\Foundation\BPMA\EPMABatchClient\epma-batch-client.bat -C"F:\Scripts\Import.txt" -R"F:\Scripts\Output.log" -Uadmin -Ppassword

I am going to use the same import script and log so it is just a matter of transferring the command line to the parameter value.

The working directory has been set to the location of the batch client.

The command to execute calls the client and passes in parameters for the script to use, the log to output to and the account details.
Synchronous is set to Yes so that ODI will wait for a return code before continuing.

I have left the other parameters such as Output file and Error file as blank because the batch client will output all the information to the file Output.log

The EPMA batch client script is the same as from the last part in the series and logs in and imports from interface tables to EPMA based on a profile “SAMPLE_APP”

If you wanted to get clever you could actually create the script on the fly but that is not the objective for today.

As I mentioned earlier I would normally use variables like the example above, these can be set a runtime and make the integrations much more versatile.

So there we go end of blog, not so quick, what happens if there are errors in the output file generated by the batch client.

There are a number of different factors which can generate errors or failures.

A fatal error will cause the package to fail and in the example above I changed the working directory to an invalid location.

Fatal errors should be rare once you have defined all the correct parameters and can be acted upon easily e.g. send an email if the OS command fails or just simply check the Operator.

If say an incorrect password or profile is used then step will not fail.

In the example above I changed the import profile in the script to an invalid one, as you can see the step is successful but it has failed from viewing the log.

Now there are lots of different ways of dealing with this error e.g. create a batch script with error handling, reading the return code, parsing the file….

I am going to use a different method to the ones mentioned whether this is the right option or the best option then I don’t think I can answer that, I am sure somebody will come up with an annoying best practice statement to try and define what is correct.

If you take a look at the log file you will see that it is in a fixed format meaning that the start of each column is known by its position.

Now the idea is to load this log file into a table and then query it to see if there was failure which is defined by an ERROR line in the log.

First of I created a new file Data Store and pointed it to the Output.log file

The File Format was set to Fixed and no header line.

As the columns are fixed I could set the Start position of each column, I set the message column to a length of 200 which should be plenty

Viewing the Data Store shows that the columns are being separated correctly.

The next step was to create a simple interface that loads the log information from into a table.

The interface using “LKM File to SQL” and “IKM SQL Control Append” with the option to truncate the table each time it is run.

Now the log information is the table I created a refreshing variable to check if there was a failure.

When executed the number of errors will be stored in the variable, if the number is greater than zero then the script failed.

These are the steps in the package up to now, the first step uses the File delete tool and deletes the output log, this is because the EPMA batch client appends to the log and only the information from the last run is required.

The next step uses the OS command tool and calls the EPMA batch client and defined script, the output log is loaded to table and the refreshing variable stores the amount of errors.

The variable holding the numbers of errors can now be evaluated to see if the value is greater than zero.

If the value is greater than zero the step will complete with a success otherwise produce a failure so the output defines how to proceed.

I have decided that if there has been a failure then I want to store the failure message and then raise an error with the stored message which will cause the package to fail.

Another refreshing variable was created which queries the table holding the log information and stores the error message.

A new procedure was created with a step using Jython as the technology.
An error is raised with the message which has just been stored using the refreshing variable.

These steps are then added to the package, to add extra functionality an additional step could easily be inserted to send out a failure email and attach the output log.

If there any fatal errors in the log then the package will fail with a message informing of the reason.

You could move on deploying the EPMA application to planning using the same type of logic that I have used up to now in the package but there may be a possibility that there were no fatal errors in loading from the interface tables but there were normal errors generated, if there were errors you might not want to deploy the application just yet.

In this example I changed a member’s property so that it was set to a non-existent Smart List and ran the package again, the package was successful but there is an error in the log.

I don’t want the package to fail and I don’t want the application to be deployed but instead an email sent out informing of the error.

Time for another refreshing variable which will look for the line with the Import details and store the number defining the number of errors, I am only storing the first digit as I only interested to know whether it is greater than zero.

Like previously the variable can be evaluated and then steps adding depending on whether the result is true or false.

So now the log will be checked to see if there are any import errors, if there are errors then an email is sent attaching the log for information and the integration then ends, if there are no import errors the EPMA application is deployed to planning using an OS command.

I am not going to cover the deployment as it uses the same logic as the import and in the last part of the series I went through how to script it.

I added in the metadata load interfaces and some more additional error logging around the deployment from EPMA to planning which means in the end you have a solution that will take metadata from a source and end up to deploying to planning/essbase with relatively little coding that can then be easily scheduled or executed the web using the console.

Obviously you can make it a lot more sophisticated with added functionality and make more use of variables but hopefully you get the idea.

If the package does get complex and you are using ODI 11.1.1.5+ then you could look into whether using load plans would add any benefit.

I am going to leave it there for today, this was going to be the last part but I have had a few queries around using interface which I may cover if I find the time.

Loading to EPMA planning applications using interface tables – Part 5

2011-11-27T04:00:00.001-08:00

In the last part of the series I had pretty much achieved what I set out to do and that was load planning metadata from a source into interface tables and then push into EPMA. The last step of moving the metadata from the interface tables to EPMA was a manual process and in an ideal world you would want this to be automated.

Today I am going to look at automating the process using the EPMA batch client and then move on to using ODI to take over control.

To be honest I am not a big fan of the EPMA batch client and when you compare it to say the outlineload utility it seems so far behind.

I feel that Oracle really needs to develop an API or improve the web services to control EPMA functionality.

When Oracle first released EPMA there was documentation available on the web services but this seems to have disappeared even though the product is still using web services behind the scenes.
Enough of my moaning and let’s get on with the batch client.

The client is usually available on the machine hosting EPMA e.g. on 11.1.2.1 <MIDDLWARE_HOME>\EPMSystem11R1\products\Foundation\BPMA\EPMABatchClient\

It is possible to install the client on a machine of your choice but you will need a number of installation files.

Oracle Hyperion Enterprise Performance Management System Installer, Fusion Edition Release 11.1.2.1.0
Oracle Hyperion Foundation Services Release 11.1.2.1.0 Part 1 of 7
Oracle Hyperion Foundation Services Release 11.1.2.1.0 Part 5 of 7
Oracle Hyperion Enterprise Performance Management Architect, Fusion Edition Release 11.1.2.1.0

The documentation for the client is available here

The client is launched by running epma-batch-client either using interactive command line or a script.

Using the interactive command line is a good way to make sure you are using all the correct commands and set all the parameters before putting them into script.

My intention is to use a script as interactive command line is back to the manual world again.

There are a number of command line arguments that can be used with the client and if you call it with –H it will provide help on the available arguments.

The arguments I am interested in using are

-C is the filename of a script to be used e.g. -C"F:\Scripts\Import.txt"

-R is the filename to output the results of the script to e.g. -R"F:\Scripts\Import.log"

-U is the username to log into EPMA e.g. -Uadmin

-P is the password to log into EPMA e.g. –Ppassword

That is straightforward enough so moving on to the script; there are two important variables that are required to be set before using any client commands.

set bpmaserverurl=http://<servername>/hyperion-bpma-server;
set workspaceurl=http://<servername>:<port>/workspace;

Basically these system variables need to be set to tell the client the location of workspace and EPMA, if you don’t use them the script will fail with a message informing they are missing.
It is important to separate each command with a semi colon.

The next step in the script is to log into EPMA and as I am passing the username and password into the client I only need to use the following

Login;

Once logged in I want to import the metadata from the interface tables and push that into EPMA which is possible using the “Execute Import ..” command

The format of the command is

Execute Import
Parameters(importtype, profilename, filename, waitforcompletion)Values(‘’, ‘’, '’, ‘’);

importtype – set the type of import and the available values are

FlatFile
InterfaceTables

profilename—The name of an existing import profile

filename – If using Flat files as the import then it is the name of the import file.

waitforcompletion - If set to true, the Batch Client waits for the job to finish. If set to false, the Batch Client submits the job and continues.

Now this is where I was a bit confused because all through this series I have been using a Load ID with the interface tables, even when you manually import through EPMA web it requires a Load ID but it is nowhere to be seen in the command parameters, if I am not missing something obvious then how is it possible to set which Load ID to use and what does the client use as a default?

As I only have one Load ID at present I will just carry on and populate the parameters with known values, the importtype will be InterfaceTables and the profilename which I have been using in this series is SAMPLE_APP

Execute Import Parameters(importtype, profilename, filename, waitforcompletion)Values('InterfaceTables', 'SAMPLE_APP', '', 'true');

So the script to import from interface tables to EPMA is

Using command line the following can be run

E:\Oracle\Middleware\EPMSystem11R1\products\Foundation\BPMA\EPMABatchClient\epma-batch-client.bat -C"F:\Scripts\Import.txt" -R"F:\Scripts\Import.log" -Uadmin -Ppassword

Which produces an output log of

The output log is exactly what is outputted to the command window, as you can see on one of the lines it completed with a code of 0 which means it was successful.

There is a list of return codes available here

If I update the script to put in an invalid profile name and the run script again the output would be

When using a batch script to call the EPMA client it is possible to check the return codes and act on them, for instance using command line in windows you could use something like

IF ERRORLEVEL 0 goto COMPLETED_SUCCESS
IF ERRORLEVEL 407 goto IMPORT_FAILED

:COMPLETED_SUCCESS
do something....

:IMPORT_FAILED
do something....

The output log also has a link to the job console where the status for import can be viewed.

Now that the metadata has been successfully loaded into EPMA then it is possible to deploy the application to planning and push the outline changes to essbase.

The commands available are “Execute Deployment” and “Execute Redeployment”.

The deployment command has the following format if being used with planning

Execute Deploy Parameters (ApplicationName, InstanceName, ApplicationServer, HubProject, ClearAll, CheckIntegrity, waitforcompletion, purgeTransactions, deployOption, datasourceName,CreateOutline,RefreshOutline,CreateSecurityFilters,SharedMembersSecurityFilters,ValidateSecurityFilterLimit,Notes)
Values(….);

ApplicationName—The name of an existing application to deploy to.

InstanceName—Name of the instance to deploy to.

ApplicationServer—Name of the application server to deploy to.

HubProject—Shared Services project to use.

ClearAll— This is for HFM

CheckIntegrity—This is for HFM

PurgeTransactions—Purges the transaction history. A value of True will purge the history, and a value of False will not.

deployOption—Specifies the type of deployment that you want to perform, available values are

AppView—Deploy the application only. (This setting is the default.)
Rules—Deploy calculation rules.
All—Deploy calculation rules and the application.

WaitForCompletion—If set to True, the Batch Client waits for the job to finish. If set to False, the Batch Client submits the job and continues.

datasourceName—The data source to be used for the deployment

CreateOutline—Creates the Essbase outline, values : True or False

RefreshOutline—Refreshes the essbase database outlines, values: True or False

CreateSecurityFilters— Create essbase database filters based on access permissions in planning, values: True or False

SharedMembersSecurityFilters—Evaluates access permissions that are set for all instances of the member (base and shared) and applies the least restrictive access permission to them all, values: True or False

ValidateSecurityFilterLimit—Identifies security filters that exceed the Oracle Essbase security filter limit of 64 KB per row, values: True or False

Notes—Optionally stores any comments or notes that you may want to add regarding the deployment.

I find that the easiest way to find out what values to put against the parameters is to initiate a deployment of the application from EPMA.

The deployment window gives you a good indication on the values to populate the command with.

Execute Deploy Parameters(ApplicationName, InstanceName, ApplicationServer, HubProject, ClearAll, CheckIntegrity, waitforcompletion, purgeTransactions, deployOption, datasourceName,CreateOutline,RefreshOutline,CreateSecurityFilters,SharedMembersSecurityFilters,ValidateSecurityFilterLimit,Notes) Values ('EPMASAMP', 'Default', 'Default', 'Planning', 'false', 'false', 'true', 'true', 'AppView', 'EPMASAMP','false','true','false','false','false','EPMASAMP deployment');

This command can then be added to the existing script and executed.

The deployment was successful and once again a link is provided to view the deployment status in the job console.

If there any issues with the deployment then they will be logged.

A return code of 405 is generated which could be acted upon if the process is being scripted.

In the next part of this series I will look at using ODI to control the running of the batch client and provide relevant error handling steps, this will then be added to the package which was created in the last instalment to provide a full automated EPMA interface load and deploy process.

Loading to EPMA planning applications using interface tables - Part 4

2011-11-05T03:34:00.000-07:00

A bit later than planned here is the fourth instalment on using EPMA interface tables, at the end of the last part the import profile had been created and the tables are ready to be populated so today I will go through the process of using ODI to populate the interfaces tables.

Now you don’t have to use ODI as other methods could be used to populate the tables but I am going to use it as it offers such flexibility around transformations and automation in a controlled environment.

At the start of this mini-series I said I was going to try and approach it from the angle of a classic planning application and convert this into an EPMA world, basically I am going to take the classic planning sample application and transform this to be used in an EPMA model. I think the saying “a picture is worth a thousand words” is going to be very relevant for this part of the series as there are going to be lots of screenshots.

I am using this approach as it is more than likely you will be accustomed to the format required for a classic planning application and it does need transforming before it can loaded into EPMA so this is where ODI comes into its own, it also will provide an insight to the difference in member property naming conventions between classic and EPMA.

First of all I am going to extract the metadata from a classic sample application using the outline loader utility, I have covered using the utility to extract dimensions in a previous blog though it is worth noting this functionality is only available from 11.1.2.1.

Using the utility is very straight forward and only requires one command line per dimension, I extracted all the dimensions for completeness even though I am only interested in Account, Entity, Scenario, Version and Segments.

The files produced are in CSV format and can be opened in a text editor or Excel, if you do open the file in Excel watch out for member formulas as they do cause formatting issues.
The extract using the utility seems to add a leading space to every item after the first column, this will mean that in the ODI integration all the columns will need to be trimmed.

Now that all the source files are place we can look to ODI to transform and load the metadata to the interface tables.

I will go through the step by step process of achieving this in ODI though there is going to be an assumption you are familiar with the core principals of ODI.
I will be using Oracle as the technology for the interface tables but the process will be similar if you are going to use something like SQL server.

A brief description of the process will be define physical/logical information in the topology manager, create the models, design the interfaces to load from the flat files to the EPMA interface tables and then bring it all together with a package.

First the physical information needs to be defined for the flat files and the target EPMA tables.
A Physical Schema was added to the file technology.

The directory where all the source flat files are held is manually entered in the schema boxes.

The physical schema always needs to be mapped to a context and a logical schema, the context I am using is called DEMO and I give the logical schema a name of CLASSIC_FILE_EXTRACTS.

A new Data Server using Oracle technology is created, the username/password to connect to the interface tables are entered

The JDBC connection information to the interface tables is added.

From the dropdowns the schema where the interface tables are held is selected, to make it nice and simple the schema is called INTERFACE.

Once again the DEMO context is selected a logical schema name of EPMA_INTERFACE is applied.

This is all that needs to be done in the topology so I can move on to the Designer.

A new model is created for the flat files.

The Model is associated with the file technology and assigned the logical schema that was created in the topology navigator.

A Datastore is manually created for each of the flat files as it is not possible to reverse all the files in a Model in one step, the following steps are for the file holding the account metadata.

If the file is accessible by ODI then it is possible to browse to the file, if it is not then the filename would have to be manually entered.

The format of the file is defined by setting the heading lines to a value of 1, field separator of comma and a single quote text delimiter.

If the file can be accessed by the console then all the columns can be reversed, if the file cannot be accessed by ODI it is easier to temporarily place the file in a location accessible to ODI and the reverse instead of having to manually create all the column information.
I manually updated the lengths to 80 for member/alias columns and increased the Formula column.

The process is repeated for the other flat files that are going to be used to load metadata to the EPMA tables.

A new model is created to hold each the information for each of the EPMA tables.

Oracle technology is selected and assigned to the logical schema that was created earlier in the topology navigator.

In the reverse-engineering section the target EPMA tables that are going to be used in the integrations are selected, these tables were created in part two of this series.

The tables and column definitions are automatically reversed.

Interfaces can now be built as the source and target Datastores have been defined.

I am just going to cover the Account interface as it is probably the most complex out of all of the dimension builds and gives a good overview.

If the source file did not need any transformation it would be just a case of adding the file to the source mapping area of the interface, adding the target EPMA interface table to the target then applying the one to one mapping and finishing off by setting the Knowledge Modules and options to be used.

This would have been the case if the classic planning member properties matched those of an EPMA enabled planning application, I have no idea why they don’t match as it seems like an obvious decision that would have been made when EPMA was being developed.

It is also worth noting that EPMA is very strict about the naming convention for the member properties and a slight difference such as using lower case instead of upper case for the first character can cause problems.

Above is the completed mappings from source to target and I will now go through each of the columns to explain the mappings.

In the second part of this series I went through the LOADID and this numerical value defines what records to load, as this value can change I created an ODI variable which can be applied to all the interfaces and be set at runtime.

All of the columns in the mappings use the TRIM function to remove the leading space in the source which is created by default using the outlineloader utility.

In the mapping a CASE statement is used to change the root member from Account to #root, this is required by EPMA and if it is not used then the root member will fail to load and will cause all of its descendants to fail due to missing parent.

CHILD, ALIAS and DESCRIPTION are a simple straight mapping.

The ISPRIMARY column requires either a value of 1 to define the member as a primary member or 0 to define as a shared member.

The datastorage column of the source file holds the information to whether the member is shared or not with the property value of shared.

The SORTORDER column I have left unmapped for the time being because the rows in the source file are in the correct order.

As the DATASTORAGE property will apply to each of the interfaces I created an ODI function to handle the transformation.

Theclassic properties for DataStorage are on the left and the EPMA ones are on theright and as you can see there is a slight variation.

The function takes a single input DataStorage value and converts it from classic to EPMA.
This function can simply be applied to the DataStorage target mapping for each of dimension load interfaces.

If you are new to ODI functions then it may be worth having a look at the article I wrote in the past about them.

I would usually create a function for converting the Data Type as it is used in all dimensions but this time I was just a little bit lazy.

The main differences between classic and EPMA were the initial character needed capitalizing so I used the INITCAP function.
non-currency needed converting to NonCurrency.

The biggest difference between versions is enumeration in classic and SmartList in EPMA, I do agree that SmartList is a much more user friendly naming convention and enumeration stems from the earlier days of planning.

Thereare a number of member properties in classic that are exported as true/falseand in the EPMA interface world the table definition is set up a numerical soit requires a 1 for true and a 0 for false.

Instead of having to keep repeating mappings for the different columns that require this logic I once again created an ODI function to handle them.

Like most of the mappings there are a number of differentways to transform the data to get the same result with formula I removed thequotes from the beginning and end of the syntax and the replaced double quoteswith single quotes.

SMARTLIST,UDA and PLAN1AGGREGATION were straight mappings where the source was trimmed.

MEMBERVALIDAFORPLAN1 was a conversion from true/false to 1/0 so I reused the CONVERT_BOOLEAN function again.

As there is only one plan type in the application the other columns did not require a mapping, it is dependent on how many plan types there are in an application to which target columns would need to be mapped.

The differences between classic and EPMA naming conventions are the weighted averages and capitalisation; the mapping could also have been achieved using a CASE statement instead of the combination of functions I used.

Skip Value is a strange one in classic it is zeros and in EPMA it is Zeroes, both are correct definitions but it is just typical they would differ.

Main difference is non-expense in classic and NonExpense in EPMA but a simple CASE statement takes care of that.

Very similar between classic and EPMA it is mainly just the first character that needs to be a capital in EPMA, once again the CASE statement could have been used instead of DECODE.

Only difference is the first character needs is capitalised in EPMA.

In classic the source plan type is the actual name of the Plan Type but in EPMA it follows the format of Plan1, Plan2, Plan3, PlanCX, PlanWF

As the number of records to be loaded is small then I find it perfectly fine to use LKM File to SQL instead of the more heavyweight KM’s like “LKM File to Oracle (SQLLDR)”

The KM selected to load from the staging area to the interface table was “IKM SQL Control Append”, no need for flow control and I selected to truncate the table, if there was metadata in the table against a different load id then the truncate wouldn’t have been used and the records matching the current load id would need to be deleted first.

That completes the Account dimension load from a file to the account interface table and the next steps are to be build interfaces for the other dimensions, I am not going to through them as the logic is pretty much the same though I will show screenshots of each of the interfaces for completeness.

Entity

Scenario

Segments

Version

Now to put it all together a package can be used to step through each of the interfaces.

The first step sets the load ID to a value of 1, this could actually be done at run time and instead of setting the variable it could just be declared.

All the interfaces tables have successfully been populated but that is not the end just yet as the metadata has to be imported into EPMA.

For now I am going too manually import the dimensions and this is done from the EPMA dimension library.

From the Type drop down list “Interface Tables” are selected and the profile which created in the last part of this series.

The interface Load ID is also required and set to 1 as that is the number that has been used throughout this series.

So there we go all the dimensions are now populated from the interface tables, if there are any errors these will be logged in the job console.

The application would then need to be deployed to push down the information to planning and essbase.

In reality you wouldn’t want to manually run the import so in the next part of the series I will look at automating this process.

EPM 11.1.2.1 – EAS and Studio console installers

2011-10-29T06:06:00.000-07:00

When version 11 was released it seemed like Oracle didn’t really want to make life easier when it came to installing clients, to install the clients required a number of large foundation and product related files to be extracted.

At first there was only Smart View available as a standalone installer, no Excel add-in, Financial Reporting Studio, EAS or Essbase Studio console installers were available.

It wasn’t until a patch release in 11.1.1.3 that the Excel add-in was made available as a standalone installer.

From 11.1.2.1 the Financial Reporting Studio was finally available with its own installer.

Now I may be the last to know about this but the other day I noticed the following patch.

Initially viewing patch 12978472 doesn’t provide much information as it advises to consult the Read Me file.

If you have a look at the Read Me and defects fixed in the patch you will find that installers have been created for EAS and Essbase Studio consoles.

Download the patch and there you have it two installers are available.

Just like other EPM client installers it is just a matter of running the exe and following the wizard.

So if you are looking for an easier way of installing 11.1.2.1 EAS and Studio consoles then you now have this option.

Loading to EPMA planning applications using interface tables - Part 3

2011-10-09T10:25:00.000-07:00

In the last part I went through interface tables and if you followed the blog you should now understand the requirements to load metadata to existing dimensions using them

I ended up with a set of tables ready to be populated with metadata though I am not going populate them just yet as I think it is worthwhile running through an interface import profile first.

An import profile provides all the required information about the dimensions and properties that are going to be imported, it defines the type of actions to take when loading members and their properties.

The easiest way to understand them is to go through the steps in creating one.

From within the dimension library go to File > Import > Create Profile

A profile name is entered and the Import Type is set to “Interface Tables”.

The application is set to “Shared Library” as the intention is to load to the already created Shared Dimensions, if you wanted to load directly to an application it can be selected from the dropdown.

The Data Source was created in the last part and holds the connection information to the database where the interface tables are located.

The Map Dimensions section defines the source dimensions from the interfaces tables and which dimension they will be mapped to in the Shared Library.

The source dimension information is picked up from the interface table IM_DIMENSIONS and column C_DIMENSION_NAME

There are a number of options under Process Type.

•    Merge as Primary—Merge as Primary processes all sections and adds new dimensions, members, relationships, properties and associations that exist in the source, but do not exist in the Shared Library or target application. No dimensions, members, relationships, properties, or associations are removed. If a dimension, relationship, member, or property specified in the source exists in the Shared Library or target application it is overwritten with the value specified in the source. Properties not included in the source are unchanged in the Shared Library or target application.

•    Merge as Move—This mode only processes members in the IsPrimary column that are set to “True” and ignores any others, so basically just looking at shared members.

•    Replace—All new elements are added and all property updates are made. Then, any members or member relationships that are not specified in the source are deleted from the Shared Library or target application. This option would be used if you wanted to rebuild the dimension hierarchy.

There are two options available for Reorder Type

• Merge to Top —Places new imported child members at the beginning of the child list under their parent.

• Merge to Bottom—Places new imported child members at the end of the child list under their parent.

If “Reorder Existing Members” is selected then the action depends on what has been selected in “Reorder Type”

• If “Merge to Top” is selected, the first child member in the import source becomes the first child under the parent, all imported child members are sorted to match the order in the import source, and any existing members not in the import source will be pushed to the end of the child list in their existing order.

• If “Merge to Bottom” is selected, the last child member in the import source becomes the last child under the parent, all imported child members are sorted to match the order in the import source, and any existing members not in the import source will be pushed to the beginning of the child list in their existing order.

If you want a more detailed example have a look at the following section in the EPMA admin documentation – “Reordering Existing Member Examples”

The “Dimension Mapping” section provides the functionality to map each dimension source member property to a target member property in the Shared Library.

As I have created the source table columns with the same naming convention to the member properties in EPMA it is a direct mapping.

There is another parameter that can be set for each member that defines how existing property values are dealt with.

• Clear Before Importing – if selected values will be cleared out, therefore making values match the import source exactly. If not selected, values will be merged in with existing values and all existing values will remain.

• Allow Overwrites with Blank –if the source value is blank the target property will be overwritten with the default property, so for instance if selected and there was no value in the DataStorage column the default value “StoreData” will be applied.

No need to execute the profile as there is no metadata in the hierarchy tables yet, I will be covering loading the metadata in the next instalment.

Loading to EPMA planning applications using interface tables – Part 2

2011-10-02T04:11:00.000-07:00

In the first part I went through an introduction to what this series is about and covered getting the core planning application created in EPMA.

In this part I want to look at interface tables and end up with them built ready to load metadata into.

Interface tables are basically just a set of relational staging area tables that are populated before pushing into EPMA and these can be populated from any designated source, the ultimate end game in this series of blogs is to use ODI to populate the tables and then execute the process of moving them into EPMA.

The first step that needs to be undertaken is to create the interface data source and this is done in the EPM system configurator, I am not sure why this functionality is not yet available through workspace though I am sure it will be there one day.

Setting up the interface data source is basically configuring the connection to the RDBMS where the tables will be held, you will need to have created a schema/database first.

Start up the EPM System Configurator, uncheck all, expand Foundation > Performance Management Architect and select “Configure Interface Data Source”

As this is the first time configuration “Create a New Datasource Link” is selected.

Select the RDBMS type that is going to be used for the interface tables.

A name for the data source is entered and this name that will be used in EPMA.
The connection details to database are entered.

Now there is the option to “Create interface tables”, I have selected this option and what it does is create a set of sample interface tables.

Once the interface tables have been created this is where the fear can set in.

As you can see a hell of a lot of tables have been created and you start to wonder whether this interface table route is going to be worth it, if you select one of the tables and view the amount of columns the fear gets worse but in the words of the great The Hitchhiker's Guide to the Galaxy “Don’t Panic!”

These are a set of sample tables and are not required, the columns in the tables relate to all the possible properties across Essbase, Planning and HFM.

The fact that I am initially only looking at planning and the approach I am taking is to only load metadata to dimensions which have been already created plus associations applied then honestly it is not as bad as it first looks.

The set of tables that are of more value are prefixed with IM_ which are not in the list above.

The rest of the tables that will be required are related to the metadata to be loaded for each dimension which I am going to manually create and will go through shortly.

IM_LOAD_INFO

This table basically allows grouping of metadata to be loaded, when importing dimensions in EPMA there is an option to select the interface load ID, the load ID is a numerical value.

Each dimension hierarchy table (not created yet) will have a column called LOADID that will relate back to the IM_LOAD_INFO table, it will become much clearer as we progress.

I have manually added a record to the table, it is not actually that important that the table keeps getting updated it could easily be set up once and then left alone.

I_LOAD_ID – I have started out with the value of 1

C_SOURCE_SYSTEM – Anything can be entered here but as I am initially going to be loading from files I have just set it to “FLAT_FILE”, it could be there are multiple metadata loads and you could have load ID 1 loading from flat files and Load ID 2 loading from a different source such as a warehouse.

C_USER_LAST_UPDATED – Once again anything can go here it all depends if you want to keep the user details.

D_DATE_LAST_UPDATED – Timestamp field.

C_LAST_UPDATE_LOGIN – Again anything can go here

IM_DIMENSION_ASSOCIATION – I am not going to be using this table as the dimension associations have already been applied, I may cover this at a later stage.

IM_DIMENSION

This table holds the information about the dimensions that are going to be loaded.

I_LOAD_ID – The load ID links back to the previous table IM_LOAD_INFO, so a corresponding value would be entered for the interface load.

C_DIMENSION_NAME – This is the name of the dimension in EPMA that is going to be loaded to.

C_DIMENSION_CLASS_NAME – This is the dimension type e.g. Account, Entity, Generic.

C_MEMBER_TABLE_NAME – This is the name of the table that will hold the member information. I am not going to be using this column as it can be all covered in the next column.

C_HIERARCHY_TABLE_NAME – This is the name of the table which will hold all the hierarchy and member information.

C_PROPERTY_ARRAY_TABLE_NAME – This is the name of the table which contains property information. I am not going to be using this column.

C_DIM_PROPERTY_TABLE_NAME – This is the name of the table containing all the dimension property information. I am not going to be using this column.

So out of all the columns I only need to populate four of them.

I have set the I_LOAD_ID to 1 to match what was set in the IM_LOAD_INFO table, so if a dimension import from interface tables is executed in EPMA against load ID 1 the required dimensions to be loaded and the tables holding the information are known.

For the records in column C_HIERARCHY_TABLE_NAME I have entered table names that will hold all the hierarchy/member properties, I am going to go through the process of creating them now.

It is possible to look at the HS_<DIMNAME>_HIERARCHY AND HS_<DIMNAME>_MEMBER sample tables and pick out the columns relating to Planning and then create a table based on them.

To make it easier I have gone through each dimension that I will be loading metadata to and generated the full list of columns that can be populated, many of them don’t have to be populated just like when using other methods to load metadata to a planning application

VERSION

ENTITY

SCENARIO

ACCOUNT

SEGMENTS (custom dimension = Generic dimension type in EPMA terms)

The columns in each of the tables do not include any attribute related information yet, I will cover attributes and UDAs at a later stage.

In the next part I was going to look at introducing ODI to populate the interface tables but I think it is worth covering off import profiles first.

Loading to EPMA planning applications using interface tables - Part 1

2011-10-02T03:56:00.000-07:00

In the mass of blogs I have written about using ODI with the Hyperion adaptors there is one area I have not touched upon and that is EPMA interface tables, it is true there are no knowledge modules available to make life easier when loading to EPMA type applications but by using interface tables it certainly is achievable.

In the past I have tried to steer clear of this area due to the numerous problems with EPMA but overtime many of the major flaws have been addressed and I thought it was about time to cover off ODI and interface tables, I will be perfectly honest and say using ODI with classic planning applications is still definitely my preferred option and it offers much more flexibility.

In this series of blogs I am going to basically try and replicate the sample planning application and use interface tables to populate metadata for a number of the dimensions, I am going to try and approach it from the angle of being used to loading to planning applications with the outline loader or ODI methods to make the transition over to EPMA a little bit smoother. It is not going to be all about ODI in fact you could substitute the ODI part for another tool of your choice.

There are a few important points to highlight before I start.

•    It is going to be based on EPM 11.1.2.1, the concept should be similar for earlier versions but I can’t guarantee it will be exactly the same.

•    The version of ODI being used will be 11.1.1.5 but everything covered will be valid for earlier versions like 10g.

•    This is not a guide to EPMA and there will be an assumption you have used EPMA before or have a basic understanding of it but I will try and explain as much as possible as I go along.

•    There is going to be assumption that you are familiar with the basics of ODI.

My approach is going to be that the planning application is going to be built first and the dimensions and associations created, this is the same concept that would be undertaken if using the classic planning method as the core application would be available before loading to it. Using this approach I feel that it is much easier to grasp the concept of what is required to use interface tables.

I am not sure how many parts there will be to this blog as I am going to try and spread it out and not cram too much into each session, in this first part I am really just going to get the basic core application built in EPMA.

So to start with I am going to create the applications dimensions in the shared library, yes this is a manual process but it only needs to be done once and really does not take much time at all.

Remember it is going to be based on the sample planning application so the dimensions created will be Account, Year, Period, Entity, Currency, Version, Scenario, Segments, Alias.

I have suffixed each of the dimensions in the shared library with “_Shared”, just to point out that this is not a requirement it was just my way of highlighting they are shared dimensions. The dimensions have been simply created using File > New > Dimension and then entering the dimension name and type.

Dimension        Type
Period_Shared        Period
Currency_Shared    Currency
Account_Shared    Account
Year_Shared        Year
Entity_Shared        Entity
Version_Shared        Version
Scenario_Shared    Scenario
Segments_Shared    Generic
Alias_Shared        Alias

I have cheated a little and manually populated the Period,Currency,Year and Alias dimensions. It is certainly possible to use interface tables to perform this step but due to the approach I am taking I need them to be available to be able to create the core sample application, also these dimensions are pretty much remain static.
The rest of the dimensions will be populated using interface tables.

The only additional steps was to create the associations (right click a dimension and select create association) for the currency dimension and between alias and all the dimensions

Hopefully this should all make sense as I did point out there was going to be an assumption of a basic understanding of EPMA.

On to creating the application, first of all make sure you have a relational schema/database created to hold the planning application.

File > New > Application

Basically I have replicated all the settings of the default sample application

All the dimensions from the Shared Library are associated with the dimensions in the application.

The properties of the sample application were replicated and then the application validated, any errors can easily to be resolved.

As “Deploy when finished” was enabled the Deploy window will display, if no Data Source has been created it can be generated at this point.

The application has been deployed successfully so in the next part I can move on to looking at interface tables.

Get up and running with Smart View 11.1.2.1.102

2011-09-18T02:06:00.000-07:00

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

ODI Series – Issues with 11.1.1.5 and the Hyperion knowledge modules

2011-09-16T05:21:00.000-07:00

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.

ODI Series - Summary

2011-08-29T02:07:00.000-07:00

I thought I would take this opportunity to put together a full list of links to all the ODI related blogs I have written, as I try to cover all areas of EPM I feel the ODI ones are becoming fragmented and it is worth putting together a summary page that can be used as a start point and one that I will update in the future as and when I cover ODI subject areas.

This is probably one of the few posts that I don’t have to spend hours taking screen shots and uploading them.

Many of the earlier posts were based on ODI 10g but to be honest the concept still holds true in 11g and they still can be used as a reference point, I know I still use them because I have not had the chance use ODI as much as I would like lately and with my failing memory when I do use it I need to try and remember how I went about it.

No point in babbling on but if you feel I have not covered an area or you have some ideas for articles then just get in touch, there have been quite a few posts that I have written due to being contacted one way or another.

So here is the table of contents for the ODI series.

ODI Series Part 1

Introduction and installation of ODI, this was based on 10g. If you are looking to install 11g then there are a number of different blogs that have covered off the installation.

ODI Series Part 2 - The Agent

Covers the configuration of an agent on 10g, the concept is similar if configuring a standalone agent of 11g the main difference is if running on windows then the method to run the agent as a service has changed as it can be controlled by using OPMN which I covered here.

ODI Series Part 3 - The configuration continues

A run through of the topology manager and the configuration for using flat files, rdbms, essbase and planning.

ODI Series 4 - Enter the Designer

An introduction to the designer and process to building an interface to load metadata from a flat file to a planning application.

ODI Series Part 5 - SQL to Planning

Covers the process for loading planning metadata from an rdbms source.

ODI Series Part 6 - Data load to planning

How to load data through the planning layer.

ODI - Getting text data into planning

An alternative solution and not the official method to load text data into planning, I am sure at the time of writing the blog this was the only method but I know in subsequent releases of ODI and planning then loading text data can easily be achieved by using the adaptor.

ODI - Loading text member data to planning

Part of this blog covers the direct method of loading text member data to planning, I believe this is only possible from planning 11.1.1.3+

ODI & Planning - Brief look into file manipulation

As requested an introduction to manipulating metadata from a flat file source and loading into planning.

ODI Series - Planning and multiple UDAs

How to load multiple UDAs into a planning application.

ODI Series - Handling errors with Planning/Essbase loads

A run through of a few possible methods of proactively taking actions if there are errors in Planning/Essbase loads.

ODI Series - Planning 11.1.1.3 enhancements

An overview of the list of enhancements when using Planning 11.1.1.3 or later with ODI.

ODI Series - Planning 11.1.1.3 enhancements continued

There wasn’t enough time to go through all the enhancements in the first instalment so in this part I go through the remaining ones.

ODI Series - Renaming planning members

There is currently no direct method of renaming planning members using the adaptor so in this post I go through a possible solution to achieve the renaming.

ODI Series - Deleting planning members

Step by step process overview for deleting planning members and some of the issues that may be faced.

ODI Series - When is a planning refresh not a refresh

Even though an interface may have completed successfully in the Operator there may be a possible issue with the planning refresh which I highlight and give a possible solution to the issue.

Planning 11.1.2 - additional ODI functionality mixed in with a helping of ODI 11G

A look at the additional features available when using Planning 11.1.2+ and ODI.

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

Covers an alternative way to load smart list data by loading directly to essbase instead of going through the planning later.

ODI Series - Loading Smart List data into planning

The official way of loading smart list data by loading through the planning layer.

ODI Series - Loading Essbase Metadata

Loading metadata from a flat file source into an essbase database.

ODI Series - Loading data into essbase

The title pretty much describes the post which is all about loading data into an essbase database.

ODI Series - Extracting data from essbase

Covering the three different methods to extract data from essbase databases.

ODI Series - Essbase Outline Extractor

Covers the process of extracting metadata from an essbase database.

ODI / Essbase challenge - Extracting alternate alias member information

At the time of writing this post there was no method to extract alternate alias member information using the essbase adaptor, I cover a method of customising the adaptor code to make it achievable. The code that I developed was later used by Oracle and a patch release of ODI made it possible to extract the information, I covered the patch here.

ODI Series - Loading SQL Metadata into essbase and its problems

A run through of an issue that was highlighted on the Oracle forums when loading essbase metadata from an rdbms source and changing the IKM rules separator options to values other than the default comma.

ODI Series - Extracting essbase formula issue

Highlights and provides a solution to an issue that maybe encountered extracting essbase member formulas that span over multiple lines.

ODI Series - Essbase to Planning

As there is currently no adaptor which allows the extraction of metadata from planning I go through one method to get around and this and extract the information from an essbase database and then transform and load into a planning application.

ODI Series - Deleting essbase members part 1

First of a three part series on deleting essbase members, this part goes through the standard method of using the remove unspecified option in a load rule.

ODI Series - Deleting essbase members part 2

In this second part I go through a method to delete members where the source file only contains the members that need to be removed.

ODI Series - Deleting essbase members part 3

In the final part I use a combination of Jython, essbase Java API and a customised KM to delete members based on members supplied from a source file.

ODI Series - Renaming essbase members

Based on the final part of the deleting essbase members, modifications to the Java code allow for renaming members based on a source containing the original and new member names.

ODI Series - Essbase/Planning - Automating changes in data

Using Change Data Capture (CDC) and the simple journalizing method to automate the process of loading data into an essbase database.

ODI Series - Essbase/Planning - Automating changes in data - Part 2

Using Change Data Capture (CDC) and the consistent set journalizing method to automate the process of loading metadata to a planning application.

ODI Series - Release Update

Highlights important patch updates to fix bugs with the essbase adaptors, please note this was 10.1.3.4.7_01 so no doubt you are already running a newer release than that.

ODI Series - Patch Update - Essbase data load issue resolved

A review of a patch released to fix a bug with loading data to essbase.

ODI Series - Problem with latest essbase patch release

The patch released to fix a bug with loading data to essbase did fix the original issue but now generates a new bug which I log with Oracle.

ODI Series - Patch Update - 10.1.3.5.5

A patch is released that finally does fix the issues with loading data to essbase.

ODI Series - Essbase related bug fixes

A delve into a couple of essbase related bug fixes, one of them is based around the order of members when extracting essbase metadata and the other allowing to extract alias information not only from the default table.

ODI Series - Issues with 11.1.1.5 and the Hyperion knowledge modules

A look at a couple of issues relating to 11.1.1.5 and loading metadata to essbase and loading metadata to planning using memory engine as the staging area.

ODI Series - Will the real ODI-HFM blog please stand up

Covers the configuration required to start using the HFM adaptor, some of the errors that may be encounter and finally successfully reversing a HFM application.

ODI Series - Loading HFM metadata

An overview of the knowledge modules available for HFM and then a step by step process to load metadata to a HFM application from a flat file source.

ODI Series - Loading HFM data

Step by step process to load data to HFM and a description of each of the IKM options available.

ODI Series - Extracting data from HFM

Overview of how to extract data from HFM and a description of the KM options available.

ODI Series - Putting it all together

So you have a number of interfaces, procedures, variables and you are looking to automate your processes, this post covers putting it all together with the use of a package.

ODI Series - A few extra tips

Covers loading multiple UDAs to an essbase database and how to sum up flat file source data when loading through the planning layer and not direct to essbase.

ODI Series - The final instalment

Definitely not the final instalment of the ODI series and in this post I go through generating scenarios and scheduling them in the Operator, I also go through how to execute a scenario using the Java SDK in 10g.

ODI Series - Excel functionality

A look at the process of extracting data from an excel file and loading into a relational table.

ODI Series - Reversing a collection of files from an excel template

An overview of using the Knowledge module “RKM File (FROM Excel” , this RKM provides the ability to reverse files and maintain the structure definition based on the information held in an excel template.

ODI Series - Web Services

In the first of a four part series looking at Web Services in 10g I go through setting up Axis2 and how to upload the ODI public web service to it, this allows the use of the OdiInvoke service and its five operations with one of them being the execution of scenarios using a SOAP request.

ODI Series - Web Services Part 2

Using Java to interact directly with the ODI web services.

ODI Series - Web Services Part 3

Step by Step configuration of Data Services and an example of how they could be used to load data to essbase.

ODI Series - Web Services Part 4

A look at the ODI tool - ODIInvokeWebService and putting it to practice by invoking EPMA web services.

ODI Series - Processing all files in a directory

Step through of a method to process all files in a directory and load them to a target relational table.

ODI Series - Quick look at user functions

Ever wondered what are user functions and how to use them, if so have a read of this post.

ODI Series - Executing Hyperion Business Rules

An in-depth look at using ODI to execute business rules.

ODI Series - Executing Hyperion Business Rules Part 2

If part 1 didn’t satisfy you then in this part I go through another method to execute business rules by customising a knowledge module and using Jython

Using Lifecycle Management (LCM) with ODI

A look at the possibilities of using ODI and LCM, in the example I go through the loading Cell Text to Planning

Managing ODI 11g standalone agents using OPMN with EPM 11.1.2

Step by step process of configuring a standalone agent to be controlled by OPMN, the agent is configured on an EPM hosted environment.

If you are looking for all other areas of ODI not covered here then I can recommend these excellent blogs -

ODI Experts

BI-Quotient

Data Integration

Still can't find what you are looking for or need help then the ODI forum is your next stop

ODI Forum

ODI Series - Deleting planning members

2011-08-14T11:41:00.001-07:00

I have been asked numerous times lately how to delete planning members using ODI, I did cover loading planning metadata way back in one of my early blogs but have never covered deleting and even though it is quite similar to loading metadata I thought I may as well go through the steps and highlight some errors that may be encountered, it also stops me having to repeatedly explain the process and I will be able to be lazy in future and just provide the link to this post.

The process may have been covered elsewhere on the internet but I wanted to make sure the series I have wrote is complete.

If you have experience using the Hyperion planning adaptor with ODI then this post will probably be of no benefit to you as it is aimed at beginners.

There are going to be some assumptions though before starting.

• The planning application is of the classic type and not EPMA.

• The required Knowledge modules have been imported
(source and target “IKM SQL to Hyperion Planning”)

• The planning application has been reversed into a model and Datastores using the KM
- “RKM – Hyperion Planning”

• The source Datastore containing the members to be deleted has been created whether it be a file or a relational table.

If you don’t know how to do the above then it is worth reading through the here and here as I cover off the basics that you need to know to get you up and running, I didn’t think there was any point in going over it again.

I will be going through the steps using ODI 11g but the principals are exactly the same in 10g.

If you take a look at a reversed planning applications model and then expand any dimension Datastore you will notice a column called “Operation”, now if you have only ever loaded metadata then there is a distinct possibility that you have ignored the column as when the column is left blank during an execution of an interface the default value is used and this is “Update”.

If you want to delete members then this column becomes far more important as there are a number of different values that can be set depending on the type of deletion.

The types of delete operations available are

• Delete Level 0
• Delete Descendants
• Delete Idescendants

These operations may seem very obvious to what they can achieve but I will go through each one of them so that is clear.

Before I go through them it is worth highlighting I am going to use the planning sample application and the members deleted are all going to be from the Segments dimension.

The first of the operations is “Delete Level 0”, using this operation will delete a member if is a base level member so basically it means if it has no children then it will be deleted.

To start with I need to have a list of level 0 members I want to delete in the source.

The source technology for the deletions is Oracle, if you are intending of using a flat file as the source then the process is exactly the same.

I have created a model and reversed a table called “DEL_MEMBERS”, the table has one column called “MEMBER” which contains the members to be deleted, as I mentioned earlier I am assuming you have already set up your source Datastore but if you are confused and don’t know how to then have a read here.

In the table there are two records “IPOD304” and “MP3” and if you look at the hierarchy above you will see both these members are level 0.

The source and target are ready so this means we can create an interface which will process the source records and attempt to delete what is contained in those records from the planning applications target dimension.

When creating an interface you will need to set the staging area to be different from the target as planning has no SQL capabilities and if you don’t set this then the interface will generate warnings.

The warning generated is “Staging Area's technology doesn't have the Where capability which may lead to errors at execution time. You may want to switch the Staging Area to another technology (Memory Engine for instance).”

As well as receiving warnings the planning knowledge module will not be selectable until the staging area has been defined.

If I am using an RDBMS as a source technology then I would set the staging area to be the same as the source, if using a flat file as the source then more than likely I would use the “Memory Engine” as the staging area due to the low number of records to process and complexity of the transformation which are more tuned to that of being handled in memory.

If using a flat file as the source and the file was large or the transformation of the data were complex then I would consider using an RDBMS as the staging area, this would first load the flat file into a relational table, perform any transformations and then delete the members, it is worth testing with the “Memory Engine” first and any issues are encountered then look into changing the staging area.

One thing I do notice in 11g is you can’t set the staging area until the target Datastore in the interface has been designated.

Once the interface has been created go to the Mapping tab and drag the source Datastore on to the source area and then drag the target planning dimension Datastore onto the target area. If the staging area has not been set this is the time to go back to the Overview tab and set it.

The mapping on the target are then created, in this example it is a simple one to one mapping so all that is required is the target dimension name column (Segments – this will depend on the dimension you are trying to delete members from) is mapped to the source, Target “Segments” maps to source DEL_MEMBERS.MEMBER

If the source is a file then the mapping will need to be set to execute on the staging area.

In the Operation column the type of operation is defined by entering ‘Delete Level 0’, make sure it is enclosed in single quotes or an error will be generated when executing the interface.

It is also necessary to set the mapping to execute on the “Staging Area” for the operation column or once again an error will be generated due to the target not having any transformation capabilities.

You don’t have to hardcode the operation type in the target column you could just as easily have an extra column in your source to define the type of deletion which you would just map to the target Operation column.

If at this point you are unable to select the required Knowledge Modules then it is probably down to the KM’s have not been imported or the staging area has not been set.

The important options to be set in for the IKM are

LOG_ENABLED – set whether an output log for the interface is generated.
LOG_FILE_NAME – full path and filename for the log.
LOG_ERRORS – set whether any errors during the deletions are logged.
ERROR_LOG_FILENAME – full path and filename for the error log.

The error files will be generated on the machine that the agent being used in the interface is executed against.

MAXIMUM_ERRORS_ALLOWED – The default is 0 which means if any deletion errors are generated the interface does not stop, if you want the interface to stop after a set number of errors then use this option.

REFRESH_DATABASE – This executes a planning refresh as the interface is only deleting the members from planning and if you want the deletions to be pushed to essbase then the option will need to be set to yes.

If running deletions against a number of dimensions then only set the last interface option to yes and remember that if any dense dimension members have been deleted then the time to refresh will depend on the size of the data in the essbase databases.

Once the options have been set the interface can be executed.

Once the interface has completed then check the operator for success and if you want to view how many records have been deleted then open the “Report Statistics” step. Do not be alarmed by the warning sign against this step as it doesn’t mean the interface has failed it is just the way the adaptor has been designed.

If you want to update the “Report Statistics” step to report in a more meaningful manner and remove the warning sign then have a read here of an article I wrote on how to change it.

If there are any rows rejected or the interface failed then the first place to check is the logs that would have been generated if enabled in the KM options.

If everything has run through without any issues then it is also worth checking the hierarchy in planning to be certain the members have been deleted.

If any of the members deleted also have shared members in the hierarchy they will also be deleted using the above method.

So say you only wanted to delete the shared members “IPOD304” and “MP3” from a hierarchy then you would probably think the following would work.

The adaptor unfortunately does not work in this way and even though the interface completes successfully the error log will produce errors.

The error descriptions do not directly point out the issue though it is clear the method does not work.

If you think about it the hierarchy may have multiple instances of the same shared member so the adaptor doesn’t really know if you want to delete them all or just some of them.

To be able to delete shared members then the parent of the member to be deleted is required so to get around this the Datastore can be updated to include a parent member column.

The original interface is updated to include a mapping from source to target “Parent” column and executing the interface will delete members based on the member and its parent defined in the source.

So what happens if the interface is set up to delete level 0 members and it tries to delete a member that is not level 0.

In the above interface the source contains a member called “AG” and if you look at the hierarchy this is not a level 0 member.

The interface will not fail but this time you will get a more meaningful message in the error log stating “Attempted to delete a non level 0 member using the delete level 0 operation.”

Another common misunderstanding when creating a deletion interface is the concept that it is possible to use the Alias column instead of using the member column.

In the above interface the alias is mapped instead of the member and once again the interface will run through successfully but there will be errors generated in the log.

If you only have the alias in the source then it is certainly possible to generate the member name but this requires mapping to the planning applications relational tables and not one to be covered today, if you are interested in understanding how to go about it then just get in touch.

Right, on to the next type of operation “Delete Descendants” and this basically means delete all members which are descendants of the current member defined in the source.

In the above interface the source member is “AG” and the operation is defined as “Delete Descendants”

All the descendants of the member “AG” have been deleted.

Something to be aware of when deleting descendants if all the children of a member are deleted then the parent member can still contain data, it might be that fine but just something to be sure that is what you want as you may need to run a calc after the deletion.

Watch out if the parents data storage is set “Dynamic Calc” and all its descendants are deleted as level 0 members cannot be “Dynamic Calc” unless they have a formula associated with them.

The final operation type is “Delete Idescendants” which will delete the current member defined in the source and all its descendant members.

In the above interface the source member is defined as “AG” and the operation is set to “Delete Idescendants”.

The member “AG” and its descendants have been deleted from the hierarchy.

If like in the above interface and the operation type is set to “Delete Idescendants” and a level 0 member is defined in the source the level 0 member will still be deleted as it is still a valid operation.

Another way of defining the type of operation is to use an ODI variable.

The variable can be used in the target mapping which adds more usability to the interface. If you are going to use a variable you will need to create a package first and then drag the variable to the interface to either set or declare it and then add the interface as the next step.

If you want to understand the query that is being generated on the source/staging area before the deletion is being executed on the planning side then go into the operator and open the step “Load data into planning” and switch to the Code tab where the SQL being used can be viewed.

I think that covers off the basics on deleting members from planning and should hopefully get you on your way.

EAS 11.1.2 session timeout and workspace tips

2011-07-24T00:53:00.000-07:00

I am not sure if it is just me but the session timeout in 11.1.2.x EAS has slowly managed to annoy me more and more, if you leave EAS unattended for a period of time then you will likely be hit with

The session times out, now you may be saying stop whinging and log in again which is perfectly acceptable and I have been doing that for a long while but I thought it was time to put a stop to it. I have also been wondering why it has not bothered me in the past, maybe my patience has started to run out these days.

Anyway here are the quick and easy steps to increase the timeout.

On the server where EAS is installed and in location <MIDDLEWARE_HOME>\EPMSystem11R1\products\Essbase\eas\server\AppServer\InstallableApps\Common there will be a file called eas.ear (Enterprise Archive file format), this file basically contains the EAS web applications which are deployed each time the EAS Weblogic managed server is started and within the file there is a configuration xml which defines the session timeout.

I will be using 7zip to open and extract from the ear file as personally I find it an excellent free piece of software, if you are going to be making any changes I recommend taking a backup of the eas.ear file first.

The eas.ear file is opened with 7zip and within the file there are a number of war (web application archive) files which each relate to the different EAS web components, the file eas.war is the one we are interested in as it is the main EAS web application.

The eas.war file is opened and then the directory WEB-INF

The WEB-INF directory contains the web.xml file which defines the structure of the web application; this file is to be edited so it is extracted from the archive.

The file is opened and towards the end of the file there is the line -
<session-timeout>45</session-timeout>
The default value is set is 45 which means an EAS session times out after 45 minutes of inactivity.

The value can be updated to a timeout that suites you, once updated save the file and drag it back into the archive and save the ear file, restart the EAS web application and the new timeout will be effective.

I did check back at the default value used in version 11.1.1.3 and it was set to 1440 (24 hours) so no wonder it didn’t annoy me in the past.

From one annoyance to another, if in 11.1.2.x you have ever tried to open multiple sessions of workspace then you will hit a slight problem which is you can’t seem to do it anymore. Now there may be many circumstances where you want to have multiple instances of workspace open say for instance say you log in with an admin account and are making some changes to an application and you want to test them at the same time using a different user account, if you try and log in while the admin account is logged in it will just take you straight to the currently logged in session.

Ok, you can log out and log in again or say use IE and Firefox together but there is another method.

When logging into workspace append the parameter multi_process=true as shown above.

This example is using IE8 but is similar in other supported browsers. Go to File > New Session which will open another browser

Log in again using the multi_process=true parameter and there you have it multiple instances of workspace running at once.

Seeing as we are on the topic of workspace I thought I would take this chance to briefly go through some of options that available through "Workspace Server Settings".

In the past I covered speeding up authentication through the "Enabled Products" option which you can read about here.

Enable Installer Menu Items in Workspace

The default option is "Yes"

Depending on the products installed workspace will display different available client downloads.

Setting the option to "No" will leave an empty Install menu.

Enable User Display Name

The default option is "No"

This will display the login username in workspace.

Changing the option to "Yes" will display the full name of the user logged in.

Enable Native User Password Change

The default option is "Yes"

This allows users to change their password through the Tools menu; if the option is changed to "No" then the "Change Password" menu option will no longer be visible.

Required Logon Role

The default option is "None" which basically means anybody with an account even it has no provisioning roles applied can still log into workspace.

The other options available are -

Reporting and Analysis Role

A user must be assigned a "Reporting and Analysis" role or a "Foundation" role to be able to log into workspace.

Global Role, Reporting and Analysis Role, or Application Role

A user must be assigned a "Reporting and Analysis" or a "Foundation" role or an Application role such as Planning, HFM, Financial Close etc to be able to log into workspace.

If a user does not have a required role provisioned to them then the error message generated by workspace will be an authentication message which sometimes could be misleading as it doesn’t mean the username/password is incorrect.

Message File

This allows a message to be sent to all users logged into workspace or once they log into workspace.

The "Client Message Polling Interval" default is zero which means the messaging system is disabled, if it is set to anything other than zero then this will be the time in minutes that new messages are checked for.

A user will be greeted with a message like above, unfortunately HTML code cannot be used in the message.

Accept Credentials on HTTP GET Request

The default is "No"

If the option is set to "Yes" then it is possible to log straight into workspace using the sso_username and sso_password parameters.

Client Debug Enabled

The default option is "No" if set to "Yes" then a number of debugging features are available in workspace, now I must admit I don’t really understand some of them but I thought I would show what is available.

Using the URL http://<workspaceserver>:port/workspace/debug/configInfo.jsp will provide configuration information around all the installed products.

If the parameter debug=true is appended to the workspace login URL then you will notice a window called "Logging" is available in the bottom right corner.

Double clicking the window will display a mass of logging messages.

If the parameter module=wksp.widgets if appended to the workspace URL then a number of test cases and debugging utilities are available, I have no idea of what use they are but maybe somebody will find them interesting.

I think I will leave it there for today, enjoy!

EPM 11.1.2.1 compact deployment update part 2

2011-07-03T12:07:00.000-07:00

This week’s blog is a continuation from the last one on compact deployments, it is not essential that you have read the last blog but for this one to be of any benefit then you would have had to successfully completed a compact deployment.

In the original post on EPM compact deployments I left two open questions and this week it is time for the second question to be covered off which only relates to windows OS deployments.

Once a compact mode has completed successfully a batch script named startEPMSystem.bat is generated to start up the WebLogic managed server, no windows service is created like the other WebLogic deployed products so to make life a little bit easier I set myself a task to create a windows service that will start up the EPM managed server.

Now I know it is probably possible to try and use the Weblogic install service utility but I didn’t have much success with it and I also I wanted the service to function like the other products in the stack so here are the steps I carried out to set up the service.

In a standard deployment once the Weblogic web applications have been started and if you look in task manager you will see a process for each managed server, each process has the same description as in fact they are all the same executable just with a different name.

Each of these executables are called when the service is started and they all reside in <MIDDLEWARE_HOME>\user_projects\domains\<domain name>\bin

The executables originate from <MIDDLEWARE_HOME>\EPMSystem11R1\common\utilities\JavaService, there is a 32bit and 64bit version of the executable.

When a Weblogic managed service is deployed it copies the file from the above directory into <MIDDLEWARE_HOME>\user_projects\domains\<domain name>\bin and renames it depending on which web application is being deployed.

I copied HyslJavaServiceAMD64.exe into <MIDDLEWARE_HOME>\user_projects\domains\epmsystem1\bin and renamed it to EPMSystem.exe

Usually the default deployment domain name is EPMSystem but in my compact deployment I called it epmsystem1.

If you take a look at any of the start up commands for the windows services you will see/ServiceName= and /RegKey=
these define the registry keys used and are called upon when the windows service is started.

The keys are all stored under HKLM\SOFTWARE\Hyperion Solutions and contain important variables and various paths to pass into the WebLogic managed server for it to be able to function correctly.

For the windows service I am creating I would need to create a new registry key for the EPM Managed server.

When a compact deployment is run it creates a file called setManagedParamsEPMserver.bat in <MIDDLEWARE_HOME>\user_projects\domains\<domain name>\bin

This file contains all the necessary information for the registry key so the next step was to transfer this information into a .reg file.

Instead of creating the file from scratch I did use a registry key that had already been created from another EPM 11.1.2.1 instance, I used the foundation services registry key as a starting point and these keys can be found in <MIDDLEWARE_HOME>\user_projects\<instance_name>\bin\deploymentScripts\installServiceScripts

The registry file needed a clean up as some of the options are repeated two or three times.

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Hyperion Solutions\EPMSystem0\EPM11]
"JVMOption1"="-Dweblogic.Name=EPMServer0"
"JVMOption2"="-Dweblogic.RootDirectory=E:\\Oracle\\Middleware\\user_projects\\domains\\epmsystem1"
"JVMOption3"="-Dweblogic.j2ee.application.tmpDir=E:\\Oracle\\Middleware\\user_projects\\domains\\epmsystem1\\servers\\EPMServer0\\tmp"
"JVMOption4"="-Dweblogic.management.server=http://localhost:7001"
"JVMOption5"="-Ddomain.home=E:\\Oracle\\Middleware\\user_projects\\domains\\epmsystem1"
"JVMOption6"="-Dplatform.home=E:\\Oracle\\MIDDLE~1\\WLSERV~1.3"
"JVMOption7"="-Dwls.home=E:\\Oracle\\MIDDLE~1\\WLSERV~1.3\\server"
"JVMOption8"="-Dweblogic.home=E:\\Oracle\\MIDDLE~1\\WLSERV~1.3\\server"
"JVMOption9"="-DEPM_ORACLE_HOME=E:\\Oracle\\Middleware\\EPMSystem11R1"
"JVMOption10"="-DEPM_ORACLE_INSTANCE=E:\\Oracle\\Middleware\\user_projects\\epmsystem1"
"JVMOption11"="Djava.library.path=E:\\Oracle\\Middleware\\EPMSystem11R1/products/Planning/lib64;E:\\Oracle\\Middleware\\EPMSystem11R1/common/ADM/11.1.2.0/bin-64;E:\\Oracle\\Middleware\\EPMSystem11R1/bin;E:\\Oracle\\Middleware\\EPMSystem11R1/common/EssbaseRTC-64/11.1.2.0/bin;E:\\Oracle\\Middleware\\EPMSystem11R1/products/Essbase/EssbaseServer/bin;E:\\Oracle\\Middleware\\user_projects\\epmsystem1/products/biplus/bin;E:\\Oracle\\Middleware\\EPMSystem11R1/common/ADM/11.1.2.0/ODBO/11.1.2.0;E:\\Oracle\\Middleware\\EPMSystem11R1/products/FinancialManagement/Common;E:\\Oracle\\Middleware\\EPMSystem11R1/products/FinancialManagement/Client;E:\\Oracle\\Middleware\\EPMSystem11R1/common/ADM-64/Planning/11.1.2.0/bin;E:\\Oracle\\Middleware\\EPMSystem11R1/products/Essbase/eas/server/bin; "
"JVMOption12"="-DESS_ES_HOME=E:\\\\Oracle\\\\Middleware\\\\EPMSystem11R1/products/Essbase/eas/server"
"JVMOption13"="-Dcom.hyperion.planning.datamodel=64 "
"JVMOption14"="-Dcommon.components.home=E:\\Oracle\\MIDDLE~1\\ORACLE~1"
"JVMOption15"="-Djrf.version=11.1.1"
"JVMOption16"="-Dorg.apache.commons.logging.Log=org.apache.commons.logging.impl.Jdk14Logger"
"JVMOption17"="-Djrockit.optfile=E:\\Oracle\\MIDDLE~1\\ORACLE~1\\modules\\oracle.jrf_11.1.1\\jrocket_optfile.txt"
"JVMOption18"="-Doracle.domain.config.dir=E:\\Oracle\\MIDDLE~1\\USER_P~1\\domains\\EPMSYS~1\\config\\FMWCON~1"
"JVMOption19"="-Doracle.server.config.dir=E:\\Oracle\\MIDDLE~1\\USER_P~1\\domains\\EPMSYS~1\\config\\FMWCON~1\\servers\\EPMServer0"
"JVMOption20"="-Doracle.security.jps.config=E:\\Oracle\\Middleware\\user_projects\\domains\\epmsystem1\\config\\fmwconfig\\jps-config.xml"
"JVMOption21"="-Djava.protocol.handler.pkgs=oracle.mds.net.protocol"
"JVMOption22"="-Digf.arisidbeans.carmlloc=E:\\Oracle\\MIDDLE~1\\USER_P~1\\domains\\EPMSYS~1\\config\\FMWCON~1\\carml"
"JVMOption23"="-Digf.arisidstack.home=E:\\Oracle\\MIDDLE~1\\USER_P~1\\domains\\EPMSYS~1\\config\\FMWCON~1\\arisidprovider"
"JVMOption24"="Dweblogic.alternateTypesDirectory=E:\\Oracle\\MIDDLE~1\\ORACLE~1\\modules\\oracle.ossoiap_11.1.1,E:\\Oracle\\MIDDLE~1\\ORACLE~1\\modules\\oracle.oamprovider_11.1.1"
"JVMOption25"="-Dweblogic.jdbc.remoteEnabled=false"
"JVMOption26"="-DEAS_HOME=E:\\\\Oracle\\\\Middleware\\\\EPMSystem11R1/products/Essbase/eas"
"JVMOption27"="-DDISCMAN_HOME=E:\\\\Oracle\\\\Middleware\\\\EPMSystem11R1/products/DisclosureManagement/DiscManHome "
"JVMOption28"="-DDISCMAN_INSTANCE=E:\\\\Oracle\\\\Middleware\\\\user_projects\\\\epmsystem1/DisclosureManagement/discman1"
"JVMOption29"="-Doracle.deployed.app.dir=E:\\Oracle\\Middleware\\user_projects\\domains\\epmsystem1\\servers\\EPMServer0\\tmp\\_WL_user"
"JVMOption30"="-Doracle.deployed.app.ext=\\-"
"JVMOption31"="-Dem.oracle.home=E:\\Oracle\\Middleware\\oracle_common"
"JVMOption32"="-Djava.awt.headless=true"
"JVMOption33"="-Dweblogic.management.discover=true"
"JVMOption34"="-Dhyperion.home=E:\\Oracle\\Middleware\\EPMSystem11R1"
"JVMOption35"="-DHYPERION_HOME=E:\\Oracle\\Middleware\\EPMSystem11R1"
"JVMOption36"="-Dlogging.folder=E:\\Oracle\\Middleware\\user_projects\\domains\\epmsystem1\\servers\\EPMServer0\\logs"
"JVMOption37"="-Dserver.name=EPMServer0"
"JVMOption38"="-Dsun.net.inetaddr.ttl=0"
"JVMOption39"="-Djava.io.tmpdir=E:\\Oracle\\Middleware\\user_projects\\epmsystem1/tmp"
"JVMOption40"="-Xrs"
"JVMOption41"="-XX:-FlightRecorder"
"JVMOption42"="-Xms512m"
"JVMOption43"="-Xmx1046m"
"JVMOption44"="Djava.class.path=E:\\Oracle\\MIDDLE~1\\ORACLE~1\\modules\\oracle.jdbc_11.1.1\\ojdbc6dms.jar;E:\\Oracle\\MIDDLE~1\\patch_wls1034\\profiles\\default\\sys_manifest_classpath\\weblogic_patch.jar;E:\\Oracle\\MIDDLE~1\\JROCKI~1\\lib\\tools.jar;E:\\Oracle\\MIDDLE~1\\WLSERV~1.3\\server\\lib\\weblogic_sp.jar;E:\\Oracle\\MIDDLE~1\\WLSERV~1.3\\server\\lib\\weblogic.jar;E:\\Oracle\\MIDDLE~1\\modules\\features\\weblogic.server.modules_10.3.4.0.jar;E:\\Oracle\\MIDDLE~1\\WLSERV~1.3\\server\\lib\\webservices.jar;E:\\Oracle\\MIDDLE~1\\modules\\ORGAPA~1.1/lib/ant-all.jar;E:\\Oracle\\MIDDLE~1\\modules\\NETSFA~1.0_1/lib/ant-contrib.jar;E:\\Oracle\\MIDDLE~1\\ORACLE~1\\modules\\oracle.jrf_11.1.1\\jrf.jar;E:\\Oracle\\MIDDLE~1\\WLSERV~1.3\\common\\derby\\lib\\derbyclient.jar;E:\\Oracle\\MIDDLE~1\\WLSERV~1.3\\server\\lib\\xqrl.jar;E:\\Oracle\\Middleware\\user_projects\\epmsystem1/HPS/hpsfiles/config;E:\\Oracle\\Middleware\\user_projects\\epmsystem1/DisclosureManagement/discman1/lib;"
"JVMOptionCount"=dword:0000002c
"StartIn"="E:\\Oracle\\Middleware\\user_projects\\domains\\epmsystem1"
"StartupParamCount"=dword:00000000
"SysErrFile"="E:\\Oracle\\Middleware\\user_projects\\epmsystem1\\diagnostics\\logs\\services\\EPMSystem-syserr.log"
"SysOutFile"="E:\\Oracle\\Middleware\\user_projects\\epmsystem1\\diagnostics\\logs\\services\\EPMSystem-sysout.log"
"EnvCount"=dword:00000001
"JavaDll"="E:\\Oracle\\Middleware\\jrockit_160_20\\jre\\bin\\jrockit\\jvm.dll"
"Env1"="PATH=E:\\Oracle\\MIDDLE~1\\patch_wls1034\\profiles\\default\\native;E:\\Oracle\\MIDDLE~1\\WLSERV~1.3\\server\\native\\win\\x64;E:\\Oracle\\MIDDLE~1\\WLSERV~1.3\\server\\bin;E:\\Oracle\\MIDDLE~1\\modules\\ORGAPA~1.1\\bin;E:\\Oracle\\MIDDLE~1\\JROCKI~1\\jre\\bin;E:\\Oracle\\MIDDLE~1\\JROCKI~1\\bin;%PATH%;E:\\Oracle\\MIDDLE~1\\WLSERV~1.3\\server\\native\\win\\x64\\oci920_8"

This is the registry file I came up with, it was saved as a .reg file and executing it updated the registry with the information. If you intend on using it then you will need to update the middle home directory and maybe the domain name if you use anything other than epmsystem1 as the usual domain name in an EPM deployment is EPMSystem.

One of the nice features of using this method is you get the option of generating logs like the other EPM products that use a windows service using the SysErrFile and SysOutFile options

The final step is to generate the windows service and this can be done by using the command line utility SC.

The command line used was
SC create “EPM Managed Server” binpath= “E:\Oracle\Middleware\user_projects\domains\epmsystem1\bin\EPMSystem.exe” /StartClass=weblogic.Server /ServiceName=EPM11 /RegKey=EPMSystem0

When I started up the service I did notice some errors in the log relating a performance pack which stressed that performance could be degraded, this didn’t stop the managed server from starting up and working but it needed fixing, I went back to starting up the managed server by the standard batch file and the same error was being generated so it wasn’t down to the registry file I had created.

To fix this issue I just added an extra path to the –Djava.library.path option in the registry

The path added that was added was <MIDDLEWARE_HOME>\wlserver_10.3\server\native\win\x64

I did spot one more issue which only occurred if I set the JVM maximum size over 1.5GB then an out of memory error was generated in the log and the web application would terminate.

The issue seems to be common when using jrocket as the JVM, if the maximum size was set over 4GB then the error goes away but I found it was possible to also get around the issue without having to set the maximum size to 4GB by adding an extra option in the registry which is passed into the managed server.

An extra JVMOption was added with a value of –XXcompressedRefs:size=32GB (if you want to read in-depth details on why this option may have to be used then have a read here)

As an extra option was added the JVMOptionCount in the registry has to be incremented by 1.
I have not seen any other issues and the managed server is running well when started from a service.

EPM 11.1.2.1 compact deployment update

2011-06-26T13:07:00.001-07:00

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

2011-06-05T11:02:00.001-07:00

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.

EPM 11.1.2.1 compact deployment

2011-05-29T11:24:00.001-07:00

The release of EPM 11.1.2 brought many changes across the product set and with the supporting architecture, gone was the ability to deploy web applications using Tomcat and only one option of WebLogic remained. This brought with it a much bigger footprint in terms of memory and pushed many down alternative paths to be able to run the system.

There are many reasons why high memory requirements can be restrictive, for instance if you wanted to run the full system for development, training, demos etc and intended on hosting on a laptop, yes it is possible if you pay for very high end spec machine or look to the cloud for inspiration but surely there were other possibilities.

With the release of 11.1.2.1 an additional option called compact deployment has become available which allows WebLogic based web applications to be deployed to a single managed server instead of a separate server for each product, so in theory reducing the overall memory requirement.

Today I am going to go through the process of deploying using the compact server method, now there are a few things to be aware of first.

It is aimed at 64bit operating systems.

Is available for new/upgraded 11.1.2.1 installations.

Financial Management and Financial Close management are not included; these will still need to be deployed using the standard deployment method, though saying that it is still possible to use a large percentage of FM functionality without the WebLogic piece running.

If the managed server crashes for any reason then all the web application running within it will be taken down.

This deployment method should not be considered for anything more than a development environment, it wouldn’t be supported either.

There are two methods of deploying in compact mode, one when products are configured using a single database and the other when configured using multiple databases, today I am going to cover the single database method as it makes perfect sense to only use one database in this sort of deployment.

Now I am not going to go through how to install EPM 11.1.2.1 as I have covered that in the past and it’s not the purpose of this blog, I am starting off with a fresh install of EPM 11.1.2.1 deployed on a windows 2008 R2 64bit machine, the process covered today should be pretty much the same for other operating systems and if it is unix type system then it will just be a matter of running a .sh instead of .bat

Before deploying the web applications the other areas for each product such as database configurations have to be carried out first, so the configurator was started using <MIDDLEWARE_HOME>/EPMSystem11R1common/config/11.1.2.0/config-manual.bat

Usually the configurator is started using configtool.bat but using configtool-manual.bat starts the configurator as if the web applications are to be deployed manually, this means there will be no options available for web application deployment.

As this a fresh install a new EPM Oracle instance has to be created, I stuck with the defaults which creates an instance with the name of epmsystem1, the instance name is required later in the process.

The first-time configuration option is selected and details of the database are entered, this will only have to be entered once as all products are going to be deployed using one database.

All the required options will already be selected notice that there are no options available to deploy to application server.

It is also worth noting that the essbase server configuration option is not available either, now you can leave configuring essbase until later or do what I did and configure all the above first and then run configtool.cmd, deselect all and only select Essbase > “Configure Essbase Server”.

Once all the products have been configured then it is time to move on to the web applications compact deployment.

To start up the compact deployment open a command prompt and go to <MIDDLEWARE_HOME>/user_projects/epmsystem1/bin/compact and execute “runCompactDeploy.bat”

Four pieces of information are required:-

Weblogic Domain name – this should be the same name as the domain name created in the EPM configurator, the default being EPMSystem (though I used epmsystem1 as the domain name in my example as it was a fresh install with no domain created)

Weblogic Domain user name/password – I stuck with the default username used when configuring EPM which is epm_admin and the password is your choice, you will need these account details if you intend on logging into the WebLogic admin console for the domain.

Database user password – this is the password of the account used for configuring the one database in the configurator.

Once entered the compact deployment should spring into life and all should be good…

Unfortunately I hit a bit of an issue and thought I would explain why it happened as I am sure I won’t be the only one that gets hit with it.

The compact deployment ran through and by watching the command window intently I noticed errors appear and in the end was awarded with a pop up message stating startEPMSystem.bat did not exist.

The problem started to manifest when a WebLogic script is executed to create the domain and add the EPM library templates.

The exception was “The following prerequisites were found to be missing: Oracle EPM HPS libraries – 11.1.2.1”

It seems that there are a number of mandatory products that need to be installed before the compact deployment can be carried out.

I found the details of the required libraries in the following file which is used in the compact deployment -
<MIDDLEWARE_HOME>/EPMSystem11R1/common/templates/compact/epm_system_11.1.2.1.jar

Within the jar is a file named template-info.xml which contains the required libraries, the libraries are located at <MIDDLEWARE_HOME>/EPMSystem11R1/common/templates/applications

It looks like the following products need to installed before running the compact deployment –
Foundation
Reporting and Analysis Framework
EAS
Planning
Financial Reporting
Web Analysis
HPS (Hyperion Performance Scorecard)

So depending on what products have been installed will determine the exception generated, in my case the only product I had not installed was Performance Scorecard and that is why the error around “Oracle EPM HPS libraries – 11.1.2.1” was being generated.

It may be possible to update the xml file to alter the dependencies but it is not something I have attempted. (Update: it is possible and I have wrote a post on how to do it, have a read here)

This left me the only option to install Performance scorecard and then configured the non web app elements.

Once complete “runCompactDeploy.bat” was executed again.

This time everything ran smoothly and no errors were generated in the compactDeploy window, once completed another command window opens and starts up the WebLogic admin server for epmsystem1, a final command window opened starting up all the EPM web applications in one managed WebLogic server by executing <MIDDLEWARE_HOME>/user_projects/domains/epmsystem1/bin/startEPMSystem.bat

If you log into the WebLogic admin console for the epmsystem1 domain you will notice a server has been created called “EPMServer0” running on port 9000, this means all the EPM web application will be running against that port.

To log in to the admin console go to http://localhost:7001/console and enter the username/password that was entered earlier when running the compact deployment batch script

To view all the web applications deployed in the managed server click on “EPMServer0” and go to deployments tab.

The admin server can be stopped to save memory as it is not required and the "EPMServer0" managed server can be run indepently.

It is possible to test logging into some of the products using port 9000 but for all the products to communicate correctly through workspace the final step was to configure the http web server element.

Once the web server was successfully configured using OHS the web applications were all accessible via port 19000

To start the managed server execute - <MIDDLEWARE_HOME>/user_projects/domains/epmsystem1/bin/startEPMSystem.bat

To stop the managed server execute - <MIDDLEWARE_HOME>/user_projects/domains/epmsystem1/bin/stopEPMSystem.bat

No windows service is created by default to start/stop the managed server, I am sure it is possible to create one and maybe it is something I will have a look at if I ever get the chance.
(Update: the steps to create a windows service can be found here)

A quick look at the Java process running the WebLogic managed server indicated the minimum memory being consumed was 1.3GB, so if you compared that to starting the entire set of EPM web applications in a standard deployment then there is a considerable difference, starting up just “Foundation” and “R&A Framework” web applications can consume around 1GB.

Depending on the available memory on the machine being deployed on the JVM maximum memory can be increased and with it being 64bit then there is less of a constraint on an upper limit like with 32bit.

To increase the JVM edit <MIDDLEWARE_HOME>/user_projects/domains/epmsystem1/bin/setManagedParamsEPMServer.bat and update the value for -Xmx then restart the managed server.

I have not had a chance to really put the system through its paces but the performance seems reasonable considering what it will be used for, I recommend you try it out and make up your own opinion but at least it opens the door for deployments on machines with a lower memory capacity.

Planning 11.1.2.1 – Upgrade Wizard

2011-05-15T07:16:00.000-07:00

The recent release of planning 11.1.2.1 brings new application administration features which are worth a quick mention.

These can be accessed from workspace under Administer > Classic Application Administration > Planning Administration.
Even though it is being accessed through Classic Administration it also applies to EPMA enabled applications.

The Upgrade Wizard is a new addition.

Previously if any data source elements had changed you would have to edit each data source individually, now it is possible to mass update this information.

So for instance if say the essbase server was rehosted or account information had changed then all the data sources can be updated at once.

Select the data sources that require updates, enter the new information, apply then save.

In previous versions if you wanted to upgrade a planning application to the latest release you would have to log into the planning application and select migrate, it is now possible to upgrade multiple applications at once which saves time if you have just upgraded planning.

In case of upgrade failure the upgrade process for each planning application is logged in <MIDDLEWARE_HOME>/user_projects/epmsystem1/Planning/planning1

and in the format of

PlanningAppUpgradeLog_<APPLICATION_NAME>.txt

If you have never understood what happens when a planning application is upgraded it is worth having a look at the log as it will give you a good idea of the steps taken.

The last option is “Update Reporting Essbase Servers”

If you are using the Reporting Application functionality essbase server connection information changes then it is possible update this for single or multiple applications.

I did notice a security flaw with the new functionality as if you go directly to http://<planning_server>:8300/HyperionPlanning/UpgradeWizard.jsp then anybody can access the Upgrade Wizard pages without the need for authentication, I am sure this will get fixed in subsequent patches but something to be aware of at the moment.

Planning 11.1.2.1 – Automate pushing of Reporting Application data

2011-05-14T10:37:00.000-07:00

One of the new features in planning 11.1.2 was the ability to map and push data to a reporting application, I covered the reporting functionality in an earlier blog which you can read here, one of the drawbacks was if you wanted to push the data to an essbase database it had to be done manually, depending on the size of the data the export/import could take a while and it would be something you would want to schedule out of hours.

With the release of 11.1.2.1 a new command line utility called PushData has been created, the utility provides the functionality to automate the pushing of data for any Reporting Applications that have been set up in a planning application.

The utility is available with the other planning utilities in the directory <MIDDLEWARE_HOME>/user_projects/epmsystem1/Planning/planning1

The parameters to use the utility are -

PushData [-f:passwordFile] /U:username /A:sourceApplication / M:applicationMapping [/C]

Most of the parameters are self-explanatory, if optional /C is used then the data on the target is cleared first.
If you want to understand how to create a password file have a read here

PushData.cmd /U:admin /A:PlanSamp /M:PLANASO /C

The utility also produces a log available at -
<MIDDLEWARE_HOME/user_projects/epmsystem1/diagnostics/diagnostics/logs/planning/PushData.log

The log will capture whether the pushing of data was successful or not and any members that are skipped due to not existing in the target.

It is also possible to check the status through the Job Console within the planning application.

I did notice some strange anomalies in the log as shown above.

After successfully running the command line utility it can be batched up and scheduled using your chosen scheduler to run at required intervals.

Planning 11.1.2.1 - Exporting metadata

2011-04-25T12:44:00.000-07:00

I have lost count of the amount times the question has been asked “how can I extract a planning applications outline”, until now the options have been pretty limited, one of the possible methods is to use LCM but it has its limitations and is not in the format that suits everybody.

Since the arrival of version 11 the planning Outline Load utility has done wonders and has been improving with each release, it should have been around well before 11 but at least it did arrive.

Now the utility is mainly aimed at classic planning applications, yes there are still many clients holding out with classic and why not.

With the recent release of 11.1.2.1 the utility brings in the option to export dimension metadata which I didn't see mentioned in the planning new features document, so the utility has evolved as not just a loader but an extractor as well.

The utility is available on the machine where planning has been deployed in the folder <MIDDLEWARE_HOME>\user_projects\epmsystem1\Planning\planning1\

I am not going to go through all the parameters of the utility as I have already covered the utility when it was first released

To extract a dimensions metadata it is as simple as executing:-

Outlineload /A:application /U:username /D:DimensionToExtract /E:outputFilename

For example OutlineLoad /A:PlanSamp /U:admin /D:Account /E:F:/PlanExports/acc_export.csv

If you want to include logging then you can use the parameters /L:LogFilename /X:exceptionFileName, if you don’t want to enter a password you can you the –F:passwordFile, how to set up a password file is available read here

The generated output CSV file is exactly the same easy manageable format that is required to load a dimension using the utility.

It is also possible to extract attribute dimension metadata.

To extract an attribute dimension all that is required is to provide the attribute name using the /D: parameter.

Output format is MemberName/Parent/Alias.

It is possible to export all the Smart List metadata within an application.

The parameter required for exporting Smart Lists is /DS:HSP_SMARTLISTS

Once again the output format matches the one for importing.

To export exchange rates tables the parameter used would be /DX:HSP_RATES

All the exchange rates tables available within the application are exported to the output CSV file.

The utility supports the exporting of planning unit hierarchies (which was available in 11.1.2) and the defining parameter is /D:PlanningUnitHierName

Enjoy!

Applying EPM 11.1.2.1 maintenance release

2011-04-10T12:41:00.000-07:00

I am not here today to announce the release of 11.1.2.1 as I think the news has definitely has been posted enough times now, I’d be extremely shocked if you didn’t know, maybe you have been living in a cave and decided to visit my blog when you first ventured out.

Now with this new release there are more options to move to it, you can upgrade/migrate from previous versions, fresh install or apply the maintenance patch to upgrade 11.1.2 to 11.1.2.1, some of this options I will be covering in future blogs.

This release is also about bug fixes as masses have been addressed, there are a number enhancements and new features in a selection of the products but nothing ground breaking (you may disagree), it looks like planning has a larg share of them.

As in past blogs I have gone through the installation and maintenance patches for previous releases so I thought I might as well share my experience of apply the maintenance release today. The release has only been around for a few days so I apologise if I provide any incorrect information.

The OS the maintenance release is being applied to is windows 2008 x64 SP2.

If you are intending of applying the maintenance release I recommend having a read of the “Installation and Configuration Guide” as it covers important detailed information

Next step is download all the required files, I usually obtain them from Oracle edelivery, the files that are required are covered in the install and config guide, note this release there are seven foundation files and not four, also don’t forget the additional content file.

Once you have all the files then extract them all to the same location (if you have the install files in a folder from the previous release 11.1.2 make sure you extract to a different folder), if you are installing on a distributed environment make sure you extract the necessary files on each machine and take note of information provided in the config guide, for instance

On the machine on which you plan to administer the WebLogic Server, you must install all Web applications for all applications you plan to deploy on any machine in the environment. (The WebLogic Administration Server is installed and deployed on the Foundation Services machine.)

There are a number of extra folders at root level in this release.

Now there is a list of prerequisites to be met before installing the Maintenance Release to move from 11.1.2 to 11.1.2.1, here is a summary of the main points.

• You must apply the maintenance release on top of your existing EPM System installation.

• You must apply the maintenance release to all EPM System products in the deployment. You cannot apply the maintenance release to only some products.

• If you are applying the maintenance release to move from EPM System release 11.1.2.0 to EPM System Release 11.1.2.1, or if you are reinstalling EPM System products, you must first stop all the services for EPM System products.

• If you are applying the maintenance release to move from Essbase Release 11.1.2 to Release 11.1.2.1, you must first export linked reporting object information from the 11.1.2 database, and then delete the linked reporting objects. After applying the maintenance release, import the exported linked reporting objects. More information available here

One more point before starting the installation is to be sure you have more than 5GB spare on the drive where EPM is currently installed, if there is not you will not be able to complete the installation steps.

Once all the prerequisites have been met then start up the installation by executing InstallTool.cmd/sh

A new addition to the start up of the installer is the check whether UAC is enabled, I know this could cause problem with 11.1.2 if it was enabled, if it is enabled in this release a warning will be displayed and the installer will not start up.

There are also a few more prerequisite checks, good news all have been passed.

Nothing new on this screen, displays the current Middleware Home.

Three main options to choose from and this time it is “Apply maintenance release”.

Check that there is a tick in each of the products with “11.1.2.0.0” against them, if there is not then you have not downloaded and extracted all the required files.

Once you click next a list of products to patched will be shown, click next again and the maintenance patching will start up, this is a good point to go and spend time doing something that is actually meaningful in life and then on your return hope the status is all green.

All green, see it wasn’t worth hanging around staring at the screen.

Click the Configure button, when I first installed 11.1.2 the configure button never worked but in this release it actually opened up the configurator.

You have the choice whether to stick with the original instance or create a new one, I have no reason to change this so I kept with the default.

The configurator should have checked all the pending tasks for each product that need configuring, I unchecked everything except foundation as I want to be sure there are no issues with the maintenance release, if there is a problem with foundation there wouldn’t be any point configuring the other products.

Another reason for doing this is I have used a separate Oracle schema for each product database configuration, if you leave everything selected then it wouldn’t give the option to apply to separate schemas.

This is just a personal preference Oracle no doubt recommends to leave all products checked and the choice is yours.

If you choose to only configure foundation then you would have “Configure Web Server” again when you configure the other products, this step should always be done last.

I do notice there is a new option “Setup connection to Oracle BI and Publisher”, it is not checked by default and I am not intending on changing that today.

Keep “connect to a previously configured Shared Services database” selected.

Once again there should be no need to change anything when applying the maintenance patch.

Guess what, kept the defaults, can’t beat just clicking next.

Next one more time for me and then it will configure as I am only configuring foundation

All green again, nice smooth ride up to now.

Right that’s me satisfied, back to the configurator to run through the rest of the pending tasks product by product, remembering to check “Configure Web Server” in foundation as the last step, this may not be necessary as I have already configured the web server but I am not taking any chances.
Instead of selecting uncheck all, I unchecked the products I was not configuring for, this way I knew the product I was configuring had the required tasks checked.

I am not going to paste all the screens because as this is a maintenance release there were no new surprises and the process I followed was to stick with the defaults for each product, it was basically reconfiguring the database and redeploying the web application servers.

What I did notice after installing is the number of services being used by EPMA has now been cut down, originally this is how it looked in 11.1.2

The five non web app related services have been combined into one service called “Hyperion EPMA Server”.

I also noticed after the deployment of the web applications the issue I posted about here has been resolved; the installer’s temporary directory is no longer used.

I went through each product and quickly tested I could access them without problems, understandably this is not a rigourous test but it didn't show up any major issues, I will update this post if I do come across any.

For each planning application I had to log in and upgrade from 11.1.2 to 11.1.2.1

If you are using EPMA then this is taken from the installation readme -
"After the installation and configuration of Enterprise Performance Management Architect 11.1.2.1.000, you must install Enterprise Performance Management Architect patch 11804477"

EPM 11.1.2 – 404 Issue

2011-03-27T10:58:00.000-07:00

Recently I have being working on a project where we were occasionally being hit with various web applications producing 404 errors for no apparent reason, once the issue had occurred the only way to resolve was to restart the web app.

There seemed to be no correlation with activity on the web application and the issue occurring, it was also affecting different products at different times. In the end the problem was traced back to the WebLogic web applications trying to access temporary files that no longer existed, the files were created when the web app was started up but for some reason were being deleted at random intervals.

This issue will only affect windows deployments and the reason for it happening is when the web applications are first deployed they are configured to use a temporary directory associated with the windows account that is carrying out the configuration.

Now the problem doesn’t occur just because of the temporary directory, the issue occurs when a remote desktop session is used to connect to the machine hosting the web apps and the same account is being used that deployed the EPM web applications, occasionally when the user logs out of the machine windows cleans up any temporary files that are associated with the account so this wipes out all the temporary files being used by WebLogic causing the web applications to fail.

There are a few ways to resolve this problem, the easiest being just don’t log into the machine using the account that configured the system, another is to change the configuration settings for terminal services and turn off the deletion of temporary files on exit or my personal preference is to go through the registry and update the temporary file location that is being used for each deployed web application.

This can be done by starting up the registry editor and locating
HKEY_LOCAL_MACHINE\SOFTWARE\Hyperion Solutions\<web application>0

On selecting one of the web applications there will be
“-Dweblogic.j2ee.application.tmpDir=…” usually associated with JVMOption3

Update the folder location to a permanent folder, an example permanent location that could be used is
<Middleware Home>\user_projects\domains\EPMSystem\servers\<WebAppName>\tmp

Repeat the process for each of the deployed web applications; once the registry changes have been made the web application will need to be restarted to take effect. I am sure this issue will be fixed in the imminent release of 11.1.2.1 as it is a known issue but I thought I would put a quick post out there just in case there are others that are hit with this problem and search for a resolution.

EPM 11.1.2 – Monitoring web applications

2011-03-13T09:21:00.000-07:00

Now you may not be aware that EPM 11.1.2 includes Enterprise Manager Fusion Middleware Control, this is a web based management system which can monitor the availability and performance of all the Java Web Applications across the EPM environment, plus it only requires minimal configuration to get up and running with it.

Maybe I have not paid too much notice in the past but I wasn’t aware this was included within the EPM installation and only really stumbled across it.

I am going to briefly go through the steps required to get started with it, all the configuration is done on the machine where foundation services has been installed, the foundation machine typically hosts the WebLogic admin server and by extending the WL domain it is possible add the Middleware Control components.

In the <Middlewarehome>/oracle_common/common/template/applications folder there should be a file named oracle.em_11_1_1_0_0_template.jar, this is an extension template for WebLogic which adds in the Enterprise Manager Fusion Middleware Control functionality.

Before starting make sure the WebLogic admin server is not running and then launch the WebLogic configuration wizard which is available at <Middlewarehome>/wlserver_10.3/common/bin/config.cmd or .sh

Select “Extend an existing WebLogic domain”

Select the WL Domain the default name for 11.1.2 is EPMSystem and will be situated at <Middlewarehome>/user_projects/domains

The products that have already been installed in the EPM environment will determine which templates already installed and available, select the “Oracle Enterpise Manager – 11.1.1.0” option.

Accept the defaults on the Domain Name and Location screen.

On the Configure JDBC Data Sources screen just click Next as there is no need to change any of the connection information.
The next screen will just run through all the JDBC connections and check they are valid.

You may be presented with the above screen depending on what has been installed in the environment, the section is for configuring JBDC connection details for Oracle Web Services Manager (OWSM), and this is usually part of the SOA suite.

Unfortunately it is not possible just to click next as it will complain about a password, now you have two options either put in a fake password and click passed the warning or actually create a schema and fill in all the details.

I opted for the easy option and accepted the error as it is not important.

No need to do anything on the Optional Configuration screen just click Next

Select Extend to install the Middleware Control component.

When the configuration has completed start up the EPMSystem WebLogic Admin Server.

The EM Middle Control is available on http://<admin_server_machine>:<admin_server_port>/em. The default port for the admin server is 7001. The login details are the same as used for the admin server, these are entered when the EPM system is originally configured, the username will most probably be epm_admin

Once logged in you are presented with stats for the web applications in the EPM environment, instantly you can see the number of applications that are up or down and their CPU usage.

Selecting one of the web applications will take you to a screen with more detailed information for the selected app.

The first time you select a web application you will be advised that most of the monitoring functionality is unavailable until you apply a JRF template, once applying the template the web application will need to be restarted.

It is possible to restart the web application from within the console.

It can be achieved by either going to WebLogic Server > Control > Start up / Shut Down

Or by selecting Farm > Select “Create/Delete Components”

From this page you can restart all the web applications in the domain (providing the node manager is running) which is useful functionality as it saves having to logic into the admin console.

Now the JRF template has been applied and the web application restarted full monitoring should be available, I did encounter an issue at this point with the planning web application, the console said Planning was down even though it was running without any issues; the planning log also contained the following.

I didn’t get any issues applying the JRF to the other web application in the domain, maybe it was just an issue with the environment I am testing this on, when I get a chance I will have to check on another install to see if is a common fault.

For each web application there is detailed analysis of various metrics available from CPU Usage to response times of individual JSPs.

There are many further options available from the WebLogic Server drop down.

Detailed breakdown of the various logs for each web application are available, this includes enhanced search functionality and the ability to view different types of messages so for instance it is possible to only view Errors or Warnings.

Selecting Performance Summary from the dropdown provides graphical information on different metrics; the time of analysis can easily be changed to look at different start/end times.

There are a huge number of different metrics that can be selected and analysed from the Metric Palette.

Lots more functionality is available within the application but I am going to leave it there for today, if you want detailed information then the documentation is available at :- http://download.oracle.com/docs/cd/E14571_01/core.1111/e10105/monitor.htm

The Enterprise Manager Fusion Middle Control is a powerful lightweight application and definitely worth exploring in more detail if you want to monitor the Java based web applications across the EPM environment.

EPM 11.1.2 – Speeding up workspace authentication

2011-02-19T09:01:00.000-08:00

If you have been working with 11.1.2 then you may have encountered an issue with the amount of time it takes to authenticate through workspace. You will more than likely have experienced the issue if you have a number of products installed but not active so maybe in a development/test environment.

If you do have products installed and not started then you will also see problems with menu items not displaying icons as they should.

The speed it takes to authenticate can depend on how many products are installed but not started; the more products not started the longer it takes.

Waiting around for what seems an age to log into workspace is not ideal but it doesn’t have to be that way.

Now you could install only the products you are going to use or run the configurator again and reconfigure the web server for only the required products but there is a better solution (thanks to the always helpful and extremely knowledgeable John Booth for giving a pointer to the performance degradation note in the troubleshooting guide)

In workspace if you navigate to Administer > Workspace Server settings a pop window will open.

You will see a button for Enabled Products.

The enabled products displayed will depend on what is installed in the environment.So it is just a matter of deselecting the products you are not starting up.

Once you apply the changes log out and log back into workspace the authentication should have dramatically improved.

Not only has the authenticated speeded up the products that are not being used have been removed from the menu options.

This also means you have the ability to quickly remove access to products within the workspace, so say for example you wanted to stop access to HFM or Planning (ok you can go directly to planning but there are ways to stop direct access) then you would just need to change the enabled products, there are many situations where this would be useful and does not require messing around with provisioning)

If you disable all products you can strip workspace back to the bare shell.

This led me on to think whether this could be scheduled and automated, well the answer is yes but it is not as perfect solution as I would like.

If you log into shared services, expand Application Groups > Foundation and select Deployment Metadata.

Expand Shared Services Registry > Foundation Services > WorkSpace > LWA - workspace@<machinename>_28080
Right click Properties > Export for Edit, this will let you open or save a text file called component.properties

Within the file is the parameter DisabledProducts and a summary of the possible values and how they relate to the name in the workspace server settings are:-

Cds = explore
RAFramework = Reporting and Analysis Framework
Analyzer = Web Analysis
Reports = Financial Reporting
CALC = Calculation Manager
ChangeManagement= Impact Management
HP = Hyperion Planning
HFM = Hyperion Financial Management
BPMA = Enterprise Performance Management Architect (EPMA)
APS = Provider Services
AIF = ERP Integrator
FCC = Financial Close Management

In theory you can update the component.properties file to disable the products that are not going to be started or required and then use the LCM command line functionality or you could use the command line utility epmsys_registry to directly update the property value in the Shared Services registry.

I am not going to go into how to automate LCM as I gave an overview on how to do it a while back which you can view here and the documentation is available here.

I also covered how use the epmsys_registry utility which you can read here and the documentation is available here.

If you are going to use the epmsys_registry method then here is an example command line that will update the hss registry :-

epmsys_registry updateproperty SYSTEM9/FOUNDATION_SERVICES_PRODUCT/WORKSPACE/LOGICAL_WEB_APP/@DisabledProducts "cds,RAFramework,Analyzer,Reports,CALC,ChangeManagement,HP,BPMA,APS,AIF"

Before you go ahead with any of these methods you have to realise there is a major drawback to them, they do update the properties but unfortunately the cache being used by the foundation web application is not updated, so to get the changes to be applied the foundation web app has to be restarted.
I would have thought that if the properties are updated then they should be reflected straight away.

If you are interested in where the property values are stored in the foundation relational repository then have a look at the table HSS_COMPONENT_PROPERTY_VALUES

I don’t recommend you go and update the properties directly in the table though as I don’t want to be the one blamed for corrupting the Shared Services repository.