December 20, 2022

By: Bikash Pattnaik, Associate Director - Data Engineering, LTIMindtree

Extract Data from Oracle Application Cloud using Business Intelligence Cloud Connector (BICC)

The need of the hour?

In today’s world, being ahead of the curve is what each organization is looking out for. Digitizing their offerings faster than others gives them a competitive edge over their peers. This process is vastly sped up by adopting cloud technologies.

Oracle Application Cloud is the market-leading cloud platform providing ERP, HCM, SCM, CX, and numerous other services. While organizations are quickly adopting Oracle Application Cloud, there is a constant need to leverage the best out of these applications by extracting business-critical information as expeditiously as possible.

The days of trying to extract information only through reporting tools are long gone. The need of the hour is to load petabyte-scale data efficiently and swiftly, derive intelligence from it by running machine learning algorithms, and benefit from the data insights.

The major challenge that companies are facing is this –

How do we retrieve data from the cloud in a timely, cost-effective, and secure manner for both bulk data and recurring incremental data?

What options do we have?

A myriad of options is available, but none can match the simplicity, efficiency, and robustness of the Business Intelligence Cloud Connector (BICC) to retrieve data from the cloud.

BICC involves a simple two-step process:

1) Identify, configure, and extract from Oracle Application Cloud
2) Store output in shared storage like OCI Cloud Storage or Oracle Universal Content Management (UCM) for further consumption of extracted data

Why should one use BICC Data Extraction?

The relevance of Oracle BI Cloud Connector comes into the picture while extracting the data from Oracle Fusion Cloud to external storage.

BICC is available as a part of the Oracle Application Cloud subscription ₁ and it enables data extraction in bulk mode and loads it into the designated storage areas. It enables the selection of the required Oracle Fusion offerings from which data extraction is needed and places it in the designated target, which can either be Oracle external cloud storage or Oracle Universal Content Management (UCM).

Within BICC, Public View Objects (PVO) can be associated with various offerings such as Finance, HCM, SCM, etc, which in turn contain numerous seeded PVOs. Each PVO within an offering extracts data from a group of underlying tables. You can add, modify, or delete required columns and get meta-data for each PVO in a very flexible way.

For example, Cost Center PVO is Finance offering extracts data from Tables such as GL Tree, GL Tree Version, and GL Tree History. You can also choose to add the required columns or modify them as needed.

Setup/Configuration₂:

• BICC privileges: Before initiating extract, ensure to have access to BICC and UCM server, that will store the file. The intended user needs to have ESS Administrator and Application Implementation Admin Abstract role membership for BICC and Extract Transform Load role for UCM. You can create a new role and assign these role memberships to the role and assign this new role to the intended user.
• Configure external storage: finalize your preferred storage option
• 1. • OCI Cloud Storage Connection
     • UCM Connection
• Manage offerings: Create a new offering or modify an existing offering and specify the associate data store.
• Configure and schedule extracts: Schedule the extract for specific offerings/ PVOs, and monitor the scheduled jobs. By default, the extract will be set to incremental mode based on the last extract date, however, if you wish to do a full extract, use the “Reset to Full Extract” option.
• Download the extracted files: Once the extract is complete, the extracted files will be stored in the chosen external storage and a MANIFEST.MF file will be created. You can then download these files, unzip them, and process them to load them to your preferred database/data store.
• Integration: You can also make SOAP and API calls to automate the file download and consumption.
• Salient features:
  • Help extract bulk data in a hassle-free manner from Oracle Cloud
  • Flexibility to choose target storage
  • Provides ample configuration options to extract required data as per business requirement
  • Option to schedule jobs at desired frequency without worrying about impacting source system
  • Option to configure multiple independent extracts at convenient intervals
  • Easy monitoring, governance, and management of data extracts
  • Safe and secure extraction process with added functionality for encryption of data

  Known challenges:

  The extracted time increases with a rise in the number of columns selected for extraction from PVO, even extract time will be more for the initial full extract containing a large volume of data.

  Conclusion:

  Upon completion of the initial BICC configuration, it will become a very simple process to extract full or incremental data from the Oracle Cloud to your desired data store.

  References:

  ₁ Oracle ERP, HCM, SCM, CX & Subscription Cloud, apart from this Oracle keeps adding new cloud offerings
  ₂ Creating BI Cloud Extract:https://docs.oracle.com/en/cloud/saas/applications-common/21a/biacc/creating-a-business-intelligence-cloud-extract.pdf