ODI - About Load plans

ODI is used to populate very large data warehouses and its common to have thousands of tables using hundreds of scenarios. The execution of these scenarios has to be organized in such that the data throughout from the sources to the target is the most efficient within the batch window. Load plans help the user to organize the execution of scenarios in a hieracrchy of sequential and parallel steps.

The leaf nodes of this hierarchy are scenarios, packages, mappings, variables, and procedures can be added to load plans for execution in the form of scenarios.

Load plans also support exception handling strategies in the event of a scenario ending in error.

Load plans can be started , stopped and restarted from a command line , from ODI studio. They can also be scheduled using the runtime agent's builtin scheduler or an external scheduler.

A load plan can be modified in prod environment and steps can be enabled and disabled accoeding to the prod needs.

Load plans appear in Design Navigator and in Operator in the load plans and scenarios accordion. The load plan runs are displayed in the load plan execution accordion in Operator Navigator.

When running or scheduling a load plan you provide the variable values, the contexts and logical agents used for this load plan execution.

Load plans can not be added to a load plan, however it is possible to add scenarios in form of a run scenarios step that starts another load plan using the OdiStartLoadPlan tool.

Different types of load plan steps:

A load plan is made of a several types of steps. Each step can contain several child steps. By default, a load plan contains an empty root serial step.

Serial step: a child steps is executed when the prevoius one is terminated.

Parallel step: child steps are started immediately in their order of priority.

Run scenario step: Launches the execution of a scenario.

Case step, when step, Else step: the combination of these steps allows conditional branching on the value of a variable.

Exception Step: defines a grouo of steps that is executed when an exccption is encountered in the associated step from the step hierarchy.

For more detailed information, click here

https://docs.oracle.com/middleware/1212/odi/ODIDG/loadplans.htm#ODIDG1349

ODI - Join and filter components

A join is a selector component that creates a join between multiple flows 

Implementation Steps:

Step1: Create physical and logical schemas in topology and context.

Step2: create models on logical schemas in design.

Step3: create a map and drag and drop jobs and job history tables as source in the default. Join them. And provide the join condition.

Step3: drag and drop target table and map them.

Join condition: job id from both source table.

Step4: add a filter on employee id on job history table 

Filter condition used as below.

Step5: Save and run and check operator tab and your target tables.

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.