ODI - How to use lookup component

Lookup:

• A lookup is a selector component that returns data from a lookup flow being given a value from a driving flow.The attributes of both flows are combined, similarly to a join component. 
• A lookup can be implemented in genereted SQL either through a Left outer join or a nested select statement.
• A lookup condition can be created by dragging an atrribute from the driving flow and then the lookup flow onto the lookup component. The lookup condition is an equi-join between the two attributes.
• The lookup component gives us some additional functionality compared to a join. We can supply a default value for a key that does not exist in our lookup table. Using join, we might have to do an outer join followed by an expression operator providing a CASE statement to replace the NULL from the unmatched key.
• Using lookup, we can define match row rules and no match rows too.

Implementation Steps:

Step1: In Topology, create physical and logical architecture and corresponding schemas and context.

Step2: In Designer tab, Create models and revers engineer to get the required data store tables.

Step3: Create a project and create a map.

Step4: Drag and drop the departments and employees tables and connect with the lookup.

Step5: provide the lookup condition. 

Step6: drag and drop the target table EMP_Depart and connect with the lookup.

Step7: go to the connectors point and see input and ouput connectors.

Driver table: Departments

LOOKup table: Employees

Step8 : select sk field and edit expression and use nextval sequence.

Step9: uncheck update.

Step10: add the optimization context.

Step11: Add LKM=LKM SQL to SQL for lookup

Step12: add IKM for target table and make flow control as false.

Step13: save. Run and check operator tab and target db table .

ODI - Create a date variable and use sysdate in a package

Usecase: we will create refresh variable which will store sysdate data and use in a map field. We will map source Location table to target location table with ETL_date field and use in a a package.

Source table has:

• Loc_id
• Street_address
• Postal_code
• City
• State_province
• Country

Target table has:

• Loc_id
• Street_address
• Postal_code
• City
• State_province
• Country
• ETL_date

Implementation Steps:

Step1: Create oracle technology physical and logical schema and context in topology

Step2: create model of the logical schemas and have data stores.

Step3: create a project and create new mapping

Drag and drop source and target tables

Map them auto by name

Step4: create a variable and go to refreshing section and choose schema and write the select query.

Select TO_CHAR(sysdate, 'YYYYMMDD HH24:MI:SS') from dual

Step5:Go back to the mapping and select Target ETL_date field and edit expression and use creates variable.

Save

Step6:  create a new package and drag and drop map and variable to package

Link ok between them

Step7: Go to the mapping and physical tab and choose context and Knowledge modules as below:

LKM for default: LKM SQL to SQL

IKM for target: IKM SQL Control Append

Flow control : False

Step8: create a step scenario and save and run

Step9: check the status from Operator table and also from your target db table.

ODI - Define Variable and use in a package

Usecase: we will create a projet and map source employee tablw to target employee table. Where we will create a project variable and use them in target table employee name field.

Steps:

Step1: First create the topology physical and logical schema, context

Step2: Create model based on the logical schemas and create datastore for the db tables

Step3: Then create a project and go to mappings and creatw new mapping.

Step4: Drag and drop source and target tables to the map and do automap by name.

Step5: Go to the project variables node and create new variable and provide variable name and default value as "Sri" and save.

Step6: Go to the map and select target table employee name and edit expression and add projext variable from the ODI objects sections and apply and save.

Step7: Go to thr physical tab of mapping and specify the optimization context like HR etc  save.

Step8: Go to the logical section of map and select default and provide LKM: LKM SQL to SQL.

Target table IKM : IKM SQL control append

Flow control: False as dont have primary key.

Save.

Step9: Go to project packages and create new package like PKG_EMP_Variable

Drag and drop created map to the package.

Drag and drop created variable to the package.

Link variable step to map

Select variable and go to general tab and mention step name and type as declare variable.

Note: set variable is used to use fixed data.

Save. Run and ok.

Now suppose you want to use case when and use multiple variable logic in expression.

Then create the variables required and edit expression and add the logic.

Link them in package and use declare variable as type.

ODI - Types of variables and uses

Variable:

• A variable is an object that stores a single value. This can be string  number or date etc. The value is stored in ODI and can be updated at runtime.
• A variable can be created as a global or in a project. Global variables can be used in all projects where as project variables can only be used within the project in which they are defined.
• The value of a variable can be updated from the result of a query executed on a logical schema. For example, it can retrieve current date and time froma  database.

Types of variable:

Declare variable:

When a variable is used in a package, it si strongly recommended that you insert a declare variable step in the package. This step explicitly declares the variable in the package.

Refresh variable:

This variable step refreshes the variable by running the query or command specified in the variable definition.

Set variable:

Assign - It sets the current value of a variable

Increment - increases or decreases a numeric value by the specified amount.

Evaluate variable:

This variable step type compares the value of a variable with a given vaule axcording to an operator. If the condition is met, then the evaluation step is true, otherwise it is false, this step allows for branching in package.

Create variable:

