X

Best Practices from Oracle Development's A‑Team

Integrating Oracle Enterprise Data Management Cloud Services Dimension Data Into Oracle Autonomous Data Warehouse With REST API and ODI on the Marketplace

Matthieu Lombard
Consulting Solution Architect

Written in collaboration with Anil Menon, Consulting Technical Manager at Oracle.

 

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

  1. 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/5edc20ef-f708-4bc1-a571-6d90bcadb9bb/export/download

  1. 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/4aec033a-3610-4f1d-af52-5cfd5ae58ff8

  1. 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/4aec033a-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

  1. 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 code below is the comprehensive Java BeanShell target command call

 

import java.io.BufferedReader;

import java.io.BufferedWriter;

import java.io.IOException;

import java.io.InputStreamReader;

import java.net.HttpURLConnection;

import java.net.MalformedURLException;

import java.net.URL;

import org.json.simple.JSONArray;

import org.json.simple.JSONObject;

import org.json.simple.parser.JSONParser;

import org.json.simple.parser.ParseException;

import java.io.FileReader;

import java.io.Reader;

import java.util.Iterator;

import java.time.format.DateTimeFormatter;

import java.time.LocalDateTime;

 

String ociHostName= "#04___ODI_AND_RESTAPI.EDMCS_OCI_HOSTNAME"; //ODI Variable used to store the OCI Host Name

String urlStrRestCall1 = ociHostName + "/epm/rest/v1/applications";

String ociHostPath = "#04___ODI_AND_RESTAPI.EDMCS_FILES_LOCAL_PATH";//"/u02/odirest/";

String restCall1JSONFilename = ociHostPath + "/json/restCall_1.json";

String restCall1OutFilename = ociHostPath + "/out/restCall_1.out";

 

public static String getCurrentTime () {

  DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss");

  LocalDateTime now = LocalDateTime.now();

  String currTime = dtf.format(now);

  return currTime;

 

}

 

public static void writeToFile(String fileName, String str)

  throws IOException {

 

    BufferedWriter writer = new BufferedWriter(new FileWriter(fileName, true));

    writer.append(' ');

    writer.append(str + "\r\n");

    writer.close();

 

}

 

public static void generateApplicationJSON(String urlStr, String jsonFileName, String outFileName) {

 

         try {

 

              writeToFile(outFileName, getCurrentTime() + " Creating connection and setting request properties ..." );

                URL url = new URL(urlStr);

              HttpURLConnection conn = (HttpURLConnection) url.openConnection();

    String user = "ekps.gapmdm"; // username

    String pass = "Serv!c8Acc7"; // password or API token

    String authStr = user + ":" + pass;

    String encoding = Base64.getEncoder().encodeToString(authStr.getBytes("utf-8"));

    writeToFile(outFileName, getCurrentTime() + " Authorization : Basic " + encoding);

 

              conn.setRequestMethod("GET");

              conn.setRequestProperty("Accept", "application/json");

    conn.setRequestProperty("Authorization", "Basic " + encoding);

    writeToFile(outFileName, getCurrentTime() + " Connecting ..." );

    int respCode = conn.getResponseCode();

    writeToFile(outFileName, getCurrentTime() + " Response Code : " + respCode);

 

              if (respCode != 200) {

                      throw new RuntimeException("Failed : HTTP error code : "

                                    + conn.getResponseCode());

              }

 

              BufferedReader br = new BufferedReader(new InputStreamReader(

                      (conn.getInputStream())));

 

              String output;

    writeToFile(outFileName, getCurrentTime() + " Output from Server .... " );

              while ((output = br.readLine()) != null) {

      writeToFile(jsonFileName,output);

      writeToFile(outFileName, getCurrentTime() + " Output from Server written to file :  " + jsonFileName );

              }

 

              conn.disconnect();

    writeToFile(outFileName, getCurrentTime() + " Disconnected! " );

 

         } catch (MalformedURLException e) {

 

              e.printStackTrace();

 

         } catch (IOException e) {

 

              e.printStackTrace();

 

         }

 

       }

 

writeToFile(restCall1OutFilename, "\n " + getCurrentTime() + " Variable ociHostName : " + ociHostName);

writeToFile(restCall1OutFilename, getCurrentTime() + " Variable urlStrRestCall1 : " + urlStrRestCall1);

