Overview

The Fusion Data Intelligence (FDI) Data Validation feature is used to compare metrics between your FDI instance and your Oracle Transactional Business Intelligence (OTBI) instance, which is an authoritative data validation check. It ensures the user security setup in Oracle Fusion Cloud applications and FDI is at the same access level for functional validation checks.

Why Data Validation?

It’s necessary to ensure data integrity, completeness, and consistency between the source and the target for building confidence in users. Data validation testing ensures that data has been loaded correctly and accurately into FDI applications such as Oracle Fusion CX Analytics, Oracle Fusion SCM Analytics, Oracle Fusion ERP Analytics, and Oracle Fusion HCM Analytics.

Validating the FDI data with Oracle Fusion also ensures that the downstream systems relying and consuming a validated data set, will be accurate as well. The right type of data validation makes the data useful for an organization or for a specific application operation which facilitates useful analytics for a wide variety of applications. Data validation also ensures the consistency, accuracy, and completeness of the Fusion data, particularly if data is being moved, or migrated, between locations, external systems or if data from different sources is being merged.

Data validation ensures that the warehouse data is:

  • Accurate
  • Complete
  • Consistent
  • Valid
valid-data
valid-data

Prerequisites for Data Validation: Fusion Application

Data Validation User creation

  • Before logging into Oracle Fusion, make sure that you can access OTBI and run queries, and you have the same data security privileges in OTBI and Fusion Analytics.
  • Then create a new user to validate the extracted data by launching Navigator -> Tools -> Security Console -> Users -> Create User.
dv-user-creation
dv-user-creation

Data Validation Role Creation

Once the FDI Data Validation user is created in the Fusion environment, it has to be assigned to a custom data validation role. (The following steps can be performed on existing Fusion user as well).

  • To create the same, launch Navigator -> Tools -> Security Console -> Roles -> Create Role

Follow the four-step process:

Step 1

role-step1
role-step1

Step 2

role-step 2
role-step 2

Step 3

role-step 3
role-step 3

Step 4 – Save and close to save and finish the process.

role-step 4
role-step 4
  • Ensure that the validation user is assigned into the Integration Specialist group.
dv-group
dv-group
  • On the source credentials tab of the data validation page, provide the credentials of the applicable user and save your changes.
  • Ensure that the user who validates the data in FDI also exists in Oracle Fusion Cloud Applications.
credentials
credentials

Performing Data Validation

The library of subject areas and metrics available for validation are common between Scheduled and Detailed validation. FDI can validate data in the following two ways:

1. Detailed Validation – Detailed Validation can be done on-demand within the FDI interface.

  • Users must select the Subject Area, Metric and Column Set respectively and set the values to narrow down the final value to be validated.
  • Optional values are not mandatory to select, but to get more specific values, these are useful.
  • Finally selecting the required values, click Run to see the validation result.
detailed-validation
detailed-validation
  • Click Show Summary to show the detail data; it’s useful to debug if the values don’t match. Additional columns can be shown by clicking Change Columns.
detailed-validation-summary
detailed-validation-summary
  • Mismatched records can be exported in csv file as well by clicking Download Details.
detailed-validation-records
detailed-validation-records

 2. Scheduled Validation – Validation can be scheduled as well to meet your business needs. Scheduling can be done by weekly, monthly, or quarterly increments, and it runs after each successful pipeline runs. Using the FDI Scheduled Validation feature, you can create and generate automated reconciliation report for audit purposes as well.

  • Make sure that Run automated Data Validation Reports option is enabled to perform Scheduled Validation. There will be two options to create validation report.
  • Oracle validation sets have default validation report for each Fusion pillar. Selecting each pillar will result in a pop-up to select required Metrics to create the validation report.
  • Select the required Metrics and respective mandatory values and click Save to save the report. If multiple measures are selected, the Set Common Parameters button will be enabled to provide mandatory parameter values for both the metrics, its system generated.
scheduled-validation 1
scheduled-validation 1
scheduled-validation 2
scheduled-validation 2
  • However, users can create their own validation report as well with a custom Report Name selecting for a particular month, date.
scheduled-validation 3
scheduled-validation 3
  • Once the validation report is created, it will appear in the Show details for drop-down list to edit further, if required.
scheduled-validation 4
scheduled-validation 4
  • Based on the schedule, parameters, and validation set, Fusion Analytics will validate the data and store the details in the data validation workbook available in the OAC catalog within the Common folder.
    scheduled-validation-report
    scheduled-validation-report

Logging

After running each Data Validation request/report, Oracle generates report log in both OTBI and OAC environments. Source metric query session logs can be accessed from the OTBI Administration console and the Warehouse metric query session log can be accessed from the OAC Administration console (can be navigated from the Open Classic option in OAC).

Clicking the View Log link shows the logical and physical SQL, which is very useful to debug in case of validation mismatch. The Information Type is Soap for any data validation session log.

OTBI Logging (Source value):

otbi-logging
otbi-logging

OAC Logging (Warehouse value):

oac-logging
oac-logging

Key Considerations

  • Data refresh schedule – Ad hoc Data Validation metrics may mismatch during when there is pipeline run in progress.
  • Custom subject areas – Only prebuilt subject areas are in scope of Data validation, custom subject areas are out of the scope
  • Not all of the prebuilt subject area KPIs are available for data validation. Eg- Financials – AR Aging

Data Validation Known Issues

  • Unable to connect to source – Check Data Validation user credentials/relevant privileges and roles to run the report query and retrieve the data in both FDI and OTBI/SSO enabled.
  • Unexpected error in data validation – Check permission for the FDI/Fusion user, high data volume.
  • SOAP Server Error – Check the permissions for the user against OTBI subject areas, FDI activated functional areas.
  • Source Value field is populated as null – Check permissions for the user on the Fusion OTBI side to access the data being validated. The OTBI session log query is the best way to debug the same, removing additional filters.
  • Target Value field is populated as null – Check if the respective data pipelines are completed successfully.
  • Data mismatch issues – Check the prerequisites for ERP/HCM/SCM on Fusion side, timing issue of when the Data Validation is run, and the permissions of the user running the Data Validation, etc.

References

Call to Action

Once the FDI Functional Area pipelines are completed, as a FDI system administrator and functional administrator, the Data Validation feature can be used thereafter. For more information see the Validate Oracle Fusion Data Intelligence Data documentation.

The following summary video shows the Data Validation feature within FDI:

Now that you’ve read this article, try it yourself and let us know your queries/results in the Oracle Analytics Community, where you can also ask questions and post ideas. It’s free and easy to join.