Request a Consultation

CheckPoint Consulting Blog

Maintain Custom Account Structure Requirements in Planning via Oracle DRM Sourcing: The Oracle Data Relationship Management (DRM) product has always offered impressive flexibility when it comes to meeting the metadata requirements of subscribing target systems. This flexibility extends across numerous products and systems and is not limited to just the EPM stack. This core strength goes back to the origins of the product and has been key to its evolution from its inception with Razza, on to Hyperion MDM, and now within Oracle DRM.

A client had specific account hierarchy requirements in regards to the depth of structure needed for planning and reporting activities. The enterprise-wide hierarchy maintained within DRM was many more levels deep than required for their planning and reporting needs in some of their business units. Additionally Planning applications also required input members for planning at an aggregate level within the database. The client wanted to accomplish this without having to add these custom input nodes within DRM due to complexities associated with enterprise wide master data maintenance and a federated model which is in place for processing metadata updates.

This example demonstrates a method through the usage of custom properties to maintain and support members and levels as required by a target planning system within DRM.

Custom Defined Properties for Planning Application Members

The below properties control what metadata is passed to the planning and Essbase applications in EPMA.

Properties Driven Input Accounts for Aggregate Level Planning:

BU Planning Input – This is a Boolean property that identifies the nodes that will be used for planning entries at an aggregate level within the APP (for example at an aggregate expense level). Account rollups that are required to have an input at this level are tagged as True.

BU Acct Input Attribute - Derived property value that defines a node name for input accounts. This property will be fed into the data warehouse where the BU Planning Input flag is set to true and it will become the member name of the input account in Planning. (i.e. AC_F7100000000_I). There is some customization in the data warehouse to enable this within the interface views used for EPMA. This property is fed via export to a target as the member name. This satisfies the requirement for the input accounts.

Properties Driven Level Control: DRM to Hyperion Planning:

BU AC Planning Parent – This Property is used to identify account limbs that are to be included within target planning APPs (APP0 and APP1). Note that this property also controls the level in the account structure where planning members stop in regards to depth of structure. This property works in conjunction with two other properties to enable this capability. All rollups required in the planning applications need to be tagged as true within DRM. Therefore an operational task is to monitor new account limbs being added to the hierarchy. If they are required they are to be tagged as true. This can also be made a defaulted value.

Example of a Limb in DRM that is required as a Base level member in Planning

The account structure for this hierarchy is deeper than that required by the planning applications. In order to define the AC_710000000 account as a base level member in planning the property value for the BU AC Planning Parent property is set to False.

Maintain_Custom_Acc_Structure_Image_1.png

The structure as required in planning. Note the limb node in DRM becomes a base level leaf in planning.

Maintain_Custom_acc_Strc_Image_2.png

As stated, the account structure for the F hierarchy is deeper than that required by the planning applications. In order to define the AC_710000000 as a base level member in EPMA APP1 the property value for the BU AC Planning Parent property is set to False.

** BU AC Planning Parent property is set to False in BU DRM for limb nodes in DRM that are required to be base level members in Planning.

Maintain_Custom_acc_Strc_Image_3.png

BU AC APP1 Flag – Derived property that leverages the BU AC Planning Parent property and assigns a value of ‘True’ for children where the BU AC Planning Parent = ‘True’. Therefore all children are tagged as true if the Planning Parent property is set to True.  It was determined that all children would be required for this condition. I.e. the parent flag controls which nodes are required in the target application. 

BU BUAPP1 LEV0 – For APP1 application, derived property used for accounts. Leverages the BU AC Planning Parent property. If Planning parent is True then Lev0 property is false. This final property allows us to control the leaf/limb setting as required by the planning interface. I.e. Some limbs in DRM become level 0 base accounts in the planning applications.

BU AC IsPrimary – Because the business unit is maintaining a combination of Enterprise wide governed accounts and business unit specific governed accounts for its Planning and Essbase APPs there also needs to be a method to control primary and shared nodes across these structures as they are fed into the EPM target apps. This property allows for manual control of primary or shared status for passing on to planning and Essbase. Such hybrid structures may require setting BU AC IsPrimary as False as an example for passing a shared member to the Hub and target APPs.

Summary

Though the use of the custom Boolean and Derived properties in DRM we have the ability to control what portions of the account structure are passed to planning. We can trim off the deeper more detailed levels of the structure at higher points in a hierarchy tree and have the ability to set limbs in DRM as level 0 members in planning or Essbase. We also have the ability to create customized alternate structures that are sourcing multiple DRM versions within the data warehouse interface through the usage of properties specifically setup to enable this.