Hi all! Quick post today. DEVEPM recorded another OTN 2MTT. Now we are talking about “Incredible ODI Tips to Work with Hyperion Tools (Kscope16 Preview)”. In this 2MTT we give an idea of what will be one of our session at Kscope 16 that will happen on June 27, 2016, 4:30 PM – 5:30 PM. Take a look and join us at Kscope 16!
Thank you guys and see you soon 🙂
Hi guys how are you? It has been a long time since last time I wrote something but it was for a good reason! We were working in our two Kscope sessions! Yes, this year we will have 2 sessions and I think they will be great!
Anyway, let us get to the point!
Today I want to talk about something that should be very simple to do it but in the end, it is a nightmare…. Zip a file in a remote server…
A little bit of context! I was working in a backup interface for one client and, because their cubes are very big, I was trying to improve the performance as much as I can.
Part of the backup was to copy the .ind and .pag files and the data extract files as well. For an app we are talking in 30 gb of .pag and 40 gb of data extract files.
Their ODI infrastructure is like this:
Basically I need to extract/copy data from Essbase server to the disaster recovery server (DR Server). Nothing special here. The problem is, because the size of the files I wanted to Zip the files first and then send it to the DR server.
If you use the ODI tools to Zip the file, what it does is bring all the files to the ODI Agent server, zip everything and the send it back. I really do not want all this traffic in the network and all the time lost in this process (also, the agent server is a LOT less powerful then the Essbase server).
Then I start to research how I could do that (and thank you my colleague and friend Luis Fernando Cairo that help me a lot doing a lot of tests on this)
First of all we have three main options here:
Ok, I figure out that in the end I’ll need to create/install something in the server… and I rate it. Well, let’s at least optimize the problem right!
Then I was thinking, what I have in common in all Hyperion servers? The answer is JAVA.
Then I thought, I can use the JAR command to zip a file:
jar cfM file.zip *.pag *.ind
Where:
c: Creates a new archive file named jarfile (if f is specified) or to standard output (if f and jarfile are omitted). Add to it the files and directories specified by inputfiles.
f: Specifies the file jarfile to be created (c), updated (u), extracted (x), indexed (i), or viewed (t). The -f option and filename jarfile are a pair — if present, they must both appear. Omitting f and jarfile accepts a “jar file” from standard input (for x and t) or sends the “jar file” to standard output (for c and u).
M: Do not create a manifest file entry (for c and u), or delete a manifest file entry if one exists (for u).
Humm, things start to looks better. Now I had to decide if I would use the Invoke command or Psexec.
I started trying the Invoke command, but after sometime I figure out that I can’t execute the jar command using invoke.
Then my last alternative was Psexec.
The good thing about it is that is a zip file that you need just to unzip in the agent server, set it in the Environment Variables (PATH) and you are good to go.
It works amazingly.
You can run anything remotely with this and it’s a centralized solution and non-invasive as well (what I liked).
You just need to:
psexec \\Server -accepteula -w “work dir” javapath\jar cfM file.zip *.pag *.ind
Where:
-w: Set the working directory of the process (relative to remote computer).
-accepteula: This flag suppresses the display of the license dialog.
There’s one catch, for some unknown reason, the ODI agent does not get the PATH correctly then you need to use the complete path where it was “Installed”. The ODI is like this:
OdiOSCommand “-OUT_FILE=Log_Path/Zip_App_Files-RUM-PNL.Log” “-ERR_FILE =Log_Path /Zip_App_Files-RUM-PNL.err”
D:\Oracle\PSTools\psexec \\server -accepteula -w \\arborpath\APP\RUM\PNL\ JAVA_PATH\jdk160_35\bin\jar cfM App_Files-RUM-PNL.zip *.pag *.ind
With this, we will have a process like this:
This should not be something that complicate but it is and believe me, I create a very fast process and the client is very happy.
I hope you guys enjoy it and see you soon.
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:
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:
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.
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.
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”.
Now we just need to create the rule that will call this template for the three range of periods we have:
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.
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!
…….
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 🙂
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!
Hi all! In today’s post we will talk about how you may create automatic processes to stop ODI sessions. But first let’s think why/when we should automate this kind of task.
Everybody knows that the basic way to stop an ODI session would be to go to ODI Operator, right click in a running Session and select “Stop Normal”/”Stop Immediate”. Obviously it works just fine, but it requires someone to log in, select the jobs and stop them. There will be cases that you will want to stop those scenarios without any human intervention.
So let’s imagine that you have a critical ODI job that must make sure that some other secondary ODI jobs are not running before it actually starts. Maybe you could add an OdiSleep object in the critical ODI job, wait a little bit, check if the secondary jobs are still running, sleep again and so on. It is safe approach, but sometimes this critical ODI job is also top priority and it could have permission to stop all other secondary ODI jobs before it actually starts.
Or maybe you could have an execution window that must be respected and all ODI jobs that crosses a specific range of time should be stopped no matter what. I could write some other examples, but you already got the idea. So, how do we accomplish that in ODI?
If we take a look on ODI Toolbox panel, we are going to find things like OdiStartScen and OdiStartLoadPlan, but nothing related to stop, cancel or kill a session.
I’m not sure why Oracle didn’t put this kind of objects in the Toolbox, but if we go to the ODI agent bin folder (oracledi\agent\bin) we are going to see some interesting .bat (.sh on linux) jobs there:
We may right click/edit each of those to get more information about them. Today we are interested in the last one “Stopsession.bat”. Its syntax is pretty simple:
stopsession <session_number> “-AGENT_URL=<agent_url>” [“-STOP_LEVEL=<normal(default)|immediate>”]
Pretty cool and easy to use. We may just add this call to an ODI procedure and create some logic to stop all sessions that we want. Let’s build an example and see how it would look like. Imagine that you have an ODI_JOB_A that needs to stop ODI_JOB_B and all its children (if there is any of those running) before it continues its tasks. To accomplish that, we would need to create an ODI procedure and use the command on source/target technique to select ODI_JOB_B and its children that are currently running. It would look like this:
On “Command on Source” we would write a SQL against the ODI metadata repository checking for all ODI_JOB_B sessions (and its children) that are currently running. Of course that this SQL is just an example, you may tweak it to fit your own requirements. Here we are just querying the SESS_NO that belongs to a running session of ODI_JOB_B and UNION that to all running children of a running ODI_JOB_B session.
On “Command on Target” it should be just a matter to add the stopsession cmd on “Operating System” technology, but it is not that easy. Let’s analyze the stopsession cmd again:
stopsession <session_number> “-AGENT_URL=<agent_url>” [“-STOP_LEVEL=<normal(default)|immediate>”]
session_number is a value that will return from our “Command on Source” tab, so we are good.
stop_level may be set as normal or immediate, so we are also good here.
The problem that we have is the AGENT_URL. A valid AGENT_URL would look like this:
-AGENT_URL=http://ODISERVER:9001/oraclediagent
This URL is composed with the information that is set in our Topology information, like the one below:
The problem here is that we don’t have any ODI substitution API that return this kind of information. The closest that we have is <%=odiRef.getSession(“AGENT_NAME”)%> that just returns its name, nothing more. To get around this situation, we will need to query ODI metadata repository again and compose this URL using a SQL against SNP_AGENT table. Let’s create one ODI variable for that like the one below:
Here we using the AGENT_NAME API function to get the right information for the running agent. Now we are able to finish our procedure with the “Command on Target” command:
And that’s it! Just add the AGENT_URL refresh variable and this procedure in the very beginning of ODI_JOB_A package and you will have it stop session ODI_JOB_B and its children before it moves on.
Hope you liked it! See you soon!
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 
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 
We 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: 
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). 
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. 
Results are these: 
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): 
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: 
IKM 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“) 
We 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: 
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: 
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). 
This workaround does the trick: 
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.
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.
More to life… jason’s hyperion blog Glenn Schwartzberg’s Essbase Blog Hooked On Hyperion
DEV EPM 