Archive for ODI Expert

« Older Entries
Newer Entries »

ODI 12c First impressions

Posted in EPM, New Features, ODI 12c, ODI Architecture, ODI Architecture, ODI Mapping with tags , , , on October 26, 2013 by RZGiampaoli

When I started working with ODI it was in version 10, and back there we had a few bugs, the UI was good (back there we could change the expressions and we didn’t have to take out the focus to save the changes, for example) , everything worked well, we could write a variable name with upper or lower case, the metadata navigator worked very well and that was one of the things that made the users choose ODI instead of power center Informatica, because they had an easy way to run their interface at will, and some other good things. It was a very stable version of ODI, good times.

Then 11 version came out. Well, the first thing we noticed was the UI, and the huge amount of bugs that came with it, and most of them in the interface screen. In 11 version, if you try to delete a filter, all the other filters disappears, but they are still there, if you close and re-open the interface they’ll come back, if you change a expression and doesn’t remove the focus from the filed it’ll not commit the changes, if you delete a datastore and put a new one (because some model changed for example) you have a good chance to not be able to save the interface for some bizarre error and you need to do this operation over and over, the variables name must be upper case for some odd motive, and other things. Another big loss was the metadata navigator that was replaced by ODI Console, a worse version with so many bugs that we had to stop using it. Some bugs like lack of security (everybody could see everything), all the execution ran as supervisor, we couldn’t see the load plans (only place where the security works), we couldn’t see the variables and lots of other things.

BUT despite of that, the functionalities for the DEV team were almost the same.

Now we have a new version of ODI. The 12c. Ok, this is only our first impressions and we could have been doing something very wrong (and I pray for this). When a software changes its version and two specialists takes more than 30 minutes trying to figure out why and how or what they need to do to sum a column in an interface or should I say mapping (yes, this is the new name, I liked it and this is one of the few things that made sense to me in this new version), something is very wrong with it.

Ok let’s start from the beginning. When I started to work with datamart, the first tool that I used was OWB, and after some time when I started to use ODI to make some integration, I really missed some stuffs from OWB. It makes sense to get this two tools and merge it together. From OWB we had a cool mapping process that makes easy to understand what that transformation is doing, multiple targets, and a few other things that I missed in ODI, BUT, ODI has the Agent that allow us to connect anywhere without the need to create a heterogeneous service or a dblink or other stuff like that, it has more flexibility (and when I say more you can understand infinite more), we don’t need to deploy the mapping to create a procedure in a oracle database to integrate something, what makes the development test super-fast, we have a lot of components, well, ODI is so much better in this aspect that the few things I missed doesn’t bother me at all.

So in this new version they tried to merge the two tools. What looks good in the paper (I mean blogs and documentations) looks terrible in ODI.

We installed it this weekend to see what happened in this new version and we saw a very different workspace for the interface, I mean for the mappings. This simple ODI UI…

ODI 11g Interface UI

ODI 11g Interface UI

Turned into this:

ODI 12c Interface UI

ODI 12c Interface UI

Humm looks good right? Well, yes and no. I’m working in a 60” full screen TV and I need to drag the screen left and right, up and down to make everything visible, poor devs that has a screen smaller than mine.

Ok but this is only layout, everything else should be better right? Well, unless we were doing something very wrong they put a lot of more complexity to solve some issues that we were able to solve very quickly since version 10.

First of all, in all the other versions if you want to sum something in ODI you just need to get the expression in the target datastore and put a SUM() function on it. ODI would do the group by for you and everything is ok.

ODI 11g Sum

ODI 11g Sum

In the new version you need to drag an object called Aggregate, put all the columns that you want to map trough this (like in OWB), change the options of this object, and in the end, put the same SUM() expression in this object instead of in the target datastore.

ODI 12c Sum Part 1

ODI 12c Sum Part 1

ODI 12c Sum Part 2

ODI 12c Sum Part 2

ODI 12c Sum Part 3

ODI 12c Sum Part 3

