Set up Oracle Fusion SaaS Business Intelligence Cloud Connector (BICC) to use Oracle Cloud Infrastructure (OCI) Object Storage

September 16, 2019 | 13 minute read
Jay Pearson
Consulting Solutions Architect, Business Intelligence
Text Size 100%:

 Background

* This blog was last tested on OAC 105.8.0-133 (Version 5.8) + Fusion 20D (11.13.20.10.0) *

*** Available in Fusion Applications 19C+ (11.13.19.07.0+) - ORA_ASM_APPLICATION_IMPLEMENTATION_ADMIN_ABSTRACT role required ***

This article walks through the steps to set up an Oracle Cloud Infrastructure (OCI) Object Storage Bucket, for use with the Oracle Fusion SaaS - Business Intelligence Cloud Connector (BICC).

For customers using Cloud Storage Classic Service please follow this article.

This may be of particular interest to Oracle Analytics Cloud (OAC) customers, wanting to use the new Data Replication functionality from Fusion SaaS (for more details, see this article).

*** OCI Object Storage support is available in OAC 5.4+ – ADMIN permissions required ***

This process requires an OCI Object Storage subscription (that is a supplemental service to Fusion SaaS and OAC).

Recommended Reading / Useful Links

 Create OCI Storage Bucket

Before creating the bucket you must first create a compartment. See "recommended reading / useful links" above for instructions.

09/19/19 - At the time of testing limitations were identified using an OCI Federated User to access the OCI Storage Bucket.
Using an OCI Federated User to access the OCI Storage Bucket may result in the below error from BICC:
Invalid Connection for External Storage.
[BIACM0163] Invalid Connection for External Storage. Action: Please verify the connection details provided.
[BIACM0145] Invalid connection for OCI Object Storage. Action: Verify the connection details provided.

You may also see this error from CLI when running .\oci os bucket list -ns namspace --compartment-id ocid1.compartment.{id}

{
    "code": "NamespaceNotFound",
    "message": "You do not have authorization to perform this request, or the requested resource could not be found.",
    "status": 404
}

A-Team testing found that even if the OCI Federated User was granted policy: "manage all-resources in compartment" and was able to create the bucket, they were not able to access it through BICC or CLI unless the "allow any-user to read all-resources in tenancy" policy was also granted. "Read all-resources" must be granted to any-user. Setting this at a group level does not resolve the issue.

If setting "allow any-user to read all-resources in tenancy" is not suitable for your tenant, it is suggested to use a Local OCI User to access the bucket. Using a Local OCI user does not require the "allow any-user to read all-resources in tenancy" policy to be set. When using a Local OCI user the minimal policy required is "manage object-family in compartment". For example:

Allow group <GROUP NAME> to manage object-family in compartment <COMPARTMENT NAME>

~

a) Sign into Oracle Cloud: https://cloud.oracle.com

b) Click on the Infrastructure Dashboard

c) Click top left hamburger -> select Object Storage -> Object Storage

d) Create Bucket

e) Change the default bucket name -> change other settings as desired -> click Create Bucket

  • The bucket name should not have any spaces in it.
  • Leave the Storage Tier = Standard
  • Object Events = Unchecked
  • Encryption = "Encrypt Using Oracle Managed Keys" (suggested)

Client-side encryption is not supported. Customer-managed keys can be used to encrypt the data stored in the object storage container instead of using the keys that Oracle generates. The encryption key used is transparent to the client since object storage decrypts the data before sending it to the client over SSL.

 Gather/Copy OCI Connection Properties

a) User OCID - Top Right -> Click on Profile -> User Details -> Copy User OCID

b) Tenancy OCID & Namespace -> Top Right -> Tenancy Properties -> Copy Tenancy OCID & Namespace

 BICC Settings

a) Confirm the Fusion user has the ORA_ASM_APPLICATION_IMPLEMENTATION_ADMIN_ABSTRACT role or a role that includes it.

Detailed instructions to" Provisioning a User for BI Cloud Connector Console Access" can also be found here

     Log into Fusion Home -> Click on Tools

Security

Users -> Search on your user

b) The user used to test with has the SALES_ADMINISTRATOR_JOB role assigned which includes the Application Implementation Consultant roles.

c) If you do not want to assign the Sales Administrator role, the alternative is to assign the individual required role.

d) Log onto Oracle Business Intelligence Cloud Connector Console: 

https://<Fusion-Apps-host>/biacm

*** Note you must use a local Fusion Apps User. Federated Fusion Apps users are not supported ***

e) Click on Configure External Storage


f) Click on OCI Object Storage Connection tab -> Add

g) Configure Oracle BICC - External Storage Settings:

Paste in the OCI Parameters that were gathered/copied from OCI in the previous steps.

Host is based on the data center and can be copied from the Object Storage Service API Endpoint here.

*** When pasting in the Host remove the https:// - see format below ***

objectstorage.ap-mumbai-1.oraclecloud.com
objectstorage.ap-seoul-1.oraclecloud.com
objectstorage.ap-sydney-1.oraclecloud.com
objectstorage.ap-tokyo-1.oraclecloud.com
objectstorage.ca-toronto-1.oraclecloud.com
objectstorage.eu-frankfurt-1.oraclecloud.com
objectstorage.eu-zurich-1.oraclecloud.com
objectstorage.sa-saopaulo-1.oraclecloud.com
objectstorage.uk-london-1.oraclecloud.com
objectstorage.us-ashburn-1.oraclecloud.com
objectstorage.us-phoenix-1.oraclecloud.com

*** Do NOT use console.region.oraclecloud.com *** Some customers have reported this showing a successful test connection. However, it will fail on replication ***

Generate the API Signing Key -> Export the Key.

** Keep this tab open ... as you jump back to your OCI tab to import the key. ***

You can save the connection before the Public Key has been added to OCI. However, in order to validate the connection the key must be imported into OCI first.

 Add Public Key to OCI

Return to the OCI tab -> Click (top right) on Profile -> User Details -> Scroll down to API Key -> Add the Public Key that was generated and exported from BICC.

 Test the BICC Connection

Return to the Fusion BICC Console tab -> Test the Connection -> Don’t forget to save (top right)

The Fusion SaaS - Business Intelligence Cloud Connector (BICC) has now been configured to load files to the Oracle Cloud Infrastructure (OCI) Object Storage Bucket.

For OAC (+5.4) customers, the BICC connection is also ready for use in the OAC Oracle Analytics Cloud (OAC) Data Replication Connection.

 

 Configuration Tips

By default BICC incremental extract exports all data that has changed within the last 24-hours.

To adjust this go to: Manage Offerings and Data Stores -> Actions -> Extract Preferences -> Prune time in Minutes.

 Deciphering View Objects

Documentation on View Objects is limited. Below are a few links that may assist with deciphering View Objects.

a) In this A-Team article titled “Fusion SaaS - Finding The View Object You Need for Data Extracts” it suggests to go to "Configure Offerings to Extract" (20C) then go to the section called "How to Review View Object to Database Lineage Mappings" to download the "BI View Object to Database Lineage Mapping Spreadsheets".
 
b) In this A-Team article titled “BI Cloud Connector – Custom Object Data Extraction” - scroll down to the section called “Appendix: Determining object names for Custom Subject Areas”. This describes how to get View names from the pre-built OTBI reports.

c) From the Fusion Help Center:
 
Select the relevant module. i.e. Order Management
 
On the left click on "Analyze and Report".
 
Here you will find various links on the Subject Areas.
 
i.e. https://docs.oracle.com/en/cloud/saas/supply-chain-management/20c/analyze-and-report.html
 
d) From the Fusion Help Center:
 
Select the relevant module. i.e. Order Management
 
Scroll to the bottom under “Development (APIs & Schema)”
 
Here you will see a link called “Tables and Views for {Module Name}” 

i.e. https://docs.oracle.com/en/cloud/saas/supply-chain-management/20c/oedsc/index.html

 Adding Custom View Objects

a) Navigate to Manage Offerings and Data Stores

b) Select the Offering (i.e. Financial)

c) Click the + sign (to add a data store):

d)  Enter the Data Store Key and select the Associate Offering

i.e. FscmTopModelAM.LinesDFFBIAM.FLEX_BI_LinesDFF_VI and Financial

e) Click Next


f) Customize the columns as needed.


g) Click Save to save the new data store.

 BI Cloud Connector Performance Recommendations

Go to: Oracle Fusion Transactional Business Intelligence and BI Cloud Connector Performance Recommendations (Doc ID 2679006.1)

Download: Oracle Fusion Transactional Business Intelligence and BI Cloud Connector Performance Recommendations

See chapter towards the end of the doc titled "Oracle BI Cloud Connector Performance Recommendations".

 API

There is both a SOAP and REST API available. Documentation on the API's can be found here.

The API's can be used to schedule BICC extracts programmatically.

 Want to Learn More?

Click here for Oracle doc on Creating a Business Intelligence Cloud Extract

Click here for SaaS Data Replication in Oracle Analytics Cloud (OAC, and OAAC)

Click here for Set up Oracle Fusion SaaS BI Cloud Connector (BICC) to use Classic Cloud Storage

Click here for more A-Team Oracle Analytics Cloud (OAC) Blogs

 Summary

This article walked through the steps to set up an Oracle Cloud Infrastructure (OCI) Object Storage Bucket, for use with the Oracle Fusion SaaS - Business Intelligence Cloud Connector (BICC).

Jay Pearson

Consulting Solutions Architect, Business Intelligence


Previous Post

How to use Vault to store ATP Wallet

Julio Camara | 6 min read

Next Post


Integrating Oracle Enterprise Data Management Cloud Services Dimension Data Into Oracle Autonomous Data Warehouse With REST API and ODI on the Marketplace

Matthieu Lombard | 10 min read