Saturday, 27 November 2010

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.

Monday, 8 November 2010

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.