In my last post, I highlighted a solution that was recently deployed for a customer that leveraged Enterprise Data Management Cloud Service (EDMCS), Financial Data Quality Management Enterprise Edition (FDMEE), and Cloud Data Management (CDM) to create an automated metadata integration process for both Planning and Budgeting Cloud Service (PBCS) and Financial Close and Consolidation Cloud Service (FCCS). In this post, I want to take a bit of a deeper dive into the technical build and share some important learnings.
Cloud Data Management introduced the ability to load metadata from a flat file to the Oracle EPM Cloud Services in the 17.11 patch. This functionality provides customers the ability to leverage a common platform for loading both data and metadata within the Cloud. Equally important, CDM allows metadata to be transformed using its familiar mapping functionality.
As noted, this customer deployed both PBCS and FCCS. Within the PBCS application, four plan types are active while FCCS has the default two plan types. A design decision was made for EDMCS to create a single custom application type that would store the metadata for both cloud applications. This decision was not reached without significant thought as well as counsel with Oracle development. While the pros and cons of the decision are outside the scope of this post, the choice to use a custom application registration in EDMCS ensured that metadata was input a single time but still fed to both cloud applications. As a result of the EDMCS design decision, a single metadata file (per dimension) was supplied with properties necessary to support each plan type.
CDM leverages its 23 “dimensions” to store metadata information for processing. Exactly like data, metadata is imported using an import format into the CDM relational repository. Each field from a metadata file is aligned to a CDM dimension field. The CDM Account dimension always represents the target application member name and the CDM Entity dimension represents the parent of the member. All other fields can be aligned to any of the remaining 21 dimensions. CDM Attribute dimensions can be utilized in the import and mapping process but are not available for exporting to the cloud application. This becomes an important constraint especially in a multi-plan type deployment. These 21 fields can be used to store any of the properties required to successfully load metadata to the target plan type.
Let’s consider this case study for a moment. The PBCS application has four plan types. If a process were to be built to load all plan types from a single CDM data load rule, then we would not be able to have more than five plan type specific attributes or properties because we would not have enough CDM fields/dimensions to store the relevant information. This leads to an important design approach. Instead of a single CDM data load rule to load all plan types, a data load rule was created for each plan type. This greatly increased the number of metadata properties and attributes that could be loaded by CDM and ensured that future growth could be accommodated without a redesign of the integration process.
It is important to understand that CDM utilizes the Planning Outline Load Utility (OLU) to actually perform the metadata load to the cloud application. The OLU loads metadata using merge (yes Planning experts, I realize that I am not discovering fire) which is important to understand especially when processing multiple metadata loads for a single application. When loading metadata, there are certain properties that are Application level. I like to think of these as being global. Additionally, there are plan type specific attributes that can align (or not align) to the application level value/setting. I like to think of these as local.
Why is this important? Well when loading a metadata file, if certain global properties are excluded from the metadata load file, the local properties (if specified) are utilized to default the global properties. Since metadata is loading using merge, this only becomes problematic when a new member is being added to the outline.
In this particular example, an alternate hierarchy with shared members was specified in one of the plan types. The storage property of the alternates was obviously set as Shared; however, when attempting the metadata load, the following error was encountered:
A Base Member cannot be changed to a Shared Member.
After much investigation (details to follow), I discovered that the global property should also be included in the metadata load.
While CDM utilizes the OLU to load metadata, it does not provide as much verbosity in the error information as the PBCS web interface (which also uses OLU) when loading metadata. Below is an example of the error in the CDM process log. As a tangent, I’d love to check the logs without needing to open a Service Request. Maybe Oracle will build an enhancement that allows that in the future (hint, hint, wink, wink to my friends at Oracle).
So where do I go from here? Well, what do we know about CDM loading metadata to the cloud application? We know that CDM uses the OLU to load a flat file generated by CDM. Bingo! The metadata file output by CDM is a good starting point. That file is in the Outbox of the CDM application and can be downloaded in several different ways – CDM Import process (get creative folks), CDM process details, or EPM Automate. Now we have the metadata file and can test to determine if the error is caused by CDM or the metadata itself. It’s all about ruling out variables. So, we take the metadata file and import it manually within the PBCS web interface and are able to replicate the error. But now we have an important new data point – the line number from the metadata file that is causing the error.
Now that we have actionable information, we can review each property and start isolating and eliminating different variables. We determined that this error was only occurring for new alternate hierarchy parents being added to the outline. As a test, we added the global storage property and voila, the metadata load completed successfully. Face palm!
Maybe this would have been obvious to folks with a lot of Planning experience, but there are plenty of folks learning the intricacies of Planning and Essbase (including our friends converting from HFM to FCCS), so I wanted to share a lesson learned in my journey of metadata integration using CDM.
CDM functionality for metadata represents two of the three primary operations of ETL. In my next post, we’ll dive deeper into how the extract component of ETL was accomplished to provide a seamless end- to-end ETL solution for metadata.