Finding Answers in the Cloud: Drill-Through from Oracle PBCS to Oracle Fusion Financials Cloud Service

You love Hyperion and its ease of use when it comes to “slicing and dicing” your data. You love that it’s easy to build financial statements or perform analysis on your financial data. But then you get to a point when you have questions about the numbers and the data in Hyperion, at a trial balance/summary level, it won’t answer the questions you have because it doesn’t have the necessary level of detail. So, what do you do?

Oracle/Hyperion has had a technical solution available for some time now that allows you to get to the details in Oracle Fusion Financials from Hyperion, known as Essbase Studio. But there have been some barriers to make this a widely-used solution. First, there is an additional license cost. Second, the implementation can be a project in itself. And third, it works for on-premise Planning and Essbase only. Essbase Studio would allow users to drill to additional underlying data and support answering questions regarding financial statement summary balances without having additional dimensionality and data in Hyperion.

Around the 2009-2010 time frame, Oracle decided to develop new software, ERPi, which leverages Financial Data Quality Management (FDM) and Oracle Data Integrator (ODI), to support the ability to drill to underlying data within general ledger software. The software has matured over the years, allowing more source system general ledgers and is now embedded as part of the Financial Data Quality Management Enterprise Edition (FDMEE). But all of this capability, unfortunately, was only available using on-premise Hyperion Planning.

With the introduction of Oracle Planning and Budgeting Cloud Service (PBCS) in 2012, more companies have access to implement world class software with the ease of implementation provided by the Cloud. Finance departments are still asking for detailed information and their need to get to the underlying source data of their financial statements is the same as for on-premise users.

The earlier versions of the PBCS did not have the same capabilities as the on-premise version. As PBCS has matured, more and more of these capabilities have become available. One of these is the FDMEE drill-through functionality. The only limitation, however, was with one source general ledger, the Oracle Fusion Financials Cloud Service.

In 2015, our team was tasked to implement PBCS for a client who, at the same time, was implementing Oracle Fusion Financials Cloud Service. Early in the implementation, the desire to drill to detailed data, i.e., from PBCS to Oracle Fusion Financials Cloud Service, was requested. We knew the functionality was pending and since it was not available at that time, it was tabled. After successfully implementing the Planning and Reporting solution, we revisited this request to drill to the detail and over the course of our project, this functionality was made available by Oracle. We initially leveraged our previous knowledge and experience of on-premise ERPi/FDMEE to configure the drill-through and also leveraged available documentation. We had some pieces working and other pieces were just not letting us get to the finish line. Using our partner status and connections with Oracle Development, we worked through our remaining roadblocks and we were able to successfully drill from PBCS to Oracle Fusion Financials Cloud Service.

In 2017, improvements were made…here is a glimpse into the improvements.

To enable drill-through to Oracle Fusion in PBCS:

Setup Source System – BEFORE

Configure Source Connection with Ledger user, password, and the Fusion Web Services (WSDL) URL.

Setup Source System – AFTER

  • New Source System Type: “Fusion Financials Cloud”
  • New Fusion Cloud artifact: In the “Application Filter” you can specify the
  • full Fusion application name, wild card, or a wild card
  • New Configure process: The “Initialize” process brings over GL data as ASO Essbase cubes in PBCS.
  • New Configure validation: “Test Connection”

BEFORE

Add Import Format – This will align to the Target Fields (Required dimensions and LOOKUP) > Input Drill URL

Continued…

Continued…

AFTER

Add Import Format – This will align to the Target Fields (Required dimensions and LOOKUP) > Input Drill URL

  • New Source System Selection
  • New Source field options based on Fusion Chart of Accounts

Create Location

BEFORE

Add location (the “_” character is restricted in the name):

AFTER – **No change to create a location

Create Data Load Rule

BEFORE

Select correct data format for date data:

AFTER

  • New Source System Filters
  • New Source System Filter Conditions

BEFORE

Add Target Application > Add additional LOOKUP fields required for Drill based on the Source Segments, for example:

  • ACCT – Natural Account Segment
  • COA – Category of Aid Segment
  • DPT – Department Segment
  • LOB – Line of Business Segment
  • LOC – Location Segment
  • NWT – Network Type Segment
  • …and all other required Fusion Segments in Chart of Accounts to be referenced

Continued…

AFTER

Add Target Application

  • New Target Application option: “Cloud”

 New authentication to EPM Cloud:

> Add additional LOOKUP fields required for Drill based on the Source Segments, for example:

  • ACCT – Natural Account Segment
  • COA – Category of Aid Segment
  • DPT – Department Segment
  • LOB – Line of Business Segment
  • LOC – Location Segment
  • NWT – Network Type Segment

…and all other required Fusion Segments in Chart of Accounts to be referenced

Continued…

BEFORE

Create mappings for all dimensions: Enter proper SQL that coincides with each LOOKUP dimension, data column in FDMEE table, and proper mappings for the required target dimensions.

AFTER

Create mappings for all dimensions: Data load mappings convert the chart of accounts values from the Fusion General Ledger to PBCS dimension members during the transfer. Note: No SQL required.

You can now execute the Data Load Rule and drill-through to the Oracle Fusion Financials Cloud Service. Drill though enables you to display and view the account balance summary page in the Oracle Fusion Financials Cloud Service. Data Management sets up the drill definition automatically. When the Fusion Financials Cloud Service is drilled back, Data Management passes the stored values from the trial balance data in Data Management table as parameters in the drill URL.

Blog post by Cullen Caballero and Alex Canizales of Key Performance Ideas.


 

RECENTLY
FROM THE BLOG

 

9/25

PCMCS Webinar