Customizing Oracle ODI Knowledge Modules

Oracle ODI ships with a bundle of very useful and quite diverse knowledge modules but at the same time one of the basic need it to get a customized version of the same knowledge module to get more control and gain more functionality. So how can we do that ?

Problem Statement : 
 
The Scenario we are taking about today is very simple one , if you are using any Integration Knowledge Module it creates a table with I$_  and do the further processing from their on wards. But here is a flaw , if you kick off the same job or any other job which loads the same target table ODI will launch a new instance and mess the current running job.

For example , I am using  "IKM Oracle Incremental Update" and what it does is it creates an table with I$ instead i wish to have it create a table post_fix with current SESSION number. e.g. I$

The steps are as follows
1 - Go to the project folder -->  Knowledge Modules -- > find IKM Oracle Incremental Update
2 - Right click on the selected module above and click on "Duplicate Selection".
3 - Give it a suitable name  e.g. "Custom_IKM Oracle Incremental Update"
4- Double click on the Module and click on "Details" to see the lost of steps for this module.
5- You would find a step with a title as " Create flow table I$".
6- Select that and lets see the code looks like as follows

create table <%=odiRef.getTable("L", "INT_NAME", "W")%>
(
    <%=odiRef.getColList("", "[COL_NAME]\t\t[DEST_WRI_DT] NULL", ",\n\t", "", "")%>,
    IND_UPDATE        CHAR(1)
)
<%=odiRef.getUserExit("FLOW_TABLE_OPTIONS")%>

7- Add the custom logic to append session number with the table being created.

create table <%=odiRef.getTable("L", "INT_NAME", "W")%>_<%=odiRef.getSession("SESS_NO")%>
(
    <%=odiRef.getColList("", "[COL_NAME]\t\t[DEST_WRI_DT] NULL", ",\n\t", "", "")%>,
    IND_UPDATE        CHAR(1)
)
<%=odiRef.getUserExit("FLOW_TABLE_OPTIONS")%>

 8- Save your knowledge module and that is it.
 9- Use the Custom Knowledge Module in your mapping .

You would see that the I$ table is created with a session post fix so any other instance of same job will not disturb the running flow.

Similarly you can add new steps , make them conditional and add new options to customize it more as per requirements.

Hope that helps.

Thanks



 

The Refined and Optimized Data Architecture for Enterprise Needs

We all are by now familiar with the hype of fancy terms like "Big Data", "Hadoop", "No SQL Databases" , but having said that there is one principle behind all that technology expenses when we take a look at this from the enterprise perspective and this is "How much Value is added into the Enterprise".

Believe it or not at the end of the day  I.T services are a supporting platform to benefit business users and stake holders to achieve business goals and forecast the future projections. The main points can be short listed to following

Cost : EDW Typical Storage (usually any MPP architecture solution e.g. Teradata , Netteza , Oracle Exadata etc ) is quite an expensive one and also has a constraint when it comes to keep historical data or unstructured data /sensor data , there is an alternate and that is to store the "Cold" storage data into cheap commodity hardware e.g. Hadoop

Value of return on investment :  Replacing all EDW infrastructure and legacy system with new architecture and technologies is quite an expensive idea however looking at long term prospects and return of investment the best approach is slowly to resolve the limitation of existing EDW infrastructure and make a hybrid architecture to get the maximum value out of it.

Solution Linear Scale-ability and long term solution design :  No doubt , long term solution design and a flexible architecture to handle the growing data and type of data ( social media , sensor , click stream data) is a challenge so data architecture is to be refined to keep long term prospects in mind.

Below is a somewhat close hybrid solution design of a new data architecture for an enterprize.

Above is a reference from a white paper published by Hadoop only one argument which is my personal opinion is that with shifting all ETL to the Hadoop. Instead i believe we should keep it hybrid ( at least for some time to run a parallel architecture) and Keep ONLY Non structured data feeds /sensor/click stream detailed data ETL work on Hadoop platform and let the traditional sources be running into the existing infrastructure.

Reference  : http://info.hortonworks.com/rs/549-QAL-086/images/hortonworks-data-architecture-optimization.pdf?mkt_tok=3RkMMJWWfF9wsRonvKTKc%2B%2FhmjTEU5z16uQsWaeygYkz2EFye%2BLIHETpodcMTcVnMLDYDBceEJhqyQJxPr3AKNkNy9RxRhHqDg%3D%3D

Split Excel file to Multiple TAB delimited UTF-8 files

One of the requirement on a project was a to split a excel file with multiple sheets to separate Tab delimited files. The challenge that we faced was that data was in Arabic Language and VB script was not getting it right. 

Below is the Complete code how to split an excel into multiple UTF-8 Tab delimited files.  The only importnat thing to note is that while saving file you need to provide argument 42 if you wish to save file in UTF-8 format.

oWorksheet1.SaveAs WScript.Arguments.Item(1), 42

The complete code is as follows and can  be invoked from Batch script using

XlsToCsv.vbs  Source_Excel.xls  Test1.csv Test2.csv Test3.csv Test4.csv

 - - - - Code Start Here ------- 

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If

Dim oExcel
Dim oWorkbook
Dim oWorksheet1
Dim oWorksheet2
Dim oWorksheet3
Dim oWorksheet4
Dim RowCount
Dim ColCount

Set oExcel = CreateObject("Excel.Application")
Set oWorkbook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
Set oWorksheet1 = oWorkbook.Worksheets.Item(2)
Set oWorksheet2 = oWorkbook.Worksheets.Item(3)
Set oWorksheet3 = oWorkbook.Worksheets.Item(4)
Set oWorksheet4 = oWorkbook.Worksheets.Item(5)

oExcel.Application.DisplayAlerts = False
oWorksheet1.SaveAs WScript.Arguments.Item(1), 42

oExcel.Application.DisplayAlerts = False
oWorksheet2.SaveAs WScript.Arguments.Item(2), 42

oExcel.Application.DisplayAlerts = False
oWorksheet3.SaveAs WScript.Arguments.Item(3), 42

oExcel.Application.DisplayAlerts = False
oWorksheet4.SaveAs WScript.Arguments.Item(4), 42

oWorkbook.Close
oExcel.Quit
Set oWorksheet1 = Nothing
Set oWorksheet2 = Nothing
Set oWorksheet3 = Nothing
Set oWorksheet4 = Nothing
Set oWorkbook = Nothing
Set oExcel = Nothing


--- Code End Here ----

MS SQL Server Model not showing any objects in ODI

we had a requirement to connect to the MS SQL server 2008 as our source system and once issue we noticed that we were succesfull in connecting to the database while in the model view we were not able to see any objects.

The procedure to connect to the database is standard but there is one trick which we should understand while connecting to the SQL server database.

Please find below the screen shot when u click for new data server Go to Topology - > Physical Architecture - > Technology -> MS Sql Server ->  New Data Server

As below choose the mentioned JDBC drivers for SQL Server and give the IP and port. Plase make sure that remote access is anabled and your user has access on the required database.

Next step is to create a physical schema from this data server here you must keep in mind that in SQL Server database structure is different e.g In our case the database name is "dqaa" and we wish to extract some tables from it. The owner of schema is "DBO" and hence we need to set the setting as below because the property "Local Object Mask" will use that information to extract tables /views/objects from SQL server metadata . This was the main reason why we were not able to see any objects in the Model view after reverse engineering.

Hope that helps.

What are Pluggable database in Oracle & Setting up ODI Repository with Oracle 12c.

Oracle 12 c has recently introduced a new concept of plug-gable databases which actually aims to separate the metadata from business user data. The concept is to create a container that will hold the user data schema. The details about the plug-gable database and how they work can be referenced from below. 

http://oracle-base.com/articles/12c/multitenant-overview-container-database-cdb-12cr1.php

But here are few questions?

How do I connect to a pluggable database using SQL developer or TOAD?

The Answer is simple with oracle installation it creates and ask for the name of the pluggable database e.g. PDBORCL (in our case this is the pluggable database name). If you are not sure you can login to system using SYS user and execute below query. You can see I have a PDB with name as PDBORCL and its open. If it’s not open you can open it by issuing the second SQL statement below

select name, open_mode from v$pdbs;

PDBORCL             READ WRITE

Alter pluggable database all open;

Now you have to define a TNS entry in your oracletns.ora file as follows and you will be able to connect it using TOAD/SQL Developer.

PDBORCL=

  (DESCRIPTION=

    (ADDRESS=

      (PROTOCOL=TCP)

      (HOST=localhost)

      (PORT=1521)

    )

    (CONNECT_DATA=

      (SERVER=dedicated)

      (SERVICE_NAME=PDBORCL)

    )

  )

How do I Setup the ODI repository into a pluggable database using RCU?

The RCU utility will setup the repository for you in the oracle 12c, but if you have configured the pluggable database option the RCU pre check fails with below error.

RCU-6002: The specified database does not meet the minimum requirement to load metadata repository. RCU-6080: Global prerequisite check failed - Check requirement for specified database the selected Oracle database is a multitenant container database (CDB). Connecting to a multitenant container database (CDB) is not supported. Instead, connect to a valid pluggable database (PDB).

So to solve this issue while RCU asks for the details on “Database connection details” step provide the Service Name: PDBORCL instead of your main service name e.g. ORCL which is by default.  Doing so all the repository objects and user will be created in the PDBORCL database and later you will use the below steps to create or connect to your PDB based repository.

How do I connect to a pluggable database using ODI or any other application?

For my case we have an issue while connecting to the PDB database while setting up the ODI repository we did the following while filling up the JDBC URL. Giving only the service name will give error and to resolve it we did the following by providing the full TNS entry details.

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PDBORCL)))

Hope that helps.

Reference.

https://blogs.oracle.com/dataintegration/entry/odi_creating_a_repository_in

http://en.community.dell.com/techcenter/enterprise-solutions/w/oracle_solutions/5063.how-to-connect-to-pluggable-databases-in-oracle-rac-12cr1

http://www.areaetica.com/setup-oracle-12c-enterprise-manager-express-for-a-pdb/

http://blog.e-dba.com/blog/2013/06/29/oracle-database-12c-pluggable-databases/
http://www.youtube.com/watch?v=ynUURa5dA6Q
 

Basics : ETL/ELT Concepts with Big Data

Big Data and Hadoop analytics has been a big buzz in IT industry and often you find some catchy terms associated with it. But if we take a closer look I think it would not be wrong to conclude that Big Data community has derived some terms which have the roots in traditional data warehousing or ETL implementation and are there from decades. 

We have observed that over the period of time ETL/ELT is evolving to support integration across much more than traditional data warehouses. ETL can support integration across transactional systems, operational data stores, BI platforms, MDM hubs, the cloud, and Hadoop platforms.

Below are some terms you will see when it comes to data processing with any Hadoop platform and they are listed below with corresponding concept in traditional data warehousing.

Tuple:  This term is used to define the basic information record that can be mapped to one row in the physical table in RDBMS or a record in a file.

Pipe Assembly: It is defined as SET of records which are under processing, you can imagine them as group of rows from a table or a file.

Tuple Stream:  It is actually the group of records which are under any kind of data processing and transformation, usually in any ETL tool the source data is selected and it will undergo some processing or transformation and this operation take place either in system memory or in case of a push down optimization it is done in RDMS spool space. Regardless where the transformation is applied it’s basically the set of records under processing.

Taps : Generic component independent of a platform , it can be mapped to something similar to a transformation step/stage e.g. a router or filter transformation in Informatica or Data stage ( any other ETL tool).

Flow: It is the series of Taps or transformation stages that are linked together to read, process and store some value into the target.

Cascade: Finally the term because of which I had to go through a lot of tutorials to drill down the science behind, this is a traditional concept for workflow it is defined as a collection of flow or in traditional ETL paradigm ETL Mapping Job to execute in a designed way to produce or achieve some value.

Hope that will help all people who are from DWH/BI background to get a grip quickly over the concepts related to ETL in Big Data domain.

References:

http://docs.cascading.org/tutorials/etl-log/etl.html

http://www.cascading.org/documentation/tutorials/