Sunday 16 September 2018

Integrating Oracle Financials Cloud into PBCS and a Drill Through back to source

First off, sorry for the long hiatus from blogging about Essbase.  To resume where I left off, I thought I'd share my first attempt to configure the data integration between Oracle Financial Cloud with PBCS and a drill through back to the data source.  Here goes...


Establish the link to Oracle Fusion in PBCS via Data Management
Start with setting up the connection to Oracle Fusion to allow us to then load the GL balances from the ERP system.  We will begin with adding a Source System in Data Management.  Select "Oracle Financials Cloud" from the "Source System Type" dropdown list and then click "Save".



















Click on the "Configure Source Connection" to define the login credentials and the URL to the source system.  The Oracle Fusion user you use here should be granted the "OA4F_FIN_GL_DETAIL_TRANSACTIONS_ANALYSIS_DUTY" role access.  Fill in the details and click the "Test Connection" to ensure a successful login to the source system.  Click "Configure" to conclude the configration.










Click on the "Initialize" button to create the necessary Essbase cube required to stage the data extracted from Oracle Financials Cloud.


















This step will create the Essbase cube that will appear in the Target Applications section of Data Management.


Create the Target Applications to store the extracted data
This section of Data Management contains all the target systems where the raw extracted data and the final mapped data are stored.  The "COA" is a Target Application created by Data Management when we initialized the connection between Oracle Financials Cloud and PBCS.  Leave the settings as they are.  We do not need to make any changes to the settings for "COA".


You will now need to manually add the Target Applications for the final destination of the extracted data.  We will define one for an Essbase ASO cube and another one for the PBCS BSO Planning cube.

Let's begin with the ASO cube called "FinRpt".  Here's the settings that you will need to be aware of to ensure that data is loaded to the ASO cube and have the ability to drill back to the source data.  Note that we need to select "Essbase" as the Type for an ASO Target Application.  Click on the "Create Drill Region" check box for the relevant dimensions.  Please ensure that the following dimensions are tagged with the correct "Target Dimension Class".  
  • Account_Segment should be tagged as Account
  • Cost_Centre_Segment should be tagged as Entity (if this dimension was defined as an Entity type dimension in PBCS)
  • Scenario should be tagged as Scenario (note that Data Table Column Name for this dimension will disappear when it is tagged to Scenario dimension class.  This is fine as the scenario in Data Management is derived from the Category code)
  • Version should be tagged as Version
  • Years should be tagged as Year

Failing to do this may result in an unsuccessful drill through.  Click "Save" when done.


















Click on the "Application Options" tab and turn on the Drill Through.  Click "Save" when done.


















Repeat the same for the Planning cube Target Application (in this example, named as "Fin").




















Define the Import Formats required
Now that you have the Target Applications defined, let's proceed to adding the required Import Formats.  This allows you to define the links and dimension mappings between the source and target applications.  Let's add one for the FinRpt cube.


Just select the relevant Source Dimensions from the dropdown list and map them to the correct Target.  You can also define Expressions to pad in zeroes to the values when importing to Data Management.  Click "Save" when done.  Repeat the same process for the Fin Planning cube.





















Build the Locations
Locations allow you to create different data load rules and mappings using the same Import Format.  In the example, we will be creating three locations for the ERP_FinRpt Import Format and a single location for the ERP_FinPlan Import Format.

For ERP_FinRpt, we will create

  • ERP_FinRpt_PL (for P&L load)
  • ERP_FinRpt_BS_CB (for Balance Sheet Closing Balance load)
  • ERP_FinRpt_BS_Mvmt (for Balance Sheet Movement load)

We will create ERP_FinPlan_PL for P&L load.

Let's start with ERP_FinRpt_PL.  Add the location and link the ERP_FinRpt Import Format to this location.  Click "Save" when done.




















Now repeat for the rest of the locations.  Remember to select the ERP_FinPlan Import Format for the ERP_FinPlan_PL location.





















Confirm the period mappings
The Period Mapping is used to map to the correct Period and Year in the Target Application.  In this example the application is defined with the Financial Year starting in July and ends in June the following year.  So make sure that the Year is mapped correctly.  Click "Save" when done.




















Build the Category Mappings (if required)
Categories in Data Management maps to the scenario dimension in the Planning or ASO applications.  Data Management will automatically create several categories and you can add new ones or update the ones created by Data Management as required.  Just make sure that the name of the Category matches the Scenario name in your Target Applications.  As usual, remember to click "Save" when you are done.




















Create Data Load Rules
For each Location that you've define, create a Data Load rule and define the filters for each of the dimension from the Source System.  Click "Save" when you are done.


When selecting the filters, please make sure you use fully qualified member names to avoid any duplicate member names that is possible in Oracle Financials Cloud.















Repeat for the rest of the locations that you've defined.


Configure the Dimension Mappings
Define the mappings like you would for any Data Management configuration for each of the locations created.  Remember to click "Save" when done.




















Repeat the mappings for the rest of the dimensions.




















Extract and Load the Data into PBCS
We are now ready to extract the data from Oracle Financials Cloud and load them into PBCS.  You can either do this via the Workbench or the Data Load Rule.  In this example, I'm loading the data via the Data Load Rule screen.  Click "Execute".




















Fill in your selections then click "Run".





















Drilling back to the source!  Woohoo!!
Now that we've loaded the data into PBCS, let's look at how we can drill through from the summary balances in PBCS to the underlying details that exist in Data Management.














Notice that each cell that is "drillable" has an indicator at the top-right of the cell.  Right-clicking on the drillable cell will reveal an option for you to "Drill Through".


















Select "Drill Through" from the pop-up menu and the underlying details of the selected cell will be revealed.

















The top half of the screen shows the data intersection of the selected cell while the bottom half of the screen shows the underlying records that make up the amount shown in the selected cell.

There you go, the step by step guide to integrating Oracle Financials Cloud data into PBCS with the ability to drill back to the data source.  Hope you find this blog useful and provides some guide for you.  Cheers!

And then there's the Waterfall Chart in PBCS

Ever found yourself looking to create a Waterfall chart in PBCS Dashboards only to be left disappointed?  Sure, you can have this done when ...