Dynamic Lookup Cache Scenario in Informatica

There may be cases where we need a dynamic lookup instead of static lookup. The most frequent scenario will be when the source data contains duplicate records or if you are loading same data from multiple sources in the same mapping. In such cases if we use static lookup, then duplicate data would get inserted into the target. A dynamic lookup cache will be updating the cache for any changes and hence the data loading will be correct.

Once the lookup is configured as dynamic cache, Informatica adds one extra port to the lookup: NewLookupRow It can have 3 values - 0,1, or 2 

While using a dynamic lookup cache, we must associate each lookup/output port with an input/output port or a sequence ID. The Integration Service uses the data in the associated port to insert or update rows in the lookup cache. The Designer associates the input/output ports with the lookup/output ports used in the lookup condition.

Looping of Workflows in Informatica

Scenario:

You have 3 Oracle source schemas: test_schema1, test_schema2 and test_schema3. Each schema contains 3 tables EMP, DEPT and MANUFACTURERS. The structure of tables is exactly same in each schema. You have a target schema test_schema_target which contains 3 tables: EMP_TARGET,DEPT_TARGET and MANUFACTURERS_TARGET. The stucture of tables is exactly similar as those to source schemas.

Now you have to provide an Informatica solution wherein the the tables in target schema are loaded from corresponding tables in source schema dynamically. i.e there should be only 1 set of mappings for each loading and this set should be called 3 times(since there are 3 source schemas). Everytime the schema name should should dynamically change.

Solution:

Assumption: The user in target schema has the access to tables in all 3 source schemas.

Create a control table as follows:

Build a mapping (m_par) which will select 1 row from the control table as follows:

The port v_Schema_Name will be set as SETVARIABLE($$schema_name,SCHEMA_NAME)

where $$schema_name is a mapping parameter. The source qualifier query is as follows:


SELECT
CONTROL_TABLE.SCHEMA_NAME,
CONTROL_TABLE.FLAG
FROM
 CONTROL_TABLE
WHERE
 CONTROL_TABLE.FLAG='N'
and rownum=1


The idea here is, to assign the value of schema name returned from above query to the mapping parameter $$schema_name. This mapping parameter will then be assigned to a workflow variable. This workflow variable then will be assigned to the mapping parameters of EMP, DEPT and MANUFACTURERS mappings. The flag will be updated for each successful mapping and the workflow will be called again using a CMD task. This whole process is explained below:

Now, build a mapping for each target: EMP_TARGET, DEPT_TARGET and MANUFACTURERS_TARGET.

The mapping screen shot for EMP is shown below:

The source qualifier query will be as follows:


SELECT
$$schema_name_e.EMP.EMPNO, $$schema_name_e.EMP.ENAME,
$$schema_name_e.EMP.JOB,
$$schema_name_e.EMP.MGR, $$schema_name_e.EMP.HIREDATE, $$schema_name_e.EMP.SAL,
$$schema_name_e.EMP.COMM, $$schema_name_e.EMP.DEPTNO
FROM
$$schema_name_e.EMP

Here $$schema_name_e is a mapping parameter defined in this mapping

There will be a Post-SQL on EMP_TARGET as follows:

update CONTROL_TABLE set FLAG='Y' where CONTROL_TABLE.SCHEMA_NAME='$$schema_name_e' and CONTROL_TABLE.TABLE_NAME='EMP'

Similar mapping will be created for DEPT and MANUFACTURERS also. these mappings will also have mapping parameters as $$schema_name_d and $$schema_name_m and will have the similar Source qualifier and Post-SQL in target queries.

The workflow (wf_child)  screen shot is shown below:

Here s_par is the session pointing to Control table mapping (m_par).
s_emp is pointing to EMP mapping.
s_dept is pointing to DEPT mapping.
s_manufacturers is pointing to MANUFACTURERS mapping.

In the properties of the workflow; create a wf variable as shown:

Now within the workflow, doble click on session s_par and go to Components tab:

Click on "Post session on Success Variable assignment" and assign the value $$Schema_name to $$schema_name1.

This effectively assigns the schema name extracted from control table to the workflow variable.

Create a decision task (dec_count) as $s_par.SrcSuccessRows>0

So, if there is any row pending in control_table, this workflow will proceed.

After dec_count place the sessions s_emp, s_dept and s_manufacturers in parallell. Double click on s_emp and go to components.

Set the "Pre-session variable assignment" as follows:

This effectively assigns the value of Workflow variable(derive in the previous step) to the mapping parameter
$$schema_name_e before the actual run of this session. As a result the source qualifier query will execute with this parameter value.

Repeat this exactly for s_manufacturers and s_dept to calculate the values of $$schema_name_m and $$schema_name_d.

There will be a final command task in this workflow cmd2 with the command as

pmcmd startworkflow -sv Local_IS -d "domain_name" -u "user_name" -p password -f "folder_name" wf_Parent

This calls another workflow wf_Parent(screen shot below) which in turn calls wf_Child.

The command task s_call contains a call to child workflow:

pmcmd startworkflow -sv Local_IS -d "domain_name" -u "user_name" -p password -f "folder_name" wf_Child

For both the workflows, set the property "Configure concurrent execution" as "Enabled"

The control table will keep on updating the value of FLAG column as the workflow runs for particular schema and table. After all schemas and tables are covered, the FLAG column will be Y for all rows and the loop will stop:

In this way, the Informatica workflow will run in a loop loading tables in different schemas.

Hope this helps.........

New Features of Informatica 9.0

Following are some new features introduced in Informatica 9.0 :

1.  New Client tools:

Informatica 9 includes the Informatica Developer and Informatica Analyst client tools.

The Informatica Developer tool is eclipse-based and supports both data integration and data quality for enhanced productivity. The Informatica Analyst tool is a browser-based tool for analysts, stewards and line of business managers.  This tool supports data profiling, specifying and validating rules (Scorecards), and monitoring data quality.

2. Informatica Administrator:

The PowerCenter Administration Console has been renamed the Informatica Administrator.

The Informatica Administrator is now a core service in the Informatica Domain that is used to configure and manage all Informatica Services, Security and other domain objects (such as connections) used by the new services.

The number of objects stored in the domain has increased to accommodate these new requirements.

3.  Session Log size:

You can limit the size of session logs for real-time sessions. You can limit the size by time or by file size. You can also limit the number of log files for a session.

4. Lookup Transformation:

  > Cache updates

     You can update the lookup cache based on the results of an expression. When an expression is true, you can add to or update the lookup cache. You can update the dynamic lookup cache with the results of an expression.

  > Database deadlock resilience

       

      In previous releases, when the Integration Service encountered a database deadlock during a lookup, the session failed. Effective in 9.0, the session will not fail. When a deadlock occurs, the Integration Service attempts to run the last statement in a lookup. You can configure the number of retry attempts and time period between attempts. 

  > Multiple rows return. 

     You can configure the Lookup transformation to return all rows that match a lookup condition. A Lookup transformation is an active transformation when it can return more than one row for any given input row.

 > SQL overrides for uncached lookups

   In previous versions you could create a SQL override for cached lookups only. You can create an SQL override for uncached lookup. You can include lookup ports in the SQL query.

DAC Parameters for Informatica Workflows

The main location for the parameters for DAC are in the 2 files located in $DAC_HOME/Informatica/parameters/input

• - parameterfileOLTP.txt is used for source systems/SDE mappings
• - parameterfileDW.txt is used for DW mappings/SIL and PLP mappings

Another place to define the parameters  is in the task’s Parameters tab in DAC console itself. The parameter values defined in here will overwrite those set in the text files.

When DAC starts to execute the task, it creates the parameter file needed for each workflow in Informatica on the fly based on these definitions. DAC changes the format of the file suitable for Informatica including changing the command name into actual Informatica session name [FolderName.SessionName]. It also adds common DAC parameters into each workflow parameter file.

Simple things about Lookup Transformation

Q Define lookup transformation?

A lookup transformation is used to lookup data in a ‘data-pool’. This data-pool may be a flat-file, relational table, view or a synonym. You can also create a lookup definition from a source qualifier. The Integration Service queries the lookup source based on the lookup ports in the transformation and a lookup condition. The Lookup transformation returns the result of the lookup to the target or another transformation.

Lookups are generally used to get a related value, to perform a calculation using the derived related value or to update a slowly changing dimension. 

When you configure a flat file Lookup transformation for sorted input, the condition columns must be grouped. If the condition columns are not grouped, the Integration Service cannot cache the lookup and fails the session. For optimal caching performance, sort the condition columns. The Integration Service always caches flat file and pipeline lookups. If you configure a Lookup transformation to use a dynamic cache, you can use only the equality operator (=) in the lookup condition.

Q What are the differences between connected and unconnected lookups?

1. Connected Lokkup uses a dynamic or static cache while unconnected lookup uses only static cache.

2. Connected lookup can return multiple columns from the same row or insert into the dynamic lookup cache while unconnected lookup returns one column from each row.

3. Connected lookup supports user-defined default values while unconnected lookup does not supports user-defined default values.

Q How can you return multiple ports from an unconnected lookup transformation?

Unconnected lookup transformation returns only 1 port. To return multiple ports, concatenate all those ports in the overwritten lookup query and return the concatenated port. Now separate out those columns in an expression transformation.

Q How can you optimize a lookup transformation?

1. If you have privileges to modify the database containing a lookup table, you can improve lookup initialization time by adding an index to the lookup table.

2. You can improve performance by indexing the columns in the lookup ORDER BY.

3. By default, the Integration Service generates an ORDER BY clause for a cached lookup. The ORDER BY clause contains all lookup ports. To increase performance, you can suppress the default ORDER BY clause and enter an override ORDER BY with fewer columns. Place two dashes ‘--’ as a comment notation after the ORDER BY clause to suppress the ORDER BY clause that the Integration Service generates.

4. If you include more than one lookup condition, place the conditions in the following order to optimize lookup performance:

- Equal to (=)

- Less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=)

- Not equal to (!=)

5. Improve session performance by caching small lookup tables.

6. If the lookup table is on the same database as the source table in the mapping and caching is not feasible, join the tables in the source database rather than using a Lookup transformation.

Informatica Scenario - 5

Previous Scenarios

Scenario: A source table contains emp_name and salary columns. Develop an Informatica mapping to load all  records with 5th highest salary into the target table.

Solution:

The mapping will contain following transformations after the Source Qualifier Transformation:

1. Sorter : It will contain 2 ports - emp_name and salary. The property 'Direction' will be selected as 'Descending' on key 'Salary'

2. Expression transformation: It will 6 ports as follows -
     a> emp_name : It will be an I/O port directly connected from previous sorter transformation
     b> salary_prev : It will be a variable type port. Give any vriable name e.g val in its Expression column
     c> salary : It will be an I/O port directly connected from previous transformation
     d> val : It will be a variable port. The expression column of this port will contain 'salary'
     e> rank: It will be a variable type port. The expression column will contain decode          
                   (salary,salary_prev,rank,rank+1)
     f> rank_o : It will be an output port containg the value of 'rank'.

3. Filter Transformation : It will have 2 I/O ports emp_name and salary with a filter condition rank_o = 5

The ports emp_name and salary from Filter Transformation will be connected to target

Informatica Scenario based Questions and Solutions (Part-1)

Scenario1:

We have a target source table containing 3 columns : Col1, Col2 and Col3. There is only 1 row in the table as follows:

Col1 Col2 Col3
-----------------
  a       b       c

There is target table containg only 1 column Col. Design a mapping so that the target table contains 3 rows as follows:

Col
-----
a
b
c

Solution: Not using a Normalizer transformation:

Create 3 expression transformations exp_1,exp_2 and exp_3 with 1 port each. Connect col1 from Source Qualifier to port in exp_1.Connect col2 from Source Qualifier to port in exp_2.Connect col3 from source qualifier to port in exp_3. Make 3 instances of the target. Connect port from exp_1 to target_1. Connect port from exp_2 to target_2 and connect port from exp_3 to target_3.

Scenario 2:

There is a source table that contains duplicate rows.Design a mapping to load all the unique rows in 1 target while all the duplicate rows (only 1 occurence) in another target.

Solution :

Bring all the columns from source qualifier to an Aggregator transformation. Check group by on the key column. Create a new output port count_col in aggregator transformation and write an expression count(key_column). Make a router transformation with 2 groups:Dup and Non-Dup. Check the router conditions count_col>1 in Dup group while count_col=1 in Non-dup group. Load these 2 groups in different targets.

Scenario 3:

There is a source table containing 2 columns Col1 and Col2 with data as follows:

Col1   Col2
 a          l
 b         p
 a         m
 a         n
 b         q
 x          y

Design a mapping to load a target table with following values from the above mentioned source:

Col1    Col2
  a        l,m,n
  b       p,q
  x        y

Solution:

Use a sorter transformation after the source qualifier to sort the values with col1 as key. Build an expression transformation with following ports(order of ports should also be the same):

1. Col1_prev : It will be a variable type port. Expression should contain a variable e.g val
2. Col1 : It will be Input/Output port from Sorter transformation
3. Col2 : It will be input port from sorter transformation
4. val : It will be a variable type port. Expression should contain Col1
5. Concatenated_value: It will be a variable type port. Expression should be decode(Col1,Col1_prev,Concatenated_value||','||Col2,Col1)
6. Concatenated_Final : It will be an outpur port conating the value of Concatenated_value

After expression, build a Aggregator Transformation. Bring ports Col1 and Concatenated_Final into aggregator. Group by Col1. Don't give any expression. This effectively will return the last row from each group.

Connect the ports Col1 and Concatenated_Final from aggregator to the target table.

Scenario 4:

Design an Informatica mapping to load first half records to 1 target while other half records to a separate target.

Solution:

You will have to assign a row number with each record. To achieve this, either use Oracle's psudo column rownum in Source Qualifier query or use NEXTVAL port of a Sequence generator. Lets name this column as rownumber.

From Source Qualifier, create 2 pipelines:

First Pipeline:
Carry first port Col1 from SQ transformation into an aggregator transformation. Create a new output port "tot_rec" and give the expression as COUNT(Col1). Do not group by any port. This will give us the total number of records in Source Table. Carry this port tot_rec to an Expression Transformation. Add another port DUMMY in expression transformation with default value 1.

Second Pipeline:
from SQ transformation, carry all the ports(including an additional port rownumber generated by rownum or sequence generator) to an Expression Transformation. Add another port DUMMY in expression transformation with default value 1.

Join these 2 pipelines with a Joiner Transformation on common port DUMMY. carry all the source table ports and 2 additional ports tot_rec and rownumber to a router transformation. Add 2 groups in Router : FIRST_HALF and SECOND_HALF. Give condition rownumber<=tot_rec/2 in FIRST_HALF. Give condition rownumber>tot_rec/2 in SECOND_HALF. Connect the 2 groups to 2 different targets.

Performance Tuning in Informatica

Performance Tuning is done to improve session performance. To tune a session, performance bottlenecks are identified and eliminated. The bottlenecks should be looked in following places in the given order:

1. Target

2. Source

3. Mapping

4. Session

5. System

General Techniques to identify bottlenecks:

1. Thread statistics should be analysed to identify source, target or transformation bottlenecks. By default, the Integration Service uses 1 reader thread, 1 tranformation thread and 1 writer thread to process a session. The thread with the highest busy percentage identifies the bottleneck.

2. Add a filter transformation after each source qualifier. Set the filter condition to false so that no data is passed fromk filter. If the time to run the session remains about the same, there is a source bottleneck.

3. Add a filter transformation before each target definition. Set the filter condition to false so that no data is loaded into the target. If the time taken to run the session is almost same as before, there is a mapping bottleneck.

 General Techniques to improve session performance:

1. If a target bottleneck is identified, consider dropping key constraints and indexes on the target tables before the session is run. Rebuild those constraints and indexes after the session run.

2. Use bulk loading incase of insertion of large amount of data. When bulk loading, the Integration Service bypasses the database log which speeds up performance. But there is a trade-off here. Without writing to the database log, the target database can not perform rollback.

3. If multiple source tables are joined in one source qualifier, optimize the query by using hints and/or adding indexes on the joined columns, GROUP BY or ORDER BY columns. Configure the database to run parallel queries to improve performance.

4. Reduce unnecessary transformations in the mapping and delete unnecessary links between transformations to optimize the mapping.

5. If the session reads from a flat file source, the performance can be improved by setting the number of bytes the Integration Service reads per line.By default, the Integration service reads 1024 bytes per line. If each line in source file is less than the default setting, the 'line sequential buffer length' should be decreased in the session properties.

6. Use filters as early as possible in the mapping.

7. Eliminate unnecessary datatype conversions. Try to use integer values in place of other datatypes when performing comparisons using lookup and filter transformations.

8. The integration service reads expressions written with operators faster than the expressions with functions. Wherever possible, operators should be used. e.g in place of CONCAT function use || operator.

9. When Aggregator Transformation is used, following guidelines should be used -

• 
  
  The group by should be done on simple columns. If possible, the number type columns should be preffered for GROUP BY.
• 
  
  Use sorted input. When sorted input option is used, the Integration Service assumes that all data is sorted by group. It does not wait for the whole data to come into memory for aggrgation.
• 
  
  Use incemental aggrgation if the changes in source affect less than half the target. In this case, the Integration Service updates the target incrementally, rather than processing the entire source and recalculating the same calculations everytime you run the session.
• 
  
  Filter data before aggregation. This will avoid unnecessary aggregation.
• 
  
  Unncessary port connections should be avoided to reduce the amount of data the aggregator transformation stores in the data cache.

10. When using Joiner Transformation, following things should be considered:

• 
  
  In case of large data sets, configure joiner transformation to use sorted input. In this case, the Integration service improves performance by minimizing disk input/output. Also, designate the master source as the source with fewer duplicate key values. When Integration Service processes a sorted joiner transformation, it caches rows for 100 unique keys at a time. If the master source contains many rows with the same key value, the Integration Service needs to cache more rows and hence performance can be slowed.
• 
  
  Designate master source as the source with fewer rows. This is because during a session run, the joiner transformation compares the value of each row of the detail source against the master source. The fewer rows in the master, the fewer iterations of the join comparison occur, which speds the join process.
• Perform join in the database or source qualifier transformation if possible

11. When using Lookup Transformation, following things should be considered:

• If you have privileges to modify the database containing a lookup table, you can improve lookup initialization time by adding an index to the lookup table.
• You can improve performance by indexing the columns in the lookup ORDER BY.
• By default, the Integration Service generates an ORDER BY clause for a cached lookup. The ORDER BY clause contains all lookup ports. To increase performance, you can suppress the default ORDER BY clause and enter an override ORDER BY with fewer columns. Place two dashes ‘--’ as a comment notation after the ORDER BY clause to suppress the ORDER BY clause that the Integration Service generates.
• If you include more than one lookup condition, place the conditions in the following order to optimize lookup performance:- Equal to (=),Less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=), Not equal to (!=)
• Improve session performance by caching small lookup tables.
• If the lookup table is on the same database as the source table in the mapping and caching is not feasible, join the tables in the source database rather than using a Lookup transformation.

12. If possible, run sessions and workflows concurrently to improve performance.

13. Increase the commit interval. By doing so, the number of times, Integration Service commits data decreases and hence performance is gained. However, if the commit interval is too high, the Integration Service will fill the database log file and cause the session to fail.

14. If the session runs if 'high precision' setting enabled, then disabling this property may reult in performance gain.

15. Set the proper error tracing level in session. e.g Verbose Data setting should only be used for debugging mappings and should be avoided in the normal run.

16. Use pipeline partitioning to improve session performance. Increasing the number of partitions or partition points increases the number of threads. If the Integration Service nodes contain enough CPU bandwidth, this approach can significantly improve performance.

Informatica Basics and Interview FAQs

Q1 What is Informatica Powercenter?

Ans Powercenter is a data integration software of Informatica Corporation which provides an environment that allows to load data into a centralized location such as data warehouse. Data can be extracted from multiple sources , can be transformed according to the business logic and can be loaded into files and relation targets. It has following components:
PowerCentre Domain
PowerCenter Repositiory
Administration Console
PowerCenter Client
Repository Service
Integration service
Web Services Hub
Data Analyzer
Metadata Manager
PowerCenter Repository Reports

Q2 What is Data Integration?

Ans Data Integration is the process of combining data residing at different sources and providing the user with a unified view of these data.

Q3 Explain PowerCenter Repository?

Ans Repository consist of database tables that store metadata. Metadata describes different types of objects , such as mappings or transformations , that you can create using PowerCenter Client tools. The interation service uses repository objects to extract , transform and load data. The repository also stores administrative information such as user names, passwords , permissions and previleges. When any task is performed through PowerCenter Client application such as creating users, analyzing sources , developing mapping or mapplets or creating workflows , Metadata is added to repository tables.

.

Q4. What is a Mapping?

Ans A mapping is a set of source and target definitions linked by transformation objects that define the rules for data transformation. Mappings represent the data flow between sources and targets. When the Integration Service runs a session, it uses the instructions configured in the mapping to read, transform, and write data.

.

Q5. What is a mapplet?

Ans A mapplet is a reusable object that contains a set of transformations and enables to reuse that transformation logic in multiple mappings.

.

Q6. What is Transformation?

Ans Transformation is a repository object that generates,modifies or passes data.Transformations in a mapping represent the operations the Integration Service performs on the data. Data passes through transformation ports that are linked in a mapping or mapplet.

Q7. What are Mapping Parameters and Variables? Whats the difference between them?

Mapping parameters and variables are used to make mappings more flexible.

A mapping parameter represents a constant value that can be defined before running a session. It retains the same value throughout the session. Using a parameter file, this value can be changed for subsequent sessions.

A mapping variable represents a value that can change through sessions. The Integration Service saves the value of a mapping variable to the repository at the end of each successful run and uses that value in the next run of the session.

Q8 What happens when you do not group values in an aggregator transformation?

When the values are not grouped in aggregator transformation, Integration service returns 1 row for all input rows. It typically returns the last row of each group (or the last row recieved) with the result of the aggregation. However, if you specify a particular row to be returned (e.g through FIRST function), then that row is returned.

Q9 How does using sorted input improves the performance of Aggregator Transformation?

When sorted input is used, the Integration Service assumes that all data is sorted by group and it performs aggregate calculations as it reads rows for a group. It doesnot wait for the whole data and hence this reduces the amont of data cached during the session and improves session performance. While when unsorted input is used, Integration service waits for the whole data and only then performs aggregation.

Sorted Input should not be used when either of the following conditions are true:

•  The aggregate expression uses nested aggregate functions
•  The session uses incremental aggregation

Q10 How does a join in Joiner transformation different from normal SQL join?

The joiner transformation join can be done on hetrogeneous sources but SQL join can be done only on tables.

Q11 What are the criteria for deciding Master and Detail sources for a joiner transformation?

• The master pipeline ends at the joiner transformation while the detail pipeline continues to the target. So, accordingly the sources should be decided as master or detail
• For optimal performance, if an unsorted joiner transformation is used, then designate the source with fewer rows as the master source. During a session run, the joiner transformation compares each row of the master source against the detail source.
• For a sorted joiner transformation, designate the source with fewer duplicate key values as master.

Q12  Explain Informatica Domain, node and services

The PowerCenter domain is the fundamental administrative unit in PowerCenter. The domain supports the administration of the distributed services. A domain is a collection of nodes and services that you can group in folders based on administration ownership. A domain can run a single version of application services or multiple versions of application services. A domain that can run multiple versions of application services is called a mixed-version domain.

A node is the logical representation of a machine in a domain. One node in the domain acts as a gateway to receive service requests from clients and route them to the appropriate service and node. Services and processes run on nodes in a domain. The availability of a service or process on a node depends on how you configure the service and the node. A node can be a gateway node or a worker node. A gateway node is any node you configure to serve as a gateway for the domain. One node acts as the gateway at any given time. That node is called the master gateway. A gateway node can run application services, and it can serve as a master gateway node. The master gateway node is the entry point to the domain. The Service Manager on the master gateway node performs all domain operations on the master gateway node. The Service Managers running on other gateway nodes perform limited domain operations on those nodes.

Services for the domain include the Service Manager and a set of application services:

Service Manager. A service that manages all domain operations. It runs the application services and performs domain functions on each node in the domain. Some domain functions include authentication, authorization, and logging. When you start Informatica Services, you start the Service Manager

Application services. Services that represent PowerCenter server-based functionality, such as the Repository Service and the Integration Service. The application services that run on a node depend on the way you configure the services. In mixed-version domain, multiple versions of application services can run in the same domain. The Service Manager and application services control PowerCenter security. The Service Manager manages users and groups that can log in to PowerCenter applications and authenticates the users who log in to PowerCenter applications. The Service Manager and application services authorize user requests from PowerCenter applications.  The PowerCenter Administration Console consolidates the administrative tasks for domain objects such as services, nodes, licenses, and grids and for users, groups, and roles. You manage the domain and the security of the domain through the Administration Console.

----- To be continued

Incremental Data Extraction

Define a logic to incrementally extract data through an Informatica Mapping. On every session run, the mapping should start loading data from the row where it ended loading in the last run. There is column rowdatetime for every row in the table.

Solution:

Create a user defined mapping variable $$LastUpdateDateTime that saves the timestamp of the last row, the integration service read in the previous session. There is a build-in variable $$SessStartTime for the end time stamp. Use the following filter in source filter of source qualifier to incremetally extract data:

Mytable.rowdatetime > TO_DATE('$$LastUpdateDateTime') and mytable.rowdatetime < TO_DATE('$$SessStartTime')

 

Constraint Based Loading and Target Load Order

Define Informatica's Constraint Based Loading and Target Load Order. Whats is the difference between the two?

Constraint Based Loading is a session level property. When this option is selected in session properties, the Integration Service orders the target load on a row by row basis. For every row, the transformed row is loaded first to the primary key table and then to the secondary key table. Target tables in the mapping should have 1 active source (Targets in separate pipelines are not applicable) and should have key relationships(non-circular in nature). Also the session option 'Treat Source Rows As' should be set to Insert. Updates can not be used with constraint based loading.

Target Load Order Group is a collection of source qualifiers, transformations and targets in a mapping. Thus a mapping containing more than 1 pipeline is eligible to Target Load Order. Target Load Order sets the order in which Integration Service sends rows to targets in different target load order groups.

Differences:

1. Constraint Based Loading requires targets to have only 1 active source while Target Load Order is for targets having different active sources.

2. Constraint Based Loading can not be done to maintain referential integrity for updates while Target Load Order can be used to maintain referential integrity when inserting, deleting or updating tables that have primary key and foreign key constraints.