Validation

• Validated on 11/20/2020 with :

  • ODI Version 12.2.1.4.201011

  • ADW Version Oracle Database 19c Enterprise Edition Release - Production Version 19.5.0.0.0

  • EDMCS Version 20.10.70

Introduction

In order to have a single source of truth for an organization’s data, a reference data solution is often required. This reference data once define can be used by operational systems as well as reporting and analytics system. A common analytics requirement is to integrate and ingest reference data into a data warehouse or a data mart.

The Oracle Cloud solution to support reference data is called Oracle Enterprise Data Management Cloud (EDMCS). EDMCS is the cloud offering from Oracle to replace Data Relationship Management (DRM) in order to support the following data use cases:

• Master data management
• Reference Data
• Hierarchy Data
• Dimension Data
• Mapping Data

According to the Gartner definition, “Master data is the consistent and uniform set of identifiers and extended attributes that describes the core entities of the enterprise including customers, prospects, citizens, suppliers, sites, hierarchies and chart of accounts.”

EDMCS offers a set of REST APIs to allow data extraction. The purpose of this blog is to detail the approach of extracting Dimensions Data from EDMCS and loading them into Oracle Autonomous Data Warehouse (ADW) using Oracle Data Integrator (ODI) in the Marketplace.

As of the writing of this blog, the ODI in the Marketplace Virtual Machine comes at no additional licensing cost: our customer only pay for the Compute instance fees.

Before you begin

Prerequisites

To achieve the goal of integrating EDMCS data into ADW, there is a couple of requirements to be met:

• Having access to an EDMCS instance with dimension data created
• Having access to an ODI on the Marketplace VM
• Having access to a ADW Database

EDMCS

The main features of EDMCS are :

• Collate enterprise data from contributing applications
• Curate changes to enterprise data elements
• Conform changes across different business perspectives
• Consume changes among downstream business applications

Figure 1: EDMCS Entity Maintenance screen

For more details on EDMCS, please visit : https://cloud.oracle.com/enterprise-data-management-cloud

EDMCS Rest API Interface

REST APIs in Oracle Enterprise Data Management Cloud Service use the RESTful architecture style and HTTP methods to create, update, read, or delete data. This architecture includes the following features.

• Stateless : The client maintains the session state. Each request from the client contains the information that the server requires to respond to the client.
• Consistent interface : Oracle REST API uses the following design features to make sure the resources are consistent:
  • Named URLs that identify each resource;
  • Resource representation that manipulates each object;
  • Each resource provides the metadata required to manipulate each object;
  • Each response contains hypermedia links.
• Hypermedia: Provides the Engine of Application State. Hypermedia uses hypermedia links that include responses that can facilitate dynamic navigation of interfaces.

Enterprise Data Management Cloud Service REST APIs support data interchange by using JavaScript Object Notation (JSON) format.

In our particular use case, we need to call a sequence of methods to achieve the following:

1. Retrieve the dimension information (ID) for the EDMCS application from which metadata will be exported.

GET <your EDMCS Server URL>/epm/rest/v1/applications

Example: GET https://edmcs.oraclecloud.com/epm/rest/v1/applications

2. Run an export of the dimension(s)

POST <your EDMCS Server URL>/epm/rest/v1/dimensions/<your dimension ID from the application metadata in step 1>/export/download

Example: POST https://edmcs.epm.us6.oraclecloud.com:443/epm/rest/v1/dimensions/5edr20ef-f708-4bc1-a571-6d90bcadb9bb/export/download

3. Verify and confirm the status of export

GET <your EDMCS Server URL>/epm/rest/v1/jobRuns/<the job ID generated as a response of step 2>

Example: GET https://edmcs.oraclecloud.com:443/epm/rest/v1/jobRuns/4aec033t-3610-4f1d-af52-5cfd5ae58ff8

4. Finally download the export to a flat file (csv in our case)

GET <your EDMCS Server URL>/epm/rest/v1/files/temp/<job ID from step 2>?fileName=<your file name>

Example: GET https://edmcs.oraclecloud.com:443/epm/rest/v1/files/temp/4aec033p-3610-4f1d-af52-5cfd5ae58ff8?fileName=dimension.csv

For more information about EDMCS Rest API, please visit the following links: https://docs.oracle.com/en/cloud/saas/enterprise-data-management-cloud/edmra/edmcs_url_structure_rest_api_resource.html

https://docs.oracle.com/en/cloud/saas/enterprise-data-management-cloud/edmra/op-v1-dimensions-dimensionid-export-download-post.html

Using Java BeanShell Scripts to Call EDMCS Rest API

