Advanced Planning Forms Build using Planning repository


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.

Advertisements

5 Responses to “Advanced Planning Forms Build using Planning repository”

  1. That is ridiculously awesome!

    I’m guessing it’s possible to do a similar thing with UDAs? As in, build a planning dataform that uses UDAs to define one of the dimensions (whether that’s in the POV or the rows?)

    It’s always been a bugbear that they aren’t dynamic – adding a uda to a member will make it work in calc scripts but not dataforms.

    • In fact you can do it in anyway you want, rows, pov, page, columns, by UDA, by shared members, by comments, by attributes, by hierarchy, anything. For example, I created a former to check if there are the same amount of prototypes and shared members (business requirements , put it in the Odi, and it runs with the maintenance cycle. We are writing a white paper to do the same but with security (we have a post about it too). Cool stuff.
      Thanks.

  2. Chris Rothermel Says:

    What happens when you want to migrate this via LCM or FormDefUtil? I’d be very interested to see this exported to XML via FormDefUtil and then try to import it to a new environment doing using that utility again.

    • After you populate the form with all members you want, to planning is the same as if you put member a member inside the form wizard. There are no difference. In fact you can do the same with security, dimensions (but it’s a little more difficult because all the constraints that the repository has) and everything else that exists in the repository.

      We wrote a white paper about populate security dynamically with a similar approach to the technical paper. Very soon it’ll be published. Take a look on it! You will like it (I think ;))

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: