Direct Data Access In Fusion Cloud Applications

This article provides a summary of the options available for Direct Data Access in Fusion Cloud Applications.

Let's start discussing the most common question - 'can I setup direct SQL access?' At the time of writing direct client connections to issue queries to the applications database are not possible for Oracle Cloud Applications, due to the robust security layers in place. As such tools like SQL Developer cannot be used. However, you can access most of the applications data through specific supported features and published API's. This article looks at these options.

Web Services

This is the recommended mechanism for getting, creating, updating and deleting small to medium volumes of records by remote systems. We've written about both the RESTful and SOAP services in this blog, plus they are documented in the Oracle API Catalog (for REST) and (for SOAP) in product-specific documentation books, such as the one for common objects.

Angelo Santagata from the Oracle A-Team provided the following summary of API support across the products (R11) in his recent PaaS4SaaS presentation at the UKOUG conference.

** Controlled Availability. Bulk refers to file based loader, see below.

BI Subject Areas

When creating custom reports and analytics fields are selected from the huge catalog of Subject Areas. This abstracts the complexity of using the underlying data model, making report creation fast and simple. As such this is the recommended path for creating reports, and with every release the Subject Areas are expanded to include more data.

It is not possible to define entirely new subject areas in Fusion Cloud Applications (outside of supporting custom objects in Application Composer), as it requires deep OBIEE administrator access which opens up the possibility of making unsupported changes and untested configurations.

Based on subject areas, reports and analytics are commonly developed to deliver rich HTML-based visual data insights. The flexibility of the BI authoring tools also allows XML and CSV as output formats, and combined with scheduling and web service accessibility it opens use-cases beyond simple visual consumption by human users. An example might be providing a data file of new suppliers registered in the system over the past quarter to issue agreement letters using a secondary system.

BI Publisher

This is frequently the go-to tool for getting data from an environment, especially when coming from E-Business Suite where this tool originated. In addition to using BI Subject Areas for creating reports, this tool allows for SQL statements to be issued when creating a new Data Model upon which to build a report layout.

Using the documentation for tables and views (E.g. financials) together with the data model diagrams (we republished) specialists can build detailed custom reports. Obviously testing should be undertaken upon upgrade, as internal data models may be subject to change.

Again BI Publisher supports raw data formats such as XML and CSV (in addition to visual equivalents like PDF) and web service access (video), scheduling, and output delivery features - shown below. Read more here.

File Based Loader Export

Across all products there is a standard mechanism for importing and exporting data to/from business objects. The file based loader has broad support for most record types (including customizations) shown in the user interface. Below is the main configuration screen, showing the selection of opportunity object data to be exported. Once scheduled and submitted the result is a zip of CSV files, one for each object included (the parent and its children). The features are explained in more detail in this series of articles with explanations and detailed video demonstrations.

Product-specific Options

Many products have specific business processes that include options for sharing data with external systems. Examples are running payroll or calculating tax. One such product with multiple options is HCM, and under the Data Exchange icon there are options for different types of data.

More detail on each of these is provided in the HCM administration guide. Similar administration guides exists for each product family.

ADFdi and Export-to-Excel

ADF Desktop Integration (ADFdi) is also part of the ADF framework offering Excel spreadsheet templates to validate entry of large volumes of data into Oracle Cloud Applications.
The integration provided with ADFdi includes not only transaction records, but also populated pick lists of valid
values, applications validation during data entry, error messages, and immediate submission
of transactions directly from Excel.

While the focus of ADFdi is on data entry from Excel, the provided templates are populated with live data and could be used to support external interrogation needs. It is a flexible system that is an alternative way to work with applications records remotely. Check product-specific features for support for ADFdi and for general information consult this Common features guide.

In addition to ADFdi there are some tables which support the option to Export to Excel. This is predominantly supported in the Classic / Desktop UI however this is accessible in-context using the "show more details" menu item. The example below is from the Accounts object record where an advanced search provides a very specific data set downloaded after just a few clicks.

Oracle BI Applications Configuration Manager

Also known as 'Oracle BI Cloud Connector Console' this standalone tool embedded within Fusion Cloud Applications allows you to extract data for use with secondary BI systems, such as
Oracle BI Cloud or an on-premise OBIEE data warehouse. It is possible to refine the seeded extract definitions and schedule regular
extracts (using ESS). The results are forwarded to an Oracle Cloud Storage target or put on to the
internal Webcenter Content (UCM) server which can be accessed directly.

The administration screens are accessible for admin users from the following URL and full details in given in the documentation.

• https://[yourCloudAppsBIdomain].com/biacm

Summary

This article explains how data access requirements can be met without direct SQL access. Clearly this is a big paradigm-shift from running on-premises applications and takes some time to get used to, however every day existing Oracle Cloud Applications customers use the solutions above. Selecting the right solution, or using a combination of options, will provide access to the data in Fusion Cloud Applications.

It is also worth considering why direct SQL access is not practical. Reasons include, but are not limited to:

• Security - the applications database is at the heart of the system and Oracle Cloud Applications provide multi-layered security including firewalls, advanced networking and physical security measures.
• Assured Consistency - the cloud model shifts the IT burden from the customer to Oracle, meaning Oracle is responsible for stability, data integrity and for seamless upgrades. Only validated and validating APIs (such as web services) can guarantee this data integrity. Any direct data updates to the tables could violate that and destabilize the environment.
• Scalability - Our cloud services must scale, as we meet the needs of thousands of customer environments. This is especially important with modern rapid update cycles happening several times a year.

Please share any additional suggestions or experience of data-access related solution in the comments below.