writeToFile(restCall1OutFilename, getCurrentTime() + " Variable ociHostPath : " + ociHostPath);

writeToFile(restCall1OutFilename, getCurrentTime() + " Variable restCall1JSONFilename : " + restCall1JSONFilename);

writeToFile(restCall1OutFilename, getCurrentTime() + " Variable restCall1OutFilename : " + restCall1OutFilename);

 

generateApplicationJSON(urlStrRestCall1, restCall1JSONFilename, restCall1OutFilename);

 

  1. 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

 

  1. 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 code below is the comprehensive Java BeanShell target command call

 

import javax.net.ssl.HttpsURLConnection;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import java.io.BufferedReader;

import java.io.BufferedWriter;

import java.io.IOException;

import java.io.InputStream;

import java.io.InputStreamReader;

import java.io.DataOutputStream;

import java.io.FileReader;

import java.io.Reader;

import java.net.*;

import java.nio.charset.StandardCharsets;

import java.nio.file.CopyOption;

import java.nio.file.Files;

import java.nio.file.Paths;

import java.nio.file.StandardCopyOption;

import java.lang.StringBuilder;

import java.net.HttpURLConnection;

import java.net.MalformedURLException;

import java.net.URL;

import java.util.Base64;

import java.util.Iterator;

import java.time.format.DateTimeFormatter;

import java.time.LocalDateTime;

 

String ociHostName= "#04___ODI_AND_RESTAPI.EDMCS_OCI_HOSTNAME"; //ODI Variable used to store the OCI Host Name

String urlStrRestCall2WithoutDimID = ociHostName + ":443/epm/rest/v1/dimensions/";

String urlStrRestCall2WithDimID ;

String ociHostPath = "#04___ODI_AND_RESTAPI.EDMCS_FILES_LOCAL_PATH" ;//"/u02/odirest/";

String restCall2JSONFilename = ociHostPath + "/json/restCall_2.json";

String dimFileName = ociHostPath + "#04___ODI_AND_RESTAPI.EDMCS_DIMENSION_ID" + "_" + "#04___ODI_AND_RESTAPI.EDMCS_DIMENSION_NM" + ".csv";

String restCall2OutFilename = ociHostPath + "/out/restCall_2.out";

String dimensionID = "#04___ODI_AND_RESTAPI.EDMCS_DIMENSION_ID";

 

public static String getCurrentTime () {

  DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss");

  LocalDateTime now = LocalDateTime.now();

  String currTime = dtf.format(now);

  return currTime;

}

 

public static void writeToFile(String fileName, String str)

  throws IOException {

 

    BufferedWriter writer = new BufferedWriter(new FileWriter(fileName, true));

    writer.append(' ');

    writer.append(str + "\r\n");

    writer.close();

}

 

public static void postDimIDGenerateJobURL(String urlStr, String jsonFileName, String outFileName) {

 

  writeToFile(outFileName, getCurrentTime() + " Creating connection and setting request properties ..." );

  HttpsURLConnection connection;

  //try {

  String user = "ekps.gapmdm"; // username

  String pass = "Serv!c8Acc7"; // password or API token

  String authStr = user + ":" + pass;

  String encoding = Base64.getEncoder().encodeToString(authStr.getBytes(StandardCharsets.UTF_8));

  writeToFile(outFileName, getCurrentTime() + " Authorization : Basic " + encoding);

 

  URL url = new URL(urlStr);

  connection = (HttpsURLConnection) url.openConnection();

  connection.setDoOutput(true);

  connection.setDoInput(true);

  connection.addRequestProperty("Content-Type", "application/json");

  connection.setRequestProperty("Authorization", "Basic " + encoding);

  connection.setConnectTimeout(1000000);

  connection.setReadTimeout(1000000);

 

  connection.connect();

  writeToFile(outFileName, getCurrentTime() + " Connection Created !" );

 

  OutputStream os = connection.getOutputStream();

  os.write("{\"fileName\": \"dimension.csv\"}".getBytes(StandardCharsets.UTF_8));

  os.flush();

 

  int respCode = connection.getResponseCode();

  writeToFile(outFileName, getCurrentTime() + " Response code : " + respCode);

 

  writeToFile(outFileName,getCurrentTime() + " Reading response ...");

  BufferedReader br = new BufferedReader(new InputStreamReader(connection.getInputStream(), StandardCharsets.UTF_8));

  StringBuilder response = new StringBuilder();

  String responseLine = null;

  while ((responseLine = br.readLine()) != null) {

    response.append(responseLine.trim());

  }

  writeToFile(outFileName,getCurrentTime() + " Writting response to file ...");

  writeToFile(jsonFileName,response.toString());

  br.close();

  writeToFile(outFileName, getCurrentTime() + " JSON reponse written to : " + jsonFileName);

 

  connection.disconnect();

  writeToFile(outFileName, getCurrentTime() + " Disconnected! " );

 

}

 

 

