ODI Series - Deleting planning members

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.