11.1.2 Planning – Managing planning unit hierarchies Part 4

Much much later than planned I am going to go through the final method of managing planning hierarchies using ODI.
If you don’t use ODI within your company then I wouldn’t start to promote using it just for the sole purpose of loading planning unit hierarchies, one of the other methods I have been through would suite you more.

Today I am going to give a simple example scenario to hopefully make it more interesting and take you through the steps of achieving it.

Here is the brief, you have been set a task of automating the management of a planning hierarchy, a business user will provide a flat file to a shared drive, this file will provide most of the information except the user will only supply the alias names for the primary members, as all the methods for loading planning hierarchies do not accept aliases the member name will need to retrieved from planning.

Once the flat file has been placed in the designated location the process should automatically start, once completed an email should be sent out to the business user attaching any errors from the planning unit hierarchy load.

So lets go through the process in trying to complete the given task. The concept of loading the planning unit hierarchies is the same as the other methods, I am going to assume you have a basic understanding of using ODI and planning, if you don’t there are plenty of blogs I have written in the past of you can have a read of.

I am will be using ODI11G but it is all achievable using ODI10G.

First of all you have to make sure a default planning hierarchy has been created in planning.



Once you have created the hierarchy, in ODI you can reverse engineer your planning application.



If you take a look at the reversed model you will notice a Datastore has been created with the same name as the planning unit hierarchy.



Within the Datastore the columns that have been generated are exactly the same as the columns that have been used in all the previous methods (you will need to go back to the first part if you want to understand what each of the column means and how it relates to the planning unit hierarchy)

The user has produced the following planning hierarchy flat file to be uploaded to planning :-



The primary members are all alias names.

Next step is to reverse engineer the flat file, remember that the agent you run the integration against will need to be able to access the file, otherwise you will need to look at alternatives of accessing the file such as one of the ODI ftp tools.



Remember that you can’t reverse engineer flat files through the model; you need to create a new flat file Datastore and reverse it within the columns tab.



As the source file only has alias names we will have to join it to the planning applications underlying tables to be able to retrieve the member name, this means the next stage is to create a connection to the planning application relational database in the topology manager, create a model and reverse the required tables.



The only tables we are interested in and need reversing are HSP_ALIAS and HSP_OBJECT

Now that the source and targets are in place we can move on to creating the interface to load the planning unit hierarchy.



As the target technology is going to be planning and it has no JDBC capabilities a staging area has to be selected.



The planning unit Datastore was dragged on to the target.



The planning unit flat file datastore was dragged on to the source then the planning application tables HSP_OBJECT, HSP_ALIAS and again HSP_OBJECT.

The HSP_OBJECT table is the core table in the planning application and contains the member names and alias names that we are interested in. The Planning unit hierarchy Datastore joins to the HSP_OBJECT DataStore, the join is
PLANNING_UNIT_HIER.Primary_Member > HSP_OBJECT.OBJECT_NAME

The HSP_ALIAS table contains IDs for the member name and alias and these IDs join to the HSP_OBJECT table.
HSP_OBJECT.OBJECT_ID > HSP_ALIAS.ALIAS_ID
HSP_ALIAS.MEMBER_ID > HSP_OBJECT.OBJECT_ID



The joins between the Datastores were created by dragging the required columns from one Datastore to another.



A filter was added to HSP_OBJECT_OBJECT_TYPE with a value of 10, this means it will only return records that are alias related. If you want to understand the numbers that relate to object types then you can view them in table HSP_OBJECT_TYPE.



The target Datastore columns were then mapped to the sources.

Basically the flat file will join by alias to the planning tables to return a member name that relates to the alias.



In the flow diagram "LKM File to SQL" was used to load the Planning unit hierarchy flat file to the Staging area. The "IKM SQL to Hyperion Planning" was used to load from the Staging area to Planning.

In the IKM option both logs were enabled and a location and filename provided, the logs will be used in the final stage of the integration when the completion email is sent out.



Now that we have the main part of the integration built we can put it all together in a package.



The first step is to use the OdiFileWait tool, this will wait for the planning hierarchy flat file, it will check every 60 seconds and once the file exists in the specified location it will move on to the next step.



The next step is the interface that loads the planning unit hierarchy into planning.



And finally the step to send out the email confirming the completion of the planning unit hierarchy load and attaching the error log file generated.

Obviously you can make it a much more sophisticated package with failure handling, file archiving and executing the scenario once the package has completed but you get the idea.

11.1.2 Planning – Managing planning unit hierarchies Part 3

In the last blog I went over loading a planning unit hierarchy directly through planning, today I am going to through the next method and that is the outline line utility.

The outline line utility appeared in the first release of version 11, even though a couple of versions late in my opinion it has turned out to be the most useful utility for classic planning, if you need to quickly load metadata and you can generate a fixed format file then it can save a lot of time, especially if you are prototyping.

Using the outline utility uses the same principal as the other methods, same source file template, just this time command line driven.



