ODI Series – Deleting essbase members part 2

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

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

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

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

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



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



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



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



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



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



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



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

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



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

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



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



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

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

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

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

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



A new Dataset can be added from the interface toolbar.



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



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



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



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



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



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



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

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



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

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



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

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



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

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



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


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



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

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



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



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

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

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