Archive for Essbase

Newer Entries »

Reviewing “Developing Essbase Applications – Hybrid Techniques and Practices” from an ETL Architect perspective

Posted in Book, EPM, Review with tags , , , , , on January 8, 2016 by Rodrigo Radtke de Souza

Hi guys! Today we are very happy to be reviewing “Developing Essbase Applications – Hybrid Techniques and Practices” for you. We will review the book not once, but twice and the reason behind that is that Ricardo and I (Rodrigo) have different backgrounds and this creates very different opinions around the book topics.

My professional career comes much more strongly from an ETL Architect position, so although I know the basics and principals around Essbase, for me it was always one more target/source system to pull/retrieve data from. Ricardo in the other hand has his major years working with EPM space and he knows/uses Essbase at its fullest. So based on that, let’s see how our reviews will differ from one another!

51QGgxNE-vL._SX340_BO1,204,203,200_

ETL Architect perspective review

“Developing Essbase Applications – Hybrid Techniques and Practices” may be considered the “second” book of the “Developing Essbase Application” series (“Developing Essbase Applications: Advanced Techniques for Finance and IT Professionals” is the first one and it is a must to have!) but it is completely different from the first book, since it cover different topics with some different authors. Speaking on topics, this is what you will find in “Hybrid Techniques and Practices”:

  • Exalytics
  • Hybrid Essbase
  • Young Person’s Guide to Essbase Cube Design
  • Essbase Performance and Load Testing
  • Utilizing SQL to Enhance Essbase
  • Integrating OBIEE and Essbase
  • Managing Spreadsheets with Dodeca
  • Smart View

The books is edited by Cameron Lackpour along with John Booth, Tim German, William Hodges, Mike Nader, Martin Neuliep, and Glenn Schwartzberg. Those names are more than a reference in the EPM community and it is great to see great minds putting their effort to create this book. We know that anything in life (it can be a book/work/presentation/project) will be great when the people that are involved in that process are passionate about what they do and this is well represented here. All these people just love EPM/Essbase and this passion alongside with their great knowledge about the topics created a fantastic book.

The chapters in the book are well separated, so you may read any topic in any order that you prefer. First topic will talk about Exalytics and its “Secret Sauce”. Hardware aficionados will love this chapter, but “not-hardware guys” like me may fell kind of lost. I got that Exalytics is great and do great things for Essbase, but all metrics and hardware comparison tables I’ll just leave for the hardware/environment guys 🙂

The second chapter for me is the shiny gem of the book and it alone is already worth the cost of the book (it couldn’t be different, since this chapter is also the book’s title). Hybrid is a very new concept in the Essbase world so you will read what it is, its architecture, what you can/can’t do with Hybrid right now and so on. For someone to understand what Hybrid means, he must also understand the concepts of ASO/BSO and these are well covered in this chapter as well. In resume, this is fantastic overview what Hybrid currently is and how to get along with it.

I read the third chapter before I got the book because it is available for free on OTN!  You may download it and read it entirely! This chapter I would recommend to everyone that will have any kind of iteration with Essbase, especially those people that ask requirements to be implemented :). Although the information may be considered “basic” Essbase design, it is the core foundation to have a good Essbase implementation. Fantastic chapter and as I said, it is free, so go there and read it (after you finish to read the rest of the post, of course)!

I was extremely glad to read chapter 4 because it talks about Essbase performance and load test. This kind of subject is very rare to be found and this chapter contains a very good overview on some techniques that could be applied to have a performance test on Essbase. This is a complicated topic since testing Essbase is not a trivial thing. There are too many factors to consider but this chapter does a great job explaining those details and how you could accomplish a good test scenario.

On the very beginning of chapter five, Glenn states that his chapter contains very basic information about SQL and if you know SQL already you may just skip the first part of it. Since SQL is in my blood for some years now, I skipped almost the entire chapter, just reading the end of it when it talks about how to use SQL within Essbase. This chapter is for people that uses Essbase but are now aware of what SQL could do for them. My opinion is that everyone that “works with numbers” should know at the least the basic concepts of SQL and it heavily applies for Essbase users.

Chapter 6 will give you a very good perspective of what you can do when reporting Essbase using OBIEE, its limitations, its good practices and the workarounds that may be used to implement some of the most common requirements that are “not natively” supported by the tool. A great chapter that I’ll just keep coming back to it whenever I have some doubt about the do’s and don’ts around Essbase and OBIEE integration.

I’ll talk about chapter 7 and 8 together since they talk about tools that can be used to interact with Essbase data. I must say that I just use Smart view to check if something got loaded or to do some basic tie out checks, so my iteration with those tools are pretty basic. Chapter 7 talks all about Dodeca and I got some pretty interesting information that I was not aware of, like its architecture, how it differs from other tools and so on. Chapter 8 goes deep into Smart View and tell us how it can be customized to allow the users to retrieve more value from the tool. From my personal perspective I don’t know how much I’ll be able to use it in my daily work but at least now I know where to find some answer if some more advanced Essbase user comes to talk to me about those tools.

