ODI Series – Deleting essbase members part 1

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

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

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

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



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

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



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



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

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

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



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



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



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

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




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

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

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



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



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



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

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



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



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

The interface can now be executed within ODI.



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



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

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