Sunday, August 28, 2016
Sunday, June 26, 2016
Saturday, November 14, 2015
Friday, November 13, 2015
Sunday, November 8, 2015
Monday, November 2, 2015
Stored Procedure Transformation In Informatica Part1
Stored Procedure Transformation In Informatica
Introduction
As the name suggests is used to execute stored procedures through Informatica
ETL
It can also be used to call functions to return calculated values.
The Stored Procedures that are to be executed should be pre-built on the
database which can be connected through Informatica.
This transformation provided by Informatica though rarely used, can prove to be a
major stress buster at times of performance bottleneck due to major calculations.
}Stored Procedure Transformation is a passive transformation.
}Stored procedure transformation can be used in both connected and
unconnected mode.
unconnected mode.
When to use Stored Procedure Transformation
}Complex calculations where Database Procedures perform better than using
multiple transformations in Informatica.
}Re-usability of the Complex procedures in different ETLs in the form of Stored
Procedure transformation.
}Breaking of complex calculations into multiple Stored Procedures and using
those as Stored Procedure transformation in the same ETL. The output of one SP
transformation can act as the input to the other.
When to use Stored Procedure Transformation
}Check the status of a target database before loading data into it.
}Determine if enough space exists in a database.
}Perform a specialized calculation.
}Dropping and recreating indexes.
Transformation Type
}Stored Procedure transformation is a passive transformation and like our Lookup
transformation - can be either of connected or unconnected type.
Connected SP transformation
}When the SP transformation exists along the line of data flow, it is called a
Connected SP transformation.
}The Stored Procedure type for a Connected SP Transformation is always set to
‘Normal’. This means that the Procedure would be called for every row processed.
Unconnected SP transformation
}This type of SP transformation does not exist along the pipeline of the mapping
and can be called as and when required.
}There are different scenarios of using an Unconnected SP transformation. A few
of them are given below:
}Conditional execution of a Stored Procedure
}Execution of SP which does not have any input or output variable
Types:
}Calling of Stored Procedures during different times of session execution
◦Source Pre Load: Runs before the session reads data from the source. Useful for verifying the existence of tables or performing joins of data in a temporary table.
◦Source Post Load: Runs after reading data from the source. Useful for removing temporary tables.
◦Target Pre Load: Runs before the session sends data to the target. This is useful for verifying target tables or disk space on the target system.
◦Target Post Load: Runs after loading data into the target. This is useful for re-creating indexes on the database.
}In this case we don’t have to explicitly call the procedure in any expression or
other transformation. The procedure would be executed according to the
specified type.
specified type.
}Normal: The stored procedure transformation runs for each row passed in the
mapping. This is useful when running a calculation against an input port.
Connected stored procedures run only in normal mode.
}Once we specify any of the other Stored Procedure Type other than ‘Normal’, the
‘Call Text’ Attribute in the Properties tab gets enabled. Here we have to specify
how the procedure has to be called
How to call procedure residing in another schema:
}There is a scenario where our Informatica connects to the Target database
through a user ‘USER_INFA’ and the procedure that we have to execute exists in
another schema ‘EMP’.
}Now in such a case we can use our SP transformation to execute the procedure
by making a very simple change.
}We have to prefix the procedure call with the schema name in the Call text
attribute provided the user has execute privilege on the object.
}Executing Multiple sprocs one after other:
}If the Stored procedure type is any of the available four types other than
‘Normal’, we have to use the Mapping Property – Stored Procedure Plan.
}If the Stored Procedure Type is ‘Normal’ then we have to call the procedures through an expression one by one.
Sunday, October 25, 2015
Saturday, October 10, 2015
Monday, September 14, 2015
Monday, September 30, 2013
Sequence Generator in Informatica
Sequence Generator:
The Sequence Generator transformation generates numeric values.
Using this transformation we can create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.
Ports:
The Sequence Generator transformation generates numeric values.
Using this transformation we can create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.
Ports:
The Sequence Generator transformation provides two output ports: NEXTVAL and CURRVAL.
We cannot edit or delete these ports.
Likewise, we cannot add ports to the transformation.
NEXTVAL:
Use the NEXTVAL port to generate sequence numbers by connecting it to a Transformation or target.
For example, we might connect NEXTVAL to two target tables in a mapping to generate unique primary key values.
CURRVAL:
CURRVAL is NEXTVAL plus the Increment By value.
We typically only connect the CURRVAL port when the NEXTVAL port is Already connected to a downstream transformation.
If we connect the CURRVAL port without connecting the NEXTVAL port, the Integration Service passes a constant value for each row.
when we connect the CURRVAL port in a Sequence Generator Transformation, the Integration Service processes one row in each block.
We can optimize performance by connecting only the NEXTVAL port in a Mapping.
Disadvantage:
Using a sequence generator transformation to generate unique primary key values can cause performance issues as an additional transformation is required to process in mapping.
We can use expression transformation to generate surrogate keys in a dimensional table.
Friday, August 23, 2013
Informatica flat file load
This video will explain how to load a flat file using Informatica mapping
Here source is a table in Sql server and loading that table data into a flat file.
Here source is a table in Sql server and loading that table data into a flat file.
Wednesday, May 15, 2013
Updating records using update strategy transformation
UPDATE STRATEGY TRANSFORMATION IN INFORMATICA:
Update strategy transformation is an active and connected transformation. Update strategy transformation is used to insert, update, and delete records in the target table. It can also reject the records without reaching the target table. When you design a target table, you need to decide what data should be stored in the target.
The design of the target table decides how to handle the changes to existing rows. In the informatica, you can set the update strategy at two different levels:
Session Level: Configuring at session level instructs the integration service to either treat all rows in the same way (Insert or update or delete) or use instructions coded in the session mapping to flag for different database operations.
Mapping Level: Use update strategy transformation to flag rows for inert, update, delete or reject.
Flagging Rows in Mapping with Update Strategy:
You have to flag each row for inserting, updating, deleting or rejecting. The constants and their numeric equivalents for each database operation are listed below.
DD_INSERT: Numeric value is 0. Used for flagging the row as Insert.
DD_UPDATE: Numeric value is 1. Used for flagging the row as Update.
DD_DELETE: Numeric value is 2. Used for flagging the row as Delete.
DD_REJECT: Numeric value is 3. Used for flagging the row as Reject.
The integration service treats any other numeric value as an insert.
Updating records using update strategy transformation part1
Updating records using update strategy transformation part2
Update strategy transformation is an active and connected transformation. Update strategy transformation is used to insert, update, and delete records in the target table. It can also reject the records without reaching the target table. When you design a target table, you need to decide what data should be stored in the target.
The design of the target table decides how to handle the changes to existing rows. In the informatica, you can set the update strategy at two different levels:
Session Level: Configuring at session level instructs the integration service to either treat all rows in the same way (Insert or update or delete) or use instructions coded in the session mapping to flag for different database operations.
Mapping Level: Use update strategy transformation to flag rows for inert, update, delete or reject.
Flagging Rows in Mapping with Update Strategy:
You have to flag each row for inserting, updating, deleting or rejecting. The constants and their numeric equivalents for each database operation are listed below.
DD_INSERT: Numeric value is 0. Used for flagging the row as Insert.
DD_UPDATE: Numeric value is 1. Used for flagging the row as Update.
DD_DELETE: Numeric value is 2. Used for flagging the row as Delete.
DD_REJECT: Numeric value is 3. Used for flagging the row as Reject.
The integration service treats any other numeric value as an insert.
Updating records using update strategy transformation part1
Updating records using update strategy transformation part2
Saturday, April 27, 2013
Update records with out update strategy in Informatica
This video will explain how to insert or update a record into a target table with out using update strategy transformation
Draback:
Using update strategy we can inactive an existing record and insert a new record. With the above approach(with out update strategy) we can not inactive an existing record based on an incoming record from source however we can update an existing one and insert a new one
Draback:
Using update strategy we can inactive an existing record and insert a new record. With the above approach(with out update strategy) we can not inactive an existing record based on an incoming record from source however we can update an existing one and insert a new one
Thursday, April 25, 2013
informatica parameter file sample
Informatica parameters and variables are used to minimize hard-coding in ETL, thereby increasing flexibility, reusability, readabilty and avoides rework by changing the values in the ETL
Parameter files provide us with the flexibility to change parameter and variable values every time we run a session or workflow with out touching the mapping
A parameter file contains two sections:
1. Heading section:
It is to identify the integration service, folder, workflow, worklet, session to which the parameters or variables apply
2. Definition Section
This section contains list of parameters and variables with their assigned values
Tuesday, April 23, 2013
Sunday, April 21, 2013
Friday, April 19, 2013
Informatica mapping,session and workflow creation
This video will explain how to create an Informatica mapping, session and workflow. This will also explains an ETL functionality, how to take connections, verifying the session log and bad file.
Subscribe to:
Posts (Atom)