writeToFile(restCall2OutFilename, "\n " + getCurrentTime() + " Variable ociHostName : " + ociHostName);

writeToFile(restCall2OutFilename,  getCurrentTime() + " Variable urlStrRestCall2WithoutDimID : " + urlStrRestCall2WithoutDimID);

writeToFile(restCall2OutFilename,  getCurrentTime() + " Variable urlStrRestCall2WithDimID : " + urlStrRestCall2WithDimID);

writeToFile(restCall2OutFilename,  getCurrentTime() + " Variable ociHostPath : " + ociHostPath);

writeToFile(restCall2OutFilename,  getCurrentTime() + " Variable restCall2JSONFilename : " + restCall2JSONFilename);

writeToFile(restCall2OutFilename,  getCurrentTime() + " Variable restCall2OutFilename : " + restCall2OutFilename);

writeToFile(restCall2OutFilename,  getCurrentTime() + " Variable dimFileName : " + dimFileName);

writeToFile(restCall2OutFilename,  getCurrentTime() + " Variable dimensionID : " + dimensionID);

 

urlStrRestCall2WithDimID = urlStrRestCall2WithoutDimID + dimensionID + "/export/download";

//urlStrRestCall2WithDimID = "https://edmcs-test-ekps.epm.us6.oraclecloud.com:433/epm/rest/v1/dimensions/5edc20ef-f708-4bc1-a571-6d90bcadb9bb/export/download";

writeToFile(restCall2OutFilename,  getCurrentTime() + " POST constructed URL : = " + urlStrRestCall2WithDimID);

postDimIDGenerateJobURL(urlStrRestCall2WithDimID, restCall2JSONFilename, restCall2OutFilename);

  1. 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

  1. Execute the RunDimensionDownloadJob ODI procedure

 

Figure 14: RunDimensionDownloadJob Procedure Step

The code below is the comprehensive Java BeanShell target command call

import java.io.BufferedReader;

import java.io.BufferedWriter;

import java.io.IOException;

import java.io.InputStreamReader;

import java.net.HttpURLConnection;

import java.net.MalformedURLException;

import java.net.URL;

import org.json.simple.JSONArray;

import org.json.simple.JSONObject;

import org.json.simple.parser.JSONParser;

import org.json.simple.parser.ParseException;

import java.io.FileReader;

import java.io.Reader;

import java.util.Iterator;

import java.time.format.DateTimeFormatter;

import java.time.LocalDateTime;

 

String ociHostName; //ODI Variable used to store the OCI Host Name

String urlStrRestCall3; // = ociHostName + "/epm/rest/v1/applications";

String ociHostPath ="#04___ODI_AND_RESTAPI.EDMCS_FILES_LOCAL_PATH";//"/u02/odirest/";

String restCall3JSONFilename = ociHostPath + "/json/restCall_3.json";

String restCall3OutFilename = ociHostPath + "/out/restCall_3.out";

String urlJobFileName = ociHostPath + "/csv/joblink.csv";

 

public static String getCurrentTime () {

  DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss");

  LocalDateTime now = LocalDateTime.now();

  String currTime = dtf.format(now);

  return currTime;

}

 

public static void writeToFile(String fileName, String str)

  throws IOException {

 

    BufferedWriter writer = new BufferedWriter(new FileWriter(fileName, true));

    writer.append(' ');

    writer.append(str + "\r\n");

    writer.close();

}

 

