Oracle Cloud Infrastructure Documentation

About Augmenting Your Data

Enhance the data used in your analytics with additional data, various calculations, and combinations to enable comprehensive analytics and multi-faceted visualizations. By augmenting the data, you can reduce or even eliminate the manual intervention in developing meaningful insight of the business data.

Tutorial icon LiveLabs Sprint

Data augmentation enables you to augment the data you bring from Oracle Fusion Cloud Applications and other sources that you can connect to using the Oracle Fusion Analytics Warehouse connectors. See the Connectors section in Features Available for Preview. You can add data to your reports from various data stores (Business Intelligence view objects) of the Oracle Fusion Cloud Applications data sources. You can augment your reports with datasets created by extending an existing entity or group of facts, by adding a new dimension in the target instance, and by adding a new fact in the target instance.

Select the columns from data stores, create an augmentation dataset, and use that dataset to create data pipelines for functional areas. Using an augmentation dataset enables you to seamlessly extract and load data from additional Oracle Fusion Cloud Applications data stores and make the data available to tables in the data warehouse. You can then use the data for visualization and analysis. To find the data stores that are available for extraction using augmentation, see the Data Stores section in Reference for Oracle Fusion SCM Analytics, Reference for Oracle Fusion HCM Analytics, and Reference for Oracle Fusion ERP Analytics. Although there is no technical limit, you can create a maximum of hundred data augmentations for a single tenant to ensure optimal performance of all data pipelines. Contact Oracle Support if you have further questions.

Here are a few use cases when augmenting your Oracle Fusion Cloud Applications data with data from several data stores enables in-depth and focused insights:
  • Product sales – Add similar product information from different data sources to create a report that compares similar products in a specific region.
  • Average of expense invoices – Add various expense invoices to create an average of periodic expenses.

Augment Your Data

You can supplement the data in your reports by using datasets that you create with specific columns from various data stores (Business Intelligence view objects) of the Oracle Fusion Cloud Applications data sources.

Refer to Review Database Lineage Mappings and View Objects while deciding which view objects to use in the data augmentations.

While creating a data augmentation, you can select these:
  • Augmentation Type: When you want to create a data augmentation on the data loaded from a connector or from the Oracle Fusion Cloud Applications source, you need to create a dimension with a column identified as "primary key" and then join this dimension table with a fact table where the same column is assigned the Dimension attribute, so that column is your join key. In this drop-down list, you can select the appropriate step.
  • Source Dataset Type: For a dataset that doesn't require any transform, select Supplemental Data. If transform is required, then select the Transformation option.
  • Pillar: This option is available if your source is Oracle Fusion Cloud Applications. Select the applicable pillar as the data source. For sources that have only one pillar or don't have any pillars, this option isn't visible.
  • Source Table Type: You can use the system provided or customer provided source tables. The system provided tables are pre-validated by Oracle Fusion Analytics Warehouse. The customer provided tables are other source tables that are available for extraction but aren’t validated by Oracle Fusion Analytics Warehouse. As a user with the functional administrator or system administrator application role, you can allow usage of a particular table that isn’t pre-validated by Oracle Fusion Analytics Warehouse. However, Oracle can't ensure the success of processing such custom tables or any performance impacts, such as delays in the daily refreshing of data.For the remote agent sources like on-premises E-Business Suite, PeopleSoft, and JD Edwards, use the system provided source tables option. The extract service can’t connect to these remote sources directly to fetch the column list for the customer provided table.
  • Source Table: You can provide a single table name or a comma separated list of source table names in this field.

While creating a data augmentation, you can change the size of a column. However, you must ensure that the maximum size is within the allowed permission limit for a given datatype in the target database. To determine the maximum permissible size for each data type, see the "Oracle Built-in Data Types" section in the Oracle database documentation. Currently, the allowed datatypes in data augmentation are DATE, NUMBER, TIMESTAMP, and VARCHAR2.

After you create the augmentations, you see them on the Data Augmentation page with one of these statuses:
  • Activation in Progress - You can’t edit, delete, or schedule a data augmentation pipeline job while activation is in progress.
  • Activation Completed - You can edit the data augmentation to add or delete attributes of the view objects and save the changes. You can’t modify the schedule in this status.
  • Activation Scheduled - You can edit the data augmentation to add attributes of the view objects, save the changes while retaining the existing schedule, reschedule the execution date and time, or execute the plan immediately.
Note

During the activation process, if the number of invalid records is substantial, then Oracle Fusion Analytics Warehouse rejects the data augmentation. You can view the DW_ERR_RECORDS table to understand why the input data has been rejected.

You can change the names of the columns that you’ve added from the various data sources in your data augmentation. Later if you delete a data augmentation, then you must wait for the daily incremental run to complete to see the change in the reports, visualizations, and workbooks.

When you edit an augmentation, Oracle Fusion Analytics Warehouse submits a request to refresh the data immediately. During this time, you can't perform another edit action. You must ensure not to modify the definition of the data augmentation pipeline job while the job is in process. In case you need to modify the job definition while in process, then you must resubmit the data augmentation pipeline job.