Click the variables node in a project  or the Global variables node in the Global Objects.

Right click and new variable

Specify name, data type and default value

If you want the variable's value to be set by a query:

Select the refreshing tab

Select the logical schema where the command will be executed.

Select query to be executed.

To delete a variable:

Click the variable in thr appropriate tree view

Right click and select delete.

Click ok

Variable scope:

A variable can and should be used by explicitly specifying its scope, using syntax 

#GLOBAl.<variable name> for global variables.

#<project code>.<variable name> for project variables

Using the value of the variable:

Variables can be used in all ODI expressions:

• Mapping
• Filters
• Joins
• Constraints  etc.

To substitute the value of the variable into the text of an expressiob , precede its name by the # character.

For instance, you want to use the global variable Month, so use it like #GLOBAL.Month

To insert a variable step in a packege:

For instance , declare variable

• Open the package editor and go to the diagram tab
• In the designer navigator, select the variable to add to the package from the projects tree for a project variable or from global sections tree for a global variables.
• Drag and drop the variable into the diagram. A variable step appears 
• Click on the step icon in the diagram. The properties panel opens 
• In thr general tab, fill in the step name field and select step type as declare variable.
• From the file menu, click save.

ODI - creating Scenario under package

We can create scenarios under package, procedure and interface.

Here, we will create a scenario under a package.

UseCase: Create a map between a source and target db table and use them in a package and create a scenario and execute.

Steps:

Step1: Connect to your odi mastet and work repository

Step2: Project - new mapping - MAP_SCENARIO ok

From model drag and drop source and target tables in the logical schema section.

Do auto map by name.

• Region_id to region_id
• Name to name

Save

Step3: Go to Physical tab and select default and Loading knowledge module as "LKM SQL to SQL".

Select target table and IKM as "IKM SQL Contol append".

Flow control = False as we dont have any primary key.

Save run ok

Step4: Go to operator tab -  check date - today

Step5: Package - new package - PKG_Scenario

Choose map_scenario into the package.

Run ok

Step6: Click on PKG_SCENARIO and generate scenario

Providr package name PKG_Scenario and version 001

Ok

It will show under scenarios and package

Step7: Right click on pkg_scenario version 001 and run

Check operator tab for execution status

Note: Suppose now we add some filter in the map and edit source table field "Region_id" as Default.REGIONS.region_id in (1,2) for region id

Save and run the scenario again but the change will not refelect . 

Create another version 002 scenario and run . This time the change will reflect.

ODI - MobaXterm and connect to private subnet using jumpbox

 What is MobaXterm:

• MobaXterm is ultimate and poweful toolbox for remote computing. In a single Windows application, it provides loads of functions that are tailored for programmers, webmasters, IT administrators and pretty much all users who need to handle their remote jobs in a more simple fashion.
• MobaXterm provides all the important remote network tools (SSH, X11, RDP, VNC, FTP, MOSH, ...) and Unix commands (bash, ls, cat, sed, grep, awk, rsync, ...) to Windows desktop, in a single portable exe file which works out of the box. 

You can choose to create a new SSH, Telnet, Rlogin, RDP, VNC, XDMCP, FTP, SFTP, Mosh oe seria session:

For more details:

https://mobaxterm.mobatek.net/

How to connnect a private subnet using jumpbox:

 Step1: Click on Session and VNC

Step2: Provide remote private subnet host or ip and port details and click Network setting and click ssh gateway

Step3: provide jump box or gateway host details.

Host , port, username, use ssh key(private key)

Step4: go to bookmark setting and provide a session name and ok

Step5: provide VNC password

Note: to get access to the private subnet or jumpbox, you have do the following steps to the hosts:

Login to that host 

Sudo su - userid and ls -la

Cd ./ssh

Vi authrization_keys and add your public key here and save.

You can generate private and public key pairs with puttygen app.

ODI - mapping - hardcode data and case when in expression

UseCase: Here, we will have two tables. One source table has 2 columns Train id and name and 2nd target table has 3 columbs train id   name and city. We will hardcode city values using hardcode expression and case when expression.

Implementation Steps:

Step1: Project - mappings - new mappings - name as int_hardcode_data

Step2: Open the logical tab of mapping and drag and drop source and target tables. Map the train id and name.

Step3: Select city and open properties tab and write in the expression section: 'London'

Step4: Go to the physical tab and select IKM as IKM SQL insert default.

Select default and select LKM: LKM SQL to SQL

Step5: Save and run with simulation ok. Check the script.

Step6: Run now without simulation ok. Go to the operator tab and check Date today

Step7: Check your target database for records.

For case when:

Step1: Go to the logical tab of mapping and edit the city expression

Write:

Case 

When deafult:Regions.region_id =1 then 'London'

When deafult:Regions.region_id =2 then 'New York'

When deafult:Regions.region_id =3 then 'Tokyo'

When deafult:Regions.region_id =4 then 'Dubai'

END

Save and run again.