public static void runDimensionDownloadJob(String urlStr, String jsonFileName, String outFileName) {

  writeToFile(outFileName, getCurrentTime() + " Creating connection and setting request properties ..." );

         try {

 

              URL url = new URL(urlStr);

              HttpURLConnection conn = (HttpURLConnection) url.openConnection();

    String user = "ekps.gapmdm"; // username

    String pass = "Serv!c8Acc7"; // password or API token

    String authStr = user + ":" + pass;

    String encoding = Base64.getEncoder().encodeToString(authStr.getBytes("utf-8"));

    writeToFile(outFileName, getCurrentTime() + " Authorization : Basic " + encoding);

 

              conn.setRequestMethod("GET");

              conn.setRequestProperty("Accept", "application/json");

    conn.setRequestProperty("Authorization", "Basic " + encoding);

    int respCode = conn.getResponseCode();

    writeToFile(outFileName, getCurrentTime() + " Response Code : " + respCode);

 

              if (respCode != 200) {

                      throw new RuntimeException("Failed : HTTP error code : "

                                    + conn.getResponseCode());

              }

    writeToFile(outFileName,getCurrentTime() + " Reading response ....");

              BufferedReader br = new BufferedReader(new InputStreamReader(

                      (conn.getInputStream())));

 

              String output;

    writeToFile(outFileName, getCurrentTime() + " Output from Server .... " );

              while ((output = br.readLine()) != null) {

      writeToFile(jsonFileName,output);

              }

          writeToFile(outFileName, getCurrentTime() + " JSON Response File written to file : " + jsonFileName);

              conn.disconnect();

    writeToFile(outFileName, getCurrentTime() + " Disconnected! " );

 

         } catch (MalformedURLException e) {

 

              e.printStackTrace();

 

         } catch (IOException e) {

 

              e.printStackTrace();

 

         }

 

       }

 

 

  public static String getJobURLFromFile(String fileName){

    BufferedReader reader;

    String urlStr;

              try {

                      reader = new BufferedReader(new FileReader(fileName));

                     String line = reader.readLine();

        urlStr = line.replace("\"", "");

                      reader.close();

        return urlStr;

              } catch (IOException e) {

                      e.printStackTrace();

              }

  }

 

 

writeToFile(restCall3OutFilename,  " \n" + getCurrentTime() + " Variable urlStrRestCall3 : " + urlStrRestCall3);

writeToFile(restCall3OutFilename,  getCurrentTime() + " Variable ociHostPath : " + ociHostPath);

writeToFile(restCall3OutFilename,  getCurrentTime() + " Variable restCall3JSONFilename : " + restCall3JSONFilename);

writeToFile(restCall3OutFilename,  getCurrentTime() + " Variable restCall3OutFilename : " + restCall3OutFilename);

writeToFile(restCall3OutFilename,  getCurrentTime() + " Variable urlJobFileName : " + urlJobFileName);

writeToFile(restCall3OutFilename,  getCurrentTime() + " Reading Job URL from file : " + urlJobFileName);

 

urlStrRestCall3 = getJobURLFromFile(urlJobFileName);

writeToFile(restCall3OutFilename,  getCurrentTime() + " Job URL :  " + urlStrRestCall3);

runDimensionDownloadJob(urlStrRestCall3, restCall3JSONFilename, restCall3OutFilename);

  1. 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

  1. Execute the downloadDimension ODI procedure

 

Figure 15: downloadDimension Procedure Step​​​​​​​​​​​​​​

​​​​​​​

The code below is the comprehensive Java BeanShell target command call

import java.io.BufferedReader;

import java.io.BufferedWriter;

import java.io.IOException;

import java.io.InputStreamReader;

import java.net.HttpURLConnection;

import java.net.MalformedURLException;

import java.net.URL;

import org.json.simple.JSONArray;

import org.json.simple.JSONObject;

import org.json.simple.parser.JSONParser;

import org.json.simple.parser.ParseException;

import java.io.FileReader;

import java.io.Reader;

import java.util.Iterator;

import java.time.format.DateTimeFormatter;

import java.time.LocalDateTime;

 

String ociHostName; //ODI Variable used to store the OCI Host Name

String urlStrRestCall4; // = ociHostName + "/epm/rest/v1/applications";

String ociHostPath ="#04___ODI_AND_RESTAPI.EDMCS_FILES_LOCAL_PATH";//"/u02/odirest/";

