The Operational Data Store (ODS) concept is a viable architectural option when considering complex requirements that deal with data transformation and mapping logic for large data sets. At times, data feeds from multiple source systems require manipulation through the use of various reference data that may exist either as look-up tables or master data tables residing within the repositories of Oracle Enterprise Performance Management (EPM) – also known as Hyperion – solutions. You can gain flexibility by building in a relational layer between your source systems and downstream analytic tools.
As an example, a recent client chose a solution that requires both an Essbase Aggregate Storage Option (ASO) analysis cube and a Block Storage Option (BSO) Planning cube. The sources of data are the same for each of these; however the requirements, functionality, and data needs are quite different when comparing the two. Furthermore, there are multiple years of data in the source systems that include many attribute-type data elements. The files themselves are also large with many exceeding 1GB in size per each year of data being sourced. These characteristics require an agile solution that can adapt with regards to the handling and distribution of data.
By importing source data initially into a relational layer (tables, views, and stored procedures), we can quickly modify outbound data files according to the changing needs of the receiving analysis systems (Essbase cubes in this example). Also given the increasing size of the data sets involved, the ODS proves to be a flexible and effective tool as data is turned around quickly to meet needs downstream.
On the master data front, the ODS also provides benefits. We are able to generate Oracle Hyperion Enterprise Performance Management Architect (EPMA) load format (.ads) files directly out of the ODS layer for loading into EPMA. This is a required step in the administrator-owned data load process for aligning the ODS exported data files with the base-level hierarchy members available in Essbase dimensions.
The EPMA .ads files can be generated in two ways for loading EPMA. We first are able to generate a full file of all base members required in the dimension (a complete product listing, for example). Secondly, a subset file can be created which contains just newly added members (e.g., products). The latter is accomplished by joining to EPMA repositories and essentially conducting a compare to see what already exists in the downstream Essbase analysis cube. This file is then used for incrementally updating the members in the hierarchy versus conducting a full replacement. This is an added benefit to the administrator who maintains parent/child relationships within EPMA and only needs to add new members.
The SQL code built into the ODS views also allows the ability to control member and alias naming conventions via string length and prefix/suffix handling, which eliminates the need for global transformations in the Essbase load process.
In summary, an ODS can provide benefits that include:
- Offers scalable and flexible control of data per constantly changing business requirements
- Handles large data sets effectively, sourcing from one or multiple systems/databases
- Enables fast turnaround, with ownership on the business applications side of the house
- Offers data scrubbing and transformation, with the ability to modify both data and master/meta-data
- Provides the capability to build queries against tables and views for quick answers to large, complex data sets
Keep warm this winter and stay tuned to our blogs for additional discussions regarding BI solutions! If you would like to learn more, contact us at firstname.lastname@example.org.
Author: Jason Sawicki, Performance Architects