Archive for Architecture

DEVEPM will be at KScope 16!!!

Posted in ACE, EPM, Kscope, Kscope 16, ODI, ODI Architecture, ODTUG, Tips and Tricks with tags , , , , , on December 14, 2015 by radk00

Hi all, how are you doing? We are very happy to announce that, not one, but TWO presentations were approved for KScope 16! Here they are:

1) Incredible ODI tips to work with Hyperion tools that you ever wanted to know
“ODI is an incredible and flexible development tool that goes beyond simple data integration. But most of its development power comes from outside the box ideas.
Did you ever wanted to dynamically run any number of “OS” commands using a single ODI component?
Did you ever wanted to have only one datastore and loop different sources without the need of different ODI contexts?
Did you ever wanted to have only one interface and loop any number of ODI Objects with a lot of control?
Did you ever need to have a “Third Command Tab” in your procedures or KMs to improve ODI powers?
Did you still use an old version of ODI and miss a way to know the values of the Variables in a scenario execution?
Did you know that ODI has 4 “Substitution Tags”? And do you know how useful they are?
Do you use “Dynamic Variables” and know how powerful they can be?
Do you know how to have control over you ODI priority jobs automatically? (Stop, Start and Restart scenarios)
If you want to know the answer of all this questions please join us in this session to learn the special secrets of ODI that will take your development skills to the next level.”

The idea behind this presentation is to show the main secrets we use daily basis to improve code quality and re-use, as well explain why and what more we could do with each of the tips we will present. It’ll be a extreme helpful presentation with a lot of cools stuff and real life example.

2) Take a peek at a smart EPM global environment
“In a fast-moving business environment, finance leaders are successfully leveraging technology advancements to transform their finance organizations and generate value for the business.
Oracle’s enterprise performance management (EPM) applications are an integrated, modular suite that supports a broad range of strategic and financial performance management tools that helps business to unlock their potential.
A global financial environment contains over 10000 users around the world and rely on a range of EPM tools like Hyperion Planning, Essbase, SmartView, DRM and ODI to meet its needs.
This session shows all the complexity of this environment, describing all the relationship between those tools, the technics used to maintain such a large environment in sync, and meeting the most varied needs from the different business and laws around the world to create a complete and powerful business decision engine that takes a global company to the next level.”

The idea in this presentation is to show the design we uses in one big client and why we use it, the gains and how it works. In fact for the ones that follow our blog and our presentations, this will be the tie point of everything we talk about. It’ll be a excellent presentation for people looking for ideas of integrated environments.

We are very excited about it since we’ll be talking about how to improve EPM tools potential using ODI and also how EPM tools connects with each other in a global financial environment. 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 stuff!

Kscope is the largest EPM conference in the world and it will be held on Chicago, Illinois on June 2016. 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. If you are still unsure about it, read our post about how Kscope/ODTUG changed our lives! Kscope is indeed a life changer event!

kscope16

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

Advertisements

Oracle Database Developer Choice Awards!

Posted in ACE, Oracle Database, OTN with tags , , , , , , , , , on September 28, 2015 by RZGiampaoli

Hi guys, just an important reminder…

The Oracle Database Developer Choice Awards vote period is almost in the end! Please vote for your favorite community experts in SQL, PL/SQL, APEX, ORDS, DB Design!

Voting closes 15 October #odevchoice https://community.oracle.com/community/database/awards

For people that doesn’t know what this is all about here’s the Oracle announcement 🙂

You are part of an enormous, worldwide community of Oracle Database technologists, with deep expertise in a variety of areas. And you have likely heard of Andy Mendelsohn, who runs the Database Server Technologies group at Oracle, and has been deeply involved in Oracle Database for decades. This year, Andy has established a new awards program to honor and give more visibility to members of the community who combine technical excellence with a commitment to sharing their know-how with developers.

The Oracle Database Developer Choice Awards – https://community.oracle.com/community/database/awards

This is a different kind of awards program for Oracle; it’s called “Developer Choice” because with these awards, our users nominate people and ACE judges come up with a list of finalists. Thirty-two finalists have been selected in five categories: SQL, PL/SQL, Application Express, ORDS and Database Design.

Now it’s time for everyone in the community determine the winners by voting at the Oracle Technology Network: https://community.oracle.com/community/database/awards. Winners will be announced at the YesSQL celebration at Oracle Open World 2015 on 27 October.

That’s right. You get to help decide the winners: it’s by popular vote. So please take a moment to visit the awards page, check out the lists of finalists in each category and cast your vote!

These are the categories:

Categories

Don’t forget to vote. This is really important for the community.

Thanks you and see you soon!!

Vote today

Tips and Tricks: Working with ODI Variables and Global Parameters

Posted in ODI Architecture with tags , , , , , , , , , , , , on September 7, 2015 by RZGiampaoli

Hi guys, today we’ll talk about some very simple but powerful technic that we always use in our integrations. Its joins two concepts together and make our lives a lot easier and our integration a lot more dynamic. We are talking about variables and the concept of “Global” parameters.

In our integrations we never, ever have anything hard coded. Every time you hard code something it will come back to bite you in the future that is for sure.

Then the first thing we do in a project is to create a table that we call ODI_PARAMETER. This table will contain all configuration and parameters that needs to be validated, hard coded and so one.

I like to create this table in our work schema (to make easier to use) and its look like this:

ODI_PARAMETER Table

The “SESSION_NM” is used to make the variable reusable in all scenarios that we want, meaning we’ll have only one variable for packages in the project or even for all projects (if we make this variable global in ODI).

How it works? First of all we need to get the “Session Name” for our “Scenario/Package”. Why did I say “Scenario/Package”? Because the result could change depending if you are running a Scenario or a Package. Let me explain this.

To get the “Session Name” in ODI we use an ODI Substitution method called “odiRef.getSession”. This method has other parameter that could return the Session ID, and other stuff but what matters for us is the “SESS_NAME” parameter, that will return the name of the session, the same thing that appears in the operator when we run any object in ODI.

Why I said object? Because if you run a variable the session name will be the variable name. If you run an interface, the session name will be the interface name, it goes to procedure, package and scenario, and that is why I separate the “Scenario/Package” because if we do not pay attention, the name of the package would be different of the name of the scenario, causing a problem when we run one of them.

Let me show how it works. First of all, we’ll create a Global ODI variable called SESSION_NM (could be whatever you want, I just like to call it like this) and we’ll put this code inside of it:

SESSION_NM Variable

After that, we will run this variable to see the results:

SESSION_NM Results

As we can see, the value of the variable was the name of the Variable itself. Now, let us create a package, put this variable inside it, and see what’s happens:

Package test 1

Here is what the interface looks like and above its results:

Package test 1 results

As we can see the result of the variable is the same as the session but in UPPER case since I create the variable like this. But why I did that? Let me create a scenario of this package to show you why:

Scenario Creation

And this is why I create in the variable getting the result and put in UPPER and why I said we need to worry about some peculiarity regarding Scenarios and Packages. When you create a scenario will have the name of the interface in UPPER case and also, NO SPACES. Now, if we run the just created scenario we will have:

Scenario results

Meaning, if we will use the result of this variable as a way to return data from a table, we’ll have a problem because it’ll not find the same result if you run the package or the scenario of that package.

The easiest way to resolve that is to have the name of the main scenario (the scenario that will contain all the other scenarios) with no spaces and no special characters (ODI also transform special characters like % in to _).

Doing that and we are good to continue as we can see below:

Package results

Now we have the same results if we run the package or the scenario.

Ok next let us create another variable to return the LOG_PATH, the path where we will store all our logs from our integrations. The code that we will use for this variable is:

Query ODI_PARAMETER

As we can see we are using the result of the “SESSION_NM” variable in this “LOG_PATH” variable. This is what’ll make this variable reusable in all “Packages/Scenarios/Procedures”. Let us insert a value inside our ODI_PARAMETER Table and run the Package to see the results:

Insert Test 1

Package 1 Results

Now let us create a new package with a different name, use the same variable as above, and insert a new line in our ODI_PARAMETER table for the new interface:

Package 2 results

See, same code, two different results. That means, 90% of the interfaces needs just to be duplicated and the parameters in ODI_PARAMETER needs to be inserted for the new interface and it is done. Also, we don’t need a ton of variables to get different results. And there is more.

The code of the variable also does not change that much. For a new variable, we just need to duplicate the LOG_PATH variable and change the PARAMETER_TYPE, PARAMETER_NAME and PARAMETER_VALUE to get any other information from the ODI_PARAMETER. That means, easier to maintain.

However, let us not stop here. In this example, we are getting the LOG_PATH for our logs in our integrations. Normally this path does not change from integration to integration. What changes is the name of the integration that we are logging right? In addition, with our SESSION_NM variable we could just put in our LOG_PATH variable the root of our LOG folder and then use like this:

#LOG_PATH\#SESSION_NM

This would make the LOG_PATH equal for all integration right. Nevertheless, in the way we create our variables we will need to insert one line for each integration in our ODI_PARAMETER table right.

Well, we just need to change a little bit our code in our variable to create the concept of GLOBAL parameters. How it will work:

First, we will delete the two lines we just created and then we will insert just one line in ODI_PARAMETER table:

Insert Global

Now we just need to change the code from our LOG_PATH variable to this:

Query ODI_PARAMETER global

And here we go:

Global results

We have one global parameter that can be used for all integrations. And the cool thing is that the code above tests if we have a parameter for the actual SESSION_NM and if not it’ll get the parameter from the GLOBAL parameter, meaning if any integration needs a special LOG_PATH or something you just need to insert a new line in the ODI_PARAMETER to get the value just for that integration:

Global results exceptions

This will guarantee that you never ever needs to touch your code again to test or change anything that the business ask you for.

As I said, is a simple but very powerful tool to use.

Hope you guys enjoy and see you soon.

Stopping ODI sessions in an automated way

Posted in Hacking, ODI, ODI 11g, ODI Architecture with tags , , , on September 4, 2015 by radk00

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.

Toolbox

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:

  • Restartloadplan.bat
  • Restartsession.bat
  • Startloadplan.bat
  • Startscen.bat
  • Stoploadplan.bat
  • Stopsession.bat

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:

Command on Source

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:

Agent

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:

Variable

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:

Command on Target

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!

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.

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!