Archive for the Hyperion Planning Category

DEVEPM on ODTUG’s Technical Journal – Unleashing Hyperion Planning Security Using ODI

Posted in ACE, EPM, Hyperion Planning, InfraStructure, Kscope 14, ODI, Technical Journal with tags , , , , , , on April 22, 2015 by radk00

Hi all,

ODTUG’s Technical Journal just released our article about our Kscope 14 presentation “Unleashing Hyperion Planning Security Using ODI”. In this article you will find all the details on how to dynamically setup Hyperion Planning security based on the application metadata repository information. Here is the abstract:

“In some Hyperion Planning projects, security becomes so complex that it takes more than just granting access to some security groups on the high-level members of the dimensions. Global companies often have the necessity to create multiple Planning applications to meet the diverse regions of the globe. However, what happens when the business requires a single application with a single plan type that contains cost centers from different regions around the entity hierarchy? Moreover, is that data restricted according to the region’s security group using only one attribute dimension? Furthermore, does each user need to see aggregated values correctly for your region only? This paper demonstrates how to generate and maintain leaf-level member security settings based on attribute dimension on a global Hyperion Planning application using only ODI and Hyperion Planning application metadata repository information.”

Link to the full article.

ODTUG’s Technical Journal is an excellent place where you can find awesome technical content for free! You just need to become a member (create a free login at the ODTUG’s site). If you are not a member yet, I really recommend you to become one ASAP!

See you later!

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!