If you try to put the expression as before (<=11g) it’ll not create the group by and you’ll not be able to run the interface because it will just simply fail ….

Well, at least in OWB when you use the Aggregate object it’ll aggregate the columns that you define without the need to write the SUM() function. Why they put this new complexity? Ok you can execute this SUM() in another place different from the source or the target but still…

We have some other components that we need to use in the interface right now.

ODI 12c Mapping Components

ODI 12c Mapping Components

The dataset is used when you want more than one dataset in the source (we already have it in the 11g, the difference now is that you need more screen to manage it but the bright side is that you’ll not forget to change anything because you missed the datastore tab like in the old version [yes I did it a lot])

ODI 11g Datastore

ODI 11g Datastore

ODI 12c Datastore

ODI 12c Datastore

The distinct component does not need to be explained, the only thing I had to say is that in the old versions you need only to flag it in a simple check box and now we need to add a distinct component in the flow, drag all columns to it and them drag those columns again to the target. A complete waste of time.

ODI 11g distinct

ODI 11g distinct

ODI 12c Distinct

ODI 12c Distinct

The expression… well, almost the same as Aggregate. Now instead of just write any expression in the target datastore you may add this object in the flow, BUT it will work if you just write the expression on the target. So, why do we need to have this additional object???

ODI 11g Expression

ODI 11g Expression

ODI 12c Expression

ODI 12c Expression

For the filter, join and lookup table nothing changed.

ODI 12c Lockup

ODI 12c Lockup

The set is to define the type of union you can have between the datasets, same as before but now it’s in the mapping too.

ODI 12c Set

ODI 12c Set

We now have a sort component, so now we may stop doing “SQL injection” or “KMs changes” for a simple order by component (of course I liked this one).

ODI 12c Sort

ODI 12c Sort

And the Split component. This one is what I missed the most in OWB. This allow us to say something like: if the DIMENSION is Account, all the data goes to DIM_ACCOUNT, DIMENSION =  ENTITY then DIM_ENTITY, the others goes to DIM_OTHERS for example.

This is a cool thing but easily done using our command on source and target in a procedure (See this post 10 Important Things to Improve ODI Integrations With Hyperion Planning Part 2).

ODI 12c Split

ODI 12c Split

As we can see a lot of things were made for this version, but all this things makes it unusable. Really, in the old versions I already tried to not use interface, only if was absolute necessary, because they are time consuming, inflexible, hard to maintain and there’s nothing you can’t do in a procedure that is a lot better and faster to create then interfaces, in fact I only use interface when I want to use the CKM to use some constraints for data quality, nothing I can’t do in a procedure, but this is for sure something easier to achieve using interfaces. Despite of that, everything else I prefer to use a procedure, mainly because I can get rid of the models, that looks good, but for me they are the true villain of ODI. Models are hard coded information, and I hate hard code.

In resume, in this new version things that were relative simple to use, now are a nightmare to create. Of course that things will get more visual but the developers will pay a very high price for that cool looking. Oracle just added foolish complexity on things that were simple and that worked very well. Do you want a final example? On prior versions of ODI, you would import all KMs that you needed for that specific project and you would pick one of them from the combo box on the flow tab. If you needed to change it later, just pick another one from that same list and that’s it:

KMs 11g

ODI 11g KM Selection

On version 12, first you need to be on the Logical tab of the Mapping object, click on the target table to get its focus, expand “Target” properties on the right panel and select its target “Integration Type”. This type will filter which KMs you will be able to see in the Physical tab:

IntegrationType1

ODI 12c KM Selection 1

In the Physical tab, click again on the target table, expand “Integration Knowledge Module” and select one of the KMs of that type that you filtered in the previous tab:

IntegrationType2

ODI 12c KM Selection 2

And what happens if you want to change the KM? If it is from a different type, first you need to go to the Logical tab, change its type, go to Physical, and select another KM. Ok, they have categorized the KMs and this is a good thing but why they didn’t add the Integration Type in the same tab of the KM selector??? Now we need to go back and forth without any good aparent reason and if you are in doubt on which KM to select and you want to read their descriptions to see which one best fits your needs, then you are totally screwed.