Verdict

In resume, this is a great book to have and it will for sure bring you some great information about Essbase and the things that goes around it. My “personal award” goes to chapter two since I was really interested to learn what Hybrid was all about and it got accomplished reading it. One thing that I (of couse) would love to see would be a chapter around data integration but I get why there was none. The first book already talked about ODI integration with Essbase and since then there wasn’t anything new around this topic. At the last Oracle Open World, Oracle gave some insight around new ODI KMs for Essbase and Planning, as well as some new ODI components like Dimensions and Cubes, so maybe we may have something new to write about in a third volume of the book??? DEVEPM will be very willing to even contribute for this writing if that would be the case 🙂

And that’s it folks, I hope you have enjoyed this review. By the next days we will be posting about a more “Essbase experienced” kind of review, so let’s see how it goes!

See ya!

Advertisement

Leave a comment »

Using templates to create dynamic rules in Calcmanager 11.1.2.4

Posted in 11.1.2.4, ACE, BSO, Business Rules, CalcScript, Calculation Manager, Calculation Script, EPM, Essbase, Hacking, Hyperion Essbase, Hyperion Planning, New Features, Oracle, Performance, Templates, Tips and Tricks with tags , , , , , , , , , , , on January 1, 2016 by RZGiampaoli

Hi guys and happy new year!!!

And to start well the new year what’s best then a post?

Today I want to talk about the new version of Calculation manager (11.1.2.4). I know that it is out for a while now but still I think it has some cool features that are not explored.

In all Planning project, sooner or later, we come to a time that we need to create a currency conversion Rule (at least I like to create a custom Rule for performance reasons). Also some companies uses a lot of currencies.

Before continue I need to say that in our case I find out that less code is equal a less performance. What I mean by that is that for the forecast horizon range period for example, instead of use “IF” and test my 15/18 months horizon I triplicate the code using “FIX” and using “SET EMPTYMEMBERSETS ON ;”.

This set command ignores the “FIX” if it returns an empty set. This approach increases the performance a lot, some times more than 8 times (In this currency example, if I ran it at channel level with “IF”, toke 8 hours, with “FIX” takes 1 hours).

Ok that means I rarely use “IF” in my Rules.

Well, you can already imagine the size and row boring and prone error is the Rules if I use only “FIX” right? However, with the “Template” feature in calcmanager and the ability to call any template or rule using a script this nightmare turns in to a dream!

Let us see how it works!

A Currency conversion for forecast applications normally has two parts:

First parts is a period range part.

Second part is the currency conversion itself.

With calcmanager, we can create two template, one for the period and the other for call the currency conversion part.

Then for the Currency conversion calculation, I create a simple core template with just a formula and a script on it:

UDA Loop Template

The “dtp_Quote_UDA”  is a DTP (design time prompt) variable with a function that will insert double quotes in every value that comes from the “dtp_UDA” DTP variable (this will be used to get values from the outside template), this way we can have use just one variable to do two papers, currency name and UDA value. The code is:

@QUOTE([dtp_UDA])

The inside the Currency calculation script we will have:

Currency Script

As we can see inside the script, I used the “dtp_Quote_UDA” as well the “dtp_UDA”. This simplify the amount of parameters I need to pass and the maintenance as well. Let’s think, we need the same information, one with double quotes, for the UDA values and other without quotes, for the Rate name.

With this technic we need to pass just once the value, let’s say BRL, and in the code Calcmanager will replace before the execution in all places, and we’ll have @UDA(Entity,”BRL”) as well HSP_Rate_BRL.

This is awesome because now I have just 8 line of code that will be transformed in any amount of times I want. The best thing is, or everything is right or everything is wrong J

Because calcmanger now we have a layer between the code written and the code generated, and this is pretty cool because opens a huge windows for creativity. You can even generate the entire code dynamically.

Ok, the next step is to loop this template once for each currency we have. For this, I created another template. This one will be used for the Forecast horizon period range as well for loop the currencies.

Period loop template

Again, the code is pretty simple, just  two fixes and one script.

For the “Period FIX” we use two DTP variable to get the value of Year and period from the outside rule ([dtp_Period] and [dtp_Year]).

The product fix is just something related with our architecture and we do not need to bother about it.

Now the “Loop Currency” is a script that will call N number of times our first template. How can we do that with a script?

Basically every time you drag and drop a template inside a rule or to another template behind the graphic design calcmanager generate a command line. This code exists thanks to its API, and you can use it to manipulate and generate almost any kind of code inside calcmanger.

Currency loop template

As we can see, inside the script we have a “Fix” for the USD currency, (that is the only different conversion) and one row for each currency.

