Hyperion Planning

The Challenges of Hyperion Support - Part 1: Metadata Management – Governance is the Key (Continued)

The Bean Consulting Group

In addition, if metadata between the shared dimension library and the application level becomes out of sync, or if a local dimension: a dimension specific to an application, needs to match a dimension in the shared dimension library, the administrator can use the synchronize feature in EPMA to achieve these types of functions. The synchronize feature is a powerful tool that allows an administrator two options to synchronize local dimensions in an application.

• Push metadata from a dimension in the Shared Dimension Library to the local dimension.

• Push metadata from the local dimension to the dimension in the Shared Dimension Library.

With either option you can Merge As Shared or Replace. Use the Merge As Shared option to merge similar dimensions from two different applications; essentially, you will be creating a master dimension.  The synchronize feature is extremely helpful in managing metadata: it can help reduce maintenance, create consistency and minimize complexity.

Enterprise Resource Planning Integration (ERPi)

Prior to diving into the specifics of ERPi, it is important to distinguish it from its stand-alone, related tools to determine why it is the future of integration.  As mentioned, ERPi is a module for Financial Data Quality Management (FDM) that leverages ODI as its processing engine, so how do these two products differ?  FDM is strictly a data integration tool that provides drill-through capabilities from a Hyperion Planning or Hyperion Financial Management application.  End-users, from EPM Workspace, can access further detail on the FDM relational repository as it relates to consolidated values on data forms or reports.  However, it cannot manage metadata builds.  ODI, on the other hand, is an Extract, Transform, and Load, or ETL, tool that uses a series of knowledge modules to integrate both data and metadata with target EPM applications, among several other target technologies.  But any drill-through is lost.  ERPi offers both; metadata hierarchies can be managed, maintained, and synced for target applications, and data can be fully mapped while offering drill-through functionality all the way back to the source ERP system.  That’s right, all the way back to the source system!

Since the focus of this article is metadata management, after all, how can ERPi specifically help an organization maintain governance over its metadata?  As detailed throughout the article, the management of hierarchies is an ongoing process with frequent changes and updates to business functions and organizations.  Certain dimensions may also be more dynamic than others; an Employee or Position dimension may require substantially more effort to maintain than an Entity or even Account dimension.  Therefore, manual file manipulations may become too cumbersome and time consuming for system administrators.  Additionally, as a best practice, a unique prefix or suffix may be used for each dimension to ensure uniqueness across all other members.  So, it is crucial that consistency is created throughout the life of the application.  Well, ERPi, through the use of metadata mapping rules, extracts dimension members and particular properties from the source system and loads them directly into the target application.  A built-in task scheduler can then be configured to run updates as frequently as every hour or as infrequently as once per month.  

When defining metadata mapping rules within ERPi, there are several features that strive to streamline the process and facilitate tight metadata governance.  Rules can be established for each of the required dimensions based on the EPM application type (i.e. Account, Entity, Scenario, Version, Period, Year, etc…) as well as custom dimensions.  For each dimension, prefixes or suffixes can be defined as unique identifiers, and ERPi will ultimately assign them to the source values.  Furthermore, based on the dimension, certain properties can either be sourced directly from the ERP system or defined while creating the metadata rule.  See Table 1 for the member properties of the Account dimension that can be set through ERPi.  In addition to these properties, “Valid for Plan Type” and “Source Plan Type” options can be selected for the Entity and Account dimensions.

Table 1: Metadata Attributes in ERPi

Segment hierarchies define those members that are extracted from the source system, and both base and alternate hierarchies can be created in the target application.  For instance, if the source ERP is PeopleSoft, different Trees and Effective Dates can be selected to represent base hierarchies and/or necessary alternate reporting roll-ups.  So, if an organization has different internal reporting requirements as opposed to what is required by banking institutions, ERPi can manage and maintain both sets of hierarchies.  

Data Relationship Management (DRM)

Up to this point, the methods detailed have focused on updating strictly Hyperion tools, which heavily rely on the accuracy of the source information.  Whether it is flat files, PeopleSoft’s hierarchical Trees, or any other relational table, the source must be maintained separately, and manual updates, file manipulations, or mapping rules are used to integrate.  However, Oracle offers a central hub where database/reporting structures are maintained and validated before moving throughout the entire enterprise.  Business Intelligence (BI) solutions, EPM applications, data warehouses are all maintained and updated by a single source.  The tool is Data Relationship Management (DRM).

User security, or Permissions, is an important element to manage in DRM.  In DRM, users access hierarchy nodes and their properties through Node Access Group Assignments.  Individual users can be assigned to groups, which grant permissions to specific nodes and align with functional areas of an organization.  Any node that is not granted access is not displayed.  For example, within legal entities, a group of users may only need to maintain the Information Technology structure; furthermore, similar access can be assigned to the chart of accounts structure or any other hierarchy.  Access can also be granted to Versions and Property Categories.  With various users and user groups accessing different hierarchies and nodes, it is important that appropriate logs are tracked.  All DRM operations – data actions such as adding or removing a hierarchy or deleting a version and administrative actions such as adding a Node Access Group or updating Property Definitions – are logged in the Transaction History.  The Transaction History records a timestamp, a username, the type of action performed, and any other relevant information.

Versions – sets of data categorized into hierarchies, nodes, and properties - drive all data-related operations within DRM.  Typically, separate versions are used to maintain historical copies of hierarchies, nodes, and properties, create new data or model changes to existing data, and load data from different sources to be compared and/or blended.  Versions also have a certain lifecycle to represent a period of time, and it is determined by Statuses.  There are four different Statuses – Working, Submitted, Finalized, and Expired.

• Working – users are able to edit versions through different tools, including manual updates, Action Scripts (formerly Automators or Automator Scripts), and Blenders.

  • Action Scripts allow users to process mass changes in an automated fashion and can be used to build alternate hierarchies or manage a departmental reorganization.  When developing Action Scripts, there are two important elements to keep in mind.  First, each record within the script represents a separate action to be performed and is process individually from other actions; and second, specific parameters are required in each flat file column.  

  • Blenders combine elements of two different versions into the same version, and they can be used to process adds, moves, or deletes to an existing hierarchy, create new hierarchies, and map nodes from a source hierarchy to nodes in a target hierarchy.

• Submitted – Validations are performed to ensure integrity of the data and comparison reports can be developed to determine the differences between the current version and any previous Finalized versions.  

• Finalized – Exports are performed from the Finalized version to send hierarchy data to downstream systems, and can be output to flat files or relational tables.  After all exports are complete and have been loaded to the destination system, all participating systems have consistent metadata as a basis for the period end reporting process.  

• Expired – Versions can then be stored for possible future use in historical analyses or as an audit record

While users are adding, deleting, or updating hierarchies, nodes, and properties, Validations check for data integrity against critical, core requirements as developed by the organization.  Validations can either be real-time or run explicitly by a user at the version, hierarchy, or node level.  Real-time validations run at the time a change is being made and will prevent the change if the validation fails.  Batch Validations (formerly Verification), on the other hand, are executed prior to pushing metadata changes downstream to subscribing systems, and verify changes that were not passed through real-time validations.  See Table 2 for example validations that can be created for a Hyperion Planning application.

Table 2: Validations

Conclusion

In conclusion, it’s clear that metadata governance is extremely important in the support and maintenance of Hyperion applications.  The more tightly that enterprise metadata can be controlled the less data scrubbing, cleansing, and mapping is required – which means less maintenance. With the various tools available for metadata governance, administrators are finding metadata management less strenuous and more flexible. Metadata governance is fundamental in building a system that provides one version of the truth.

E-mail: info@thebean.co