Archive for Essbase

Article “Unleashing Hyperion Planning Security Using ODI” was published in OTN

Posted in Configuration, Hyperion Planning, ODI, Security with tags , , , , , , , , , , , , , on July 16, 2015 by RZGiampaoli

Hi guys!

OTN just published a new article from devepm entitled “Unleashing Hyperion Planning Security Using ODI”.

This article is all about Planning security and how you can use ODI to create cell level security based in anything you like (in our case is Attribute Dimension).

This is a study case and it’s in production for 3 years right now.

Please take a look and let us know your thoughts.

Thank you and see you soon.

Advertisements

DEVEPM on 2MTT: “Loading Data into Essbase with ODI” – Kscope15 is just around the corner!

Posted in 2MTT, ACE, ArchBeat, Essbase, Hyperion Planning, KScope 15, ODI with tags , , , , , , on June 5, 2015 by radk00

Hi all,

Kscope15 is almost upon us! Just two weeks now for the greatest EPM event of the world! And in order to get ready for that, DEVEPM recorded another video for OTN’s 2MTT: “Loading Data into Essbase with ODI”. This tip is based on our Kscope15 session “No more Unknown Members! Smart data load validation for Hyperion Planning using ODI”, that will presented at ODTUG Kscope 2015 in Hollywood, FL on June 22, 2015,  11:30 am – 12:30 pm. It’s very hard to resume everything in only 2 minutes, but you can get a glimpse of what is going to be presented at Kscope!

If you are planning to go to Kscope, please don’t forget to meet us there! It’s going to be a pleasure for us. This year DEVEPM will try to blog daily posts while we are there, kind of “covering” the conference from the DEVEPM perspective. I say that we will “try” to post it daily because Kscope is so intense and full of excellent things to do that “free time“ is something that you do not get easily (if at all).

If you are already registered to Kscope don’t forget to plan your schedule with Kscope15 Mobile App (Cameron describes all the details in his blog). And if you get some free time after the conference while you are at Florida, you may check Ludovic’s post about “What else can you do in Sunny Florida?” which includes some nice places to visit while you are there.

Thanks all, hope to see you at Kscope15!!!

Essmsh/OdiOSCommand/Echo Trick

Posted in EPM, Essbase, Essmsh, MaxL, ODI, Tips and Tricks with tags , , , , , on March 9, 2015 by radk00

Hi all, today’s post is a very simple but useful trick using ODI and Essmsh command. As you know, the MaxL Shell (essmsh) is a pre-parser mechanism for entering MaxL statements. In order to use it, first you need to log into a server:

1_essmsh

After you login, you may start issuing MaxL commands against that specific host. But this is an interactive login, where you need to manually specify the user/password/host. Fortunately essmsh contains some options that allow us to pass the login (-l) and the server (-s) as parameters. It looks like this:

essmsh -l ServiceUser password -s ANY.SERVER.COM

This is great, but imagine that you want to login and execute some essmsh commands to automate a specific process. Imagine that after the login you want to run the following commands:

  • set message level error;
  • display partition on database CONWF_M.WrkForce advanced;

Here is where the “echo” trick comes in. essmsh have another option (-i) that starts a MaxL session which reads from <STDIN>, piped in from another program. So basically you can do the following:

echo set message level error; display partition on database CONWF_M.WrkForce advanced;| essmsh -l ServiceUser password -s ANY.SERVER.COM -i

Now this single OS command will connect to a server, login and execute the set message and display commands. Very simple, but very powerful!

It gets even better if you add ODI to the game. ODI is extremely flexible to develop this kind of automation processes. Here is what it would look like. Create an ODI procedure and go to “Command on Source” Tab. Select the “Schema” where you want to execute your essmsh commands:

2_essmsh

No code is needed here, since we will just use it to retrieve the Essbase connection information, like user/pass/server/app/database. On the “Command on Target” tab, you may add something like this:

3_essmsh

Here we are calling a login on essmsh and we are “echoing” some MaxL commands against an Essbase application/database using the –i parameter. The output of this command will be redirected to the -OUT_FILE where you may check it or use it in another process. Very handy and easy for a lot of Essbase automation with ODI.

Hope you liked this quick tip! See ya!

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.