String dimFileName = ociHostPath + "/csv/" + "#04___ODI_AND_RESTAPI.EDMCS_DIMENSION_NM" + ".csv";

String restCall4OutFilename = ociHostPath + "/out/restCall_4.out";

String urlJobFileName = ociHostPath + "/csv/downloadlink.csv";

 

public static String getCurrentTime () {

  DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss");

  LocalDateTime now = LocalDateTime.now();

  String currTime = dtf.format(now);

  return currTime;

}

 

public static void writeToFile(String fileName, String str)

  throws IOException {

 

    BufferedWriter writer = new BufferedWriter(new FileWriter(fileName, true));

    writer.append(' ');

    writer.append(str + "\r\n");

    writer.close();

}

 

public static void downloadDimension(String urlStr, String jsonFileName, String outFileName) {

  writeToFile(outFileName, getCurrentTime() + " Creating connection and setting request properties ..." );

         try {

 

              URL url = new URL(urlStr);

              HttpURLConnection conn = (HttpURLConnection) url.openConnection();

    String user = "ekps.gapmdm"; // username

    String pass = "Serv!c8Acc7"; // password or API token

    String authStr = user + ":" + pass;

    String encoding = Base64.getEncoder().encodeToString(authStr.getBytes("utf-8"));

    writeToFile(outFileName, getCurrentTime() + " Authorization : Basic " + encoding);

 

              conn.setRequestMethod("GET");

              //conn.setRequestProperty("Accept", "application/json");

    conn.setRequestProperty("Authorization", "Basic " + encoding);

    conn.connect();

 

    writeToFile(outFileName, getCurrentTime() + " Connection Created !" );

 

    //writeToFile(outFileName, getCurrentTime() + " Writting payload !" );

 

    //OutputStream os = conn.getOutputStream();

    //os.write("fileName=dimension.csv".getBytes(StandardCharsets.UTF_8));

    //os.flush();

 

    int respCode = conn.getResponseCode();

    writeToFile(outFileName, getCurrentTime() + " Response Code : " + respCode);

 

              if (respCode != 200) {

                      throw new RuntimeException("Failed : HTTP error code : "

                                    + respCode);

              }

    writeToFile(outFileName,getCurrentTime() + " Reading response ....");

              BufferedReader br = new BufferedReader(new InputStreamReader(

                      (conn.getInputStream())));

 

              String output;

    writeToFile(outFileName, getCurrentTime() + " Output from Server .... " );

              while ((output = br.readLine()) != null) {

      writeToFile(jsonFileName,output);

              }

            writeToFile(outFileName, getCurrentTime() + " Dimension data written to file : " + jsonFileName);

              conn.disconnect();

    writeToFile(outFileName, getCurrentTime() + " Disconnected! " );

 

         } catch (MalformedURLException e) {

 

              e.printStackTrace();

 

         } catch (IOException e) {

 

              e.printStackTrace();

 

         }

 

       }

 

 

  public static String getDownloadURLFromFile(String fileName){

    BufferedReader reader;

    String urlStr;

              try {

                      reader = new BufferedReader(new FileReader(fileName));

                      String line = reader.readLine();

        urlStr = line.replace("\"", "");

        urlStr = urlStr.substring(0, urlStr.indexOf("?"));

                      reader.close();

        return urlStr;

              } catch (IOException e) {

                      e.printStackTrace();

              }

  }

 

 

writeToFile(restCall4OutFilename, " \n" + getCurrentTime() + " Variable urlStrRestCall4 :  " + urlStrRestCall4);

writeToFile(restCall4OutFilename,  getCurrentTime() + " Variable ociHostPath :  " + ociHostPath);

writeToFile(restCall4OutFilename,  getCurrentTime() + " Variable dimFileName :  " + dimFileName);

writeToFile(restCall4OutFilename,  getCurrentTime() + " Variable restCall4OutFilename :  " + restCall4OutFilename);

writeToFile(restCall4OutFilename,  getCurrentTime() + " Variable urlJobFileName :  " + urlJobFileName);

urlStrRestCall4 = getDownloadURLFromFile(urlJobFileName);

writeToFile(restCall4OutFilename, getCurrentTime() + " Download URL :  " + urlStrRestCall4);

downloadDimension(urlStrRestCall4, dimFileName, restCall4OutFilename);

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

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha