Declarative Design

Conventional ETL

Consider, for example, a common case in which sales figures must be summed over time for different customer age groups. The sales data comes from a sales management database, and age groups are described in an age distribution file. In order to combine these sources and then insert and update appropriate records in the customer statistics systems, you must design each step, which includes

1. Load the customer sales data in the engine

2. Load the age distribution file in the engine

3. Perform a lookup between the customer sales data and the age distribution data

4. Aggregate the customer sales grouped by age distribution

5. Load the target sales statistics data into the engine

6. Determine what needs to be inserted or updated by comparing aggregated information with the data from the statistics system

7. Insert new records into the target

8. Update existing records into the target

Conventional ELT

With declarative design, you just need to design what the process does, without describing how it will be done.

In our example, what the process does is

• Relate the customer age from the sales application to the age groups from the statistical file

• Aggregate customer sales by age groups to load sales statistics 

LookUp Concept in ODI 11g

Scenario:
We need to populate manager for an employee using ODI.
In employees table we will have manager id and we need to look that id again with employee table to get name of the manager.

Approach:
1.Creating an interface
2.Creating lookup in an interface

Pre-requisites:
1.You need to know how to create the logical and physical schema.
2.You should aware of the creating project
3.You should aware of how to create file datastore and employee datastore as well.

Execution:
1.Creating an interface
    (a.)Expand the project folder
               go to the First Folder and expand it
                    right click on the interface
                          Create new interface
     (b.)Drag and drop the Employee datastore from model to the source side of an interface
     (c.)Drag and drop the Employee_Manager_list datastore (file datastore) from model to the target side of an interface.
 2.Click on

            Then it will show some pop up for us

Expand your model where your employee datastore available and click next.

Select Manager Id and Employee Id and click on the Join Button .
Lookup condition you observe as follows
EMPLOYEES.MANAGER_ID=EMPLOYEES1.EMPLOYEE_ID
and click finish.

Finally interface looks as follows:

Output:

Post your comments and your questions if you have any.

Looping in ODI

Scenario:
Generate separate file for each department with list of Employees.

Approach:
1.Create an interface and generate scenario
2.Create procedure for looping for each deparment

Pre-requisites:
1.You need to know how to create the logical and Physical Schema
2.You should aware of the creating project
3.You should aware of how to create model

Execution
1.Creating interface
 (a.)Expand your project and then
              expand the first folder
                     Right click on interface and create new interface
     
 (b.)Enter
              Name : Whatever you want
              Select the Logical schema and context
 (c.)Go to Mapping tab
              Drag and drop the Employees datastore and Departments datastore from Model in Source side.
              Drag and drop the FileDatastore in Target side

        Filter condition Should be department id.
       (DEPARTMENTS.DEPARTMENT_ID = #dept_no)
         
(Note : you can get these tables from HR Schema)
 (d.)Create variable for store dept_no
             Right click on variable
                    Create new variable
                         Name: dept_no

 (e.)Create file datastore as follows

For Resource name we mentioned : Employee_List_#dept_no.csv
Why we are mentioning #variable name at the end means we need to generate different files for different department.

 (f.)Make sure that you need to select corresponding knowledge module
IKM SQL to File Append

(g.)Generate Scenario for the interface
      Right click on interface and select generate Scenario
           It pop up as follows

         Click ok.
2.
(a.)Create the procedure to call the scenario
     Right click on procedure
              Create new Procedure as follows
                 

    Command on target 
    Technology : ODI Tools
  Command:
             OdiStartScen "-SCEN_NAME=EMPLOYEE_LIST" "-SCEN_VERSION=001" "-BLOG.dep_no=#deptNo"
     Command on Source
   Technology : Oracle
   Schema : <logical_schema>

Command:
 SELECT DEPARTMENT_ID  deptNo FROM DEPARTMENTS

here we are passing department id from source to target.
That means we are assigning the value to that particular variable.

3.Create a package
          Declare the variable and procedure drag and drop into that package.
 Package looks as follows:

4.Execute it.

Output: