DEV EPM

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.

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:

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:

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’
;

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’
;

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
;

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’)
;

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
;

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
;

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
;

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
;

Now 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
;

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:

There they are. Now let us restart planning service and see the results:

Here 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.