Extract PBCS Metadata – Use Cases and Code Free Automation

Overview

The ability to extract PBCS metadata can be very handy for EPM teams. Unfortunately, there aren’t a lot of options – especially if you want to do it in an automated way. Unless you want to write hundreds of lines of code and have plenty of time for troubleshooting, there isn’t an out of the box option that’s suitable. Data Management doesn’t support it. EPM Automate and the powerful REST API have a command to export PBCS metadata, but all it does is extract the metadata to a zip file which you can then download.

Please note: this article also applies to you if you are looking for how to extract EPBCS metadata.

In this article we are going to

• Discuss various advanced use cases for your exported metadata,
• Review Standard Options to Extract PBCS metadata
• See how easy it is to extract PBCS metadata without writing a single line of code
• Leverage useful information that only exists in ICE Cloud
• Look at a screenshot of a PBCS Metadata Extract in ICE Cloud

But before we go into more detail, let’s start with the WHY so we can discuss the various use cases.

Why do you want to extract PBCS Metadata?

PBCS offers the ability to extract metadata. Let’s see what you can use it for.

Note: please be aware that there are different use cases for extracting PBCS metadata. Some of them provide more value than others. In the sections below we are first describing the use cases, and then we provide a rating for the added value that it provides.

Backups

It’s always a good idea to backup your data and metadata. The ability to export PBCS metadata can be very valuable. However, since this is such a common requirement, Oracle already does this out of the box for you as – by default – the metadata of the entire PBCS application is included in the Daily Snapshot. If you just need metadata as a reference point without direct access to it, there’s nothing you have to do other than making sure your Daily Snapshots are configured correctly.

Added Value: LOW (unless you have additional requirements to customize your backups)

Audit Trail

Another great use case for exporting PBCS Metadata is to keep track of changes. The ability to answer when a new member was added, modified and deleted saves a lot of time when preparing reports for audit and compliance inquiries. Again, PBCS can do this out of the box. Therefore there is no need to manually extract the metadata for PBCS – unless there are additional requirements that PBCS doesn’t provide out of the box.

Added Value: LOW (unless you have additional audit requirements)

Share PBCS Metadata With Other Applications

EPM teams are doing an outstanding job when it comes to metadata management. Their attention to detail is required in order to provide mission critical information via EPM applications. Therefore, the quality of the managed metadata is typically setting the gold standard for application management in organizations. Congrats, folks!

Thanks to the high quality of the metadata of EPM applications, members and hierarchies (as they exist in EPM applications) are often used as the single source of truth. Therefore, other applications source their metadata from EPM applications. Here are some common examples for downstream systems which receive EPM metadata:

• BI: Domo, OBIEE/OAC, Power BI, Qlikview, Tableau, etc.
• CRM: Hubspot, Marketo, Oracle CX (Sales), Salesforce
• ERP: Microsoft Dynamics, NetSuite, Oracle ERP Cloud/E-Business Suite, SAP, Sage Intacct, etc.
• Financial Close applications: Workiva, Blackline, FloQuast, TrinTech etc.
• HR: ADP, Jobvite, Oracle HCM, Success Factors, Taleo, Workday, etc.
• Alternate EPM platforms: Anaplan, CCH Tagetik, Hyperion/Oracle EPM, OneStream, Planful, etc.

Note: some of these applications are, of course, managing certain aspects of the enterprise-wide master data themselves. For example, CRM systems provide the customer master, HR applications manage employees and positions, ERP systems manage the chart of account. However, there are several other segments/members/dimensions/rollups/hierachies – however you want to call it – which are often sourced from EPM applications and shared with other transactional systems.

Added Value: HIGH

Validate Member Properties

Over time metadata will change. The initial setup (hopefully) included a meticulous definition of your hierarchies along with purposeful default properties.. However, changes to the business processes require adding new members, alternate hierarchies or complete reorgs. Even the best guidelines are often times not followed as initially intended. Therefore it is a good idea to validate member properties from time to time and perform clean up activities. A metadata extract allows administrators to review all properties at once. This way it is easy to filter out those members which need to be updated.

Added Value: HIGH

Validate Alternate Hierarchies

