Archive for July, 2013

10 Important Things to Improve ODI Integrations with Hyperion Planning Part 6 (Metadata validation when loading data to Hyperion Planning)

Posted in EPM, Hyperion Planning, ODI Architecture, ODI Architecture with tags , , , , , on July 25, 2013 by Rodrigo Radtke de Souza

Hi all! It’s good to be back! As we have seen in our last post, we can easily extract all existing metadata information from any number of planning applications, but what can we do with all this information? Well, we can do a lot of great stuff. One of them, as mentioned in the last post, is to compare the existing metadata information to the new metadata that we will load to planning and load just what have changed. This will be covered in details in a later post. Today I’ll be talking about a simpler but very powerful usage of this existing metadata information: metadata validation when loading data to Hyperion Planning!

Everyone that has loaded data into Hyperion Planning using ODI already passed through this situation: you get some data to load into planning and let’s say that this data load takes five minutes to complete. You are happy, the business team is happy, but for some reason in a random day the data load takes six hours to complete. The end user complains, you go check the logs and you find something like that:

2009-07-30 18:00:52,234 DEBUG [DwgCmdExecutionThread]: Error occurred in sending record chunk…Cannot end data load. Analytic Server Error(1003014): Unknown Member [ACT001] in Data Load, [0] Records Completed
2009-07-30 18:00:52,234 DEBUG [DwgCmdExecutionThread]: Sending data record by record to Essbase

This error happens when you are trying to send data using a member that is not part of your outline. ODI is great to load data into Hyperion Planning, but it has a weird behavior when you have an unknown member in you data load. In a perfect world, ODI reads its source database, gets a big chunk of data and sends straight to Essbase. Essbase process it, sends and OK to ODI and ODI sends another big chunk of data. This works pretty fast because it loads big chunks of data at a time, but if you have an unknown member in the data load, Essbase will send to ODI an error stating that there is one unknown member in that data chunk and ODI will switch to “record by record” mode. In this mode ODI will not send a chunk of data but it will send record by record to Essbase and this may take forever depending on how much data you have to load.

I don’t really know why ODI behaves like this, but this is what happens in reality. To avoid that we have a very powerfull technique that we will always talk about: metadata from planning repository. We already know how to read all existing metadata from a Planning application, so it is just a matter to compare all members that we will be sending to Hyperion Planning against all existing metadata in that application prior to the load. This will guarantee that only existing members of that application will be sent to Essbase, insuring that ODI will not flip to “record by record” mode.

How can we accomplish that? We have a lot of possibilities as it depends on the current database structure and tables that your system has, but I will display one that is generic enough to be applicable to any system.

1) Create a table with the following structure:

Figure7

You may want to add more columns to this table if you need, but those should be fine for our example. This table is used to store all existing metadata from any number of Hyperion Planning applications that you may have. You may populate it using the techniques seen in our last post.

2) Create an inbound table containing the data that you will send to Hyperion Planning. This table will contain one column for each dimension that you may have plus a column named “DATA” that will contain the value of that intersection and APP_NAME that will contain the name of the Hyperion Planning application which that data will be loaded to. Here is one example:

Figure8

Why do we need to create this table? As I said, there are many ways to do this verification and maybe you may not need to create it, but I strongly recommend doing so. The reason is that you create a data layer between your source system and Hyperion Planning that is centralized in one single point where you can have data for all applications that you may have (you may partition this table by app for example) and you may add centralized ODI check constraints in one single table as we can see below.

3)  Create ODI check constraints to validate all dimension members. For each dimension column in your INBOUND_GENERIC table, you will create an ODI check constraint that will validate those members against the existing metadata in that application. Let’s use ACCOUNT as an example:

figure1

Go to INBOUND_GENERIC model in ODI and add a New Reference constraint. Change the type to “Complex user reference” and select your model that contains the TBL_EXISTING_METADATA table.

figure2

Go to Expression tab and add your constraint SQL there as below:

figure3

