Creation of Master and Work Repositories

 Master Repository Creation:

Creating the master repository creates an empty repository structure and seeds metadata (for example, technology definitions, or built-in security profiles) into this repository structure.

Step1: First you need to create schema for Master Repository

Open the sql developer

And create new schema with the name MasterRepo

Grant the required permissions

Step2: Start the ODI Studio

Step3: To create the master repository:

Open the New Gallery by choosing File > New.

In the New Gallery, in the Categories tree, select ODI.

step4: Select from the Items list the Master Repository Creation Wizard.

Click OK.

The Master Repository Creation wizard opens.

Step 5:

Specify the Database Connection parameters as follows:

Technology: From the list, select the technology that will host your master repository. Default is Oracle.

JDBC Driver: The driver used to access the technology, that will host the repository.

JDBC URL: The URL used to establish the JDBC connection to the database.

Note that the parameters JDBC Driver and URL are synchronized and the default values are technology dependant.

User: The user ID / login of the owner of the tables (for example, odim).

Password: This user's password.

DBA User: The database administrator's username

DBA Password: This user's password

Note: It is strongly recommended that this ID is unique and not used for any other master repository, as it affects imports and exports between repositories

Step6:
Specify the Repository Configuration parameters as follows:
ID: A specific ID for the new repository, rather than the default 0.
Click Test Connection to test the connection to your master repository.

The Information dialog opens and informs you whether the connection has been established. If the connection fails, fix the connection to your master repository before moving to next step.

Click Next.

Do one of the following:

Step 7:

Select Use ODI Authentication to manage users using ODI's internal security system and enter the following supervisor login information:
Properties Description
Supervisor User User name of the ODI supervisor.
Supervisor Password This user's password
Confirm Password This user's password

 

Step 8:

In the Master Repository Creation Wizard click Finish to validate your entries.

   Work Repository Creation:

 Several work repositories can be designated with several master repositories if necessary. However, a work repository can be linked with only one master repository for version management purposes.

To create a new work repository: First create the schema for work repository

I am using sql developer to create schemas

\

 

In the Topology Navigator, go to the Repositories panel.

Right-click the Work Repositories node and select New Work Repository.

The Create Work Repository Wizard opens.

Specify the Oracle Data Integrator work repository connection details as follows:

 

Technology: Choose the technology of the server to host your work repository. Default is Oracle.

JDBC Driver: The driver used to access the technology, that will host the repository.

JDBC URL: The complete path of the data server to host the work repository.

 

Note that the parameters JDBC Driver and URL are synchronized and the default values are technology dependent

 

User: User ID / login of the owner of the tables you are going to create and host of the work repository.

Password: This user's password. This password is requested for attaching this work repository to a different master.

Specify the Oracle Data Integrator work repository properties:

 

ID: A specific ID for the new repository, rather than the default 0.

 

Note: It is strongly recommended that this ID is unique and not used for any other work repository, as it affects imports and exports between repositories

 

Click Test Connection to verify that the connection is working.

 

Click Next.

 

Working with more than one work repository in Console

 

When we are working on more than two different work repositories, we should not configure the work repository with the same schema, if it does then the latest configuration will be saved ( it will show the pop-up as below)

If we click NO, then it will save the latest one else it will re-attach the WR.

If we want to get the projects created in two different work repositories separately in ODI Console, then first we need to configure the work repository details in WebLogicConsole.

Go to weblogic console -> Services -> Datasource ->new->generic data store

The JNDI Name in WLS console and Work JNDI Url should be same; if not the selected work repository details will not be displayed in odi console.

 

Click next

Next, we need to configure in ODI Console also, so that we will get the projects, scenarios etc. in ODI console also.

Configuring Repository in ODI console

Login to the ODI console with MR then goto-> management->Repository Connections->create

After clicking create a pop up will come, where we need to configure the WR.

Connection alias name: Name of WR to be displayed while logging in ODI Console.

Master JNDI URL: JNDI URL of the datasource to connect the master repository database.

Example: jdbc/odiMasterRepository

Supervisor Key: user name of the ODI having supervisor privileges, recommended to use SUPERVISOR.

Work JNDI URL: JNDI URL of the datasource to connect the work repository database. If no value is given in this field, the repository connection will allow connection to the master only, and the Navigation will be limited to Topology information.

The name which we gave at weblogic console (JNDI name), same should be given here too

Example: jdbc/odiWorkRepository

JNDI URL in JNDI Standard format?: Check this option if you want to use the Environment Naming Context (ENC). When this option is checked, Oracle Data Integrator Console automatically prefixes the data source name with the string java:comp/env/ to identify it in the application server's JNDI directory. Note that the JNDI Standard is not supported by Oracle WebLogic Server or for global data sources.

For example: jdbc/odiWorkRepository

Default: Check this option if you want this Repository Connection to be selected by default on the login page.

Then the name given at connection alias name will be displayed while logging into ODI Console.

Select the WR and give the credentials to login.

 

 

load data from table to file using free hand sql (OdisqlUnload) in odi 11g\odi 12c

 Advantage:

Ø  OdiSqlUnload Tool is built in tool in odi 11g, used in procedures only.

Ø  using OdiSqlUnload Tool, Load the data from Oracle table to Flat file , Excel, Xml , Pdf

Ø  Generates a data file by executing the SQL query <sql_query> on the data server whose connection parameters are provided by <driver>, <url>, <user> and <encoded_pass>. The file is written to the path defined by <filename> (relative to the agent).

 

Command:

 OdiSqlUnload -FILE=<file_name> -DRIVER=<driver> -URL=<url> -USER=<user> -PASS=<password> [-FILE_FORMAT=<file_format>] [-FIELD_SEP=<field_sep> | -XFIELD_SEP=<field_sep>] [-ROW_SEP=<row_sep> | -XROW_SEP=<row_sep>] [-DATE_FORMAT=<date_format>] [-ABS=<yes|no>] [-CHARSET_ENCODING=<encoding>] [-XML_CHARSET_ENCODING=<encoding>]  [-FETCH_SIZE=<array_fetch_size>] [CR/LF <sql_query> | -QUERY=<sql_query> | -QUERY_FILE=<sql_query_file> ]

 

Create a Project to create procedure:

 

Go to Designer Navigator. Select new project & enter the name for project

 

 

Create a Procedure for load the data from Oracle DB to Flat File:

Step: 1) Go to Designer, click the Projects tab. Expand your project WORK_ON_LOAD ORCL DB TO FLAT FILE, and then expand First Folder.  Right-click procedure and selects new procedure.

Step: 2) Select the  F details tab. Click the   + add symbol for command to write a SQL Query.

 

Step: 3) I have enter command in following screen shot. Here,

Source: File

FILE=D:\TEXT\Test data of procedure.txt

Target: Oracle

JDBC DRIVER: oracle.jdbc.OracleDriver

 JDBC URL=jdbc:oracle:thin:@190.160.0.10:1521:DEVDWH

USER=DEVDWH

QUERY: select * from DEVDWH.DWT_SECTOR,DEVDWH.DWT_INDUSTRY

 

Note: Source file is not only text file (.txt) but also .PDF, .CSV, .doc, .xls extenstions.

 

OdiSqlUnload "-FILE=D:\TEXT\Test data of procedure.txt" "-DRIVER=oracle.jdbc.OracleDriver" "-URL=jdbc:oracle:thin:@190.160.0.10:1521:DEVDWH" "-USER=DEVDWH" "-PASS=hpfHiT7Ql0Hd79KUseSWYAVIA" "-FILE_FORMAT=VARIABLE" "-FIELD_SEP=," "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1" select * from DEVDWH.DWT_SECTOR,DEVDWH.DWT_INDUSTRY

 

Step: 4) check the TEXT folder (source file), this is empty folder.

 

 

Step: 5) To test your project click Execute icon (Green play button

). Then following screen appears.

 

 

 

Step: 6) To verify that your interface was executed successfully, you need to open ODI Operator Navigator. Click the ODI Operator tab. In ODI Operator, click Session List. Expand Sessions > FLAT TO Oracle, and view the execution results for each step. Double-click any step and click the Description and Execution tabs to see the execution results. Double-click step 1. View the number of rows that are inserted into the target flat file. Click OK. Close the tabs.

Step: 7) Go to D:\TEXT path and after execute, automatically generate the text file with file name and see the output.

 

 

 

 

 

 

 

Implement SCD type3 in ODI 11g\12c

Ø  In Type 3 Slowly Changing Dimension, there will be two columns to indicate the particular attribute of interest, one indicating the original value, and one indicating the current value. There will also be a column that indicates when the current value becomes active.

