#informatica classes

Introduction to Informatica PowerCenter Client Quontra Solutions offer online and In-class IT Training programs with Placement and Project assistance in different platforms with real time industry consultants to provide Quality Training for all IT Professionals, Corporate clients and Students etc.

Topics: Introduction to Informatica PowerCenter Client components, Designer, Workflow Manager, Workflow Monitor and Repository Manager.

This Informatica Tutorials for beginners provide self explanatory and easy to follow steps and complete the training with enterprise ready experience.

For the complete list of training Sessions, Training Manual and User Guide, Please visit Our Website For more information

http://quontrasolutions.co.uk/

http://www.quontrasolutions.co.uk/sel…

Assume that we have a flat file with header row, footer row and detail rows. Now Lets see how to load header row into one table, footer row into other table and detail rows into another table just by using the transformations only.

First pass the data from source qualifier to an expression transformation. In the expression transformation assign unique number to each row (assume exp_count port). After that pass the data from expression to aggregator. In the aggregator transformation don’t check any group by port. So that the aggregator will provide last row as the default output (assume agg_count port).

Q1) I have a flat file, want to reverse the contents of the flat file which means the first record should come as last record and last record should come as first record and load into the target file.

As an example consider the source flat file data as

Informatica Enterprise Solution Informatica Power center Informatica Power exchange Informatica Data quality

The target flat file data should look as

Informatica Data quality Informatica Power exchange Informatica Power center Informatica Enterprise Solution

Solution:

Follow the below steps for creating the mapping logic

Create a new mapping.

Drag the flat file source into the mapping.

Create an expression transformation and drag the ports of source qualifier transformation into the expression transformation.

Create the below additional ports in the expression transformation and assign the corresponding expressions

Variable port: v_count = v_count+1 Output port o_count = v_count

Now create a sorter transformation and drag the ports of expression transformation into it.

In the sorter transformation specify the sort key as o_count and sort order as DESCENDING.

Drag the target definition into the mapping and connect the ports of sorter transformation to the target.

Q2) Load the header record of the flat file into first target, footer record into second target and the remaining records into the third target.

The solution to this problem I have already posted by using aggregator and joiner. Now we will see how to implement this by reversing the contents of the file.

Solution:

Connect the source qualifier transformation to the expression transformation. In the expression transformation create the additional ports as mentioned above.

Connect the expression transformation to a router. In the router transformation create an output group and specify the group condition as o_count=1. Connect this output group to a target and the default group to sorter transformation.

Sort the data in descending order on o_count port.

Connect the output of sorter transformation to expression transformation (don’t connect o_count port).

Again in the expression transformation create the same additional ports mentioned above.

Informatica 8.x or later versions provides a feature for generating the target files dynamically. This feature allows you to

Create a new file for every session run

create a new file for each transaction.

Informatica provides a special port,”FileName” in the Target file definition. This port you have to add explicitly. See the below diagram for adding the “FileName” port.

Go to the Target Designer or Warehouse builder and edit the file definition. You have to click on the button indicated in red color circle to add the special port.

Now we will see some informatica mapping examples for creating the target file name dynamically and load the data.

1. Generate a new file for every session run.

Whenever the session runs you need to create a new file dynamically and load the source data into that file. To do this just follow the below steps:

STEP1: Connect the source qualifier to an expression transformation. In the expression transformation create an output port (call it as File_Name) and assign the expression as ‘EMP_’||to_char(sessstarttime, ‘YYYYMMDDHH24MISS’)||’.dat’

STPE2: Now connect the expression transformation to the target and connect eh File_Name port of expression transformation to the FileName port of the target file definition.

STEP3: Create a workflow and run the workflow.

Here I have used sessstarttime, as it is constant throughout the session run. If you have used sysdate, a new file will be created whenever a new transaction occurs in the session run.

The target file names created would look like EMP_20120101125040.dat.

2. Create a new file for every session run. The file name should contain suffix as numbers (EMP_n.dat)

In the above mapping scenario, the target flat file name contains the suffix as ‘timestamp.dat’. Here we have to create the suffix as a number. So, the file names should looks as EMP_1.dat, EMP_2.dat and so on. Follow the below steps:

STPE1: Go the mappings parameters and variables -> Create a new variable, $$COUNT_VAR and its data type should be Integer

STPE2: Connect the source Qualifier to the expression transformation. In the expression transformation create the following new ports and assign the expressions.

v_count (variable port) = v_count+1 v_file_count (variable port) = IIF(v_count = 1, SETVARIABLE($$COUNT_VAR,$$COUNT_VAR+1),$$COUNT_VAR) o_file_name (output port) = 'EMP_'||v_file_count||'.dat'

STEP3: Now connect the expression transformation to the target and connect the o_file_name port of expression transformation to the FileName port of the target.

3. Create a new file once a day.

You can create a new file only once in a day and can run the session multiple times in the day to load the data. You can either overwrite the file or append the new data.

This is similar to the first problem. Just change the expression in expression transformation to ‘EMP_’||to_char(sessstarttime, ‘YYYYMMDD’)||’.dat’. To avoid overwriting the file, use Append If Exists option in the session properties.

4. Create a flat file based on the values in a port.

You can create a new file for each distinct values in a port. As an example consider the employees table as the source. I want to create a file for each department id and load the appropriate data into the files.

STEP1: Sort the data on department_id. You can either use the source qualifier or sorter transformation to sort the data.

STEP2: Connect to the expression transformation. In the expression transformation create the below ports and assign expressions.

v_curr_dept_id (variable port) = dept_id v_flag (variable port) = IIF(v_curr_dept_id=v_prev_dept_id,0,1) v_prev_dept_id (variable port) = dept_id o_flag (output port) = v_flag o_file_name (output port) = dept_id||'.dat'

STEP4: Now connect the expression transformation to the transaction control transformation and specify the transaction control condition as

IIF(o_flag = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

Introduction to Informatica PowerCenter Client

Quontra Solutions offer online and In-class IT Training programs with Placement and Project assistance in different platforms with real time industry consultants to provide Quality Training for all IT Professionals, Corporate clients and Students etc.

Topics: Introduction to Informatica PowerCenter Client components, Designer, Workflow Manager, Workflow Monitor and Repository Manager.

This Informatica Tutorials for beginners provide self explanatory and easy to follow steps and complete the training with enterprise ready experience.

For the complete list of training Sessions, Training Manual and User Guide, Please visit Our Website For more information

http://quontrasolutions.co.uk/

http://www.quontrasolutions.co.uk/sel…