Data Extensions

As a service administrator and data engineer, use data extensions to add additional data for analytics.

The storage capacity in Oracle Autonomous Data Warehouse is planned to support data coming from your subscribed Oracle Fusion Cloud Applications sources. However, if you add data coming from another source, you need to ensure that you have enough storage available. Fusion Analytics includes 50 GB of storage for external data. If you're planning to load more volume, you need to add additional capacity to the database (in storage and potentially OCPU). Adding capacity will consume Universal Credits. Plan to extend data using any or all of these options in the same environment for different use cases:

• Custom Data Configurations
• Data Augmentation
• Self-service using Oracle Analytics Cloud Datasets and Dataflows
• Third-party ETL tool such as Oracle Data Integrator

See:

• Custom Data Configurations
• Data Augmentation
• Self-Service using Fusion Analytics Datasets and Dataflows
• Third-Party ETL (Extract, Transform, and Load) Tool

Custom Data Configurations

Custom data configurations are ready-to-use templates that enable you to bring data into the autonomous data warehouse quickly.

Currently the only template available is for Descriptive Flex Fields (also referred to as DFF). You only need to select the list of DFF columns you want to import for each object. Data is loaded in the pipeline managed by Oracle and added to the semantic layer automatically.

See Extend Data with Custom Applications.

Data Augmentation

Use data augmentation to add additional data from a supported data source that doesn't require transformation. Oracle Fusion Cloud Applications data must be available in a view object to be loaded through data augmentation. Data augmentation can extend existing dimensions, create new dimensions, and create new facts.

There are many benefits to using data augmentation:

• Oracle manages the execution.
• It's scheduled with the prebuilt pipelines, which is essential for data consistency (extract dates are the same).
• There is no need to buy, learn, or manage another extraction tool.

But data augmentation has these limitations currently:

• No data transformation.
• No joining with other tables and data sources.
• A limited number of supported data sources, most available in preview only.

Leverage views in the OAX_USER schema built on the augmentation tables to implement transformations and joins. Depending on the performance requirements, these views can be materialized. However, this is useful only for simple use cases. Performance and maintenance quickly become issues if the transformations are too complex (multiple joins, aggregates). In that case, using a third-party extraction tool is the best option.

See About Augmenting Your Data.

Self-Service using Fusion Analytics Datasets and Dataflows

Fusion Analytics allows users to upload datasets directly and use them for reporting or joining them with existing subject areas.

These datasets can come from a file or be defined using a connection to an external application. Datasets are designed to provide self-service extension capabilities to business users to bring in external data that's not in the semantic model. Dataflows transform and load datasets data, for instance, into a custom schema in the Fusion Analytics database. Dataflows can load only a small volume of data and are currently accessible only by their owner. But once loaded in the database, the corresponding table can be added in the semantic layer. Depending on the mode selected, a dataflow can drop and recreate the target table when executed. Since tables used in the semantic layer must have access granted to user OAX$OAC, granting access by default to the entire custom schema is safer. Otherwise, execute the grant SQL statement after each execution of the data flow.

See:

• What are Datasets
• Create datasets using dataflows

Third-Party ETL (Extract, Transform, and Load) Tool

Fusion Analytics allows users to upload datasets directly and use them for reporting or joining them with existing subject areas.

Any third-party extraction tool that supports Oracle Autonomous Data Warehouse can load external data into the Fusion Analytics database. Oracle Data Integrator is an example of an extraction tool.

Plan to use an extraction tool to load external data as a last resort when none of the other solutions are appropriate. You must review data augmentation and connectors first and if you can't use either of these capabilities, then plan to use Oracle Data Integrator for Marketplace and other ETL tools.

There are a few elements that are important when loading data into the Fusion Analytics database with an extraction tool:

• Ensure that custom data loads don't run simultaneously with pipelines to avoid contention on database resources. You can determine that an incremental pipeline is finished when a new record is inserted into table DW_WH_REFRESH_SUMMARY with the process name _REFRESH_SCHEDULED.
• Use the Low service connection to the autonomous data warehouse. Using the High or Medium service consumes too many resources and can cause performance issues for other processes running on the database.
• Depending on the design of your load process, data inconsistencies and performance issues can appear in reports if users access the custom tables while they are being loaded. Try to load the data outside of business hours.

See Configuring Custom ETL from Fusion Analytics Warehouse.