Archive for January, 2015

Now we are Oracle Aces!!!

Posted in EPM, Oracle with tags , , , on January 27, 2015 by RZGiampaoli

Hi guys how are you doing?

Today we will not post anything cool for you, instead we want to post something awesome for us.

We have been accepted to receive the Oracle ACE Associate Ace Associate award . We are extreme happy and honored to be part of this team. Only to let you know how important this is for us, in the Business intelligence expertise there are only 46 members in the world and in Brazil only 3 (yes we are from Brazil).

If you do not know what is the Oracle ACE program here is a brief description about it:

“The Oracle ACE program highlights excellence within the global Oracle community by recognizing individuals who have demonstrated both technical proficiency and strong credentials as community enthusiasts and advocates”. Oracle

You can check more information Here.

Well, now what is our plan for this year? We set some goal for us and they are:

  • Write at least four big white papers;
  • Post about other EPM technologies like OBIEE, BigData, etc… (Yes we work with this too);
  • Present in Kscope 15 and maybe in other conferences;
  • Start to plan a chapter in a Book;
  • More certifications;
  • And start our Big ultra-secret project that will rock the EPM world (At least we like to think so).

Well, this is our way to give back what the community has given to us.

And for the last but not the least, we want to thank everybody that helped us to achieve this awesome award, and a especial thanks to Cameron Lackpour for starting this process and believe in us (more than ourselves).

Thank you very much all of you guys.

Aiming to the ACE Director award!!!

 

See you guys.

Ricardo GiampaoliAce Associate

Rodrigo RadtkeAce Associate

 

Advanced Planning Forms Build using Planning repository

Posted in EPM, Hyperion Planning with tags , , , , on January 26, 2015 by RZGiampaoli

Today I saw in the OTN a question about how to build a Planning Form with some parents and all the members under it with a determined attribute. I decide to answer here because I could put a little more detail and help more than one person.

First let us take a look in the Planning form wizard. To do so let us create a “Test” form to be our guide in this Post. It is always easier to have a starting point, than start all from the scratch.

Forms and Ad Hoc Grid

I set the POV in the way I want it and put three members of Entity (That in my case has the some attribute dimensions on it).

Let us look in the member selection prompt:

Member Selection Filter

As we can see, we can filter the members by Name, Alias, Descripition and UDA, also we can keep only the members with a specific attribute:

Member Selection keep Only Attributes

However, we cannot have both, the descendants of a member and only the children with a specific attribute, at least not in the tool.

What we can do is create the list of members we want and insert it right in the Planning repository. Planning stores all information about a Form inside three tables (there are other tables for forms but they are setting for composite forms and so on! Information about the members and the form are in these tables):

SELECT * FROM HSP_FORM;
SELECT * FROM HSP_FORMOBJ_DEF;
SELECT * FROM HSP_FORMOBJ_DEF_MBR;

In addition, Planning stores all metadata information in HSP_OBJECT, then we need to use this table to get information about the metadata instead of a lot of ID’s! Then we need to join this table to get the actual name of the stuff)

In the first table, you have the form itself! If we join the first table with the HSP_OBJECT table we can filter our Form:

SELECT HF.*, HOF.OBJECT_NAME
FROM HSP_OBJECT HOF, HSP_FORM HF
WHERE 1=1
AND HF.FORM_ID=HOF.OBJECT_ID
AND OBJECT_NAME=’Test’
;

Query Results Forms 1Query Results Forms 2

As we can see we already have our Forms selected, and, there are only information regarding the Form itself as precision, column width and so on. That is mean, we do not need to touch this table, and we need only to use to select the desired form.

Now we need to get the POV settings by query the second table, HSP_FORMOBJ_DEF:

SELECT HFD.*, HOF.OBJECT_NAME
FROM HSP_OBJECT HOF, HSP_FORM HF, HSP_FORMOBJ_DEF HFD
WHERE 1=1
AND HF.FORM_ID=HOF.OBJECT_ID
AND HF.FORM_ID=HFD.FORM_ID
AND OBJECT_NAME=’Test’
;

Query Results Forms 3

Ok, here the only important information is the OBJDEF_TYPE. This columns means:

0=Point of View

1=Page

2=Rows

3=Columns

With this, we can select the area that we want to work in the form. In this case, we want the rows, then let us filter it and make a join with the last table, the one that stores the Members in that specific POV, HSP_FORMOBJ_DEF_MBR.

SELECT HD.FORM_ID, HOF.OBJECT_NAME AS FORM_NM, HDM.MBR_ID, HOM.OBJECT_NAME AS MEMBER_NM, SEQUENCE, ORDINAL, LOCATION, HD.STYLE
FROM HSP_FORMOBJ_DEF_MBR HDM, HSP_FORMOBJ_DEF HD, HSP_OBJECT HOF, HSP_OBJECT HOM
WHERE 1=1
AND HDM.OBJDEF_ID=HD.OBJDEF_ID
AND HD.FORM_ID=HOF.OBJECT_ID
AND HDM.MBR_ID=HOM.OBJECT_ID
AND HOF.OBJECT_NAME=’Test’
AND HD.OBJDEF_TYPE=2
AND HOM.OBJECT_TYPE=33
ORDER BY HDM.SEQUENCE
;

Query Results Forms 4

Well, we have some interesting information here:

First of all, we have the for filtered by his name (After the join with the HSP_OBJECT to get the OBJECT_NAME;

We have the OBJDEF_TYPE filtering the type 2, that means ROWS;

And we have the OBJECT_TYPE filtered in 33, that’s means “Entity”. We can find out all the object types by querying (SELECT * FROM HSP_OBJECT_TYPE;)

As we can see, we have all the three members set in the Form “Test” in the right order (SEQUENCE column). Now we need just to select the members and sequence that we want then inserted and filter it by an attribute.

Well, we already know that the HSP_OBJECT table stores all metadata information in Planning then, nothing is more fare than use this table to select all members that we desire:

SELECT *
FROM HSP_OBJECT HO
CONNECT BY PRIOR HO.OBJECT_ID=HO.PARENT_ID
START WITH OBJECT_NAME IN (‘CBO_CC_GCC’)
;

Query Results Members 1

This simple query bring me all children of “CBO_CC_GCC” including himself. Also we get other cool information like the OBJECT_TYPE, GENERATION and HAS_CHILDREN, that can help us a lot to select what we want, for example:

We can select all members of a generation, only what doesn’t have children (leaf), or only what has children, and so on.

Inside or query we can have more than one parent, this is why I put an “IN” in the START WITH clause. Also, we can use other than OBJECT_NAME in the clause, it could be the OBJECT_TYPE, to get an entire dimension, or PARENT_NAME, but normally we use the OBJECT_NAME and if you want the entire dimension you can ask for it (‘Entity’).

Ok, now we need only to filter from that list all the children that has a specific Attribute. For this we need the Attribute tables:

SELECT HO.MEMBER_ID, HO.MEMBER_NM, HO.OBJECT_TYPE, HO.GENERATION, HO.HAS_CHILDREN, HOA.OBJECT_NAME AS ATTR_NM, HOD.OBJECT_NAME AS ATTR_DIM
FROM
(
SELECT HO.OBJECT_ID AS MEMBER_ID, HO.OBJECT_NAME AS MEMBER_NM, HO.OBJECT_TYPE, HO.GENERATION, HO.HAS_CHILDREN, HO.POSITION
FROM HSP_OBJECT HO
CONNECT BY PRIOR HO.OBJECT_ID=HO.PARENT_ID
START WITH OBJECT_NAME IN (‘CBO_CC_GCC’)
) HO,
HSP_MEMBER_TO_ATTRIBUTE HMA,
HSP_OBJECT HOA,
HSP_OBJECT HOD
WHERE 1=1
AND HO.MEMBER_ID=HMA.MEMBER_ID(+)
AND HMA.ATTR_MEM_ID=HOA.OBJECT_ID(+)
AND HMA.ATTR_ID=HOD.OBJECT_ID(+)
ORDER BY HO.POSITION
;

Query Results Members 2

The table that stores the relationship between the member and his attributes is the HSP_MEMBER_TO_ATTRIBUTE. The only thing is that, if we want to get the parent and all children with a specific attribute we need to do some left joins, since the parent members does not have any attribute member associated on it.

Other than that it’s a pretty forward query and as we can see it gets all the attributes from the children of CBO_CC_GCC cost center. Now we need only to filter it:

SELECT HO.MEMBER_ID, HO.MEMBER_NM, HO.OBJECT_TYPE, HO.GENERATION, HO.HAS_CHILDREN, HOA.OBJECT_NAME AS ATTR_NM, HOD.OBJECT_NAME AS ATTR_DIM
FROM
(
SELECT HO.OBJECT_ID AS MEMBER_ID, HO.OBJECT_NAME AS MEMBER_NM, HO.OBJECT_TYPE, HO.GENERATION, HO.HAS_CHILDREN, HO.POSITION
FROM HSP_OBJECT HO
CONNECT BY PRIOR HO.OBJECT_ID=HO.PARENT_ID
START WITH OBJECT_NAME IN (‘CBO_CC_GCC’)
) HO,
HSP_MEMBER_TO_ATTRIBUTE HMA,
HSP_OBJECT HOA,
HSP_OBJECT HOD
WHERE 1=1
AND HO.MEMBER_ID=HMA.MEMBER_ID(+)
AND HMA.ATTR_MEM_ID=HOA.OBJECT_ID(+)
AND HMA.ATTR_ID=HOD.OBJECT_ID(+)
AND (HOD.OBJECT_NAME = ‘Support_Geography’ OR HOD.OBJECT_NAME IS NULL)
AND (HOA.OBJECT_NAME IN ( ‘SUPP_Americas_Supp’,’SUPP_Australia’) OR HOA.OBJECT_NAME IS NULL)
ORDER BY HO.POSITION
;

Query Results Members 3

Do not forget to create a OR to null in the filters because parents will not have any attributes.

I choose the Dimension “Support Geography” and the attributes “SUPP_Australia andSUPP_Americas_supp. As we can see, we have now selected all the childrens of CBO_CC_GCC that has two attributes. We need only to insert this in the Planning repository.

Ok, to insert let us get or SQL that query the Forms tables and let us make it return only one row (That will be used as base information to insert) and let’s see that data that we need to insert.

SELECT HDM.*
FROM HSP_FORMOBJ_DEF_MBR HDM, HSP_FORMOBJ_DEF HD, HSP_OBJECT HOF, HSP_OBJECT HOM
WHERE 1=1
AND HDM.OBJDEF_ID=HD.OBJDEF_ID
AND HD.FORM_ID=HOF.OBJECT_ID
AND HDM.MBR_ID=HOM.OBJECT_ID
AND HOF.OBJECT_NAME=’Test’
AND HD.OBJDEF_TYPE=2
AND HOM.OBJECT_TYPE=33
AND HOM.OBJECT_NAME=’CBO_CC_GCC’
ORDER BY HDM.SEQUENCE
;

Query Results Members 4

Ok, this will be our guide! We need that because there are some information that we cannot get from the query that generate the members but we can get for our “guide member”. Looking in the result, we will need to get only the MBR_ID and SEQUENCE from the query that filter the members for our Form, and everything else will be a clone of our previous set member. Also we’ll make sure that the members that already exists in the form will not be inserted by mistake again.

Now let us join the two queries and get our results.

SELECT HDM.OBJDEF_ID, HDM.ORDINAL, HO.MEMBER_ID, HDM.QUERY_TYPE, HO.POSITION, HDM.SUBST_VAR, HDM.LABEL, HDM.FORMULA_DATA_TYPE, HDM.FORMULA_ENUMERATION_ID
FROM
(
SELECT HO.OBJECT_ID AS MEMBER_ID, HO.OBJECT_NAME AS MEMBER_NM, HO.OBJECT_TYPE, HO.GENERATION, HO.HAS_CHILDREN, HO.POSITION
FROM HSP_OBJECT HO
CONNECT BY PRIOR HO.OBJECT_ID=HO.PARENT_ID
START WITH OBJECT_NAME IN (‘CBO_CC_GCC’)
) HO,
HSP_MEMBER_TO_ATTRIBUTE HMA,
HSP_OBJECT HOA,
HSP_OBJECT HOD,
(SELECT HDM.*
FROM HSP_FORMOBJ_DEF_MBR HDM, HSP_FORMOBJ_DEF HD, HSP_OBJECT HOF, HSP_OBJECT HOM
WHERE 1=1
AND HDM.OBJDEF_ID=HD.OBJDEF_ID
AND HD.FORM_ID=HOF.OBJECT_ID
AND HDM.MBR_ID=HOM.OBJECT_ID
AND HOF.OBJECT_NAME=’Test’
AND HD.OBJDEF_TYPE=2
AND HOM.OBJECT_TYPE=33
AND HOM.OBJECT_NAME=’CBO_CC_GCC’
ORDER BY HDM.SEQUENCE
)HDM
WHERE 1=1
AND HO.MEMBER_ID=HMA.MEMBER_ID(+)
AND HMA.ATTR_MEM_ID=HOA.OBJECT_ID(+)
AND HMA.ATTR_ID=HOD.OBJECT_ID(+)
AND (HOD.OBJECT_NAME = ‘Support_Geography’ OR HOD.OBJECT_NAME IS NULL)
AND (HOA.OBJECT_NAME IN ( ‘SUPP_Americas_Supp’,’SUPP_Australia’) OR HOA.OBJECT_NAME IS NULL)
ORDER BY HO.POSITION
;

Query Results Members 5Now we have almost all set, we need only to remove the member that already exists in the table (“CBO_CC_GCC”), this way it will not duplicate it.

SELECT HDM.OBJDEF_ID, HDM.ORDINAL, HO.MEMBER_ID, HDM.QUERY_TYPE, HO.POSITION, HDM.SUBST_VAR, HDM.LABEL, HDM.FORMULA_DATA_TYPE, HDM.FORMULA_ENUMERATION_ID
FROM
(
SELECT HO.OBJECT_ID AS MEMBER_ID, HO.OBJECT_NAME AS MEMBER_NM, HO.OBJECT_TYPE, HO.GENERATION, HO.HAS_CHILDREN, HO.POSITION
FROM HSP_OBJECT HO
CONNECT BY PRIOR HO.OBJECT_ID=HO.PARENT_ID
START WITH OBJECT_NAME IN (‘CBO_CC_GCC’)
) HO,
HSP_MEMBER_TO_ATTRIBUTE HMA,
HSP_OBJECT HOA,
HSP_OBJECT HOD,
(SELECT HDM.*
  FROM HSP_FORMOBJ_DEF_MBR HDM, HSP_FORMOBJ_DEF HD, HSP_OBJECT HOF, HSP_OBJECT HOM
  WHERE 1=1
  AND HDM.OBJDEF_ID=HD.OBJDEF_ID
  AND HD.FORM_ID=HOF.OBJECT_ID
  AND HDM.MBR_ID=HOM.OBJECT_ID
  AND HOF.OBJECT_NAME=’Test’
  AND HD.OBJDEF_TYPE=2
  AND HOM.OBJECT_TYPE=33
  AND HOM.OBJECT_NAME=’CBO_CC_GCC’
)HDM
WHERE 1=1
AND HO.MEMBER_ID=HMA.MEMBER_ID(+)
AND HMA.ATTR_MEM_ID=HOA.OBJECT_ID(+)
AND HMA.ATTR_ID=HOD.OBJECT_ID(+)
AND (HOD.OBJECT_NAME = ‘Support_Geography’ OR HOD.OBJECT_NAME IS NULL)
AND (HOA.OBJECT_NAME IN ( ‘SUPP_Americas_Supp’,’SUPP_Australia’) OR HOA.OBJECT_NAME IS NULL)
AND NOT EXISTS (SELECT 1
                FROM HSP_FORMOBJ_DEF_MBR HDM2
                WHERE 1=1
                AND HDM.OBJDEF_ID=HDM2.OBJDEF_ID
                AND HO.MEMBER_ID=HDM2.MBR_ID
                                )
ORDER BY HO.POSITION
;

Query Results Members 6

With this last “NOT EXISTS” clause we have our final SQL and we can now insert into the HSP_FORMOBJ_DEF_MBR our members and then restart planning service to get the new members in the form.

INSERT INTO HSP_FORMOBJ_DEF_MBR
(
SELECT HDM.OBJDEF_ID, HDM.ORDINAL, HO.MEMBER_ID, HDM.QUERY_TYPE, HO.POSITION, HDM.SUBST_VAR, HDM.LABEL, HDM.FORMULA_DATA_TYPE, HDM.FORMULA_ENUMERATION_ID
FROM
(
SELECT HO.OBJECT_ID AS MEMBER_ID, HO.OBJECT_NAME AS MEMBER_NM, HO.OBJECT_TYPE, HO.GENERATION, HO.HAS_CHILDREN, HO.POSITION
FROM HSP_OBJECT HO
CONNECT BY PRIOR HO.OBJECT_ID=HO.PARENT_ID
START WITH OBJECT_NAME IN (‘CBO_CC_GCC’)
) HO,
HSP_MEMBER_TO_ATTRIBUTE HMA,
HSP_OBJECT HOA,
HSP_OBJECT HOD,
(SELECT HDM.*
  FROM HSP_FORMOBJ_DEF_MBR HDM, HSP_FORMOBJ_DEF HD, HSP_OBJECT HOF, HSP_OBJECT HOM
  WHERE 1=1
  AND HDM.OBJDEF_ID=HD.OBJDEF_ID
  AND HD.FORM_ID=HOF.OBJECT_ID
  AND HDM.MBR_ID=HOM.OBJECT_ID
  AND HOF.OBJECT_NAME=’Test’
  AND HD.OBJDEF_TYPE=2
  AND HOM.OBJECT_TYPE=33
  AND HOM.OBJECT_NAME=’CBO_CC_GCC’
)HDM
WHERE 1=1
AND HO.MEMBER_ID=HMA.MEMBER_ID(+)
AND HMA.ATTR_MEM_ID=HOA.OBJECT_ID(+)
AND HMA.ATTR_ID=HOD.OBJECT_ID(+)
AND (HOD.OBJECT_NAME = ‘Support_Geography’ OR HOD.OBJECT_NAME IS NULL)
AND (HOA.OBJECT_NAME IN ( ‘SUPP_Americas_Supp’,’SUPP_Australia’) OR HOA.OBJECT_NAME IS NULL)
AND NOT EXISTS (SELECT 1
                FROM HSP_FORMOBJ_DEF_MBR HDM2
                WHERE 1=1
                AND HDM.OBJDEF_ID=HDM2.OBJDEF_ID
                AND HO.MEMBER_ID=HDM2.MBR_ID
                                )
)
;

We can check our Forms Query to see the new members:

Query Results Members 7There they are. Now let us restart planning service and see the results:

Planing Form Results MembersHere we go! In the Right position and only the right ones.

Hope you guys enjoy this tip, and be sure that the Planning repository is extremely powerful and help us to do a lot of stuff that Planning would not let you do.

See you guys.

DEVEPM will be at KScope 15!!!

Posted in EPM, Hyperion Planning, KScope 15, ODI, ODI 12c with tags , , , , , , , , on January 22, 2015 by radk00

Hi all, how are you doing? We are very happy to announce that our presentation “No more unknown members! Smart data load validation for Hyperion Planning using ODI” was approved for KScope 15! We’ll be there to talk about one of our preferred subject areas: How to improve EPM tools potential using ODI. 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 stuffs! Here is our presentation’s abstract:

“Usually, ODI data load interfaces for Essbase are simple and fast to be developed. But, depending on the data source quality, those interfaces may become a performance challenge. Essbase demands that all POV members in which we are trying to insert data to exist in the Essbase outline and when this is not true, Essbase switches its load method from Block Mode to Cell Mode. When this situation happens, one data load that would take only five minutes to complete may take several hours, degrading the Hyperion environment performance. Join us in this session to discover how we solved this problem in a dynamic way for any number of Hyperion Planning applications only using ODI data constraints and Hyperion Planning metadata repository to validate all POV members that will be used in the data load, guaranteeing the best performance and data quality on the Hyperion Planning environment.”

Kscope is the largest EPM conference in the world and it will be held on Hollywood, Florida on June this year. 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.

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

KScope15

Do you know about ODI project on java.net?

Posted in EPM, ODI, ODI 12c, ODI Architecture with tags , , , on January 8, 2015 by radk00

Hi all, today’s post is just a quick tip about ODI project on java.net. I’m often asked if I know a place where there could be different KMs for ODI besides the ones that comes within its installation folder. Every time that I mention the ODI project on java.net, people reaction is always the same “Wow, that’s very nice! I didn’t know that!”.

ODI project on java.net is a collaboration site that contains a lot of code samples made for ODI. It contains Docs, KMs, Open Tools, Procedures, Samples, User Functions, Utilities and so on. Also if you have something that you think that would be useful for the others, you may collaborate with the community by adding your code there. Here is the link:

https://java.net/projects/oracledi

You just need to create an account and you are good to go! Enjoy!

Follow us on Twitter: @RodrigoRadtke @RZGiampaoli @DEVEPM