10 Important Things to Improve ODI Integrations with Hyperion Planning Part 7 (Smart Metadata Loading)


Hello everybody. The time arrived to put some intelligence behind our metadata load. After some years working with Hyperion Planning, ODI and DRM (or any other metadata repository), I figure out that 90% of the metadata does not change in the month cycle maintenance (in a normal Planning application). That means, 90% of the time that a metadata integration takes is useless. It’s a lot of time if you are maintaining a big client as one of mine that the maintenance cycle took more than 8 hours for all their regions.

Luckily for them I figure out a very effective and easy way to decrease that time and now it takes less than 30 minutes for the entire maintenance cycle. Basically I developed a method that categorizes each metadata row in our tables, and based in this category the interface knows what it need to do with that data. Let’s see how it works.

After we have an inbound and extract tables with all metadata from source and target systems (as we saw in the part 5 of our series), we need to compare them and decide what to do with each metadata member. For this tie out process we created the metadata tie out table that is a merge of both inbound and extract tables containing all source and target columns with a prefix identifying each one of them plus a column called CONDITION. This extra column is used to describe what the metadata load process should do with that particular member. It is important for this table to have all sources and target columns because then we can actually see what has changed from source to target metadata of that member.

Metadata tie out process will be responsible to read both source and extract tables and populate the metadata tie out table with all source, extract and CONDITION information. The tie out process has a built in intelligence that analyzes several different load situations to each member and populates the final result in the CONDITION column. The tie out process always searches for a parent/member/application/dimension combination in the source table and match it to the parent/member/application/dimension on the target table. The process uses this combination because these are the information that represents a unique member in Planning.

Here are the possible CONDITION statuses created by the tie out process:

CONDITION status

When it happens

Match

All metadata information from the inbound source table is equal to the extract table information, so no further action is needed.

No Match

Any column from the inbound source table is not equal to the extract table information. This member will need to be updated in the target Planning Application.

Exists only in Source

If it is a new member and exists only in the inbound source metadata table it needs to be loaded to the Planning Application.

Exists only in the Application

If a member was deleted on the source system but still remains in the planning application. For those cases we created a “Deleted Hierarchy” member and move the deleted members under it. The process doesn’t physically delete the member to keep the data associated with it intact.

Moved Member

If a member moves from one parent to the other and needs to be updated in the Planning Application.

Changed Attribute member

When one attribute is moved from his parents to another parent.

Reorder sibling members

When a new member needs to be inserted in the place where other member previously belongs or a member changed place order with one of its siblings.

Deleted Share Members

When one shared member stops to exist in the inbound table and needs to be deleted from the Planning Application.

The first four conditions status are achieved by a “Full Outer Join” between the Inbound and the Extract table and a “Case When” to define the CONDITION column as we can see in the below:

Tieout Query example

Tieout Query example

This query compares all metadata columns in the source and extracts tables to see what has changed and adds to the CONDITION column what the load process should do with that row afterwards. For the other four conditions status we need to work in the data just created by the figure 9 queries.

  • Moved Members: When we execute the query from Figure 9 we get an unexpected behavior regarding moved members. A moved member is a member that changed from one parent to another. Since the query compares the member and parent names to decide if that is a new, modified or deleted member, it will consider that the source member is a new member (because it has a new parent) and the extracted member will be considered as a deleted member (because its parent/member combination does not exist in the source) generating two rows in the tie out table instead of one. To solve this issue the tie out process merge those two rows into a single one. This merge happens for all multiple rows that have the same member name but one with “Existing only in Source” condition and another one with “Exists only in the Application” condition;
  • Changed Attribute Member: Attribute members require a special logic because Hyperion Planning treats them differently. When you want to move an attribute member from one parent to another, you first need to delete the member and then insert it back in the new parent. So this is a two-step operation, instead of the normal move member operation. When the process deletes the attribute first Hyperion Planning automatically removes its value from its associated dimension member. If we don’t load the associated dimension members again their attribute values will be missing in the end of the metadata load process. To solve this issue the metadata tie out process searches for all dimension members that have a moved attribute associated with it and change their condition to NO_MATCH. This will guarantee that after moving the attribute to a new parent the process also loads all the dimension members again with its attribute values. Another particularity with attributes is that if an attribute doesn’t exist anymore in the source system it is deleted from the planning application. It is not moved to a deleted hierarchy because no data is associated directly with the attribute member, thus no data is lost;
  • Reorder sibling members: When a single member is added to an existing parent member and this parent member has other child members, planning adds the new member in the end of the list. This is because Hyperion planning doesn’t have enough information to know in which order to insert this new member as it does not have its sibling’s orders to compare to it. So the tie out process also search for all existing siblings of the new member and mark them as NO_MATCH to indicate that they should be loaded all together. This way Hyperion Planning will have all siblings orders and will load the members in the correct order;
  •  Deleted Share Members: If a share member ceases to exist in the source metadata, it is removed completely from the planning application. There is no reason to move them to a deleted hierarchy member because no data is associated directly with it;

When the tie out process finishes populating the metadata tie out table we will have all information to load only the necessary members to Planning. As this table is centralized and has all applications and dimensions in it, it is just a matter to loop it for every application and dimension needed to be loaded by the generic load component. To accomplish this,the next post will show how to make the KM and the ODI models dynamic enought to handle this.

See you next time.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: