Cloud

Best Practices for loading Oracle Cloud App Data into BICS

Mdata-sync-incony 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.

otbi-data-extract-options

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.

gl-account-sql

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.

ap-transaction-fact

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.

dim-gl-account

dim-supplier

ap-invoice-fact

joins-v2

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

John Whitaker

John is a Senior Project Manager in the National Oracle Business Analytics Practice. He has over 18 years of experience in the design, development and implementation of Enterprise Software Applications including Business Intelligence, Data Warehousing and Industry Analytics solutions. John has expertise in leading large scale projects in multiple industries including Fortune 500 companies, leading research universities and government agencies. He is currently specializing in the development of Oracle BI Cloud Services solutions including Retail, Healthcare and Higher Ed Analytics.

More from this Author

Subscribe to the Weekly Blog Digest:

Sign Up
Follow Us
TwitterLinkedinFacebookYoutubeInstagram