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:
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.
To achieve the goal of integrating EDMCS data into ADW, there is a couple of requirements to be met:
The main features of EDMCS are :
Figure 1: EDMCS Entity Maintenance screen
For more details on EDMCS, please visit : https://cloud.oracle.com/enterprise-data-management-cloud
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:
GET <your EDMCS Server URL>/epm/rest/v1/applications
Example: GET https://edmcs.oraclecloud.com/epm/rest/v1/applications
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
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
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
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
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
Figure 2: Project File Topology
Figure 3: Project ADW Topology
Figure 4: ODI Models for File and ADW data servers
To implement the loop through the different dimensions, the following variables are used :
Figure 5: Project variables used to implement the loop
We are using five central packages here:
This package has four steps:
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
Figure 7: Main Package - Generate Execute All Dimensions Download and Ingestion step
This package executes the following steps:
Figure 8: GenerateApplicationJson Procedure step
The pseudo code below details the approach taken by the Java BeanShell target command 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
Figure 10: Load Dimension List Mapping step
This package executes the following steps:
Figure 11: the main loop
This package executes the following steps:
Figure 12: the Download Dimension Package
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
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
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
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
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
This package executes the following steps:
Figure 16: The Ingest Dimension package and Mapping example
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
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
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
The Oracle A-Team is a central, outbound, highly technical team of enterprise architects, solution specialists, and software engineers.
The Oracle A-Team works with external customers and Oracle partners around the globe to provide guidance on implementation best practices, architecture design reviews, troubleshooting, and how to use Oracle products to solve customer business challenges.
I focus on data integration, data warehousing, Big Data, cloud services, and analytics (BI) products. My role included acting as the subject-matter expert on Oracle Data Integration and Analytics products and cloud services such as Oracle Data Integrator (ODI), and Oracle Analytics Cloud (OAC, OA For Fusion Apps, OAX).
Previous Post
Next Post