Integrating DRM with Oracle Planning and Budgeting Cloud Service

By on March 14th, 2017

The Cloud

The Oracle Cloud has taken the EPM Market by storm (bad pun intended) and for those in the industry, this means change.

Oracle Planning and Budgeting Cloud Service (PBCS) is available, and despite some integration limitations, has proven itself to be a great product and a great value for companies interested in the software as a service model.

What does this mean for our old friend DRM? At the moment, not much. Perhaps one day DRM will join Planning in the Oracle Cloud, but for now, it is business as usual. The question has come up many times: can DRM publish metadata for consumption into Oracle Cloud, the answer is yes, with a couple asterisks.

At present, all integrations into PBCS (metadata and data) are accomplished via flat file that can be loaded from a client machine (running EPM Automate), or uploaded into the PBCS repository. These files are loaded into PBCS using predefined (by admin) jobs which can be run manually or automated using the new command-line scripting tool, the EPM Automate Utility.

Automation

If you wanted to automate loading metadata from DRM to PBCS, the process could look like this:

  1. Setup properties in DRM to manage metadata for PBCS (in this post we will setup a few simple properties to demonstrate how to get the file format correct, in a real-world application we would have more properties and logic to make your maintenance easier)
  2. Build a DRM export to write nodes and properties to a TXT file (preferably to a UNC path)
  3. Write a shell script (or other simple utility) to ZIP up your dimension file(s)
    1. the file names need to match the import file names defined in your job, or the end of the file name can indicate the dimension– e.g., metadata_Account.txt
  4. Launch the EPM Automate Utility to login, upload the ZIP file to the PBCS inbox, and load the metadata

Oracle Data Integrator (ODI) would be a great option to schedule and run this entire process.

EPM Automate

Say goodbye to MaxL and say hello to EPM Automate for all your scripting needs.

This is the syntax for running a metadata import into PBCS:

epmautomate importmetadata JOB_NAME [FILE_NAME]

Where JOB_NAME is a predefined job in EPM Automate, and FILE_NAME is the name of the ZIP file to be imported.

I found this sample Entity metadata import file in Oracle’s documentation and for those of you with experience using Planning’s Outline Load Utility (OLU), this file should look very familiar! I’ve worked with this utility a lot and am a big fan. One of the things I like about it is that the field mapping is accomplished by the strings in the header record. As such, the field headers are both case and space sensitive.

Our task is to generate a metadata import file in the correct format. The OLU always worked with CSV files, but we can now use TXT files and many different delimiters; I’ll be using vertical lines (pipe) for my file.

We’re not going to create every property that can be loaded with this tool. Instead, we’ll create a small set of the properties with the emphasis on getting the file format correct. The hierarchical structure of the dimension is defined using the Parent and “Child” fields, although the child field is named after the dimension itself. In the example, above, the child field is Entity. In my example, I’m going to build a sample import file for a fictitious and very limited Product dimension so the “child” field will be called Product.

Enter DRM

Properties

As I said before, we’ll not create every property that can be loaded through EPM automate, but rather we’ll load a few to demonstrate how to create the metadata import file correctly. I also want to point out the configuration of these properties are as simple as possible. In a real-world application, we’d leverage more business logic to aid in metadata maintenance and would have the capability to control which nodes go to which application.

There are 2 dependent properties referenced in the code below. Custom.DRM.MidName is a derived property that strips off any prefix or suffix from the node name (see my DRM Blog Series for information about using prefixes for node typing). Core.Descr is a built-in system property to store a node’s description. I am translating that node description into the alias that will be published to PBCS.

We’ll be using the Label on each property to control the string written to the header for each field being written to our PBCS dimension import file.

Custom.PBCS.Parent

if ( node.Level > 1 ) { return node.Parent.PropValue("Custom.DRM.MidName"); }

else { return ""; }

Custom.PBCS.Child.Product

return node.PropValue("Custom.DRM.MidName");

Custom.PBCS.Alias.Default

return node.Descr;

Custom.PBCS.DataStorage

Custom.PBCS.Aggregation.Report

DRM Export

Create a new Hierarchy Export, and add the nodes below the root.

Keep the default options on Style tab because we want All Nodes.

In a real-world application, we would use a predefined export query on the Filter tab to control which nodes go to the target application, but in the interest of simplicity, we’ll be taking all nodes.

On the Columns tab, select all the PBCS properties you defined.

Lastly, on the Target tab, use Server File Device option to create the file in a UNC path. I’ve selected (Other) for the Field Delimiter and entered a vertical bar., and enter metadata_Product.txt for the File Name.

Save and run the export.

Then track down the server file that was created. DRM for some reason has placed this empty line between the header and the body of the file, but that line is ignored on import so not a deal breaker.

Conclusion

As we’ve demonstrated, you can absolutely use DRM to manage metadata for Oracle Planning and Budgeting Cloud Service (PBCS). At this point in time, all integrations are happening with flat files, but I suspect (and hope) that in the not-too-distant future Oracle will add ODBC capability to their Cloud integrations.

Tags: ,

Leave a Reply

Your email address will not be published. Required fields are marked *

© Copyright 2017 TopDown Consulting. All Rights Reserved.