Here we are comparing all members in ACCOUNT column in our INBOUND_GENERIC table against all ACCOUNT members in TBL_EXISTING_METADATA table that has a specific PLAN_TYPE and DATASTORAGE. Again, this is just an example and you may tweak it to your reality. You will do this to all dimensions that you may have and you may also add other constraints as duplicated keys, invalid amounts and so on:

figure4

4) The last part is just a matter to select a CKM in your ODI interface that will load the INBOUND_GENERIC table and see the results. You will have INBOUND_GENERIC table with only metadata that exists in your Hyperion Planning application and an E$ table (created by ODI CKM) with all non-existing members in your outline!

figure5

Now you may load from INBOUND_GENERIC table to Hyperion Planning application with the guarantee that it will always run fast without worrying about unknown members in your outline. Also as a bonus, you have E$ table with all members that are missing in the outline, so you may use it to warn the end users/support team and so on.

I hope you all enjoy!

1 Comment »

10 Important Things to Improve ODI Integrations with Hyperion Planning Part 5 (Extracting existing metadata from Planning)

Posted in EPM, Hyperion Planning, ODI Architecture, ODI Architecture with tags , , , , , , , , , , , on July 24, 2013 by RZGiampaoli

Today we will talk about something very important when we work with Hyperion Planning: its repository.

Today I can say that, if it was not for the Planning repository, 10% of the solutions/issues that I had in my clients should not have been implemented/fixed and 50% the solution/issues should take 10 times more to be implemented/fixed.

Well, as we are talking about integration in this post I will focus on this for now but I promises that I will talk about the repository itself in another time, focusing in Planning development, security and maintenance.

Let us start. First, what is the planning application repository? Basically it is used for manage all existing metadata in an planning application, which means, we can find all forms information, security, dimensions, settings, workflows and other stuffs of an application there and the best part is that we’ll find this in a relational table (normally oracle).

This allow us to easily get any information from any applications and, in an integration perspective, creating smarter integrations.

I said that because normally when we create an ODI interface to load metadata we basically use an ODI datastore for the mapping. as a source table to a planning dimension, and then load everything that we have in that table into planning.

However, is this a smart way to load metadata? I can say no. I have a good example in a big client that I’m currently working. They have an entity dimension with more than 23000 members, and in every maintenance cycle, only 1% of those entities changes or are added. That means that only 230 cost centers are new/changed every month. Well, in a normal ODI development, you will load all the 23000 members every month! And it is not only that: my client has seven applications, all of them with more or less the  same size. Making the math, we would load more than 160000 members every month and only 1600 were needed to being loaded.

What can we do to solve that? Very simple solution. We only need to know what metadata already exists in the planning applications and compare it with the source metadata repository. The difference from this comparison is what we really need to load.

But how we can do that? With only two steps, one for extract data (that is the focus of this post) and other to compare the data (that we’ll talk in a later post of our ODI series).

Let’s start:

A Planning application repository has a central table where every object that you create in the application is stored. This table is the key source to extract metadata from the applications and its name is HSP_OBJECT. From this table we can get every information needed for the extract process and it makes easy to extract any necessary dimension information we need. In this table, we have six important columns:

  • OBJECT_NAME: This column stores the object name;
  • OBJECT_TYPE: This column stores the type of the member (Shared Member, Version, Entity, Time Period, Cube, Currency, Member, Planning Unit, Year, Attribute Dimension, Dimension, Alias, Folder, Form, Attribute Member, Group, User, Account, Calendar, Scenario, Currency, FX Table and User Defined Dimension Member);
  • OBJECT_ID: This is the ID of the Object;
  • PARENT_ID: This is the ID of the objects Parent;
  • GENERATION: This informs from what generation the object belongs;
  • HAS_CHILDREN: This informs if that object has a children.

We can notice that this table is based in a parent child relationship. This type of relationship is perfect in cases where we want to store a hierarchy inside of a table because in this way we do not need to know how many generations one hierarchy will have to create the table. We only need two columns with the parent and child IDs to rebuild that correlation between then. To achieve that Oracle database give us a very useful command: Connect by Prior … Start With.

