What are different ports in Informatica ?

Input, Output, Variable, Return, Rank, Lookup and Master.

Type 3 Slowly Changing Dimension

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.

In our example, recall we originally have the following table:

Customer Key	Name	State
1001	Christina	Illinois

To accommodate Type 3 Slowly Changing Dimension, we will now have the following columns:

• Customer Key
• Name
• Original State
• Current State
• Effective Date

After Christina moved from Illinois to California, the original information gets updated, and we have the following table (assuming the effective date of change is January 15, 2003):

Customer Key	Name	Original State	Current State	Effective Date
1001	Christina	Illinois	California	15-JAN-2003

Advantages:

- This does not increase the size of the table, since new information is updated.

- This allows us to keep some part of history.

Disadvantages:

- Type 3 will not be able to keep all history where an attribute is changed more than once. For example, if Christina later moves to Texas on December 15, 2003, the California information will be lost.

Usage:

Type 3 is rarely used in actual practice.

When to use Type 3:

Type III 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.

Type 2 Slowly Changing Dimension

In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The newe record gets its own primary key.

In our example, recall we originally have the following table:

Customer Key	Name	State
1001	Christina	Illinois

After Christina moved from Illinois to California, we add the new information as a new row into the table:

Customer Key	Name	State
1001	Christina	Illinois
1005	Christina	California

Advantages:

- This allows us to accurately keep all historical information.

Disadvantages:

- This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.

- This necessarily complicates the ETL process.

Usage:

About 50% of the time.

When to use Type 2:

Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.

Type 1 Slowly Changing Dimension

In Type 1 Slowly Changing Dimension, the new information simply overwrites the original information. In other words, no history is kept.

In our example, recall we originally have the following table:

Customer Key	Name	State
1001	Christina	Illinois

After Christina moved from Illinois to California, the new information replaces the new record, and we have the following table:

Customer Key	Name	State
1001	Christina	California

Advantages:

- This is the easiest way to handle the Slowly Changing Dimension problem, since there is no need to keep track of the old information.

Disadvantages:

-       All history is lost. By applying this methodology, it is not possible to trace back in history. For example, in this case, the company would not be able to know that Christina lived in Illinois before.

-       Usage:

About 50% of the time.

When to use Type 1:

Type 1 slowly changing dimension should be used when it is not necessary for the data warehouse to keep track of historical changes.

Kindle, Wi-Fi, Graphite, 6" Display with New E Ink Pearl Technology - includes Special Offers & Sponsored Screensavers

Workflow Execution Process

Pictorial Representation of Workflow execution:

1. A PowerCenter Client request IS to start workflow
2. IS starts ISP
3. ISP consults LB to select node
4. ISP starts DTM in node selected by LB

Integration Service (IS)

The key functions of IS are

• Interpretation of the workflow and mapping metadata from the repository.
• Execution of the instructions in the metadata
• Manages the data from source system to target system within the memory and disk

The main three components of Integration Service which enable data movement are,

• Integration Service Process
• Load Balancer
• Data Transformation Manager 

Integration Service Process (ISP) 

The Integration Service starts one or more Integration Service processes to run and monitor workflows. When we run a workflow, the ISP starts and locks the workflow, runs the workflow tasks, and starts the process to run sessions. The functions of the Integration Service Process are,

• Locks and reads the workflow
• Manages workflow scheduling, ie, maintains session dependency
• Reads the workflow parameter file
• Creates the workflow log
• Runs workflow tasks and evaluates the conditional links
• Starts the DTM process to run the session
• Writes historical run information to the repository
• Sends post-session emails

Load Balancer

The Load Balancer dispatches tasks to achieve optimal performance. It dispatches tasks to a single node or across the nodes in a grid after performing a sequence of steps. Before understanding these steps we have to know about Resources, Resource Provision Thresholds, Dispatch mode and Service levels

• Resources – we can configure the Integration Service to check the resources available on each node and match them with the resources required to run the task. For example, if a session uses an SAP source, the Load Balancer dispatches the session only to nodes where the SAP client is installed
• Three Resource Provision Thresholds, The maximum number of runnable threads waiting for CPU resources on the node called Maximum CPU Run Queue Length. The maximum percentage of virtual memory allocated on the node relative to the total physical memory size called Maximum Memory %. The maximum number of running Session and Command tasks allowed for each Integration Service process running on the node called Maximum Processes
• Three Dispatch mode’s – Round-Robin: The Load Balancer dispatches tasks to available nodes in a round-robin fashion after checking the “Maximum Process” threshold. Metric-based: Checks all the three resource provision thresholds and dispatches tasks in round robin fashion. Adaptive: Checks all the three resource provision thresholds and also ranks nodes according to current CPU availability
• Service Levels establishes priority among tasks that are waiting to be dispatched, the three components of service levels are Name, Dispatch Priority and Maximum dispatch wait time. “Maximum dispatch wait time” is the amount of time a task can wait in queue and this ensures no task waits forever

 A .Dispatching Tasks on a node

1. The Load Balancer checks different resource provision thresholds on the node depending on the Dispatch mode set. If dispatching the task causes any threshold to be exceeded, the Load Balancer places the task in the dispatch queue, and it dispatches the task later
2. The Load Balancer dispatches all tasks to the node that runs the master Integration Service process

B. Dispatching Tasks on a grid,

1. The Load Balancer verifies which nodes are currently running and enabled
2. The Load Balancer identifies nodes that have the PowerCenter resources required by the tasks in the workflow
3. The Load Balancer verifies that the resource provision thresholds on each candidate node are not exceeded. If dispatching the task causes a threshold to be exceeded, the Load Balancer places the task in the dispatch queue, and it dispatches the task later
4. The Load Balancer selects a node based on the dispatch mode 

Data Transformation Manager (DTM) Process

When the workflow reaches a session, the Integration Service Process starts the DTM process. The DTM is the process associated with the session task. The DTM process performs the following tasks:

• Retrieves and validates session information from the repository.
• Validates source and target code pages.
• Verifies connection object permissions.
• Performs pushdown optimization when the session is configured for pushdown optimization.
• Adds partitions to the session when the session is configured for dynamic partitioning.
• Expands the service process variables, session parameters, and mapping variables and parameters.
• Creates the session log.
• Runs pre-session shell commands, stored procedures, and SQL.
• Sends a request to start worker DTM processes on other nodes when the session is configured to run on a grid.
• Creates and runs mapping, reader, writer, and transformation threads to extract, transform, and load data
• Runs post-session stored procedures, SQL, and shell commands and sends post-session email

After the session is complete, reports execution result to ISP

Difference between 7.x and 8.x --- Part2

Below are the difference between 7.1 and 8.1 of infa..

 1) powercenter connect for sap netweaver bw option

 2) sql transformation is added

 3) service oriented architecture

 4) grid concept is additional feature

 5) random file name can genaratation in target

 6) command line programms: infacmd and infasetup new commands were added.

 7) java transformation is added feature

 8)concurrent cache creation and faster index building are additional feature in lookup  

     transformation.

 9) caches or automatic u dont need to allocate at transformation level

 10) push down optimization techniques,some

11) we can append data into the flat file target.

12)Dynamic file names we can generate  in informatica 8

13)flat file names we can populate to target while processing through list file .

14)For Falt files header and footer we can populate  using advanced options in 8 at session level.

15) GRID option at session level

Effective in version 8.0, you create and configure a grid in the Administration Console. You configure a grid to run on multiple nodes, and you configure one Integration Service to run on the grid. The Integration Service runs processes on the nodes in the grid to distribute workflows and sessions. In addition to running a workflow on a grid, you can now run a session on a grid. When you run a session or workflow on a grid, one service process runs on each available node in the grid.

Difference between 7.x and 8.x

Power Center 7.X Architecture.

Power Center 8.X Architecture.

Developer Changes:

For example, in PowerCenter:

•         PowerCenter Server has become a service, the Integration Service

•         No more Repository Server, but PowerCenter includes a Repository Service

•         Client applications are the same, but work on top of the new services framework

Parameters and Variables

Parameter file it will supply the values to session level variables and mapping level variables.

Variables are of two types:

·        Session level variables

·        Mapping level variables

Session level variables are of four types:

·        $DBConnection_Source

·        $DBConnection_Target

·        $InputFile

·        $OutputFile

Mapping level variables are of two types:

·        Variable

·        Parameter

What is the difference between mapping level and session level variables?

Mapping level variables always starts with $$.

A session level variable always starts with $.

Flat File

Flat file is a collection of data in a file in the specific format.

Informatica can support two types of files

·        Delimiter

·        Fixed Width

In delimiter we need to specify the separator.

In fixed width we need to known about the format first. Means how many character to read for particular column.

In delimiter also it is necessary to know about the structure of the delimiter. Because to know about the headers.

If the file contains the header then in definition we need to skip the first row.

List file:

If you want to process multiple files with same structure. We don’t need multiple mapping and multiple sessions.

We can use one mapping one session using list file option.

First we need to create the list file for all the files. Then we can use this file in the main mapping.

Informatica Basics

System Variables:

$$$SessStartTime returns the initial system date value on the machine hosting the Integration Service when the server initializes a session. $$$SessStartTime returns the session start time as a string value. The format of the string depends on the database you are using.

Session: A session is a set of instructions that tells informatica Server how to move data from sources to targets.

WorkFlow: A workflow is a set of instructions that tells Informatica Server how to execute tasks such as sessions, email notifications and commands. In a workflow multiple sessions can be included to run in parallel or sequential manner.

Source Definition: The Source Definition is used to logically represent database table or Flat files.

Target Definition: The Target Definition is used to logically represent a database table or file in the Data Warehouse / Data Mart.

Aggregator: The Aggregator transformation is used to perform Aggregate calculations on group basis.

Expression: The Expression transformation is used to perform the arithmetic calculation on row by row basis and also used to convert string to integer vis and concatenate two columns.

Filter: The Filter transformation is used to filter the data based on single condition and pass through next transformation.

Router: The router transformation is used to route the data based on multiple conditions and pass through next transformations.

It has three groups

1) Input group

2) User defined group

3) Default group

Joiner: The Joiner transformation is used to join two sources residing in different databases or different locations like flat file and oracle sources or two relational tables existing in different databases.

Source Qualifier: The Source Qualifier transformation is used to describe in SQL the method by which data is to be retrieved from a source application system and also

 used to join two relational sources residing in same databases.

Informatica Transformations

Mapping: Mapping is the Informatica Object which contains set of transformations including source and target. Its look like pipeline.

Mapplet:

Mapplet is a set of reusable transformations. We can use this mapplet in any mapping within the Folder.

A mapplet can be active or passive depending on the transformations in the mapplet. Active mapplets contain one or more active transformations. Passive mapplets contain only passive transformations.

When you add transformations to a mapplet, keep the following restrictions in mind:

• If you use a Sequence Generator transformation, you must use a reusable Sequence Generator transformation.
• If you use a Stored Procedure transformation, you must configure the Stored Procedure Type to be Normal.
• You cannot include the following objects in a mapplet:
• Normalizer transformations
• COBOL sources
• XML Source Qualifier transformations
• XML sources
• Target definitions
• Other mapplets
• The mapplet contains Input transformations and/or source definitions with at least one port connected to a transformation in the mapplet.
• The mapplet contains at least one Output transformation with at least one port connected to a transformation in the mapplet.

Input Transformation: Input transformations are used to create a logical interface to a mapplet in order to allow data to pass into the mapplet.

Output Transformation: Output transformations are used to create a logical interface from a mapplet in order to allow data to pass out of a mapplet.

Informatica is a powerful Extraction, Transformation, and Loading tool and is been deployed for data warehouse development in the Business Intelligence Team. Informatica comes with the following clients to perform various tasks.

·        Designer – used to develop transformations/mappings

·        Workflow Manager / Workflow Monitor replace the Server Manager - used to create sessions / workflows/ worklets to run, schedule, and monitor mappings for data movement

·        Repository Manager – used to maintain folders, users, permissions, locks, and repositories.

·        Integration Services – the “workhorse” of the domain. Informatica Server is the component responsible for the actual work of moving data according to the mappings developed and placed into operation. It contains several distinct parts such as the Load Manager, Data Transformation Manager, Reader and Writer.

·        Repository Services-  Informatica client tools and  Informatica Server connect to the repository database over the network through the Repository Server.