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 🙂
Oracle just released his new addition to their Metadata line of product, Oracle Enterprise Metadata Management or OEMM. Today we will talk about the Installation of the OEMM.
First, you need to download it. You can get it from:
https://edelivery.oracle.com or in www.oracle.com
By now, there is only a windows version of this product. This is the first indication that this product should have a new version very soon, probably with the option to deploy it in weblogic and make it more “Enterprise”.
After download, open the Zip file and double click the .exe file on it.
Choose between OEMM and OEMM for OBI:
Next you need to choose the installation and data folder:
Next is the port that will be used by Tomcat (this is another indication of an earlier release):
After this initial setup we need just to wait it to finish.
With the Installation done, you can configure the service (If you want to change the User that the server will use or the java memory settings or any other motive you could have) or just start the service as is.
The options that we have in the service configuration is the same ones that any windows service has:
General configuration, to set if you service will startup manual or automatically:
Log On Configuration, to set with account the service will use to log in:
Logging, to set the log level of the service and path:
Java, to set Java Heap memory and options:
Startup, to set some option to be used when the service starts:
And Shutdown, to set the shutdown properties:
In our case, we just started the service by clicking in “Start (Restart) Server as a Service” in the “Start” menu:
We also can start the service in “Administrative tools”/“Services”:
After that, you can go to any certified web browser (that includes Firefox 24+, Google Chrome 33+, Internet Explorer 10.x, 11.x and Safari 7.x) and type:
(The port can vary if you changed it in the installation)
The first screen that you will see is for configure a database to be used as repository:
In the oracle documentation (after installation, you can find it here: INSTALLATION_FOLDER\Documentation\ReadMe), they have a database preparation that should be done before to attempt to create the repository.
We just created a User MM_REP and grant it a DBA rights. Also, we had to grant execute to the DBMS_LOCK package and to CTX_DLL.
CREATE USER MM_REP IDENTIFIED BY xxxxxxxxx;
GRANT DBA TO MM_REP;
GRANT EXECUTE ON DBMS_LOCK TO MM_REP;
GRANT EXECUTE ON CTX_DDL TO MM_REP;
After set, the configuration screen and clicking the “Save” button it will start to create the repository:
As a good developer, I did not read the documentation before to start install, then I got 2 erros (regarding the two grants I mentioned before). Every time I got an error I had to delete manually all the objects from the schema:
SELECT ‘DROP TABLE ‘||TABLE_NAME||’ PURGE;’ FROM USER_TABLES;
Because some reason the sql that should delete this objects are not working.
After the repository installation, a User and password screens appears:
User: Administrator
Password: Administrator
That is it guys, the Oracle Enterprise Metadata Managing is installed, up and running. Now we are figuring out what it does and what it is good for. Soon we will write another post about that but as far we played along, we may guarantee that it does not replace DRM and in fact it has nothing to do with it. OEMM is all about metadata governance, how your metadata is connected between the processes and what is the impact when you need to change it.
Thanks guys, I hope you enjoy it. See you next time.
Today we will talk about something very important when we work with Hyperion Planning: its repository.
Today I can say that, if it was not for the Planning repository, 10% of the solutions/issues that I had in my clients should not have been implemented/fixed and 50% the solution/issues should take 10 times more to be implemented/fixed.
Well, as we are talking about integration in this post I will focus on this for now but I promises that I will talk about the repository itself in another time, focusing in Planning development, security and maintenance.
Let us start. First, what is the planning application repository? Basically it is used for manage all existing metadata in an planning application, which means, we can find all forms information, security, dimensions, settings, workflows and other stuffs of an application there and the best part is that we’ll find this in a relational table (normally oracle).
This allow us to easily get any information from any applications and, in an integration perspective, creating smarter integrations.
I said that because normally when we create an ODI interface to load metadata we basically use an ODI datastore for the mapping. as a source table to a planning dimension, and then load everything that we have in that table into planning.
However, is this a smart way to load metadata? I can say no. I have a good example in a big client that I’m currently working. They have an entity dimension with more than 23000 members, and in every maintenance cycle, only 1% of those entities changes or are added. That means that only 230 cost centers are new/changed every month. Well, in a normal ODI development, you will load all the 23000 members every month! And it is not only that: my client has seven applications, all of them with more or less the same size. Making the math, we would load more than 160000 members every month and only 1600 were needed to being loaded.
What can we do to solve that? Very simple solution. We only need to know what metadata already exists in the planning applications and compare it with the source metadata repository. The difference from this comparison is what we really need to load.
But how we can do that? With only two steps, one for extract data (that is the focus of this post) and other to compare the data (that we’ll talk in a later post of our ODI series).
Let’s start:
A Planning application repository has a central table where every object that you create in the application is stored. This table is the key source to extract metadata from the applications and its name is HSP_OBJECT. From this table we can get every information needed for the extract process and it makes easy to extract any necessary dimension information we need. In this table, we have six important columns:
We can notice that this table is based in a parent child relationship. This type of relationship is perfect in cases where we want to store a hierarchy inside of a table because in this way we do not need to know how many generations one hierarchy will have to create the table. We only need two columns with the parent and child IDs to rebuild that correlation between then. To achieve that Oracle database give us a very useful command: Connect by Prior … Start With.
The start with .. connect by clause can be used to select data that has a hierarchical relationship and Prior word is used to create recursive-condition. Long story short, everything we need to do is:
This command will retrieve all the Account Dimension hierarchy and if we need to rebuild the metadata from another dimension the only thing that we need to do is change the OBJECT_NAME to the desired dimension name, E.g. Entity. This query will be the core of our extract process. For now, we have the hierarchy built, but only this is not enough, as we need to have all information about the members in order to compare to our inbound table. We need to take the other metadata information from planning repository as well. We need to join all the following tables together to have everything that we need:
|
Repository Table Name |
Extract Table |
|
HSP_OBJECT |
Member |
|
Parent |
|
|
Generation |
|
|
Has_Children |
|
|
Postion |
|
|
HSP_OBJECT_TYPE |
Dim_Type |
|
HSP_MEMBER |
Data Storage |
|
Data Type |
|
|
Two Pass Calculation |
|
|
Aggregation (Plan1) |
|
|
Aggregation (Plan2) |
|
|
Aggregation (Plan3) |
|
|
Aggregation (Wrkforce) |
|
|
Aggregation (Capex) |
|
|
HSP_ALIAS |
Alias: Default |
|
HSP_MEMBER_FORMULA |
Formula |
|
HSP_DIMENSION |
Hier_Name |
|
HSP_STRINGS |
Description |
|
HSP_ACCOUNT |
Account Type |
|
Time Balance |
|
|
Skip Value |
|
|
Exchange Rate Type |
|
|
Variance Reporting |
|
|
HSP_PLAN_TYPE |
Plan Type (Plan1) |
|
Plan Type (Plan2) |
|
|
Plan Type (Plan3) |
|
|
Plan Type (Wrkforce) |
|
|
Plan Type (Capex) |
|
|
Source Plan Type |
|
|
HSP_ENTITY |
Base Currency |
|
HSP_ENUMERATION |
Smart List |
|
HSP_MEMBER_TO_ATTRIBUTE |
Associated Attributes |
|
HSP_UDA |
UDA |
This table contains the mapping of the tables and the information that you can find in the application repository. Two important things to say when you build the final extraction query from planning repository:
With this query ready, all we need to do is loop it passing the Dimension name to the core query mentioned above and it will extract all dimensions from planning.
See Part 2 of our ODI series.
This is it. We only need to compare the extracted planning data to our inbound data and we’ll see that in a future post.
See you soon.
Hi guys how are you doing? We are very happy and proud to announce that our article “Dynamic Integrations for Multiple Hyperion Planning Applications” was approved for KScope 13! We’ll be there to talk about one of our preferred subject areas: integration of Hyperion Planning applications. We’ll be very pleased if you guys could show up in our presentation. It’ll be excellent to meet everyone there to talk about EPM and other stuffs.
Above is the link with more information about our article.
This article is all about what we have being writing in our blog series “10 Important Things to Improve ODI Integrations with Hyperion Planning” and some other surprises that we’ll talk at the presentation. You guys can expect a lot of technical information, a new way to view and work with Hyperion Planning integrations plus some real facts about what has happened after a successful implementation of this approach.
Thank you very much everybody and we’ll be waiting for you in Kscope 13.
Kscope 13 Speaker
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.
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.
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:
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:
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:
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.
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.
More to life… jason’s hyperion blog Glenn Schwartzberg’s Essbase Blog Hooked On Hyperion
DEV EPM 