Archive for the Hyperion Planning Category

Advanced Planning Forms Build using Planning repository

Posted in EPM, Hyperion Planning with tags , , , , on January 26, 2015 by RZGiampaoli

Today I saw in the OTN a question about how to build a Planning Form with some parents and all the members under it with a determined attribute. I decide to answer here because I could put a little more detail and help more than one person.

First let us take a look in the Planning form wizard. To do so let us create a “Test” form to be our guide in this Post. It is always easier to have a starting point, than start all from the scratch.

Forms and Ad Hoc Grid

I set the POV in the way I want it and put three members of Entity (That in my case has the some attribute dimensions on it).

Let us look in the member selection prompt:

Member Selection Filter

As we can see, we can filter the members by Name, Alias, Descripition and UDA, also we can keep only the members with a specific attribute:

Member Selection keep Only Attributes

However, we cannot have both, the descendants of a member and only the children with a specific attribute, at least not in the tool.

What we can do is create the list of members we want and insert it right in the Planning repository. Planning stores all information about a Form inside three tables (there are other tables for forms but they are setting for composite forms and so on! Information about the members and the form are in these tables):

SELECT * FROM HSP_FORM;
SELECT * FROM HSP_FORMOBJ_DEF;
SELECT * FROM HSP_FORMOBJ_DEF_MBR;

In addition, Planning stores all metadata information in HSP_OBJECT, then we need to use this table to get information about the metadata instead of a lot of ID’s! Then we need to join this table to get the actual name of the stuff)

In the first table, you have the form itself! If we join the first table with the HSP_OBJECT table we can filter our Form:

SELECT HF.*, HOF.OBJECT_NAME
FROM HSP_OBJECT HOF, HSP_FORM HF
WHERE 1=1
AND HF.FORM_ID=HOF.OBJECT_ID
AND OBJECT_NAME=’Test’
;

Query Results Forms 1Query Results Forms 2

As we can see we already have our Forms selected, and, there are only information regarding the Form itself as precision, column width and so on. That is mean, we do not need to touch this table, and we need only to use to select the desired form.

Now we need to get the POV settings by query the second table, HSP_FORMOBJ_DEF:

SELECT HFD.*, HOF.OBJECT_NAME
FROM HSP_OBJECT HOF, HSP_FORM HF, HSP_FORMOBJ_DEF HFD
WHERE 1=1
AND HF.FORM_ID=HOF.OBJECT_ID
AND HF.FORM_ID=HFD.FORM_ID
AND OBJECT_NAME=’Test’
;

Query Results Forms 3

Ok, here the only important information is the OBJDEF_TYPE. This columns means:

0=Point of View

1=Page

2=Rows

3=Columns

With this, we can select the area that we want to work in the form. In this case, we want the rows, then let us filter it and make a join with the last table, the one that stores the Members in that specific POV, HSP_FORMOBJ_DEF_MBR.

SELECT HD.FORM_ID, HOF.OBJECT_NAME AS FORM_NM, HDM.MBR_ID, HOM.OBJECT_NAME AS MEMBER_NM, SEQUENCE, ORDINAL, LOCATION, HD.STYLE
FROM HSP_FORMOBJ_DEF_MBR HDM, HSP_FORMOBJ_DEF HD, HSP_OBJECT HOF, HSP_OBJECT HOM
WHERE 1=1
AND HDM.OBJDEF_ID=HD.OBJDEF_ID
AND HD.FORM_ID=HOF.OBJECT_ID
AND HDM.MBR_ID=HOM.OBJECT_ID
AND HOF.OBJECT_NAME=’Test’
AND HD.OBJDEF_TYPE=2
AND HOM.OBJECT_TYPE=33
ORDER BY HDM.SEQUENCE
;

Query Results Forms 4

Well, we have some interesting information here:

First of all, we have the for filtered by his name (After the join with the HSP_OBJECT to get the OBJECT_NAME;

We have the OBJDEF_TYPE filtering the type 2, that means ROWS;

And we have the OBJECT_TYPE filtered in 33, that’s means “Entity”. We can find out all the object types by querying (SELECT * FROM HSP_OBJECT_TYPE;)

As we can see, we have all the three members set in the Form “Test” in the right order (SEQUENCE column). Now we need just to select the members and sequence that we want then inserted and filter it by an attribute.

Well, we already know that the HSP_OBJECT table stores all metadata information in Planning then, nothing is more fare than use this table to select all members that we desire:

SELECT *
FROM HSP_OBJECT HO
CONNECT BY PRIOR HO.OBJECT_ID=HO.PARENT_ID
START WITH OBJECT_NAME IN (‘CBO_CC_GCC’)
;

Query Results Members 1

This simple query bring me all children of “CBO_CC_GCC” including himself. Also we get other cool information like the OBJECT_TYPE, GENERATION and HAS_CHILDREN, that can help us a lot to select what we want, for example:

We can select all members of a generation, only what doesn’t have children (leaf), or only what has children, and so on.

Inside or query we can have more than one parent, this is why I put an “IN” in the START WITH clause. Also, we can use other than OBJECT_NAME in the clause, it could be the OBJECT_TYPE, to get an entire dimension, or PARENT_NAME, but normally we use the OBJECT_NAME and if you want the entire dimension you can ask for it (‘Entity’).

Ok, now we need only to filter from that list all the children that has a specific Attribute. For this we need the Attribute tables:

SELECT HO.MEMBER_ID, HO.MEMBER_NM, HO.OBJECT_TYPE, HO.GENERATION, HO.HAS_CHILDREN, HOA.OBJECT_NAME AS ATTR_NM, HOD.OBJECT_NAME AS ATTR_DIM
FROM
(
SELECT HO.OBJECT_ID AS MEMBER_ID, HO.OBJECT_NAME AS MEMBER_NM, HO.OBJECT_TYPE, HO.GENERATION, HO.HAS_CHILDREN, HO.POSITION
FROM HSP_OBJECT HO
CONNECT BY PRIOR HO.OBJECT_ID=HO.PARENT_ID
START WITH OBJECT_NAME IN (‘CBO_CC_GCC’)
) HO,
HSP_MEMBER_TO_ATTRIBUTE HMA,
HSP_OBJECT HOA,
HSP_OBJECT HOD
WHERE 1=1
AND HO.MEMBER_ID=HMA.MEMBER_ID(+)
AND HMA.ATTR_MEM_ID=HOA.OBJECT_ID(+)
AND HMA.ATTR_ID=HOD.OBJECT_ID(+)
ORDER BY HO.POSITION
;

Query Results Members 2

The table that stores the relationship between the member and his attributes is the HSP_MEMBER_TO_ATTRIBUTE. The only thing is that, if we want to get the parent and all children with a specific attribute we need to do some left joins, since the parent members does not have any attribute member associated on it.

Other than that it’s a pretty forward query and as we can see it gets all the attributes from the children of CBO_CC_GCC cost center. Now we need only to filter it:

SELECT HO.MEMBER_ID, HO.MEMBER_NM, HO.OBJECT_TYPE, HO.GENERATION, HO.HAS_CHILDREN, HOA.OBJECT_NAME AS ATTR_NM, HOD.OBJECT_NAME AS ATTR_DIM
FROM
(
SELECT HO.OBJECT_ID AS MEMBER_ID, HO.OBJECT_NAME AS MEMBER_NM, HO.OBJECT_TYPE, HO.GENERATION, HO.HAS_CHILDREN, HO.POSITION
FROM HSP_OBJECT HO
CONNECT BY PRIOR HO.OBJECT_ID=HO.PARENT_ID
START WITH OBJECT_NAME IN (‘CBO_CC_GCC’)
) HO,
HSP_MEMBER_TO_ATTRIBUTE HMA,
HSP_OBJECT HOA,
HSP_OBJECT HOD
WHERE 1=1
AND HO.MEMBER_ID=HMA.MEMBER_ID(+)
AND HMA.ATTR_MEM_ID=HOA.OBJECT_ID(+)
AND HMA.ATTR_ID=HOD.OBJECT_ID(+)
AND (HOD.OBJECT_NAME = ‘Support_Geography’ OR HOD.OBJECT_NAME IS NULL)
AND (HOA.OBJECT_NAME IN ( ‘SUPP_Americas_Supp’,’SUPP_Australia’) OR HOA.OBJECT_NAME IS NULL)
ORDER BY HO.POSITION
;

Query Results Members 3

Do not forget to create a OR to null in the filters because parents will not have any attributes.

I choose the Dimension “Support Geography” and the attributes “SUPP_Australia andSUPP_Americas_supp. As we can see, we have now selected all the childrens of CBO_CC_GCC that has two attributes. We need only to insert this in the Planning repository.

Ok, to insert let us get or SQL that query the Forms tables and let us make it return only one row (That will be used as base information to insert) and let’s see that data that we need to insert.

SELECT HDM.*
FROM HSP_FORMOBJ_DEF_MBR HDM, HSP_FORMOBJ_DEF HD, HSP_OBJECT HOF, HSP_OBJECT HOM
WHERE 1=1
AND HDM.OBJDEF_ID=HD.OBJDEF_ID
AND HD.FORM_ID=HOF.OBJECT_ID
AND HDM.MBR_ID=HOM.OBJECT_ID
AND HOF.OBJECT_NAME=’Test’
AND HD.OBJDEF_TYPE=2
AND HOM.OBJECT_TYPE=33
AND HOM.OBJECT_NAME=’CBO_CC_GCC’
ORDER BY HDM.SEQUENCE
;

Query Results Members 4

Ok, this will be our guide! We need that because there are some information that we cannot get from the query that generate the members but we can get for our “guide member”. Looking in the result, we will need to get only the MBR_ID and SEQUENCE from the query that filter the members for our Form, and everything else will be a clone of our previous set member. Also we’ll make sure that the members that already exists in the form will not be inserted by mistake again.

Now let us join the two queries and get our results.

SELECT HDM.OBJDEF_ID, HDM.ORDINAL, HO.MEMBER_ID, HDM.QUERY_TYPE, HO.POSITION, HDM.SUBST_VAR, HDM.LABEL, HDM.FORMULA_DATA_TYPE, HDM.FORMULA_ENUMERATION_ID
FROM
(
SELECT HO.OBJECT_ID AS MEMBER_ID, HO.OBJECT_NAME AS MEMBER_NM, HO.OBJECT_TYPE, HO.GENERATION, HO.HAS_CHILDREN, HO.POSITION
FROM HSP_OBJECT HO
CONNECT BY PRIOR HO.OBJECT_ID=HO.PARENT_ID
START WITH OBJECT_NAME IN (‘CBO_CC_GCC’)
) HO,
HSP_MEMBER_TO_ATTRIBUTE HMA,
HSP_OBJECT HOA,
HSP_OBJECT HOD,
(SELECT HDM.*
FROM HSP_FORMOBJ_DEF_MBR HDM, HSP_FORMOBJ_DEF HD, HSP_OBJECT HOF, HSP_OBJECT HOM
WHERE 1=1
AND HDM.OBJDEF_ID=HD.OBJDEF_ID
AND HD.FORM_ID=HOF.OBJECT_ID
AND HDM.MBR_ID=HOM.OBJECT_ID
AND HOF.OBJECT_NAME=’Test’
AND HD.OBJDEF_TYPE=2
AND HOM.OBJECT_TYPE=33
AND HOM.OBJECT_NAME=’CBO_CC_GCC’
ORDER BY HDM.SEQUENCE
)HDM
WHERE 1=1
AND HO.MEMBER_ID=HMA.MEMBER_ID(+)
AND HMA.ATTR_MEM_ID=HOA.OBJECT_ID(+)
AND HMA.ATTR_ID=HOD.OBJECT_ID(+)
AND (HOD.OBJECT_NAME = ‘Support_Geography’ OR HOD.OBJECT_NAME IS NULL)
AND (HOA.OBJECT_NAME IN ( ‘SUPP_Americas_Supp’,’SUPP_Australia’) OR HOA.OBJECT_NAME IS NULL)
ORDER BY HO.POSITION
;

Query Results Members 5Now we have almost all set, we need only to remove the member that already exists in the table (“CBO_CC_GCC”), this way it will not duplicate it.

SELECT HDM.OBJDEF_ID, HDM.ORDINAL, HO.MEMBER_ID, HDM.QUERY_TYPE, HO.POSITION, HDM.SUBST_VAR, HDM.LABEL, HDM.FORMULA_DATA_TYPE, HDM.FORMULA_ENUMERATION_ID
FROM
(
SELECT HO.OBJECT_ID AS MEMBER_ID, HO.OBJECT_NAME AS MEMBER_NM, HO.OBJECT_TYPE, HO.GENERATION, HO.HAS_CHILDREN, HO.POSITION
FROM HSP_OBJECT HO
CONNECT BY PRIOR HO.OBJECT_ID=HO.PARENT_ID
START WITH OBJECT_NAME IN (‘CBO_CC_GCC’)
) HO,
HSP_MEMBER_TO_ATTRIBUTE HMA,
HSP_OBJECT HOA,
HSP_OBJECT HOD,
(SELECT HDM.*
  FROM HSP_FORMOBJ_DEF_MBR HDM, HSP_FORMOBJ_DEF HD, HSP_OBJECT HOF, HSP_OBJECT HOM
  WHERE 1=1
  AND HDM.OBJDEF_ID=HD.OBJDEF_ID
  AND HD.FORM_ID=HOF.OBJECT_ID
  AND HDM.MBR_ID=HOM.OBJECT_ID
  AND HOF.OBJECT_NAME=’Test’
  AND HD.OBJDEF_TYPE=2
  AND HOM.OBJECT_TYPE=33
  AND HOM.OBJECT_NAME=’CBO_CC_GCC’
)HDM
WHERE 1=1
AND HO.MEMBER_ID=HMA.MEMBER_ID(+)
AND HMA.ATTR_MEM_ID=HOA.OBJECT_ID(+)
AND HMA.ATTR_ID=HOD.OBJECT_ID(+)
AND (HOD.OBJECT_NAME = ‘Support_Geography’ OR HOD.OBJECT_NAME IS NULL)
AND (HOA.OBJECT_NAME IN ( ‘SUPP_Americas_Supp’,’SUPP_Australia’) OR HOA.OBJECT_NAME IS NULL)
AND NOT EXISTS (SELECT 1
                FROM HSP_FORMOBJ_DEF_MBR HDM2
                WHERE 1=1
                AND HDM.OBJDEF_ID=HDM2.OBJDEF_ID
                AND HO.MEMBER_ID=HDM2.MBR_ID
                                )
ORDER BY HO.POSITION
;

Query Results Members 6

With this last “NOT EXISTS” clause we have our final SQL and we can now insert into the HSP_FORMOBJ_DEF_MBR our members and then restart planning service to get the new members in the form.

INSERT INTO HSP_FORMOBJ_DEF_MBR
(
SELECT HDM.OBJDEF_ID, HDM.ORDINAL, HO.MEMBER_ID, HDM.QUERY_TYPE, HO.POSITION, HDM.SUBST_VAR, HDM.LABEL, HDM.FORMULA_DATA_TYPE, HDM.FORMULA_ENUMERATION_ID
FROM
(
SELECT HO.OBJECT_ID AS MEMBER_ID, HO.OBJECT_NAME AS MEMBER_NM, HO.OBJECT_TYPE, HO.GENERATION, HO.HAS_CHILDREN, HO.POSITION
FROM HSP_OBJECT HO
CONNECT BY PRIOR HO.OBJECT_ID=HO.PARENT_ID
START WITH OBJECT_NAME IN (‘CBO_CC_GCC’)
) HO,
HSP_MEMBER_TO_ATTRIBUTE HMA,
HSP_OBJECT HOA,
HSP_OBJECT HOD,
(SELECT HDM.*
  FROM HSP_FORMOBJ_DEF_MBR HDM, HSP_FORMOBJ_DEF HD, HSP_OBJECT HOF, HSP_OBJECT HOM
  WHERE 1=1
  AND HDM.OBJDEF_ID=HD.OBJDEF_ID
  AND HD.FORM_ID=HOF.OBJECT_ID
  AND HDM.MBR_ID=HOM.OBJECT_ID
  AND HOF.OBJECT_NAME=’Test’
  AND HD.OBJDEF_TYPE=2
  AND HOM.OBJECT_TYPE=33
  AND HOM.OBJECT_NAME=’CBO_CC_GCC’
)HDM
WHERE 1=1
AND HO.MEMBER_ID=HMA.MEMBER_ID(+)
AND HMA.ATTR_MEM_ID=HOA.OBJECT_ID(+)
AND HMA.ATTR_ID=HOD.OBJECT_ID(+)
AND (HOD.OBJECT_NAME = ‘Support_Geography’ OR HOD.OBJECT_NAME IS NULL)
AND (HOA.OBJECT_NAME IN ( ‘SUPP_Americas_Supp’,’SUPP_Australia’) OR HOA.OBJECT_NAME IS NULL)
AND NOT EXISTS (SELECT 1
                FROM HSP_FORMOBJ_DEF_MBR HDM2
                WHERE 1=1
                AND HDM.OBJDEF_ID=HDM2.OBJDEF_ID
                AND HO.MEMBER_ID=HDM2.MBR_ID
                                )
)
;

We can check our Forms Query to see the new members:

Query Results Members 7There they are. Now let us restart planning service and see the results:

Planing Form Results MembersHere we go! In the Right position and only the right ones.

Hope you guys enjoy this tip, and be sure that the Planning repository is extremely powerful and help us to do a lot of stuff that Planning would not let you do.

See you guys.

DEVEPM will be at KScope 15!!!

Posted in EPM, Hyperion Planning, KScope 15, ODI, ODI 12c with tags , , , , , , , , on January 22, 2015 by radk00

Hi all, how are you doing? We are very happy to announce that our presentation “No more unknown members! Smart data load validation for Hyperion Planning using ODI” was approved for KScope 15! We’ll be there to talk about one of our preferred subject areas: How to improve EPM tools potential using ODI. We’ll be very pleased if you guys show up in our presentation. It’ll be great to meet everyone there and talk about EPM and other cool stuffs! Here is our presentation’s abstract:

“Usually, ODI data load interfaces for Essbase are simple and fast to be developed. But, depending on the data source quality, those interfaces may become a performance challenge. Essbase demands that all POV members in which we are trying to insert data to exist in the Essbase outline and when this is not true, Essbase switches its load method from Block Mode to Cell Mode. When this situation happens, one data load that would take only five minutes to complete may take several hours, degrading the Hyperion environment performance. Join us in this session to discover how we solved this problem in a dynamic way for any number of Hyperion Planning applications only using ODI data constraints and Hyperion Planning metadata repository to validate all POV members that will be used in the data load, guaranteeing the best performance and data quality on the Hyperion Planning environment.”

Kscope is the largest EPM conference in the world and it will be held on Hollywood, Florida on June this year. It will feature more than 300 technical sessions, five symposiums, deep dive sessions, and hands on labs over the course of five days. Got interested? If you sign up by March 25th you’ll take advantage of the Kscope early bird rates, then don’t waste more time and let’s be part of the greatest EPM event in the world.

Thank you very much everybody and we’ll be waiting for you at Kscope 15!

KScope15

Really using ODI 12c for Data Integration with Oracle Hyperion Planning/Essbase

Posted in EPM, Hacking, Hyperion Essbase, Hyperion Planning, Kscope 14, ODI, ODI 12c, ODI Architecture with tags , , , , , on July 18, 2014 by RZGiampaoli

On Kscope 14 Oracle announced that ODI 12c would not have support to Planning/Essbase and HFM. You people could imagine that this bomb would make a lot of noise in EPM world.

Because we did not liked the ODI 12c interface, we did not care too much about this announcement, but after Oracle post this: https://blogs.oracle.com/dataintegration/entry/using_odi_12c_for_data, we heard a lot of users complaining about it.

Also we thought a little about our dear users and imagined how our beautiful ODI environment that orchestrates the entire EPM environment would look like if we had to create a lot of extra steps to export data from tables to files and use some CMD commands to load it into Planning/Essbase/HFM.

We decided to make some tests to see if we were able to use ODI 11 KMs with minimal changes to them (in order to increase acceptance of this method), and do our part to help the EPM world.

After a couple of hours (it was more like 6 hours…) we figure out how to make the KMs from ODI 11 work with minimal changes in ODI 12. Let us stop talking and start showing what we need to change to make ODI 12 compatible with EPM.

Technologies Technologies We do not need to import or change anything in the Technologies tree because Oracle left all the technologies for the EPM tools there.

Jars

We do not need to change or copy any Jar because Oracle was kind enough to maintain them there. The only thing we need to do is to Import the EPM KMs from the ODI 11 to ODI 12.

KMs KMSWe have seven KM that we need to import from ODI 11.

For Essbase:

RKM Hyperion Essbase

LKM Hyperion Essbase DATA to SQL

LKM Hyperion Essbase METADATA to SQL

IKM SQL to Hyperion Essbase (DATA)

IKM SQL to Hyperion Essbase (METADATA)

For Planning:

RKM Hyperion Planning

IKM SQL to Hyperion Planning

After we import the KMs, we need to changes a few things on them.

RKMs

In the RKMs we saw the first changes Oracle did in the way ODI works. In the 11 version, ODI used the <%=odiRef.getModel(“ID“)%> substitution method to get the ID of the model there we would like to reverse. If we take a look in the master repository, in ODI 12 we’ll see a new column in the table: Master Repository This is the first thing we need to update in the RKM. ODI 11 used the ID, and if you try to use it as is, Oracle created a validation that makes the interface fails. (It was a good error message because we could figure out the problem reading it). Global ID Error Then, basically we need to update it to the new GLOBAL_ID.

RKM Hyperion Essbase

Step Reset MetaData:

We need to update the code:

SnpsReverseResetTable -MODEL=<%=odiRef.getModel(“ID“)%>

To

SnpsReverseResetTable -MODEL=<%=odiRef.getModel(“GLOBAL_ID“)%>

Step Start the Reverse:

We need to update the code to the new standard and also enclosure the substitution method with double quotes because now it returns a string instead of a numeric value.

imod = <%=snpRef.getModel(“ID“)%>

To

imod = “<%=snpRef.getModel(“GLOBAL_ID“)%>”

Step Set MetaData:

We need only to update to GLOBAL_ID.

SnpsReverseSetMetaData -MODEL=<%=snpRef.getModel(“ID“)%>

To

SnpsReverseSetMetaData -MODEL=<%=snpRef.getModel(“GLOBAL_ID“)%>

RKM Hyperion Planning

For the Planning KM we just need to update the code exactly in the same way we did in the Essbase KM.

Step Reset MetaData:

SnpsReverseResetTable -MODEL=<%=odiRef.getModel(“ID“)%>

To

SnpsReverseResetTable -MODEL=<%=odiRef.getModel(“GLOBAL_ID“)%>

Step Start the Reverse:

imod = <%=snpRef.getModel(“ID“)%>

To

imod = “<%=snpRef.getModel(“GLOBAL_ID“)%>”

Step Set MetaData:

SnpsReverseSetMetaData -MODEL=<%=snpRef.getModel(“ID“)%>

To

SnpsReverseSetMetaData -MODEL=<%=snpRef.getModel(“GLOBAL_ID“)%>

After this, just create a model and reverse something to test it. RKM Execution

Results are these: Planning ReverseEssbase Reverse

With this we are ready with the RKM part and we can start to create interfaces. Sweet.

IKMs

The IKMs were the easiest part. Oracle created some categories to group similar KMs. We need just to choose one of the 3 possibilities to make the KM shows in the Mappings (the new Interface object of ODI 12): Integration Type

We choose “Control Append” since the EPM IKMs does not have any type of integration strategy, but really does not matter with one you choose (I just like things organized).

IKM SQL to Hyperion Essbase (DATA)

We need to double click the KM and select the integration type: KM Integration type selectionIKM SQL to Hyperion Essbase (METADATA)

For the Metadata we need to do the same thing, add the Integration type (We chose “Control Append” again)

IKM SQL to Hyperion Planning

Same thing for the Planning IKM. Add the Integration type. (We chose “Control Append“) IKMs executionsWe are done with the IKM part.

LKMs

LKM Hyperion Essbase DATA to SQL

There is no change in this KM. Works as is. I like it.

LKM Hyperion Essbase METADATA to SQL

This was the tricky one. And not because it’s too different than the others. In fact, if it was not for a BUG in the API it would not need any changes to make it works (On Monday we will open a SR with Oracle about this bug).

The thing is: In the step “Begin Essbase Metadata Extract”, we have a line with the follow command: stagCols = ‘<%=snpRef.getColList(“”, “[CX_COL_NAME]=[COL_NAME] “, “, “, “”, “”)%>’

This command basically create a mapping between the temporary table and Essbase to start the extract. Essbase and its API is case sensitive with this information, and the pattern COL_NAME, in this version, seems to be bugged or changed the behavior (that I do not believe is the case, I will explain later).

In this version instead of bring something like this:

stagCols = ‘PARENTNAME=ParentName, MEMBERNAME=MemberName , ALIAS=Alias

It’s bring something like this:

stagCols = ‘PARENTNAME=PARENTNAME, MEMBERNAME= MEMBERNAME, ALIAS= ALIAS

The pattern is always returning in Uppercase, even when you change the execution to Stage or Target areas, and because of this, we got an error saying that the member PARENTNAME is not valid for the dimension XXXXX (And this does not happen in ODI 11 if you set the execution to Stage or Target areas).

Anyway, we start to test the behavior and we found out that in ODI 12 it is impossible (by now) to create an Oracle table like this: Oracle Model test Because the pattern COL_NAME always return, uppercase no matter what and it removes the double quotes from the expression. This is why we think it is a bug, because it removes a functionality from the Oracle DB as well.

For a quickly workaround (yes we know it’s ugly, but Oracle will fix that in no time and we’ll be able to get rid of it) we changed the code from:

stagCols = ‘<%=snpRef.getColList(“”, “[CX_COL_NAME]=[COL_NAME] “, “, “, “”, “”)%>’

To

stagCols = ‘<%=snpRef.getColList(“”, “[CX_COL_NAME]=[COL_DESC] “, “, “, “”, “”)%>’

After this, we just need to copy the column name to the description inside the attributes in the model: Models Details Double clicking the column, it will open its properties and in the description tab we only need to copy the name of the column to its description because the COL_DESC will get exactly what is written there (with one exception: if you put a quote or double quotes it will turn it in space, but we will not use it here). Description workaroundThis workaround does the trick: LKMs Executions Yes, we know that we could use a lot of other methods, as Flexfields for example, to do this or even do some code changes in the KM, but the idea here is to show that is possible to use ODI 12 with minor changes. And with this we can use ODI 12 in our EPM environment just like we do with the 11 version.

We didn’t test HFM here because we don’t have it installed in our infra, but probably the amount of changes for HFM is the same as it’s for Essbase and Planning.

We hope you guys like this and please give us any feedback if you guys implement this solution. As far as we test it, it should work exactly like ODI 11.

See you next time.

——————Edited on 07/22/2014——————-

About the Case sensitive issue of [COL_NAME].

We installed the ODI 12.1.2.3 today and repeated the tests we did in the previous version.

The [COL_NAME] for IKM is working, and that means, the issue to create an Oracle table with 2 columns with the same name but different case is solved.

But the [COL_NAME] for LKM still doesn’t works. All LKM have the same behavior and we can see that in the logs when we tried to replicate the IKM behavior in a LKM.

LKM testIKM test

As we can see, the behavior was fixed in the IKM but not in the LKM. This happens because the KMs are executed in different ways depending on its type (I, L, CKM) as we can see here:

http://docs.oracle.com/middleware/1212/odi/ODIKD/odiref_reference.htm#CIADFCFJ

For now please stick with our workaround until Oracle fix this in the LKMs.

See you next time.

KScope14 presentation: Unleashing Hyperion Planning Security Using ODI

Posted in Hyperion Planning, Kscope 14, KScope 15, ODI, ODI Architecture, ODI Architecture with tags , , , , , on July 14, 2014 by radk00

Hi all!

We are back from KScope14! What a great conference! KScope is indeed the world’s greatest EPM conference ever! It’s the best place to talk to the best EPM professionals, learn about cutting edge technology and, in our case, also present some of the work that we are currently working on 🙂

Here is the PDF containing our session at KScope14. Please feel free to write comments about it! It’s always great to receive some feedback!

KScope 14 Presentation

Hope you enjoy it! If you were not able to go to KScope14 this year, please make an effort to go next year (it will be held at Hollywood!!! Kscope15). It’s worth every dollar invested!

See ya!

 

 

10 Important Things to Improve ODI Integrations with Hyperion Planning Part 9 (Generic Data Load)

Posted in EPM, Hyperion Planning, ODI, ODI Architecture with tags , , , , , on April 25, 2014 by radk00

Hi all, today let’s see how we may create dynamic data load components for any number of Hyperion Planning applications. As you know, all data related to Hyperion Planning actually resides in an Essbase database and ODI has a Knowledge module that loads data into it called “IKM SQL to Hyperion Essbase (DATA)”. In this post we will tweak this Knowledge Module allowing it to read the Planning Metadata repository and dynamically build its own data store mappings and definitions depending on which Hyperion Planning application it will load data to. But first let’s take a look on how the original “IKM SQL to Hyperion Essbase (DATA)” works:

9_1

As you can see, it is a fairly simple KM with two main steps: “Prepare for loading” and “Load data into Essbase”. If you open “Prepare for loading” step you will see some Jython code there getting all configurations and options to be used in your data load and since our objective here is to make it dynamic to load any Hyperion Planning application we should focus on the bellow settings:

9_2

These settings are getting all the connection information related to the target data store in the Interface ODI component. If you already worked with Hyperion Planning and ODI, you know that the target data store contains all dimensions from a particular Hyperion Planning application and a column named “Data” that will contain the value for that particular intersection. This data store belongs to a data model, which is connected to a logical schema that is tied (by a context) to a certain physical schema that contains the application server/user/password/application/database.

In this example we will consider that we have five Hyperion Planning applications and all of them resides in the same server and uses the same Essbase username and password to connect to them, but if your architecture is different from that, please feel free to customize this information as well. In order to make this KM dynamic, let’s add two more options to it called “APPLICATION” and “CUBE” and modify the application and database information code to get their value from options instead of the target data store, like this:

9_3

These two options will contain two ODI variables that will indicate which application/cube you will be loading. Keep those two variables in mind, as we will talk more about those later in the post. Now if you open “Load data into Essbase” step you will see that this one is responsible to actually fetch the data from a table based in the source and target data store columns and mappings as below:

9_4

If we want it to load any Hyperion Application, we need to change this to not rely on the target data store (that is a static information) in order to know which dimensions to load for a specific application. If you are a reader of this blog, you probably already know where the answer to this problem relies, right? If you thought about Hyperion Planning metadata repository, you are right! Let’s change this KM to read the metadata repository, figure out which dimensions that application has and load data to it. First let’s create another option in this KM called “PLANNING_SCHEMA”, which will contain the actual database schema name where the Hyperion Planning application resides in Oracle. Then we will add the following query in the source tab of “Load data into Essbase” step:

9_5

Befire we continue, this command LISTAGG only exists in Oracle DB 11.2 or newer. If you DB is in an older version please try the WM_CONCAT command. This is a undocumented command but works very well. You need to change the SQL projection from:

LISTAGG(MAPPING, ‘,’) WITHIN GROUP (ORDER BY 1) MAPPING

To

WM_CONCAT (MAPPING)  MAPPING

Also you will need to create and add to this source tab a logical schema that points to the database that contains the Hyperion Planning repository. One very important thing about this technique: it only works when you have all Hyperion Planning applications repository in a single database because even though the schema name will be changed dynamically, giving us the possibility to load any number of applications with a single component, the connection to the metadata database will be a single one. Of course that you can further change your process to accept dynamic topology information as seen in our previous post here, but in this example we will keep things simpler to demonstrate the technique. After you add this query in source, it’s just a matter to change the target code to something like this:

9_6

Our source query will retrieve one row only (called #MAPPING) with the exact number of dimensions that one specific planning application has (based on “PLANNING_SCHEMA” option) and will pass it to the target tab, where it will be used to retrieve the data. But you may be thinking now…. What about the source table for this data load? It may have different formats, different names, and different sources… it will not work in a dynamic load component…. and you are right, it won’t, so that’s why we need something extra to make this component work: an INBOUND_GENERIC table.

INBOUND_GENERIC is a table that contains all possible columns (and each column is a planning dimension) for all Planning applications that you will need to load. For example: if you need to load five applications and together they have 15 distinct dimensions, you will have an INBOUND_GENERIC table with 15 columns (one for each dimension name) plus three auxiliary columns: APP_NAME (the Hyperion Planning application name), CUBE (the cube that you will load data within that application) and INTERFACE_NAME (that is the job name that is loading that data). These three columns give you the flexibility to have in the same table information about different applications, cubes and even inbound jobs! For example, if you have only one application, but contains several inbound jobs, you still may have one single Essbase load component for your entire architecture, saving you time for any maintenance or new development. Also a good performance trick here is to have this table partitioned by INTERFACE_NAME, so you may easily truncate the partition before any new inbound job runs (and even parallel inbound executions).

Also you may be thinking now: ok, I have 10 inbound jobs for 3 different applications. I may create this INBOUND_GENERIC table and load all data to it and have it loaded by this generic Essbase load component, but I’ll still need to create 10 inbound interfaces to load the INBOUND_GENERIC table, right? Yes, you will, but it is worthy. Here are some good points of this architecture:

  • You will have only one single component that loads data to Essbase and all the possible future maintenance will be done in just one point, not in multiple;
  • Since it is modular, it can be added to any other inbound interface easily;
  • ODI constraints work great in this architecture. Think about it: you may have one constraint for each column and you may validate it against the planning metadata repository in order to check if all data is correct before loading to Essbase, which means no more “Unknown Members”, see our post about it here;

After those changes, you should end up with an interface more or less like this:

9_7

It doesn’t matter what you do in your mapping here because the KM will build the columns based in the planning metadata repository. Also is important to notice that no transformation will be done in this component, so be sure to do you ETL when you load INBOUND_GENERIC table. In resume, this component considers that the data is already validated, transformed and ready to be consumed by Essbase. The filter here contains which application/cube/interface you are currently loading.

Now it is just a matter to add this interface to an ODI scenario and add the following ODI input variables to it:

  • APP_NAME: Planning application name;
  • CUBE: Planning cube/database name;
  • PLANNING_SCHEMA: Oracle schema name which contains the Planning Metadata installation;

Then you may loop this scenario for any number of applications as you may see in our post about it here.

Thanks everyone! I hope you have enjoyed it!

We’ll be at KScope 14

Posted in EPM, Hyperion Planning, Kscope 14, ODI, ODI Architecture, ODI Architecture with tags on March 18, 2014 by RZGiampaoli

Hi guys how are you doing? We are very happy and proud to announce that our article “Unleashing Hyperion Planning Security using ODI” was approved for KScope 14! We’ll be there to talk about one of our preferred subject areas: How to improve the EPM tools potential using ODI. We’ll be very pleased if you guys show up in our presentation. It’ll be great to find everyone there and talk about EPM and other cool stuffs.

In our session we’ll show some tricks to unleash the planning security and enable it to be create at cell level, automaticaly and using any attribute dimension or UDA.

You can expect a lot of technical informations, a new way to view and work with Hyperion Planning and ODI plus some real facts about what has happened after a successful implementation of this approach.

And remember, if you sign up by March 25th you’ll take advantage of the Kscope early bird rates, then don’t waste more time and let’s be part of the greatest EPM event in the world.

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

Kscope 14 Speaker