Ø  Type 3 slowly changing dimension should only be used when it is necessary for the data warehouse to track historical changes, and when such changes will only occur for a finite number of time, that means only recent data stored in table.

			Create project   Go to Designer Navigator. Select new project.       Import Knowledge Modules
			For this SCD TYPE3, The Knowledge Modules (KMs) that are required for this are IKM SQL Incremental Update & CKM Oracle To import the KMs, click the Project tab in the left panel and expand the Knowledge Modules folder. Right-click on KM and select Import Knowledge Modules.  On the screen that follows, in the File Name, navigate to xml reference directory as shown in the following screenshot. Click Open. The files to import should appear in the Import Knowledge Modules window. Press and hold the CTRL key to select  IKM SQL Incremental Update & CKM Oracle. Click OK.
			On Import Report window, click Close. Expand the IKM & CKM folder
			Add the command to IKM for impenting scdtype3: Change IKM SQL Incremental Update to IKM SQL Incremental Update SCD TYPE3 Add command to historical update rows is as follows         UPDATE    <%=odiRef.getTable("L", "TARG_NAME", "A")%> T set <%=odiRef.getColList("", "T.[COL_NAME]", ",nt", "", "(UD2)")%> = <%=odiRef.getColList("", "T.[COL_NAME]", ",nt", "", "(UD1)")%> where    (<%=odiRef.getColList("","T.[COL_NAME]", ", ", "", "UK")%>)     in    ( select    <%=odiRef.getColList("","T.[COL_NAME]", ",nttt", "", "UK")%> from     <%=odiRef.getTable("L", "TARG_NAME", "A")%> T , <%=odiRef.getTable("L", "INT_NAME", "A")%> S where   <%=odiRef.getColList("","T.[COL_NAME]", ", ", "", "UK")%> = <%=odiRef.getColList("","S.[COL_NAME]", ", ", "", "UK")%> AND <%=odiRef.getColList("", "T.[COL_NAME]", ",nt", "", "(UD1)")%>!= <%=odiRef.getColList("", "S.[COL_NAME]", ",nt", "", "(UD1)")%>         )       Create Data model for source & target tables:    Source table : create table scdtype3_source as select * from employee         desc scdtype3_source   TABLE scdtype3_source  Name                                      Null?    Type                         ----------------------------------------- -------- ----------------------------  EMPNO                                              NUMBER(4,)                   ENAME                                              VARCHAR2(10)                 JOB                                                VARCHAR2(9)                  MGR                                                NUMBER(4,)                   HIREDATE                                           DATE                         SAL                                                NUMBER(7,2)                  COMM                                               NUMBER(7,2)                        DEPTNO                                             NUMBER(2,) Target Table:     CREATE TABLE scdtype3_target( EMPNO NUMBER(4) PRIMARY KEY, ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,CURSAL NUMBER(7,2),PREVSAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER (2))         desc scdtype3_target     TABLE scdtype3_target  Name                                      Null?    Type                         ----------------------------------------- -------- ----------------------------  EMPNO                                     NOT NULL NUMBER(4,)                   ENAME                                              VARCHAR2(10)                 JOB                                                VARCHAR2(9)                  MGR                                                NUMBER(4,)                   HIREDATE                                           DATE                         CURSAL                                             NUMBER(7,2)                  PREVSAL                                            NUMBER(7,2)                  COMM                                               NUMBER(7,2)                  DEPTNO                                             NUMBER(2,)   Required output: For example,  if sal changes 800 to 40000 then cursal, prevsal as EMPNO ENAME      JOB MGR HIREDATE CURSAL PREVSAL COMM DEPTNO EFFECTIVEDATE 7369 SMITH CLERK 7902 17-DEC-80 40000 800   20 31-AUG-2015 7499 ALLEN SALESMAN 7698 20-FEB-81 1600   300 30 31-AUG-2015         Create Interface for SCD TYPE3: Step 1) Open the Designer, expand the project. Right click on interfaces and click on new interface. Give the name as SCD TYPE3 Step 2) Drag and drop source(SCDTYPE3_SOURCE)  from the SCD model on to the source pane & Drag and Drop target(SCDTYPE3_TARGET) table from  SCD model on to the target pane. Click ok to perform Automatic mapping. For last three columns put     Step 3) Click on FLOW tab , on Target to see the IKM target properties. Select IKM SQL Incremental Update in the flow tab. Similarly CKM Oracle in the control tab. Set the "flow_control" option to true . Click on save. Click on execute.           Step 4) Go to operator log. Check the log for this session. Open it and check the number of inserts & updates. Here there is no previous data.     Step 5) Go to Designer tab, click the source & target tables.     Modify the Source table: There are 14 rows inserted. Let’s do some modification in the source table to know whether its working properly or not. Here I am updating the salary of SMITH to 30000. Here new record should not be added instead it should be updated as we have selected overwrite on change. After changes commit the data store.   update scdtype3_source set sal=30000 where empno=7788       Check the source :   Run the interface again. Here you can see the same thing reflected as per our expectation. Its your turn now. Do the changes on source side and run the interface to get these data on target.         Output: