X

Welcome to All Things Data Integration: Announcements, Insights, Best Practices, Tips & Tricks, and Trend Related...

ODI KMs for Business Intelligence Cloud Service

In this article we will learn how to leverage Oracle Data
Integrator’s extensible Knowledge Modules (KM) framework to create knowledge
modules to load data into the Oracle Business Intelligence Cloud Service (BICS).
The following instructions are targeted for BICS instances using Schema Cloud
Service, if your BICS instance uses Database as a Service then you can directly
load data into DBaaS tables as described in the blog post ODI
12c and DBaaS in the Oracle Public Cloud
. More details on implementing
Knowledge Modules can be found in Knowledge
Module Developer Guide
.

BICS exposes REST APIs that allows programmatically
creating, managing, and loading schemas, tables, and data into Oracle BI Cloud
Service. We will invoke these REST APIs using the Jersey client libraries providing
wrapper implementation for invoking RESTful web services.

Get Jersey libraries
for making RESTful web service calls

Download jersey and dependent libraries from https://jersey.java.net/download.html
and add it to your Java project to compile the KM code. In this article I have
provided code snippets for Jersey 1.x libraries APIs.

Jersey APIs allows you to invoke RESTful web-services with
simple APIs calls

WebResource.Builder
webResource = getResourceBuilder(<path>, <params>);

ClientResponse
response = webResource.get(ClientResponse.class);

<T> tables =
response.getEntity(<T>.class);

Create BICS
Technology

Create a new technology for BI Cloud Service. Select the
technology type as Bean Scripting Framework and select the Data Server and
Catalog checkboxes in the Naming Rules.


In the Language tab add JYTHON language.

Add a data type for each of the valid data types in BICS.
Currently, the data types that BICS support for the data load API are varchar2,
number and date.

Create Data Server
and Physical Schema

Create a data server and physical schema under the BICS
technology.

Note: Do not enter the /analytics suffix to the URL. e.g  https://businessintelxxx-gboraclexxx.analytics.us2.oraclecloud.com

Create an RKM to
reverse engineer metadata from BI Cloud Service

Create an RKM and select BI Cloud Service as source
technology.

Add the applicable RKM options

Add following reverse engineering tasks.

Details of each task shown above are as follows


  • Reset MetaData: To reset the reverse tables in
    repository

SnpsReverseResetTable
-MODEL=<%=snpRef.getModel("GLOBAL_ID")%>


  • Start Reverse: Add Jython code to invoke
    connectivity information to your BICSModelImporter implementation class.

from java.util import HashMap

from java.lang import Boolean

from oracle.odi.rest.bics import ODIConstants

from oracle.odi.rest.bics import BICSModelImporter

# BICS connection properties

serviceUrl =
"<%=snpRef.getInfo("SRC_DSERV_NAME")%>"

userName =
"<%=snpRef.getInfo("SRC_USER_NAME")%>"

password = "<%=snpRef.getInfo("SRC_PASS")%>"

tenant =
"<%=snpRef.getInfo("SRC_CATALOG")%>"

# Put the connection properties and initialize the
BICS connection

srcProps = HashMap()

srcProps.put(ODIConstants.SERVICE_URL,serviceUrl)

srcProps.put(ODIConstants.USER,userName)

srcProps.put(ODIConstants.PASSWORD,password)

srcProps.put(ODIConstants.TENANT,tenant)

modGUID = "<%=snpRef.getModel("GLOBAL_ID")%>"

tableNameFilter =
"<%=snpRef.getOption("TABLE_NAME")%>"

rkmOptions = HashMap()

rkmOptions.put(ODIConstants.OPTION_TABLE_NAME,
tableNameFilter)

#get connection to repository

repCon = odiRef.getJDBCConnection("DEST")

# START THE REVERSE

bicsModeImporter = BICSModelImporter()

bicsModeImporter.importModel(modGUID, srcProps,
repCon, rkmOptions)

  • Set MetaData: To create datastores from the
    metadata in reverse tables

SnpsReverseSetMetaData
-MODEL=<%=snpRef.getModel("GLOBAL_ID")%>

Implement Java code for
BICSModelImporter.importModel(). The pseudo code and corresponding code snippet
for it will be as follow



  • Get list of Tables
MultivaluedMap<String, String> params
= new MultivaluedMapImpl();

params.add("name",
tableNamePattern);

WebResource.Builder webResource = getResourceBuilder(“https://< BICS-URL?>/dataload/v1/tables/<TABLE_NAME>”,
params);

ClientResponse response =
webResource.get(ClientResponse.class);

String[] tables =
response.getEntity(String[].class);

  • Get metadata from each of the tables

WebResource.Builder webResource =
getResourceBuilder("https://<BICS-URL>/dataload/v1/tables/<table-name>");

