Every company that moves between systems in an effort to modernize—e.g., moving from Hyperion Enterprise to Oracle Hyperion Financial Management (HFM)—must validate their data. This process is often made more complicated by differences in hierarchies, the inclusion of additional data sources, and changes required to utilize additional capabilities in the new tool. In addition, transitioning from one tool to another often involves a minimum period of time. This transition may consist of a few months when both systems are run in parallel or several years as one portion of the company after another is slowly migrated from an old system to a new one. During these times, it is critical to be able to validate between these systems and, possibly, to provide reporting using the original hierarchies.
A relational database can be an invaluable tool as part of this process. Databases are designed specifically to handle the types of queries and comparisons needed for such validation efforts. Data and hierarchies can be passed from HFM to the database, using the Extended Analytics tool. A mapping process can be built within the database between the new and old versions of the hierarchies as well as any filtering of data from other data sources not used in the original system. The HFM extracted data is stored in a star-schema, which can be used to create an Essbase cube for reporting and validation or accessed directly using a variety of tools. Data pulled directly from the source system can also be loaded into the database for use in comparisons. Views and procedures can be built to provide a toolset to identify differences and issues.
Many companies also take advantage of the development effort required to move from one system to a new one as an opportunity to build in additional business requirements, and implement changes and improvements to both the system and processes that have been identified since the previous system was built. This often leads to changes in hierarchies, possibly combining or splitting them, or even adding or completely removing them as well. This further complicates validation but can be handled within a relational database. One method would be to build mapping tables, views and procedures that recreate the mapping process used to move data from the original system to the new one. A much better option is to take advantage of the results of any mapping tools you may use during the development or even for the production system.
Mapping tools often provide a way to code rules used to translate metadata and data from the source system to the target system. A tool such as Oracle’s Financial Data Quality Management (FDM) is often used to map metadata and data from legacy systems or from data sources in newly acquired companies. By running a sample file with all of the possible combinations from the source through the tool, a map from the source to the target is created. In the case of FDM, this mapping is stored in the backend tables. These results can also be used to create a reverse mapping. Again, tables, views and procedures can be used to analyze the mapping and to create an Essbase cube for reporting. Often, by utilizing Essbase Studio or Essbase load rules, a reporting cube can be created from a star-schema built using the mapped dimensions and mapped data, which mimics the original source system. Also, by querying the data before and after mapping, any records that are not mapped can easily be identified.
Once again, the database has proven to be a valuable resource. Data validation between two sources can be complicated, especially when hierarchies have been added/deleted/altered and additional data sources are introduced. A database is an excellent place to build out a small focused toolset in a robust enterprise data environment instead of relying only on Excel or some other office tool. A relational database is purpose built to handle data. It should be considered a valuable asset capable of performing validation during development as well as during the transition period and for reporting. It isn’t just for your old production system.