But there are two really cool things about this 12c version. First one debugger! Finally they added a debugger to ODI! This feature was a long waited one because it was simply terrible to debug things in ODI. Now you can go execute the code step by step, take a look in the variables content for that session and you can even query uncommitted data through the transactions:

debug_odi

ODI 12c Debug

Second cool stuff: Roles in Security Module! Again, another long awaited simple feature that did not exist until now. Roles are similar to Groups where the security added to a Role is replicated to all users that belongs to that Role. This is great, because in the old days, Security configuration was madness with a lot of manual configuration. Finally now we will have a better Security framework to work on it.

Roles_ODI

ODI 12c Roles

Well, there’s a lot of thing to see in this new version yet, but the first thing wasn’t pretty. I didn’t uninstall it yet. Let’s see if we can find anything good that justify this living hell that the interfaces (mappings) turned out to be.

If someone of you learn something different or get a different idea for this new version please let us know because I still don’t believe that these changes happened and this is the way Oracle wants us to work from now on. (By the way the UI for procedures are different too and for now I’ll not say if I liked it or not because normally we need some time to get used to it [but I didn’t like it J]).

This weekend we’ll try a migration and let our impressions here.

See you guys!

Advertisement

9 Comments »

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

Posted in EPM, Hyperion Planning, ODI Architecture, ODI Architecture with tags , , , , , on September 13, 2013 by RZGiampaoli

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.

Leave a comment »

Dynamic topology configuration in ODI without using contexts

Posted in EPM, ODI Architecture with tags , , , , on August 12, 2013 by Rodrigo Radtke de Souza

— EDITED on Aug 12, 2015 —

Great news! Oracle just released “Patch 21156142: Parametric connections in ODI 12c don’t work anymore” which enables this technic to be used in ODI 12.1.3! Thanks Marius for this great tip!!!!

— End EDIT —

— EDITED on Dec 09, 2020—

5 years later, I had to implement this in ODI 12c and I could really test what some of the comments complained below. It seems that all variables works, but the password variable. However, if you use a GLOBAL variable, it works just fine. So, if you are in ODI 12c, please only use GLOBAL variables for this, otherwise you will have an issue in the password variable. Thanks!

— End EDIT —

— EDITED on May 6, 2015 —

Unfortunately, this technic only works on ODI 11 version. ODI 12c does not support dynamic password settings. We will let you know if we figure out a workaround for it.

— End EDIT —

Hi all, how are you doing? I came across this interesting situation during a project some time ago. Imagine the following scenario: You have a transactional system that was implemented in several places around the word. The system and its tables are exactly the same, but they are distributed across different databases, with different user names, database schemas, passwords, etc. Your task is to load data from this system with all these different database regions into your data warehouse, unifying all this data into one single place. Since all the tables have the same structures, how can you accomplish that with ODI without creating a lot of duplicate interfaces?

Everybody that already worked with ODI will easily answer that it may be accomplished using different contexts and they are totally right as we can see below:

image1

In resume, you create one interface object loading a table from the source system into your data warehouse and each context that you have created will point to a different database. It is just a matter to run that interface for each context and that’s it. However, there are situations where we cannot create that many contexts in a real production environment. In my case I couldn’t create those contexts because production execution repository is used for several other projects where those new contexts would just not make any sense at all. In production, the users have just one context called “Production” and they run any ODI job using that context, independently of the project which that ODI scenario belongs to. We could explain and teach the users to use multiple contexts, but my experience as a developer taught me that we need to let things as simple as possible to the end users, because there will be a day (for sure) that someone will execute a job using a wrong context and a mess may be created.

So, how can we accomplish this task without duplicating one interface per database that you need to load? Here is the technique:

Create one physical data server for each database that you need to load plus a dynamic data server:

image2