ClientResponse response =
webResource.get(ClientResponse.class);

JSONObject tableMetadata =
(response.getEntity(JSONObject.class));

  • Populate table metadata in reverse tables in ODI
    repository.

Connection
con = repConnection;

String[]
tables = getTables();

//
initialize the prepared statements

tStmt
= con.prepareStatement(“insert into SNP_REV_TABLE
(MOD_GUID,TABLE_NAME,RES_NAME,TABLE_ALIAS,TABLE_DESC,IND_SHOW,TABLE_TYPE)
values (?,?,?,?,?,?,?)");

cStmt
= con.prepareStatement("insert into SNP_REV_COL
(MOD_GUID,TABLE_NAME,COL_NAME,COL_HEADING,COL_DESC,DT_DRIVER,POS,LONGC,SCALEC,COL_MANDATORY,CHECK_FLOW,CHECK_STAT)
values (?,?,?,?,?,?,?,?,?,?,?,?)");

iStmt
= con.prepareStatement("insert into SNP_REV_KEY
(MOD_GUID,TABLE_NAME,KEY_NAME,CONS_TYPE,IND_ACTIVE,CHECK_FLOW,CHECK_STAT)
values (?,?,?,?,?,'1','1')");

icStmt
= con.prepareStatement("insert into SNP_REV_KEY_COL
(MOD_GUID,TABLE_NAME,KEY_NAME,COL_NAME,POS) values (?,?,?,?,?)");

//
import all the tables now

for
(int i = 0; i < tables.length; i++) {

JObject tableMetadata = getTableMetadata(tables[i]);

//execute prepared statement to insert table metadata into
reverse tables

importTable(modGUID, tableMatadata, tStmt, cStmt,iStmt,
icStmt);

}

Please see the BICS
REST APIs Documentation
for further details on path and parameters





Reverse engineer BI
Cloud Service tables

Create a Model for BI Cloud Service technology and reverse
engineer the tables. In the reverse engineer tab select the RKM for BI Cloud
Service and specify the applicable values for the RKM options.

Create IKM for BI
Cloud service

Create an IKM and select BI Cloud Service as target
technology. Select the Multi-Connections check box for having the staging area
different than target.

Add IKM options as needed for your use case

Add tasks to IKM for the operations you want to support.

Details of each task shown above are as follows

  • Initialize: Retrieves connectivity parameters
    and options values and prepares the BICSWriter

from java.util import HashMap

from java.lang import Boolean

from java.lang import Integer

from oracle.odi.rest.bics import ODIConstants

from oracle.odi.rest.bics import BICSWriter

# # BICS connection properties #

serviceUrl =
"<%=snpRef.getInfo("DEST_DSERV_NAME")%>"

userName =
"<%=snpRef.getInfo("DEST_USER_NAME")%>"

password = "<%=snpRef.getInfo("DEST_PASS")%>"

tenant =
"<%=snpRef.getInfo("DEST_CATALOG")%>"

# # Put the connection properites and initialize the bics
connection #

tgtProps = HashMap()

tgtProps.put(ODIConstants.SERVICE_URL,serviceUrl)

tgtProps.put(ODIConstants.USER,userName)

tgtProps.put(ODIConstants.PASSWORD,password)

tgtProps.put(ODIConstants.TENANT,tenant)

tableName =
"<%=snpRef.getTargetTable("RES_NAME")%>"

dropAndRecreateTable = <%=snpRef.getOption("DROP_AND_RECREATE_TABLE")%>

retryOnError =
<%=snpRef.getOption("RETRY_ON_ERROR")%>

commitInterval =
<%=snpRef.getOption("COMMIT_INTERVAL")%>

truncateTable =
<%=snpRef.getOption("TRUNCATE_TABLE")%>

colName = <%=odiRef.getTargetColList("\u0022",
"[COL_NAME]", ",", "\u0022")%>

colType = <%=odiRef.getTargetColList("\u0022",
"[DEST_CRE_DT]", ",", "\u0022")%>

colScale = <%=odiRef.getTargetColList("\u0022",
" [SCALE]", ",", "\u0022")%>

colLength = <%=odiRef.getTargetColList("\u0022",
" [LONGC]", ",", "\u0022")%>

colNullable =
<%=odiRef.getTargetColList("\u0022", "[COL_MANDATORY]",
",", "\u0022")%>

colDefault =
<%=odiRef.getTargetColList("\u0022", " [DEF_VALUE]",
",", "\u0022")%>

loadOptions = HashMap()

loadOptions.put(ODIConstants.OPTION_TABLE_NAME, tableName)

loadOptions.put(ODIConstants.OPTION_DROP_AND_RECREATE_TABLE,
dropAndRecreateTable)

loadOptions.put(ODIConstants.OPTION_RETRY_ON_ERROR,
retryOnError)

loadOptions.put(ODIConstants.OPTION_COMMIT_INTERVAL,
commitInterval)

loadOptions.put(ODIConstants.OPTION_TRUNCATE_TABLE,
truncateTable)

loadOptions.put(ODIConstants.COLUMN_NAME, colName)

loadOptions.put(ODIConstants.COLUMN_TYPE, colType)

loadOptions.put(ODIConstants.COLUMN_SCALE, colScale)

loadOptions.put(ODIConstants.COLUMN_LENGTH, colLength)

loadOptions.put(ODIConstants.COLUMN_NULLABLE, colNullable)

loadOptions.put(ODIConstants.COLUMN_DEFAULT, colDefault)

print "Initalizing the BICS Writer"

pWriter = BICSWriter(tgtProps,loadOptions);

  • Drop Table: Executed only if DROP AND RECREATE
    option is true. Invokes pWriter.dropTable() to drop BICS table.

from
oracle.odi.rest.bics import BICSWriter

pWriter.dropTable();

  • Create table: Executed only if DROP AND RECREATE
    option is true. Invokes pWriter.createTable() to create BICS table based upon
    the metadata set in the init task.

from
oracle.odi.rest.bics import BICSWriter

pWriter.createTable()

  • Truncate table: Executed only if TRUNCATE
    option is true. Invokes pWriter.truncateTable() to truncate data from BICS
    table

from
oracle.odi.rest.bics import BICSWriter

pWriter.truncateTable();

  • Load Data: Reads data from the staging table and
    uploads data into BICS table.

from
oracle.odi.rest.bics import BICSWriter

from
oracle.odi.rest.bics import ODIConstants

from
java.lang import Class

from
java.lang import Boolean

from
java.sql import *

from
java.util import HashMap

# #
Get the select statement on the staging area:

sql=
"""select <%=odiRef.getPop("DISTINCT_ROWS")
%> <%=odiRef.getColList("", "[EXPRESSION] [ALIAS_SEP]
\u0022[COL_NAME]\u0022", ",", "", "INS and
!TRG") %> from <%=odiRef.getFrom() %> where (1=1)
<%=odiRef.getFilter() %> <%=odiRef.getJrnFilter() %>
<%=odiRef.getJoin() %> <%=odiRef.getGrpBy() %>
<%=odiRef.getHaving() %>"""

srcCx
= odiRef.getJDBCConnection("SRC")

stmt =
srcCx.createStatement()

stmt.setFetchSize(1)

rs =
stmt.executeQuery(sql)

stats
= pWriter.loadData(rs)

#close
the database result set, connection

rs.close()

stmt.close()

  • Report Stats: Report the statistics in the error
    message

raise
"Records Loaded into BICS: ", stats

Implement BICSWriter class having corresponding methods for each of
the action which will send the REST request to the BICS web service.

  • deleteTable(): Makes REST call to delete
    resource

WebResource.Builder
webResource = getResourceBuilder(“https://<URL>/dataload/v1/tables/<table-name>”);

ClientResponse
response = webResource.delete(ClientResponse.class);

  • createTable(): Makes REST call to create
    resource at path https://<URL>/dataload/v1/tables/<table-name>.
    The columns metadata is sent in the JSON format.

JSONArray
columns = preparateJSON4Columns();

ClientResponse
response = webResource.type(MediaType.APPLICATION_JSON)

.put(ClientResponse.class,
columns);

  • truncateTable(): Makes REST call for DELETE
    method

WebResource.Builder
webResource = getResourceBuilder(“https://<URL>/dataload/v1/tables/<table-name>/data”);

ClientResponse
response = webResource.delete(ClientResponse.class);

  • loadData(): Make REST call for PUT method to add
    the data in the BICS table.
    The data is sent in the multipart payload for the REST call in JSON format.

String
jsonHeader = prepareJsonHeaderForDataLoad();

MutiPart
multiPart = new MultiPart().bodyPart( new
BodyPart(jsonHeader, MediaType.APPLICATION_JSON_TYPE)) l .bodyPart(new
BodyPart(is,MediaType.APPLICATION_OCTET_STREAM_TYPE));

WebResource.Builder
webResource = getResourceBuilder(" https://<URL>/dataload/v1/tables/<table-name>/data", true);

ClientResponse
response = webResource.type(RestAPIPathConstants.MEDIA_TYPE_MULTIPART_MIXED).entity(multiPart,
MultiPartMediaTypes.createMixed()).put(ClientResponse.class);

Create mapping to
load data into BI Cloud Service

With the help of BICS RKM you
can reverse engineer BICS datastores and then use them in ODI Mappings as
targets where the BICS IKM can be configured to load data. You can have any
kind of complex transformations before loading the BICS target. For example in
below mapping the source data is coming from a file and database table which
are joined on the staging area and then a fitler is applied before loading into
the target BICS table.

Sample Code

The sample BI Cloud Service KMs implementation is available
on java.net : RKM
and IKM for Oracle BI Cloud Service
. It contains an RKM for reverse
engineering BI Cloud Service tables and indexes, an IKM for loading data into BI
Cloud Service, and a jar containing the java implementation classes invoked by
the KMs.

The sample RKM provides following filter option to reverse
engineer only the selected tables.

  • TABLE_NAME: SQL expression to select the tables
    to reverse. Ex DEMO%

The sample IKM provides following data load options

  • DROP_AND_RECREATE_TABLE: If true then drops and
    recreates the BI Cloud Service table before loading the data.
  • TRUNCATE_TABLE: If true then truncates BI Cloud
    table before loading data into it.
  • COMMIT_INTERVAL: Specifies how many records to
    send to BI Cloud service in a REST call.
  • RETRY_ON_ERROR: Specifies the number of time a
    REST call to be retried on error.

To use the sample KMs – unzip the sample zip content into a
local directory, place the jars in ODI studio and agent class path, and import
the BI Cloud Technology, RKM and IKM into ODI repository.

Conclusion

Oracle Data Integrator provides a flexible and powerful
Knowledge Modules development framework for creating KMs for any cloud or on
premise data source. In this article we created an RKM and IKM for BI Cloud
Service. The sample implementation of it is available on Java.net : RKM
and IKM for Oracle BI Cloud Service

Join the discussion

Comments ( 5 )
  • guest Friday, June 17, 2016

    Hi Ayush,

    We are getting below error at RKM Step 2 "Start the Reverse"

    ODI-1590: The execution of the script failed.

    Caused By: org.apache.bsf.BSFException: exception from Jython:

    Traceback (most recent call last):

    File "<string>", line 6, in <module>

    ImportError: No module named bics

    Can you kindly suggest what needs to be done.

    Thank & Regards

    Subhash


  • Ayush Friday, June 17, 2016

    Hi Subhash,

    It indicates that the jars are not in the classpath. Please make sure to add it in the studio and the agent classpath where the reverse engineering session is executed.

    Thanks,


  • guest Wednesday, August 24, 2016

    Hi Ayush,

    Thanks for a wonderful write up.

    I am having an issue in the RKM Step 2 : Start the Reverse

    It says : java.lang.IncompatibleClassChangeError: java.lang.IncompatibleClassChangeError: Class com.sun.jersey.core.util.MultivaluedMapImpl does not implement the requested interface javax.ws.rs.core.MultivaluedMap

    I have tried to execute the same code in Eclipse and it works fine without any error.

    Could you please help me identify the issue.

    Thanks,

    Soumya


  • guest Wednesday, August 24, 2016

    Hi Ayush,

    Thanks for such a wonderful write up.

    I am getting below error at RKM Step 2 "Start the Reverse"

    java.lang.IncompatibleClassChangeError: java.lang.IncompatibleClassChangeError: Class com.sun.jersey.core.util.MultivaluedMapImpl does not implement the requested interface javax.ws.rs.core.MultivaluedMap

    Could you please kindly let me what could be the possible issue.

    Thanks,

    Soumya


  • guest Wednesday, August 31, 2016

    Hi Ayush,

    Great post.

    But we are getting below error at reverse engineering step 2 "Start the Reverse"

    ODI-1590: The execution of the script failed.

    Caused By: org.apache.bsf.BSFException: exception from Jython:

    Traceback (most recent call last):

    File "<string>", line 39, in <module>

    at java.util.regex.Matcher.appendReplacement(Matcher.java:857)

    at java.util.regex.Matcher.replaceFirst(Matcher.java:1004)

    at java.lang.String.replaceFirst(String.java:2178)

    at oracle.odi.rest.bics.BICSConnector.replaceFirstToken(BICSConnector.java:272)

    at oracle.odi.rest.bics.BICSConnector.replaceUrlTableName(BICSConnector.java:123)

    at oracle.odi.rest.bics.BICSConnector.getTableDefinition(BICSConnector.java:208)

    at oracle.odi.rest.bics.BICSConnector.getODITable(BICSConnector.java:75)

    at oracle.odi.rest.bics.BICSModelImporter.importBICSModels(BICSModelImporter.java:59)

    at oracle.odi.rest.bics.BICSModelImporter.importModel(BICSModelImporter.java:29)

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

    at java.lang.reflect.Method.invoke(Method.java:498)

    java.lang.IllegalArgumentException: java.lang.IllegalArgumentException: Illegal group reference

    We have added the jars in the classpath. Can you please advice on this.

    Thanks & Regards,

    Prajna


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.