The start with .. connect by clause can be used to select data that has a hierarchical relationship and Prior word is used to create recursive-condition. Long story short, everything we need to do is:

Connect by Prior .. Start With

This command will retrieve all the Account Dimension hierarchy and if we need to rebuild the metadata from another dimension the only thing that we need to do is change the OBJECT_NAME to the desired dimension name, E.g. Entity. This query will be the core of our extract process. For now, we have the hierarchy built, but only this is not enough, as we need to have all information about the members in order to compare to our inbound table. We need to take the other metadata information from planning repository as well. We need to join all the following tables together to have everything that we need:

Repository Table Name

Extract Table

HSP_OBJECT

Member

Parent

Generation

Has_Children

Postion

HSP_OBJECT_TYPE

Dim_Type

HSP_MEMBER

Data Storage

Data Type

Two Pass Calculation

Aggregation (Plan1)

Aggregation (Plan2)

Aggregation (Plan3)

Aggregation (Wrkforce)

Aggregation (Capex)

HSP_ALIAS

Alias: Default

HSP_MEMBER_FORMULA

Formula

HSP_DIMENSION

Hier_Name

HSP_STRINGS

Description

HSP_ACCOUNT

Account Type

Time Balance

Skip Value

Exchange Rate Type

Variance Reporting

HSP_PLAN_TYPE

Plan Type (Plan1)

Plan Type (Plan2)

Plan Type (Plan3)

Plan Type (Wrkforce)

Plan Type (Capex)

Source Plan Type

HSP_ENTITY

Base Currency

HSP_ENUMERATION

Smart List

HSP_MEMBER_TO_ATTRIBUTE

Associated Attributes

HSP_UDA

UDA

This table contains the mapping of the tables and the information that you can find in the application repository. Two important things to say when you build the final extraction query from planning repository:

  1. Remember that the HSP_OBJECT table has all the metadata information regarding all the objects in planning. We need to join almost all the above tables with themselves again to get the name of that particular object. E.g., HSP_ALIAS only contains the IDs to the alias; the alias itself is stored in HSP_OBJECT.
  2. Remember to make LEFT JOINS to all those tables against HSP_OBJECT. Depending of the dimension type, we will not have anything stored in some tables. E.g., Account members does not have data stored in HSP_ENTITY table.

With this query ready, all we need to do is loop it passing the Dimension name to the core query mentioned above and it will extract all dimensions from planning.

See Part 2 of our ODI series.

This is it. We only need to compare the extracted planning data to our inbound data and we’ll see that in a future post.

See you soon.

3 Comments »

We are back!!!

Posted in EPM, Hyperion Planning, Kscope 13, ODI Architecture, ODI Architecture with tags , , , , , , , , , , , , , on July 24, 2013 by RZGiampaoli

Hello guys how are you doing? It has been a long time since our last post and this is because we were writing a white paper and a presentation for Kscope13 that, by the way, it was an amazing experience for us. We strongly recommend everybody to become a member and participate in this kind of event. You may find all information related to that at www.odtug.com

We were able to meet some guys that in the beginning of our carrier with EPM/BI gave us the information that we needed through they blogs and answers in the OTN to build everything that we know now . In fact it was because of them that we decided to start a blog and share our knowledge like they do.

Well, it is with this share spirit in mind that we are attaching the write paper that we wrote for Kscope13 here in our blog. This paper presents a dynamic and robust solution for metadata integration using ODI for multiple planning applications. A new development methodology uses ODI in a better way creating dynamic components that reduces the implementation and maintenance time.

Well, I do not want to ruin the surprise, so here it is:

Dynamic Integrations for Multiple Hyperion Planning Applications

We’ll resume our “10 Important Things to Improve ODI Integrations with Hyperion Planning“ posts and will start writing some other topics that we have in mind. In fact, we have some new projects that we are working on, and probably this will generate some new interesting material. So stay tuned because we will be sharing all of it here.

See you soon.

Leave a comment »