The outline loader utility used to be available in the planning\bin directory, in this release to keep you on your toes it has moved, if it is a default install then it should be located at :-

\\user_projects\epmsystem1\Planning\planning1

The utility is named OutlineLoad.cmd or OutlineLoad.sh depending on OS.

There is a whole bunch of parameters available with the utility but the ones of importance when dealing with planning unit hierarchies are :-

[-f:passwordFile] :- This is optional if you don’t include it then the utility will prompt for a password, if you want to know how to create a password file, go here

An example on how to create the password file is shown below.



The other parameters are

/S:Servername 

/A:Application

/I:InputFileName - Include the full path and filename

/U:Username 

/X:exceptionFileName -
/L:logFileName - If you want the log files to be written to a different directory than the utility then include the full path and filename

/D: PlanningUnitName – hierarchy name you want to load to.


I don’t need to explain in any further detail the parameters as I feel they are self-explanatory.
Before running the utility you will need to make created a default planning unit hierarchy, also make sure that the hierarchy is not being edited in planning when you run the utility otherwise you will receive an error.



So basically just create a new hierarchy and give it a name, the name will need to match the name that will be used with the utilities parameter /D

To run the utility from command line first change directory to the location of the utility, then execute the utility with the required parameters



OutlineLoad.cmd -f:password.txt /S:localhost /I:h:\Temp\planunits\planhier.csv /U:admin /A:plansamp /D:MoretolifethanPlanning /X:h:\Temp\planunits\planunit.err /L:h:\Temp\planunits\planunit.log



The “Primary and SubHierarchy Selection” and “Assign Owners” sections have been populated. I have included the file template again in the screen shot so it hopefully can help a little further in understanding how the various planning unit properties are populated from a source file.

If any errors were encountered they will be written to the log defined in the parameters, more much more informative than when importing directly through planning.



There are another couple of parameters that can be used with the utility :- /R and /U

The description from the documentation is :-

/U - Delete all planning units with the /R option, or display an error if members in planning units would be deleted. Use /-U (or do not specify the /U parameter) to prevent deleting members in planning units (default).

Use /U with /R to enable deleting started planning units and deleting all members in the dimension specified in the .CSV load file.

I did have a go at using the parameters but it didn’t seem to make a difference if I included them or not, maybe you will have more success with them.

In the next blog I will look at the final method of loading planning unit hierarchies and that is through the use of ODI.

11.1.2 Planning – Managing planning unit hierarchies Part 2

In the last blog I went through the basics of planning unit hierarchies and creating a source file template to load, today I am going to go very quickly through one of the methods of loading and that is directly through Planning

This is the easiest method; create your planning unit template (look back at the previous blog if you have no idea on what to do) and make sure you save it with .exp extension.

Here is the same source file template I am going to use.



Each time you load a planning unit hierarchy it will drop any planning units in the existing hierarchy that don’t exist in the new one so make sure your template has the full hierarchy.



In planning select Administration > Process Management > File Based Import/Export



Select the existing planning unit hierarchy name you want to load to from the dropdown.
Browse the file template (remember it should have an extension of .exp)



Click OK and the file will be loaded.



The “Primary and SubHierarchy Selection” and “Assign Owners” sections have been populated.
I have included the file template again in the screen shot so it hopefully can help a little further in understanding how the various planning unit properties are populated from a source file.



If there is anything wrong with the import source file you will receive the above error message and will have to hunt down the planning log.

If planning is running on a windows OS then the best log to check will be

<EPM_HOME>\user_projects\epmsystem1\diagnostics\logs\services\HyS9Planning-syserr.log



Not the best error logging in my opinion but at least you get an idea of the issue.

In the next blog I will look at the next method of loading planning hierarchies and that is using the outline loader utility.

11.1.2 Planning – Managing planning unit hierarchies Part 1

With the release of 11.1.2 comes an overhaul of the workflow process, there has been much criticism in the past of how rigid the process had to be and it was severely lacking in anything but basic functionality.

Oracle have taken onboard this outcry and started to invest development into enhancing workflow, now the aim of this post is not to explain the how workflow operates but to look at the different options for managing planning unit hierarchies.

Prior to 11.1.2 the workflow process had a basic configuration where planning units were defined by a combination of entity, scenario and version and the promotional path was purely based on how the entity hierarchy rolled up.

From 11.1.2 a planning unit can be based upon on entity and a secondary dimension, the promotional path & email notification can also be defined, this configuration is known as a planning unit hierarchy and multiple hierarchies can be created per planning application, each hierarchy can then be applied to a scenario and version member.

In planning under Administration there is now a section for Process Management with three sub sections :- Planning Unit Hierarchy, Scenario and Version Assignment and File Based Import/Export



Selecting “Planning Unit Hierarchy” will open up the management area where new planning unit hierarchies can be created, edited, assigned or deleted.



So let’s create a new planning unit hierarchy



First you would give the hierarchy a name and if relevant a description to go with it.

Enable Process Management has the following options from the dropdown.

None :- Includes no planning units in the budget process by default, these would have to manually selected at the section “Primary and SubHierarchy Selection”


All :- Add all planning units (Entities) to the budget process.

Custom :- Define which planning units to include in the budget process.

If you select custom you can add individual planning units based on Parent member and generation criteria.



An entity member was selected “E01” and I set the relative generation to be 0 to 2
Relative Generation 0-2 will select the member E01 and the two generations below E01.

Under the “Primary and SubHierarchy Selection” tab



Once selecting the “Planning Units” radio button only the entities defined in the custom selection will be displayed.



You can also select planning units by right clicking a member and choosing from the list of functions.

Right, back to the first tab and “Process Management Template” and from the dropdown you have the choice of the following options :-

Bottom-Up Budgeting (default)



In bottom-up budgeting, data is input at the leaf member level (for example, children of Budget
Group) and consolidated by rolling data up the organizational hierarchy.
Users can view or edit data based on security defined for the planning unit. Using process management, planning units get reviewed and approved.

Distributed Budgeting

In distributed budgeting, the budget is defined at the top level of the organization and then data is distributed down the organization hierarchy. Budgets can be distributed by cascading the datadown one level in the organization, or by distributing the data to all organization members.



Free-Form Budgeting


With free-form budgeting, data is input at the leaf member, and planners select the next owner
from a drop-down list. Free-Form budgeting uses the process management model from Planning
releases earlier than Release 11.1.2.

Reapply Setting on Each Move to evaluate every entity moved into the hierarchy at the
time it is added. If the entity meets the criteria for inclusion in the budget process, it is marked for process management. If an entity does not meet the criteria, it is excluded from process management.



A new feature in 11.1.2 is the ability to select a secondary dimension to base the planning unit on; a secondary dimension can be selected from the dimension dropdown.



Once a dimension has been selected a parent member is required and then a relative generation, in the example I have selected "HA" and a relative generation of 1, so this any members that are a child of "HA".

If the Auto Include is selected it will automatically select all members based on the relative generation (it even counts the number of members and displays in the count box), if it is not selected the members can be selected manually.

After completing the subhierarchy definitions the promotional path can defined which is accessed by the “Assign Owners” tab.



In the above example you will notice that the entity and secondary dimension members are displayed.

Planning unit ownership is inherited from the planning unit parents. Planning unit reviewers are also inherited. You can also explicitly specify planning unit owners and reviewers to assign owners and reviewers other than those planning units inherit.

An owner can be a user, a reviewer can be a user or group and notications can be set to users or groups.



As you can imagine if you have a large hierarchy using a secondary dimension and a non standard promotional path then it can be a painful task to manage directly through planning, this leads me on to the reason for this post.

There are three methods available to load the planning unit hierarchy information:-

Planning web

Outline Loader utility

ODI


Each method uses the same template format that consists of the following properties

Primary Member, Primary Enabled, Secondary Dimension, Secondary Parent, Relative Generation, Auto Include, Secondary Member, Include, Owner, Reviewers, Notifiees





So now we know what each of the column properties are it should be possible to create a planning unit hierarchy template to load into planning.



The template is based on the above hierarchy;
The planning unit hierarchy also includes a secondary dimension (Segments) for the level0 entity member MA (E01_101_1100)



The Segment parent member will be HA (Home Audio) with a Relative Generation set to 1 so will include members BAS (Bookshelf Audio System) and HTAS (Home Theater Audio System)



The template has to contain member names and not aliases.
I have also included a custom promotional path of Owners and Reviewers.
 


With each of the loading methods a planning unit hierarchy has to already exist so I will quickly create one.



Besides naming the hierarchy everything else is left as default, the next step would be to use one of the available methods to load the hierarchy.

Well that is where I am going to leave it for Part 1, in Part 2 I will go through loading using Method 1 :- Planning Web.

ODI Series – issue with essbase multi-column data loads

Now I have known about this issue/feature for a long time but recently I have heard a few people mention it and I just wanted to very quickly highlight the problem if you were not aware of it.

First of all if you are going to use the essbase adaptor to perform data loads I always recommend using version 10.1.3.5.5 or newer and to use a load rule with the interface.

The issue only manifests itself when you are performing a data load with multiple data columns.

Let’s take an example in its simplest form.



In certain circumstances source data could be across a number of rows, yes you could group this information but sometimes you don’t want to add to existing values, anyway there shouldn’t be an issue loading this type of data into essbase.



Loading the data directly into essbase using a standard load rule produces a result that you would expect.



Now let’s load exactly the same data using a simple ODI interface with the same load rule set in the IKM options



Now you will notice the difference, the value for Sales is #Missing and you would expect the value to be 1.

When ODI loads data if it encounters an empty cell it treats and loads the value as #Missing to essbase.



This would be the equivalent load file if it were directly being loaded into essbase.

So is this a bug or just the way ODI requires to load data using the API, I will leave that for you to decide.

If you are going to load data in this manner then it is something you need to be aware of, if the data cannot be grouped it is worth considering an alternative approach such as having all the member information in the rows and having one data column.