ODI supports RESTful services invocation for reading or writing data. The RESTful endpoints can be configured like any other data server in topology and can be easily invoked through an ODI tool in the integration flow. In order to allow for more flexibility in the REST calls we will be executing, the approach of Java BeanShell scripts is preferred in this blog:

Using JQ to parse JSON files

As mentioned earlier, EDMCS REST API calls will produce JSON response that we will store as ASCII file in the context of this blog. JQ is a great and performant utility to parse JSON that we will be using here.

To install jq, execute the following steps on a Unix/Linux environment:

wget -O jq https://github.com/stedolan/jq/releases/download/jq-1.6/jq-linux64

chmod +x ./jq

cp jq /usr/bin

For more information about JQ, please visit:

https://stedolan.github.io/jq/download/

https://stedolan.github.io/jq/manual/#TypesandValues

Looping through the EDMCS dimensions and loading into Oracle ADW

In order to integrate all the available dimensions in EDMCS, we can implement a loop within ODI to extract each of the dimensions one by one and load the individual response csv files into their corresponding ADW table.

Please refer to Christophe’s blog for more details on loop implementation within ODI.

https://blogs.oracle.com/dataintegration/using-variables-in-odi:-creating-a-loop-in-a-package

Integrating EDMCS Dimensions

This section will describe the ODI Artefacts built in order to integrate EDMCS data into ADW.

ODI Topology

The ODI Topology is composed of two main technology : Files and Oracle ADW

• Files

Figure 2: Project File Topology

• Oracle

Figure 3: Project ADW Topology 

ODI Models

Reverse engineered from the two data server above are two main models

Figure 4: ODI Models for File and ADW data servers

ODI Projects Artefacts

Variables

To implement the loop through the different dimensions, the following variables are used :

Figure 5: Project variables used to implement the loop

Packages

We are using five central packages here:

1. PKG_EDMCS_00_Main: the package that embeds the main logic to call the rest APIs.
2. PKG_EDMCS_01_GenerateDimList that generates the dimension list from the response of the first REST API call . It is being called by the main package.
3. PKG_EDMCS_04_DownloadIngestAllDimensions: that implements the loop to download all dimensions from EDMCS into csv files and then ingest the csv files into ADW.
4. PKG_EDMCS_02_DownloadDimension: that download a dimension form EDMCS into a CSV file. It is called as a start scenario in the loop from package PKG_EDMCS_04_DownloadIngestAllDimensions.
5. PKG_EDMCS_03_IngestDimensionFileToWarehouse: that loads the dimension CSV file into an ADW table. It is also called as a start scenario in the loop from package PKG_EDMCS_04_DownloadIngestAllDimensions

PKG_EDMCS_00_Main

This package has four steps:

1. Refresh the variable storing the local file path where all files are generated
2. Refresh the variable storing the OCI host name, used as the base for our web service call (the <your EDMCS Server url> in the example above)
3. Generate the dimension list from the response of the first REST API call (through an ODI scenario start of package PKG_EDMCS_01_GenerateDimList ).

GET <your EDMCS Server URL>/epm/rest/v1/applications

Example: GET https://edmcs.oraclecloud.com/epm/rest/v1/applications

Figure 6: Main Package - Generate Dimension CSV step

4. Execute All dimension download and ingestion that starts the scenario of the package PKG_EDMCS_04_DownloadIngestAllDimensions.

Figure 7: Main Package - Generate Execute All Dimensions Download and Ingestion step

PKG_EDMCS_01_GenerateDimList

This package executes the following steps:

1. Declares variables
2. Clean up local files.
3. Connects to the EDMCS REST API to get the application definition with the generateApplicationJSON procedure call

Figure 8: GenerateApplicationJson Procedure step

The pseudo code below details the approach taken by the Java BeanShell target command call

• Declare all imports
  • java.io.* ….
• Declare variables
  • urlStrRestCall1, restCall1JSONFilename, restCall1OutFilename
• Define a function to get the current time
  • public static String getCurrentTime () {…}
• Define a function to write a string to file
  • public static void writeToFile(String fileName, String str) throws IOException {…}