Each row is calling a template “%Template(name:=Currency Conversion – 2 – UDA Loop” from an application “application:=”WWOPS””, a plan type, “plantype:=”Pnl””, and is passing two DTS values, one for the UDA and other for the Entity, “dtps:=(“dtp_UDA”:=[[AED]],”dtp_Entity:=[[dtp_Entity]])”.

As you can see, you can pass a DTP variable using the variable itself (dtp_Entity:=[[dtp_Entity]]).

If you want to create this API code and don’t know how to write the right syntax you can just drag your template to a rule/template, set everything and change your view to “Edit Script” or “View Script”.

Edit script

Now we just need to create the rule that will call this template for the three range of periods we have:

Currency rule

Again a simple design with a small amount of components. Here we have our SET commands, a main fix and the three templates, each one calling the previous template for a different period of range.

Period Range

The final result is a Rule with 1213 rows generated from a 8 rows template. This is the magic of calcmanager and templates. You can simplify everything, you can create dynamic aggregations, that will change depending of the application and cube, you can create codes that changes depending of the member that is coming from the forms, everything with small set of code that is reusable anytime we want!

Rule code 1Currency code 2

…….

A dynamic way to build a currency rule in calcmanager. A lot faster to build and a lot easier to maintain, since if a new currency start to be used you just need to copy and paste one line in “Currency Loop” script, change the currency and it’s done.

Build Rules using templates looks more work and some time a little bit complicate but I remember well how much time I expend changing BRs and I can guarantee that this way is much faster and easier to develop and mainly to maintain.

In the end we just create a Rule and two templates that contains just one core calculation, in my case a script calling 47 times this core, some fixes, and that’s all. It was less than 60 rows of written code to generate 1213 rows. Pretty good for me 🙂

Rules ante templates

Hope you guys enjoy and I wish a happy new year for all and you dears ones.

Happy new year!!!! A new year full of surprises!

 

9 Comments »

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

Posted in Hyperion Planning, ODI, Configuration, 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.

1 Comment »

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 Rodrigo Radtke de Souza

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!!!

2 Comments »

Essmsh/OdiOSCommand/Echo Trick

Posted in EPM, Essbase, Essmsh, MaxL, ODI, Tips and Tricks with tags , , , , , on March 9, 2015 by Rodrigo Radtke de Souza

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!

7 Comments »

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

Posted in ODI Architecture, Hyperion Planning, EPM, ODI 12c, ODI, Kscope 14, Hacking, Hyperion Essbase 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.

14 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 »

10 Important Things to Improve ODI Integrations with Hyperion Planning Part 2 (Procedure Loop)

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

In the last post we changed the KM to create a Hyperion Planning option, making the KM a little more dynamic. But we also need to loop this interface by any number of applications. We have some ways to do that and this post will be describing the best way to do that, in my opinion.

Normally we learn to loop in ODI using a count variable and a check variable. In our case one variable is used to get the number of applications to be looped and another variable is used to identify that application number and transform it into the application name itself. The flow should be something like this:

We set a variable with the initial value, enter in the loop and increase the variable. The App_Cnt variable uses the value of the incremental variable. The number of applications and its names are inserted inside a parameter table. The same variable checks if we need to loop more. The App_Name variable uses the App_Cnt value to take the proper App name. Then we load the data into Hyperion Planning, putting the #APP_NAME variable into the KM APPLICATION option (created in the last post), making the application name dynamic. This leads to a lot of work and variables to maintain.

There’s an easier way to do the same thing using ODI procedures. In ODI procedures we have the source and target tabs concept. Basically the command in the target tab will be executed for each row that returns from the query in the source tab. Also we can pass the information that returns in the source tab query to the target tab command. That means we can simplify the above loop just doing this:

In the command on source tab we only need to create a query that will return all apps that we want to loop. The application name will be returned in the APP_NAME column.

For the target tab we need a little more work but it is also easy. First we need to create an ODI package containing the ODI interface used to load into Hyperion Planning. Then in the target tab we need to set it as “ODI Tools” technology and write an OdiStartScen command to call the interface package passing the name of the application (#APP_NAME from the source tab) as an input parameter. This procedure will call the interface scenario for each row that results from the source tab query. This will allow us to pass one application name at a time to the scenario, creating the same effect as the previous loop but in a much simpler way to maintain.

This method works for every kind of looping in ODI, especially with Hyperion Planning in a multiple application environment. In the next post we’ll take a deeper look on how we can use this together with the planning repository metadata to create dynamic interface loads.

6 Comments »

ODI Modeless presentation

Posted in ODI Architecture with tags , , , , , on October 10, 2012 by RZGiampaoli

Hi all, please stay with a presentation about the new ODI concept to create model-less Hyperion planning integrations.

This presentation will cover:

The default EPM development architecture for Oracle Hyperion Planning;

The new architecture concept for EPM and Oracle Hyperion Planning

The advantages between the default and the new EPM architecture

http://youtu.be/2CMeM2fapOU

Greetings

1 Comment »

Newer Entries »