Best Practices for loading Oracle Cloud App Data into BICS

My last post on BICS and Cloud (Fusion) Apps Integration Architecture described how BICS Data Sync 2.2 can read the OTBI metadata to allow for near real time trickle feeds from Oracle Cloud Applications. As I described in that post, there are multiple ways to load data from the Oracle Cloud Applications into BICS.  The options in Data Sync allow you to create custom extracts by using currently created OTBI Reports, writing SQL code or simply pulling entire Subject Area tables or objects.  In my last post I showed how the Data from Objects UI works by adding a Supplier Dimension to BICS.  The other options are:

• Data from Objects – Type list of object names
• Manual Entry – SQL
• Manual Entry – Report
• Manual Entry – Subject Area.Table

To select one of these options go to the Pluggable Source Data tab on the Data Sync Project Screen, you can then select one of the options above by clicking the appropriate option as seen below.

Of these 5 methods, I prefer the SQL approach (please note that the SQL used here is Oracle BI “Logical” SQL syntax).  If you chose from a list of objects, you will get the entire contents of an OTBI folder, but you can only get items from that one folder, which is fine for simple dimensions.  However, by using SQL you can create your own table structure.  If I want to bring in GL Account information I can either chose the “Payables Invoices – Transactions Real Time”. Account object or I can specify the fields by using this SQL code.

The IT Leader's Guide to Multicloud Readiness

This guide provides practical key insights and important factors to consider to make informed decisions in your multicloud journey.

Download the Guide

To create a fact table, I always recommend creating your own SQL Statement, this approach gives you control over the data you bring in and allows you to gather all the columns you need to join your dimensions to the fact.  For a simple A/P fact, the only metric that we need is the “Original Amount” value but we need the other columns to join to our dimensions in BICS such as GL Account, Business Unit, Fiscal Calendar and Supplier.

Once all these mappings are setup, BICS will automatically create the target table for you, you simply click Run Job on the Projects screen and then the data will be uploaded to the Cloud Database. After the data load is finished, you now add the tables to the Subject Area via the BICS Modeler and join appropriately as seen below.  At this point I’ve now successfully created an A/P Subject Area from Cloud ERP data.