ADB_AMERICAS, ADB_APJ, ADB_EMEA and ADB_ZZ are the different databases that you need to connect and load data from. Those data servers are created as you normally create any data server in ODI and they will contain their own information regarding connection, data and work schemas and so on. In ADB_DYNAMIC you will create as the following:

Data Server:
User: #ODI_PROJECT.CONN_USER
Password: #ODI_PROJECT.CONN_PASS
JDBC URL: #ODI_PROJECT.CONN_URL

Physical Schema:
Schema: #ODI_PROJECT.CONN_DATA_USER
Work Schema: #ODI_PROJECT.CONN_WORK_USER

image3

image4 image5

As you can see, we have added five ODI variables in the topology information. These variables will be used to receive all the dynamic information from the different databases that we need to load. Now create one logical for each physical schema that you have created and point them accordingly in your context. Notice that we are also creating a logical schema called ADB_DYNAMIC that is going to point to our dynamic physical schema.

image13

Now this is an important step. Your models in ODI need to be pointing to ADB_DYNAMIC logical schema to make this work, but if you did not develop your interfaces yet and you also need to reverse your models and so on, you will not be able to do it pointing to this dynamic topology, since it has just ODI variables and not actual values there. So you will need to point your ADB_DYNAMIC logical schema temporally to a “normal” physical schema, let’s say ADB_AMERICAS to develop your interfaces. If you do this way, you will be able to reverse your models using ADB_DYNAMIC logical schema and you will be able to test all your interfaces pointing to one “valid” database without any additional work. After you complete your entire development, just point ADB_DYNAMIC logical schema back to ADB_DYNAMIC physical schema and proceed to the following step.

Now comes the tricky part. In order to make this technique to work, you will need to have a “control/parent” scenario that will call any “interfaces/child” scenarios passing as parameters all the connection information for each different database that you need to load. Let’s see an example:

image6

This child scenario contains all interfaces that will load your DW and it is receiving as parameters all connection information that will be used in ADB_DYNAMIC data server/physical schema. Remember that at this point, all interfaces are pointing to a physical schema that has only ODI variables on it, not actual values, so if you try to execute this package alone, it will not work. Now it is just a matter to call this scenario multiple times, one for each database that you need to load. First create a parent scenario with a procedure that will call those child scenarios as below:

image7

This procedure will have two steps for each database that you need to load. The first step will get one specific database connection and the second step will call the child scenario passing that connection information. It will look like this:

image8

If we open the first step, we will have the following:

image9

This step is creating Java variables that will temporally hold the connection information. These API commands are getting the connection information from the Command on Source tab, so in that tab we will have the following:

image10

We don’t need any code here. We just need the Schema combo box pointing to the desired database, in this case ADB_AMERICAS. This will allow all Java variables to get ADB_AMERICAS information that will be used in the next step which is the following:image11

This step will call the child scenario passing as parameters all the connection information that is needed to be used in ADB_DYNAMIC connection. When this child scenario runs, all interfaces inside of it will use ADB_AMERICAS connection information and will load data from it dynamically. Cool isn’t it? You are going to do the same configuration for the other steps in this procedure and the only thing that is going to change is the logical schema that you need to point in the “Command on Source” table. So “Get ADB_APJ Connections” will get ADB_APJ connection information and so on.

If you notice, this example is calling the child scenarios using SYNC_MODE=2, which means that they will execute the data load in all different databases in parallel, so if your architecture allows such parallelism, you may gain a great performance boost using this technique as well. We also added a “Wait for child scenarios” step as the last step, so we may control when all of the child scenarios have completed or not:

image12

So here is the end of this post. I hope you all enjoy!

35 Comments »

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 »

We’ll be at Kscope 13

Posted in Kscope 13 with tags , , , , , , , , , , , , , , on January 16, 2013 by RZGiampaoli

Hi guys how are you doing? We are very happy and proud to announce that our article “Dynamic Integrations for Multiple Hyperion Planning Applications” was approved for KScope 13! We’ll be there to talk about one of our preferred subject areas: integration of Hyperion Planning applications. We’ll be very pleased if you guys could show up in our presentation. It’ll be excellent to meet everyone there to talk about EPM and other stuffs.

Above is the link with more information about our article.

Kscope 13 Abstract

This article is all about what we have being writing in our blog series “10 Important Things to Improve ODI Integrations with Hyperion Planning” and some other surprises that we’ll talk at the presentation. You guys can expect a lot of technical information, a new way to view and work with Hyperion Planning integrations plus some real facts about what has happened after a successful implementation of this approach.

Thank you very much everybody and we’ll be waiting for you in Kscope 13.

Kscope 13 Speaker

Kscope 13 Speaker

3 Comments »

Optimization Context in ODI

Posted in ODI Architecture with tags , , , , , on December 19, 2012 by Rodrigo Radtke de Souza

Hi all. I’ve decided to take a little break in our Hyperion Planning series to talk a about a “confusing” (and underestimated) feature in ODI: Optimization Context.

Optimization Context

This is a very important feature but few people know how to use it correctly.  This setting is used in design time to make ODI aware of how it should build its code. Let’s take a look at the following example:

Data Servers

For some reason (budget constraint, laziness, etc) the sales database schema that you want to read is in the same data server then your DW schema that you want to load in development. In production the situation changes and you have two separated data server, one for each schema.

Let’s say that we have two logical schemas, one for Sales (LOGICAL_SALES) and another for DW (LOGICAL_DW). In Development we created the following Physical Topology:

Dev Servers

In Production we have this:

Prod Servers

I will not go through all the small steps to replicate this here, but we need to set up the Physical/Context/Logical information, create the data models and reverse the tables. When we build the interface, things start to get interesting. Let’s take a look at the interface using the “Development” context in Optimization Context:

Flow Tab

In the Flow tab we have only one IKM to select because in the Development context our source and target physical schemas reside in the same data server. ODI uses the Optimization context to figure out how it is going to build its flow tab. Let’s change the Optimization context to Production now:

Optimization Context

Flow Tab

Well, it changed. Now we have one LKM and one IKM. Again ODI checked the Optimization context to build its flow tab. As Production has two separate data servers, ODI added a LKM to be able to load from one server to the other. So the Optimization context is the key parameter to let ODI know how to build its code. And it can be very tricky.

Imagine that we change back to Development Optimization context. Now we have just one IKM in the flow tab. Let’s save it and generate a scenario with that interface. If we run it using the Development context it will work fine because both schemas are in the same data server. But what happens if we run it using the Production context?

Operator Code

Operator Code

It failed. If we check the steps, we have just one “Insert new rows” that tries to insert into DW schema using a select in the Sales schema:

Operator Code

As in Production the schemas are in separated data servers and ODI created its flow tab using the Development Optimization context this code will never work. If we change the Optimization context to Production, regenerate the scenario and run it again, it will create additional LKM steps and it will work fine:

Operator Code

It is worth mentioning that the opposite is also true. Imagine that in Development you have different data servers, but in Production you have only one data server. If you use Development Optimization context ODI will create its code with a LKM and it will add extra steps that would not be necessary in the Production environment. This wouldn’t give you any error, but these extra steps will be redundant and will result in less performance.

So this concludes our post. Prior to select your Optimization context, find out which is your topology in all environments. In this way you will be sure that ODI will create the correct code for all environments without any extra steps or errors.

See ya!

5 Comments »

10 Important Things to Improve ODI Integrations with Hyperion Planning Part 4 (Third Command Tab)

Posted in ODI Architecture with tags , , , , , , , , , , , , on November 30, 2012 by Rodrigo Radtke de Souza

In a previous post we saw a very powerful feature in ODI: the command on source and command on target tabs inside the procedures. This allows us to create “loop effects” and can be used even with different technologies (remembering that the source technology must always be Oracle).

But sometimes we may find situations that two tabs are not enough and we wished to have a “third command tab” or something like that.  Here is one example:

This example procedure will call an ESSMSH OS command to load an Essbase database. It will retrieve the user name, password and server name from the command on source schema information using the following:

<%=odiRef.getInfo(“SRC_USER_NAME”)%>
odiRef.getInfo(“SRC_PASS”)
<%=odiRef.getInfo(“SRC_DSERV_NAME”)%>

This will work fine as long as you have one app. Imagine now that you have multiple applications in the same server and you want to load all of them with one procedure using the target/source tabs technique. It would look like this:

Great! Now we have an Oracle command on source that will retrieve all Essbase cubes that we want to load. Those cubes will be passed as parameters to “Command on Target” tab to be executed. But unfortunately this command will not work. The reason is simple: our source tab changes from Hyperion Essbase to Oracle, the ODI commands related to user, password and server name will get the wrong information to connect in Essbase.

If only we had a “third command tab” to get the Essbase information… And we can have it using a technique for these kinds of situation. We use java variables to get connection information and use it in our command. Let’s create another step in our procedure:

This step will get user name, password and server name from Essbase and stores it in Java variables. With this information in Java variables we can change our procedure to the following:

Done! We were able to mimic a “Third command tab” situation where we get the Essbase connection using Java in one tab, use it on an OS command in another tab and we will loop it through any number of rows that returns in the Oracle tab. It gives you a lot of flexibility. This technique can be used in a lot of other situations. It only depends on your creativity. Java is a great language to be used with ODI and can give you that extra flexibility that you may need in your code.

Leave a comment »

10 Important Things to Improve ODI Integrations with Hyperion Planning Part 3 (Executing a Maxl)

Posted in ODI Architecture with tags , , , , , , , , , , , , on November 30, 2012 by RZGiampaoli

Sometimes in a project that involves an Essbase database we need to execute commands on that to get or set some information as substitution variables, execute calculation scripts and others. Normally we can set a calculation script into an option inside an interface and it’ll be executed in the beginning or in the end of its execution.

0

But sometimes we need a more robust way to execute a series of commands in Essbase. If we need to calculate some data in essbase we could need to set some substitution variables and execute more than one calculation script to calculate the right area.

Well, the easy way to do that is again the use of ODI procedures. Combining an ODI Tool inside a procedure and some OS commands we can easily create a powerful execution center to command essbase from ODI.

This example procedure will call an ESSMSH OS command to load an Essbase database. It will retrieve the user name, password and server name from the command on source schema information using the following:

<%=odiRef.getInfo(“SRC_USER_NAME”)%>
odiRef.getInfo(“SRC_PASS”)
<%=odiRef.getInfo(“SRC_DSERV_NAME”)%>

This can be used for any Maxl command available as:

  • Execute Calculations, with some steps we can set the POV using the alter variables command and then execute a calculation already created in EAS or we could send the script store in a parameter table to essbase avoiding the creation of new calculations scripts in EAS;
  • Alter database, any type of changes as load, stop, enable or disable connections and so on;
  • Studio Deploy, this is very helpful for control a metadata interface and use studio to deploy it and maintain a drill thought ASO cube ;
  • Clear Database (For ASO). Awesome way to clear data in an ASO database before insert new data;
  • Get Statistics. Very good way to track some database information or to verify if something is true and with this result execute or not some action;
  • Backups. With some steps only one procedure can backup a Essbase Application and zip otl, clc, and other files as well;

With this approach and a simple interface it’s possible to generate text files from essbase and load these text files to a table for further analysis. Here is one example of an interface to load database statistics:

Create Essbase Statistics Procedure

This procedure  will generate a file with the statistics of a cube.  Then we need only to create an interface to load this file to a table:

Load Essbase Statistics Interface

And we’ll have now an excellent way to track the cube growth, search for some anomaly before a cube crash, plan the memory allocation for the cubes based in the use of each cube and so.

Leave a comment »

« Older Entries
Newer Entries »