Alternate hierarchies are very helpful to understand business performance based on specific categories or segmentation. Maintaining these alternate hierarchies is a key requirements for providing value, though. If a new member is not added to all relevant hierarchies within a dimension, the results are incorrect. To ensure the quality of their alternate rollups, companies can extract PBCS metadata as the first step and apply conditions to verify that alternate hierarchies are managed correctly.

With ICE Cloud it is possible to automate these rules and run them whenever new members have been added.

Added Value: HIGH

Identify Kickouts / Missing Members

Data loads encounter frequent errors due to missing members in PBCS. The ability to extract PBCS metadata allows to identify them – either upfront or during the load. While it’s not super easy to set up the required cross checks from scratch, all it takes is to check a box with ICE Cloud. Here is an article that describes this process in detail.

The reason why the heading of this section differentiates between “Kickouts” and “Missing Members” is to signal the following:

• A Kickout is the result of an error that occurs during the load process. By analzing the kickout record, you can then identify the missing member. This is a REACTIVE approach.
• A Missing Member is the result of comparing the metadata in the source system with the PBCS metadata. By identifying missing errors in advance, it is possible to add all missing members to the PBCS application in advance, ensuring a successful load. This is a PROACTIVE approach.

If you are NOT using ICE Cloud yet, you have to identify whether there is a kickout by opening the error log. If you ARE using ICE Cloud, then you just have to check a box to identify the missing members.

Once the missing members are identified, it is possible to add these members to PBCS automatically. We are working on another article to show several ways to automate PBCS metadata loads in a much more advanced and flexible way than what is available today within Oracle EPM Cloud.

Added Value: HIGH

Leverage PBCS Metadata For Mappings

Mappings are obviously important for managing EPM data. While FDMEE/Data Management (DM) powerful mapping capabilities, there is one big problem: mappings in FDMEE/DM are static and have to be updated manually. Wouldn’t it be great if you could extract PBCS metadata and leverage it for your mappings? This way you always have to latest mappings without having to do anything.

Common use cases are:

• Retrieve the base currency of an Entity
• Retrieve Account properties like Type (Balance Sheet/Income Statement, Intercompany, etc.), Time Balance (to determine YTD or Periodic values)
• Actual vs. Forecast months in a forecast Scenario

Added Value: HIGH

Create a Relational Repository similar to On-Premise Hyperion Planning

This was probably one of the bigger complaints after moving from Hyperion Planning to PBCS: there is no access to the underlying relational repository. This repository provided access to valuable information that allowed to build very creative solutions. While the Planning relational repository included a lot more than just metadata, the ability to retrieve metadata was a common use case. With PBCS, it is not easy to extract metadata in an automated way. ICE Cloud bridges the gap here, though, and provides access to PBCS metadata – in seconds, without jumping through any hoops.

Before we look at how easy it is to extract PBCS metadata with ICE Cloud, let’s take a quick look at the standard options.

Added Value: HIGH

Looking for other EPM Integration and Automation Ideas?
Check out our other USE CASES.

Standard Options to Extract PBCS metadata

Let’s look at the out of the box options for exporting PBCS metadata.

#1: Export Metadata Job in PBCS

Of course, there is the manual route of running an Export Metadata job. Log on to PBCS, then navigate to Application > Overview > Dimensions. Here you can click on the Export button and follow the steps to create the Export Metadata job.

This works fine, but is not automated.

#2: EPM Automate or REST API

EPM Automate is commonly used for automating EPM processes. It has a command to export PBCS Metadata using an Export Metadata job (as created above).

The REST API for PBCS also provides a command that can be used to automate the process. It does require more effort and a strong technical foundation. Once users are more comfortable with the REST API, it offers many powerful options, though.

EPM Automate: epmautomate exportMetadata JOB_NAME [FILE_NAME] 

REST API: 
Method: POST
URL:  /HyperionPlanning/rest/{api_version}/applications/{application}/jobs 
Request Body:
{
    "jobType": "EXPORT_METADATA",
    "jobName": "JOB_NAME",
     "parameters": {         
                "exportZipFileName": "FILE_NAME"
    }
} 

If your Export Metadata job is called ExportAccountDim and you want to generate a file called AccountDim.zip, then your command would look like this:

EPM Automate: epmautomate exportMetadata ExportAccountDim AccountDim.zip

REST API: 
Method: POST
URL:  /HyperionPlanning/rest/{api_version}/applications/{application}/jobs 
Request Body:
{
    "jobType": "EXPORT_METADATA",
    "jobName": "ExportAccountDim",
     "parameters": {         
                "exportZipFileName": "AccountDim.zip"
    }
} 

Now the Metadata has been exported from PBCS and written to CSV file for review. This CSV file is included in a zip archive. It needs to be downloaded and unzipped in order to access the file.

All dressed up and no place to go: How to use the extracted PBCS metadata?

So now you have an extract of the PBCS metadata and this was quite simple. But that’s about as good as it gets. The big question now is:

What are you going to do with the PBCS Metadata Extracts?

Looking at the use cases above, the only easy ones are 1 and 2, but they already come out of the box. To get value out of the PBCS metadata extract, you need to be able to process it in some way.

This is where a lot of technical folks start to write “a script”. While that’s an option, it has several drawbacks, including:

1. It takes a lot of time to write
2. It will be hard to maintain and understand
3. There is no built in error handling
4. It’s neither a user-friendly nor scale-able solution

So what can you do instead? Well, take a look at how easy it is to manage metadata with ICE Cloud.

Extracting PBCS metadata with ICE Cloud

How is it that ICE Cloud provides a much easier way to extract PBCS Metadata?

First of all, PBCS metadata is built into ICE Cloud: when you first connect to a PBCS (or EPBCS) application using ICE Cloud, you specify the URL, Domain Id and user credentials. After that, you click on the “Sync Metadata” button. Now ICE Cloud’s Application Intelligence (“AppIntel”) module for PBCS will retrieve a lot of useful information from your PBCS application. One part of it is to automatically extract PBCS metadata and make it available for easy use.

PBCS Application Intelligence – Metadata Sync

After the Sync Metadata process is completed, ICE Cloud provides a lot of useful information. Among many other things, it generates a Metadata Resource for each dimension in the PBCS application. The screenshot below shows all resources of the PBCS Vision application in alphabetical order, therefore it is a mix of resource of different types, like PBCS Metadata, PBCS Data (Essbase Format), Multi-Column Essbase, JSON Object etc.

Please note: ICE Cloud automatically generates one Metadata Resource per PBCS dimension, as highlighted in the screenshot above. In addition there are also many other PBCS resources that can be used to load and extract data and metadata, provide access to Job Names, Job Status, Substitution Variables etc.

Standard Attributes

When you click on one of these PBCS Metadata resources, you can see all available attributes of the associated dimension. (Please note: these attributes can vary by dimension). Here is what the first few attributes of the Account Dimension look like:

Through the built-in Data Preview feature, you can look at the PBCS metadata within ICE Cloud. Administrators and consultants use this preview a lot because it provides much faster access to the metadata as compared to the series of clicks that is required to view metadata in PBCS.

The built-in filters allow to quickly find specific members and their properties. In addition to that there are also a couple of additional fields that provide very helpful information that is not available in PBCS. We are going to look at these fields in the next section.

Other Useful Fields that only exist in ICE Cloud

In addition to the standard attributes of the dimension metadata as it exists in PBCS, ICE Cloud offers two additional fields:

• Is Level 0: this field states “Y” if the member is on Level 0. Otherwise, i.e. if it is a parent member, the value in this field is “N”
• Member Rollup: this field shows the member with all of its ancestors. This is very helpful for advanced validation tasks.

Access PBCS Metadata in ICE Cloud

With these auto-generated Resources, PBCS metadata is available immediately – without having to write any scripts. In fact ICE Cloud extracts the PBCS metadata and stores it so that you can access it even easier. With this, you can access metadata like any other dataset and can even combine it with any other dataset.

All you need to do is:

1. Create a Transformation in ICE Cloud
2. Choose the PBCS Metadata Resource of the dimension you want to extract as the Source Resource
3. Define a Target Resource to extract the data into, for example a file, a table, or another EPM Metadata Resource (e.g. FCCS, Essbase, HFM)
4. Map the fields from the PBCS Metadata resource to the corresponding target columns