Recommended practices:
  • Don’t name two data augmentations the same to avoid failure.
  • Specify incremental keys to ensure daily refresh.
  • Ensure that the concatenation of the Primary Key columns doesn’t exceed the maximum length of 8192 to avoid failure of your data augmentation.
  • Don't specify a subject area if you've a complex semantic model; not specifying results in extract, transfer, and load only.
  • Data augmentations have lower priority than the ready-to-use pipelines and may get rejected if they overlap during the scheduled pipeline incremental runs.
  • If using frequent data refresh for specific datasets, then don't schedule data augmentations on the same sources or targets. For example, don't run Extend Entity for an invoice, while frequent data refresh for invoice is on.
  • Use data augmentations for specific, targeted extracts. For larger scale or complex projects, consider custom ETL.
  1. Sign in to your service.
  2. In Oracle Fusion Analytics Warehouse Console, click Data Configuration under Application Administration.
  3. On the Data Configuration page, under Configurations, click Data Augmentation.

    Description of fawag_data_augmentation_tile.png follows

  4. On the Data Augmentation page, click Create.

    Description of fawag_create_data_augmentation.png follows

    You can create the following augmentation types:

Create Dimension Augmentation Type

You can augment your reports with datasets created by adding a new dimension in the target instance.

Ensure that the custom dimensions that you create in augmentations are used by facts. If they aren’t used, then the custom dimensions aren’t visible in the subject areas. See Create Fact Augmentation Type.

You must ensure that any column with primary key doesn’t have null values, otherwise the extract process rejects the entire dataset or table. If there are multiple columns with primary keys, then you must ensure that none of those columns have null values. If any of them have null values, then Oracle Fusion Analytics Warehouse rejects the entire extraction job. If Oracle Fusion Analytics Warehouse rejects the extraction job, then the corresponding augmentation is also rejected.

  1. In step 1 of the Data Augmentation wizard, select Create Dimension in Augmentation Type to add a new dimension in the target instance.

    Description of fawag_create_dimension_da.png follows

  2. Select Supplemental Data (Regular) in Source Dataset Type.
  3. In Pillar, select a product pillar; for example, Enterprise Resource Planning.
  4. In Source Table Type, specify the source table type using either of the options and then click Next:
    • Select System Provided and then in Source Table, select a table to which you want to add the new dimension.
    • Select Customer Provided and then in Source Table, enter the name of the table to which you want to add the new dimension.
  5. In step 2 of the wizard, select the check box for the attributes from the source table that you want in your new dimension, and then click Next.
  6. In step 3 of the wizard, click Action icon for each of the selected attributes to specify the Type and Treat as settings, and then click Next.
  7. In step 6 of the wizard, provide the following details and click Finish to save and schedule your data augmentation pipeline job:
    1. Name your augmentation pipeline job; for example, Customer Class Code.
    2. Enter a suffix for the target table name using underscore in place of spaces between words and don’t use special characters; for example, Customer_Class_D. The augmentation process automatically creates the target table name.
    3. Provide a description.
    4. Select the functional area and one or multiple subject areas in which you want to include this augmentation pipeline job.
    5. Specify the options to save the data augmentation pipeline job without executing it, or schedule the execution date and time, or execute it immediately.

Create Fact Augmentation Type

You can augment your reports with datasets created by adding a new fact in the target instance.

If you've created custom dimensions for augmentations, then you can select such dimensions to map to the column that you identify as the Dimension entity type. This enables the custom dimensions to be visible in the subject areas.

You must ensure that any column with primary key doesn’t have null values, otherwise the extract process rejects the entire dataset or table. If there are multiple columns with primary keys, then you must ensure that none of those columns have null values. If any of them have null values, then Oracle Fusion Analytics Warehouse rejects the entire extraction job. If Oracle Fusion Analytics Warehouse rejects the extraction job, then the corresponding augmentation is also rejected.

  1. In step 1 of the Data Augmentation wizard, select Create Fact in Augmentation Type to add a new fact table in the target instance.

    Description of fawag_create_fact_da.png follows

  2. Select Supplemental Data (Regular) in Source Dataset Type.
  3. In Pillar, select a product pillar; for example, Enterprise Resource Planning.
  4. In Source Table Type, specify the source table type using either of the options and click Next:
    • Select System Provided and then in Source Table, select a table to which you want to add the new dimension.
    • Select Customer Provided and then in Source Table, enter the name of the table to which you want to add the new dimension.
  5. In step 2 of the wizard, select the check box for the attributes from the source table that you want in your new fact, and then click Next.
  6. In step 3 of the wizard, click Action icon for each of the selected attributes to specify the Type and Treat as settings and then click Next.
    Ensure that you select at least one attribute as a measure to proceed through the wizard.
  7. In step 5 of the wizard, specify the dimension in the data warehouse that you want to map to the column that you identified as the Dimension entity type and then click Next.
  8. In step 6 of the wizard, provide the following details and click Finish to save and schedule your data augmentation pipeline job:
    1. Name your augmentation pipeline job; for example, AP Distribution.
    2. Enter a suffix for the target table name using underscore in place of spaces between words and don’t use special characters; for example, AP_DISTRIBUTION_F. The augmentation process automatically creates the target table name.
    3. Provide a description.
    4. Select the functional area and one or multiple subject areas in which you want to include this augmentation pipeline job.
    5. Specify the options to save the data augmentation pipeline job without executing it, or schedule the execution date and time, or execute it immediately.

