For good reason, the database of Fusion SaaS products is not available for ETL tools to work directly against.
The recommended approach to extract data for most use cases, is through the BI Cloud Connector tool. This pulls data from 'View Objects' in the Business Intelligence layer of Fusion SaaS. Other tools, the Analytics Cloud Data Replicator, and Analytics Cloud Data Sync tool, can also extract from these view objects.
BI Apps customers have pre-created ODI mappings, that take the data from view objects, and load that into a pre-created data warehouse model.
For everyone else, there will be a certain amount of 'figuring out' which View Objects house the data you need, and then what are the data types, and the keys to join on.
This short article will walk through the steps to find which View Objects house the data needed.
Setting up an extract with the BI Cloud Connector, Analytics Cloud Data Replicator, or Data Sync tool, requires the name of the View Object.
Most fusion pillars provide an excel spreadsheet download that details the data available in extracts, and how that maps to the view objects.
1. Open a browser, and start at the SaaS main page in documentation: https://docs.oracle.com/en/cloud/saas/index.html
2. Select the fusion product pillar you are interested in - for instance 'Financials'
3. In the resulting screen, select the 'Analyze and Report' option
4. And then the 'Review data lineage mapping' link:
5. This will take you to a Cloud Customer Connect page where you can download excel spreadsheets with details of database tables, fields, and their corresponding view objects.
6. Download the applicable spreadsheet for the release of fusion, typically the latest one, and use that to figure out the view object that you need to use for your data extract(s).
The spreadsheets provide information on the BI Subject Area and columns, as well as the Fusion Database Table and Column names.
If you are familiar with Fusion Reporting through the OTBI portal, then you can use the BI Subject Area and Column information to narrow down to the View Object you need.
If you are familiar with the Fusion Database itself, perhaps from an on-premise install where you can access the database directly, then scroll to the right and use the Database Table and Column fields.
Use the filters in the columns to narrow down and find the names of the View Object(s) that are required for your extracts. These can then be used in the BI Cloud Connector, Analytics Cloud Data Replicator, or Data Sync, to select and extract the data required for your downstream processes.
This article walked through the steps to identify which View Objects house the data required for Fusion SaaS data extracts.