• Define the main function that generates the Application definition JSON file
  • public static void generateApplicationJSON(String urlStr, String jsonFileName, String outFileName) {
    • Creating HTTPURLconnection and setting request properties (authentication, timeout,...)
    • Connect and get response code
    • If the response code is not 200 (success), throw a runtime exception
    • Else
      • Read the response input stream
      • Write the response to a file, line by line
    • Disconnect
• Call generateApplicationJSON(urlStrRestCall1, restCall1JSONFilename, restCall1OutFilename);

4. Read the dimension ID/names pair from the Application detail json and load them into a csv file with the Generate Dimension list OS command, using jq to parse the json response from the previous procedure call.

Figure 9: Generate Dimension List step

rm -f #04___ODI_AND_RESTAPI.EDMCS_FILES_LOCAL_PATH/csv/dimensions_list.csv

cat #04___ODI_AND_RESTAPI.EDMCS_FILES_LOCAL_PATH/json/restCall_1.json | jq '.items[] | select(.name == "Data Warehouse")' | jq '.dimensions[] | select(.name)' | jq '.name + "," + .id' > #04___ODI_AND_RESTAPI.EDMCS_FILES_LOCAL_PATH/csv/dimensions_list.csv

sed -i 's/,/","/g' #04___ODI_AND_RESTAPI.EDMCS_FILES_LOCAL_PATH/csv/dimensions_list.csv

5. Load the CSV file into the EDMCS_DIMENSIONS table through an ODI mapping to allow looping through dimensions in the next package.

Figure 10: Load Dimension List Mapping  step

PKG_EDMCS_04_DownloadIngestAllDimensions

This package executes the following steps:

Figure 11: the main loop

1. Declare variables
2. Refresh the DIMENSION_COUNT variable
3. Set the DIMENSION_COUNTER to 1
4. Get the current dimension name DIMENSION_NM
5. Refresh the DIMENSION_ID based on the DIMENSION_NM variable
6. Start the download dimension scenario (PKG_EDMCS_02_DownloadDimension) with the DIMENSION_NM passed variable.
7. Start the ingest dimension scenario () with the DIMENSION_NM passed variable
8. Looping through the dimension list (comparing the values of the DIMENSION_COUNTER and the DIMENSION_COUNT and Increment the DIMENSION_COUNTER)

PKG_EDMCS_02_DownloadDimension

This package executes the following steps:

Figure 12: the Download Dimension Package

1. Declare variables
2. Refresh the DIMENSION_NM and DIM_FILE_NAME variables
3. Clean up local files
4. Execute the postDimIDGenerateJobURL procedure.

Figure 13: postDimIDGenerateJobURL Procedure Step

The pseudo code in the approach taken by the Java BeanShell target command call is similar to the one in the previous step with the appropriate REST Call

5. Get Job Link by parsing the JSON response file generated by the prior step using JQ

rm -f #04___ODI_AND_RESTAPI.EDMCS_FILES_LOCAL_PATH/csv/joblink.csv

cat #04___ODI_AND_RESTAPI.EDMCS_FILES_LOCAL_PATH/json/restCall_2.json | jq '.links[].href' > #04___ODI_AND_RESTAPI.EDMCS_FILES_LOCAL_PATH/csv/joblink.csv

6. Execute the RunDimensionDownloadJob ODI procedure

Figure 14: RunDimensionDownloadJob Procedure Step

The pseudo code in the approach taken by the Java BeanShell target command call is similar to the one in the previous step with the appropriate REST Call

7. Get Dimension Download Link by parsing the JSON response file from the previous REST API call

cat #04___ODI_AND_RESTAPI.EDMCS_FILES_LOCAL_PATH/json/restCall_3.json | jq '.links[] | select(.rel == "results")' | jq '.href' > #04___ODI_AND_RESTAPI.EDMCS_FILES_LOCAL_PATH/csv/downloadlink.csv

8. Execute the downloadDimension ODI procedure

Figure 15: downloadDimension Procedure Step

The pseudo code in the approach taken by the Java BeanShell target command call is similar to the one in the previous step with the appropriate REST Call

PKG_EDMCS_03_IngestDimensionFileToWarehouse

This package executes the following steps:

1. Declare the Dimension ID
2. Refresh the Dimension Name
3. Based on the value of the Dimension Name, execute the correct interface to load the dimension CSV file into its corresponding ADW table

Figure 16: The Ingest Dimension package and Mapping example

Summary

Through the course of this blog, we learned how to extract dimension data from EDMCS and load this data into the Oracle Autonomous Data Warehouse using ODI on the Marketplace.

For other A-Team articles by Matthieu, click here

Reference

For more details on EDMCS, please visit : https://cloud.oracle.com/enterprise-data-management-cloud

For more information about EDMCS Rest API, please visit the following links: https://docs.oracle.com/en/cloud/saas/enterprise-data-management-cloud/edmra/edmcs_url_structure_rest_api_resource.html

https://docs.oracle.com/en/cloud/saas/enterprise-data-management-cloud/edmra/op-v1-dimensions-dimensionid-export-download-post.html

For more information about JQ, please visit:

https://stedolan.github.io/jq/download/

https://stedolan.github.io/jq/manual/#TypesandValues

Please refer to Christophe’s blog for more details on loop implementation within ODI.

https://blogs.oracle.com/dataintegration/using-variables-in-odi:-creating-a-loop-in-a-package