Extend an Entity

You can augment your reports with datasets created by extending an existing entity or group of facts.

While extending an entity or group of facts, ensure that you select Descriptive Flex Field (New) as the source dataset type to select the necessary columns for the augmentation. The existing approach of skipping the column selection is deprecated and won't be available from a future release.

You must ensure that any column with a primary key doesn’t have null values, otherwise the extract process rejects the entire dataset or table. If there are multiple columns with primary keys, then you must ensure that none of those columns have null values. If any of them have null values, then Oracle Fusion Analytics Warehouse rejects the entire extraction job. If Oracle Fusion Analytics Warehouse rejects the extraction job, then the corresponding augmentation is also rejected.

  1. In step 1 of the Data Augmentation wizard, select Extend Entity in Augmentation Type.

    Description of fawag_extend_entity_da.png follows

  2. Select Descriptive Flex Field (New) in Source Dataset Type.
  3. In Pillar, select a product pillar; for example, Enterprise Resource Planning.
  4. In Source Table Type, specify the source table type using either of the options:
    • Select System Provided and then in Source Table, select a table to which you want to add the new dimension.
    • Select Customer Provided and then in Source Table, enter the name of the table to which you want to add the new dimension.
  5. Select a source table from the list of view objects that support descriptive flex fields and then click Next.
  6. In step 2 of the wizard, select the check box for the attributes from the source table that you want in your target table, and then click Next.
  7. In step 3 of the wizard, click the Action icon for each of the selected attributes to specify the Type and Treat as settings, and then click Next.
  8. In step 4 of the wizard, select the entity or group of fact tables to extend and its primary keys, and then click Next. For example, if you select ARTransaction as the entity to extend, then this process joins the ready-to-use InvoiceID descriptive flex field using the s_k_5000 primary join key with all the fact tables in the ARTransaction entity.
  9. In step 5 of the wizard, choose the primary keys for the attributes that you had specified to be treated as dimensions.
  10. In step 6 of the wizard, provide the following details and click Finish to save and schedule your data augmentation pipeline job:
    1. Name your augmentation pipeline job; for example, AP Invoice Header.
    2. Enter a suffix for the target table name using underscore in place of spaces between words and don’t use special characters; for example, AP_Invoice_Header_DFF. The augmentation process automatically creates the target table name.
    3. Provide a description.
    4. Select the functional area and one or multiple subject areas in which you want to include this augmentation pipeline job.
    5. Specify the options to save the data augmentation pipeline job without executing it, or schedule the execution date and time, or execute it immediately.

Create Input Dataset Augmentation Type

You may require a dataset to be copied into a target warehouse table, as is, and then perform semantic model extension on it. In such cases, create an input dataset.

This dataset isn’t associated with any other augmentations. Based on the incremental schedule, the data in this dataset gets refreshed during scheduled pipeline refresh. But unlike other augmentations, this augmentation isn’t linked to other augmentations, and you can’t change the attributes as dimension or measure. This dataset isn’t associated with any subject area, since it is simply copying the dataset from source and creating a warehouse table. You can perform semantic model extension after the table is created.

  1. In step 1 of the Data Augmentation wizard, select Create Input Dataset in Augmentation Type to add a new warehouse table.

    Create Input Dataset augmentation type wizard

  2. Select Supplemental Data in Source Dataset Type.
  3. In Pillar, select a product pillar; for example, Enterprise Resource Planning.
  4. In Source Table Type, specify the source table type using either of the options and then click Next:
    • Select System Provided and then in Source Table, select a table whose attributes you want to add into the input dataset.
    • Select Customer Provided and then in Source Table, enter the name of the table whose attributes you want to add into the input dataset
  5. In step 2 of the wizard, select the check box for the attributes from the source table to add to the target table, and then click Next.
  6. In step 3 of the wizard, select the settings for the selected columns, and then click Next.
  7. In step 6 of the wizard, provide the following details and click Finish to save and schedule your data augmentation pipeline job:
    1. Provide a name and description for your augmentation.
    2. Enter a suffix for the target table name using underscore in place of spaces between words and don’t use special characters; for example, Customer_Class_D. The augmentation process automatically creates the target table name.
    3. Specify the options to save the data augmentation pipeline job without executing it, or schedule the execution date and time, or execute it immediately.