Informatica Depth Analysis

Performance Tuning Overview

2011-10-04T20:27:00.000-07:00

Performance Tuning Overview

The goal of performance tuning is to optimize session performance by eliminating performance bottlenecks. To tune session performance, first identify a performance bottleneck, eliminate it, and then identify the next performance bottleneck until you are satisfied with the session performance. You can use the test load option to run sessions when you tune session performance.

If you tune all the bottlenecks, you can further optimize session performance by increasing the number of pipeline partitions in the session. Adding partitions can improve performance by utilizing more of the system hardware while processing the session.

Complete the following tasks to improve session performance:

	Optimize the target. Enables the Integration Service to write to the targets efficiently
	Optimize the source. Enables the Integration Service to read source data efficiently.

	Optimize the mapping. Enables the Integration Service to transform and move data efficiently..
	Optimize the transformation. Enables the Integration Service to process transformations in a mapping efficiently.

	Optimize the session. Enables the Integration Service to run the session more quickly.
	Optimize the grid deployments. Enables the Integration Service to run on a grid with optimal performance..

	Optimize the PowerCenter components. Enables the Integration Service and Repository Service to function optimally..
	Optimize the system. Enables PowerCenter service processes to run more quickly.

Bottlenecks Overview

The first step in performance tuning is to identify performance bottlenecks. Performance bottlenecks can occur in the source and target databases, the mapping, the session, and the system. The strategy is to identify a performance bottleneck, eliminate it, and then identify the next performance bottleneck until you are satisfied with the performance.

Look for performance bottlenecks in the following order:

	Target
	Source Mapping Session System

Use the following methods to identify performance bottlenecks:

	Run test sessions. You can configure a test session to read from a flat file source or to write to a flat file target to identify source and target bottlenecks.
	Analyze performance details. Analyze performance details, such as performance counters, to determine where session performance decreases.

	Analyze thread statistics. Analyze thread statistics to determine the optimal number of partition points.
	Monitor system performance. You can use system monitoring tools to view the percentage of CPU use, I/O waits, and paging to identify system bottlenecks. You can also use the Workflow Monitor to view system resource usage.

Using Thread Statistics

You can use thread statistics in the session log to identify source, target, or transformation bottlenecks. By default, the Integration Service uses one reader thread, one transformation thread, and one writer thread to process a session. The thread with the highest busy percentage identifies the bottleneck in the session.

The session log provides the following thread statistics:

	Run time. Amount of time the thread runs.
	Idle time. Amount of time the thread is idle. It includes the time the thread waits for other thread processing within the application. Idle time includes the time the thread is blocked by the Integration Service, but it not the time the thread is blocked by the operating system.

Busy time. Percentage of the run time the thread is by according to the following formula:

(run time - idle time) / run time X 100

You can ignore high busy percentages when the total run time is short, such as under 60 seconds. This does not necessarily indicate a bottleneck.

Thread work time. The percentage of time the Integration Service takes to process each transformation ina thread. The session log shows the following information for the transformation thread work time:

Thread work time breakdown:

<transformation name>: <number> percent

If a transformation takes a small amount of time, the session log does not include it. If a thread does not have accurate statistics, because the session ran for a short period of time, the session log reports that the statistics are not accurate.

Eliminating Bottlenecks Based on Thread Statistics

Complete the following tasks to eliminate bottlenecks based on thread statistics:

	If the reader or writer thread is 100% busy, consider using string datatypes in the source or target ports. Non-string ports require more processing.
	If a transformation thread is 100% busy, consider adding a partition point in the segment. When you add partition points to the mapping, the Integration Service increases the number of transformation threads it uses for the session. However, if the machine is already running at or near full capacity, do not add more threads.

If one transformation requires more processing time than the others, consider adding a pass-through partition point to the transformation.

Target Bottlenecks

The most common performance bottleneck occurs when the Integration Service writes to a target database. Small checkpoint intervals, small database network packet sizes, or problems during heavy loading operations can cause target bottlenecks.

Identifying Target Bottlenecks

To identify a target bottleneck, complete the following tasks:

Configure a copy of the session to write to a flat file target. If the session performance increases significantly, you have a target bottleneck. If a session already writes to a flat file target, you probably do not have a target bottleneck.

Read the thread statistics in the session log. When the Integration Service spends more time on the writer thread than the transformation or reader threads, you have a target bottleneck.

Eliminating Target Bottlenecks

Complete the following tasks to eliminate target bottlenecks:

Dropping Indexes and Key Constraints

Optimizing Flat File Targets

Increasing Database Checkpoint Intervals

Using Bulk Loads

Optimizing Oracle Target Databases

Increasing Database Network Packet Size

Source Bottlenecks

Performance bottlenecks can occur when the Integration Service reads from a source database. Inefficient query or small database network packet sizes can cause source bottlenecks.

Identifying Source Bottlenecks

You can read the thread statistics in the session log to determine if the source is the bottleneck. When the Integration Service spends more time on the reader thread than the transformation or writer threads, you have a source bottleneck.

If the session reads from a relational source, use the following methods to identify source bottlenecks:

	Filter transformation
	Read test mapping

Database query

If the session reads from a flat file source, you probably do not have a source bottleneck.

Using a Filter Transformation

You can use a Filter transformation in the mapping to measure the time it takes to read source data.

Add a Filter transformation after each source qualifier. Set the filter condition to false so that no data is processed passed the Filter transformation. If the time it takes to run the new session remains about the same, you have a source bottleneck.

Using a Read Test Mapping

You can create a read test mapping to identify source bottlenecks. A read test mapping isolates the read query by removing the transformation in the mapping.

To create a read test mapping, complete the following steps:

1.	Make a copy of the original mapping.
2.	In the copied mapping, keep only the sources, source qualifiers, and any custom joins or queries.

3.	Remove all transformations.
4.	Connect the source qualifiers to a file target.

Run a session against the read test mapping. If the session performance is similar to the original session, you have a source bottleneck.

Using a Database Query

To identify source bottlenecks, execute the read query directly against the source database.

Copy the read query directly from the session log. Execute the query against the source database with a query tool such as isql. On Windows, you can load the result of the query in a file. On UNIX, you can load the result of the query in /dev/null.

Measure the query execution time and the time it takes for the query to return the first row.

Eliminating Source Bottlenecks

Complete the following tasks to eliminate source bottlenecks:

Set the number of bytes the Integration Service reads per line if the Integration Service reads from a flat file source.

Have the database administrator optimize database performance by optimizing the query.

Increase the database network packet size.

Configure index and key constraints.

Expression Transformation Complete Overview

2011-10-04T20:03:00.000-07:00

Transformation type:

Passive

Connected

Use the Expression transformation to calculate values in a single row. For example, you might need to adjust employee salaries, concatenate first and last names, or convert strings to numbers. You can also use the Expression transformation to test conditional statements before you pass the results to a target or other transformations.

Use the Expression transformation to perform non-aggregate calculations. To perform calculations involving multiple rows, such as sums or averages, use the Aggregator transformation.

Expression Transformation Components

You can create an Expression transformation in the Transformation Developer or the Mapping Designer.

An Expression transformation contains the following tabs:

Transformation. Enter the name and description of the transformation. The naming convention for an Expression transformation is EXP_TransformationName. You can also make the transformation reusable.

Ports. Create and configure ports.

Properties. Configure the tracing level to determine the amount of transaction detail reported in the session log file.

Metadata Extensions. Specify the extension name, datatype, precision, and value. You can also create reusable metadata extensions.

how to create scdtype2

2011-10-01T00:11:00.000-07:00

how to create rank transformation

2011-10-01T00:09:00.001-07:00

how to create Union Transformation

2011-10-01T00:07:00.000-07:00

Infosys Interview Questions (23rd sep 2011)

2011-09-30T10:51:00.001-07:00

1.Explain u r project architecture?

2.What is staging area and why u r using in ur project?

3.if u have a source system contain records of three months jan,feb,march I want to load the data in to three targets jan,feb,mar draw the mapping?

4.diff b/w static lkp and dynamic lkp?

5.what is different types of joins in jnr t/r?

6.in filter t/r I can give filter condition to 4 what can happened?

7.In agg t/r I can use sorted input option but I cant pass sorted data what happened?

8.explain normalize t/r give one example how it can convert single input record to multiple o/p records?

9.what is verboseinitialization?

What is the difference between a Primary Key and a Unique Index?

2011-09-22T20:50:00.001-07:00

Column(s) that make the Primary Key of a table cannot be NULL since by definition, the Primary Key cannot be NULL since it helps uniquely identify the record in the table. The column(s) that make up the unique index can be nullable. A note worth mentioning over here is that different RDBMS treat this differently –> while SQL Server and DB2 do not allow more than one NULL value in a unique index column, Oracle allows multiple NULL values. That is one of the things to look out for when designing/developing/porting applications across RDBMS.
There can be only one Primary Key defined on the table where as you can have many unique indexes defined on the table (if needed).
Also, in the case of SQL Server, if you go with the default options then a Primary Key is created as a clustered index while the unique index (constraint) is created as a non-clustered index. This is just the default behavior though and can be changed at creation time, if needed.

So, if the unique index is defined on not null column(s), then it is essentially the same as the Primary Key and can be treated as an alternate key meaning it can also serve the purpose of identifying a record uniquely in the table.

Joiner Transformation Overview

2011-08-09T07:11:00.000-07:00

Joiner Transformation Overview

Transformation type:

Active

Connected

Use the Joiner transformation to join source data from two related heterogeneous sources residing in different locations or file systems. You can also join data from the same source. The Joiner transformation joins sources with at least one matching column. The Joiner transformation uses a condition that matches one or more pairs of columns between the two sources.

The two input pipelines include a master pipeline and a detail pipeline or a master and a detail branch. The master pipeline ends at the Joiner transformation, while the detail pipeline continues to the target.

To join more than two sources in a mapping, join the output from the Joiner transformation with another source pipeline. Add Joiner transformations to the mapping until you have joined all the source pipelines.

following limitations on the pipelines you connect to the Joiner transformation:

· You cannot use a Joiner transformation when either input pipeline contains an Update Strategy transformation.

· You cannot use a Joiner transformation if you connect a Sequence Generator transformation directly before the Joiner transformation.

you must configure the transformation properties, join type, and join condition.

You can configure the Joiner transformation for sorted input to improve Integration Service performance.

Joiner transformation properties:

Ø Case-Sensitive String Comparison

Ø Cache Directory

Ø Join Type

Ø Null Ordering in Master: Not applicable for this transformation type.

Ø Null Ordering in Detail: Not applicable for this transformation type.

Ø Tracing Level

Ø Joiner Data Cache Size

Ø Joiner Index Cache Size

Ø Sorted Input

Ø Master Sort Order

Ø Transformation Scope

To improve performance for an unsorted Joiner transformation, use the source with fewer rows as the master source. To improve performance for a sorted Joiner transformation, use the source with fewer duplicate key values as the master.

By default, when you add ports to a Joiner transformation, the ports from the first source pipeline display as detail sources. Adding the ports from the second source pipeline sets them as master sources. To change these settings, click the M column on the Ports tab for the ports you want to set as the master source. This sets ports from this source as master ports and ports from the other source as detail ports.

Use one or more ports from the input sources of a Joiner transformation in the join condition. Additional ports increase the time necessary to join two sources. The order of the ports in the condition can impact the performance of the Joiner transformation. If you use multiple ports in the join condition, the Integration Service compares the ports in the order you specify.

The Designer validates datatypes in a condition. Both ports in a condition must have the same datatype. If you need to use two ports in the condition with non-matching datatypes, convert the datatypes so they match.

If you join Char and Varchar datatypes, the Integration Service counts any spaces that pad Char values as part of the string:

Char(40) = "abcd"

Varchar(40) = "abcd"

The Char value is “abcd” padded with 36 blank spaces, and the Integration Service does not join the two fields because the Char field contains trailing spaces.

Note: The Joiner transformation does not match null values. For example, if both EMP_ID1 and EMP_ID2 contain a row with a null value, the Integration Service does not consider them a match and does not join the two rows. To join rows with null values, replace null input with default values, and then join on the default values.

Note: A normal or master outer join performs faster than a full outer or detail outer join.

If you pass unsorted or incorrectly sorted data to a Joiner transformation configured to use sorted data, the session fails and the Integration Service logs the error in the session log file.

Do not place any of the following transformations between the sort origin and the Joiner transformation:

	Ø Custom
	Ø Unsorted Aggregator

	Ø Normalizer
	Ø Rank

	Ø Union transformation
	Ø XML Parser transformation

	Ø XML Generator transformation
	Ø Mapplet, if it contains one of the above transformations

Joining Two Branches of the Same Pipeline

When you join data from the same source, you can create two branches of the pipeline. When you branch a pipeline, you must add a transformation between the source qualifier and the Joiner transformation in at least one branch of the pipeline. You must join sorted data and configure the Joiner transformation for sorted input.

For example, you have a source with the following ports:

	Employee
	Department

Total Sales

In the target, you want to view the employees who generated sales that were greater than the average sales for their departments. To do this, you create a mapping with the following transformations:

Sorter transformation. Sorts the data.

Sorted Aggregator transformation. Averages the sales data and group by department. When you perform this aggregation, you lose the data for individual employees. To maintain employee data, you must pass a branch of the pipeline to the Aggregator transformation and pass a branch with the same data to the Joiner transformation to maintain the original data. When you join both branches of the pipeline, you join the aggregated data with the original data.

	Sorted Joiner transformation. Uses a sorted Joiner transformation to join the sorted aggregated data with the original data.
	Filter transformation. Compares the average sales data against sales data for each employee and filter out employees with less than above average sales.

Source->src_source->str_src->.|……pipeline1…….Jnr->fil->trg

| pipeline 2 |

Agg|

Joining two branches might impact performance if the Joiner transformation receives data from one branch much later than the other branch. The Joiner transformation caches all the data from the first branch, and writes the cache to disk if the cache fills. The Joiner transformation must then read the data from disk when it receives the data from the second branch. This can slow processing.

Joining Two Instances of the Same Source

You can also join same source data by creating a second instance of the source. After you create the second source instance, you can join the pipelines from the two source instances. If you want to join unsorted data, you must create two instances of the same source and join the pipelines.

Note: When you join data using this method, the Integration Service reads the source data for each source instance, so performance can be slower than joining two branches of a pipeline.

Guidelines

Use the following guidelines when deciding whether to join branches of a pipeline or join two instances of a source:

	Join two branches of a pipeline when you have a large source or if you can read the source data only once. For example, you can only read source data from a message queue once.
	Join two branches of a pipeline when you use sorted data. If the source data is unsorted and you use a Sorter transformation to sort the data, branch the pipeline after you sort the data.

	Join two instances of a source when you need to add a blocking transformation to the pipeline between the source and the Joiner transformation.
	Join two instances of a source if one pipeline may process slower than the other pipeline.

Join two instances of a source if you need to join unsorted data.

Perform joins in a database when possible.

Performing a join in a database is faster than performing a join in the session. In some cases, this is not possible, such as joining tables from two different databases or flat file systems. If you want to perform a join in a database, use the following options:

· Create a pre-session stored procedure to join the tables in a database.

· Use the Source Qualifier transformation to perform the join.

Expression Transformation Overview

2011-08-09T07:09:00.001-07:00

Expression Transformation Overview

Transformation type:

Passive

Connected

Use the Expression transformation to calculate values in a single row. For example, you might need to adjust employee salaries, concatenate first and last names, or convert strings to numbers. You can also use the Expression transformation to test conditional statements before you pass the results to a target or other transformations.

Use the Expression transformation to perform non-aggregate calculations. To perform calculations involving multiple rows, such as sums or averages, use the Aggregator transformation.

The naming convention for an Expression transformation is EXP_TransformationName.

You can enter multiple expressions in a single Expression transformation by creating an expression for each output port.

Note: After you make the transformation reusable, you cannot copy ports from the source qualifier or other transformations. You can create ports manually within the transformation.

Aggregator Transformation Overview

2011-08-09T07:08:00.001-07:00

Aggregator Transformation Overview

The Aggregator transformation performs aggregate calculations, such as averages and sums.

The Integration Service performs aggregate calculations as it reads and stores data group and row data in an aggregate cache

The Aggregator transformation to perform calculations on groups. The Expression transformation permits you to perform calculations on a row-by-row basis only.

After you create a session that includes an Aggregator transformation, you can enable the session option, Incremental Aggregation. When the Integration Service performs incremental aggregation, it passes new source data through the mapping and uses historical cache data to perform new aggregation calculations incrementally.

The Aggregator is an active transformation, changing the number of rows in the pipeline. The Aggregator transformation has the following components and options:

Aggregate cache. The Integration Service stores data in the aggregate cache until it completes aggregate calculations. It stores group values in an index cache and row data in the data cache

Aggregate expression. Enter an expression in an output port. The expression can include non-aggregate expressions and conditional clauses.

Group by port. Indicate how to create groups. The port can be any input, input/output, output, or variable port. When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified.

Sorted input. Select this option to improve session performance. To use sorted input, you must pass data to the Aggregator transformation sorted by group by port, in ascending or descending order.

Aggregator Transformation Properties

· Cache Directory

· Tracing Level

· Sorted Input:

· Aggregator Data Cache Size

· Aggregator Index Cache Size

· Transformation Scope

Sorted Input: Indicates input data is presorted by groups. Select this option only if the mapping passes sorted data to the Aggregator transformation.

Aggregate Expressions

The Designer allows aggregate expressions only in the Aggregator transformation. An aggregate expression can include conditional clauses and non-aggregate functions. It can also include one aggregate function nested within another aggregate function

Nested Aggregate Functions

When you include single-level and nested functions in the same Aggregator transformation, the Designer marks the mapping or mapplet invalid. If you need to create both single-level and nested functions, create separate Aggregator transformations.

Conditional Clauses

Use conditional clauses in the aggregate expression to reduce the number of rows used in the aggregation. The conditional clause can be any clause that evaluates to TRUE or FALSE.

For example, use the following expression to calculate the total commissions of employees who exceeded their quarterly quota:

SUM( COMMISSION, COMMISSION > QUOTA )

Non-Aggregate Functions

You can also use non-aggregate functions in the aggregate expression.

The following expression returns the highest number of items sold for each item (grouped by item). If no items were sold, the expression returns 0.

IIF( MAX( QUANTITY ) > 0, MAX( QUANTITY ), 0))

Null Values in Aggregate Functions

When you configure the Integration Service, you can choose how you want the Integration Service to handle null values in aggregate functions. You can choose to treat null values in aggregate functions as NULL or zero. By default, the Integration Service treats null values as NULL in aggregate functions.

If you use sorted input and do not presort data correctly, you receive unexpected results.

Sorted Input Conditions

Do not use sorted input if either of the following conditions are true:

· The aggregate expression uses nested aggregate functions.

· The session uses incremental aggregation.

· Source data is data driven.

If you use sorted input and do not sort data correctly, the session fails.

Use sorted input to decrease the use of aggregate caches.

Sorted input reduces the amount of data cached during the session and improves session performance. Use this option with the Sorter transformation to pass sorted data to the Aggregator transformation.

Limit connected input/output or output ports.

Limit the number of connected input/output or output ports to reduce the amount of data the Aggregator transformation stores in the data cache.

Filter the data before aggregating it.

If you use a Filter transformation in the mapping, place the transformation before the Aggregator transformation to reduce unnecessary aggregation.

Hp interview Questions and answers (11-06-2011)

2011-06-11T06:24:00.000-07:00

1.what is meant by inlineview?
Ans: inline view is a SELECT statement in the FROM-clause of another SELECT statement.
In-line views are commonly used simplify complex queries by removing join operations
and condensing several separate queries into a single query.

2.what is conformed dimension?
Ans:a dimension table can be shared by more than one facttable is known conformed dmension.
ex:date dimension.

3.what is factlessfact table?
Ans:factless fact table captures many to many relationship b/w dimensions,and it dosen't contain any numeric values.
ex:studance attandence tracking.
store that did not sell a product over aperiod of time.

4.i have 3 flatfiles with same stracture how to join without using joiner transformation?
Ans:by using file list concept with file type as indirect .

5.how to delete the duplicate records in unix?
Ans:sort filename.txt|uniq -u

6.what is surrogatedkey?
Ans:surrogatedkey is a systemgenarated sequence number to be used as a primary key.

7.what is diff b/w surrogatekey and primarykey?
Ans: surrogatekey primarykey
1.artificialkey 1.naturalkey
2.generated by system sequance 2.generated by database
number

8.what is subqurey & corelated subquarey?
Ans:subquarey:-A query nested inside a SELECT statement is known as a subquery
and is an alternative to complex join statements.
CorelatedsubQuarey:-It is a sub-query (a query nested inside another query)
that uses values from the outer query in its WHERE clause. The sub-query
is evaluated once for each row processed by the outer query.

9.how to eliminate duplicate records in informatica and in sql?
Ans:in informatica by using distinct property in sourcequalifier or by using sortet t/r.
in sql delete from emp where rowid not in(select max(rowid) from emp group by empno);

Spaninfotech Interview Questions&Answers(11-06-2011)

2011-06-11T01:25:00.000-07:00

1.select max(sal),deptno from emp where max(sal)>2000 group by deptno this Query is right or wrong?
Ans:select max(sal),deptno from emp group by deptno having max(sal)>2000 it is the right one.

2. what is the diff b/w where and having clause?
Ans:where can restrict each row or record
having clause restrict group of records.

3.Can we use transaction control transformation on a flatfile target?
Ans:we can't use,Transaction Control transformations connected to any target other than relational, XML, or dynamic MQSeries targets are ineffective for those targets.

4.which one given better performnce static or dynamic lkp?

Ans:Performance wise static lookup is better than dynamic lkp,dynamic Lookup is that they are slow as the caches are updated frequently based on the transaction posted in the database.

5. when we use dynamic lookup?

Ans:if the table contains more no.of duplicate records then we use dynamc lkp to eliminate duplicates.

6.i have 10 records in my source table i can take filter transformation i can give the condition true then how many records loaded into the target tale ?

ANs :10 records,every active transaformation by default act as passive transformation.

Types of Fact Tables

2011-06-10T09:38:00.000-07:00

Types of Facts
There are three types of facts:

Additive: Additive facts are facts that can be summed up through all of the dimensions in the fact table.

Semi-Additive: Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.

Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.

Let us use examples to illustrate each of the three types of facts. The first example assumes that we are a retailer, and we have a fact table with the following columns:

Date

Store

Product

Sales_Amount

The purpose of this table is to record the sales amount for each product in each store on a daily basis. Sales_Amount is the fact. In this case, Sales_Amount is an additive fact, because you can sum up this fact along any of the three dimensions present in the fact table -- date, store, and product. For example, the sum of Sales_Amount for all 7 days in a week represent the total sales amount for that week.
Say we are a bank with the following fact table:

Date

Account

Current_Balance

Profit_Margin

The purpose of this table is to record the current balance for each account at the end of each day, as well as the profit margin for each account for each day. Current_Balance and Profit_Margin are the facts. Current_Balance is a semi-additive fact, as it makes sense to add them up for all accounts (what's the total current balance for all accounts in the bank?), but it does not make sense to add them up through time (adding up all current balances for a given account for each day of the month does not give us any useful information). Profit_Margin is a non-additive fact, for it does not make sense to add them up for the account level or the day level.

Types of Fact Tables

Based on the above classifications, there are two types of fact tables:

Cumulative: This type of fact table describes what has happened over a period of time. For example, this fact table may describe the total sales by product by store by day. The facts for this type of fact tables are mostly additive facts. The first example presented here is a cumulative fact table.
Snapshot: This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. The second example presented here is a snapshot fact table.

Accenture telephonic Interview Questions (6-06-2011)

2011-06-06T10:42:00.000-07:00

1.whandot is the diff b/w 7.x/8.x?

2.what is the sql transformation and explain?

3.what is domain and node?

4.what is the use of associated port in lkp transformation?

5.what is the diff b/w sql source qualifier and flatfile sourcequalifier?

6.I have 1000 flatfiles i had already loaded 700 flatfiles how to you load remaining 300 flatfiles?

7.i have to load 1 lakh of records to the production system in b/w the session
fail what u can do(u can't restart,and also u r not assiging session to a recovery mode)?

8.how can u migrate the code from developed environment to productionenvironment?

9.which type of scd2 can u use in ur project?

10.if ur source data contain no duplictes and also u don't want to perform incremental agrregation
and no need of sorting the data then what is the use of sorter t/r before aggregator?

11.my parameter file dosen't contain any value then how the integration service executed the parameter file?

12.if ur using mapping variable and run the session if the session fails then what is the value in repository?

13. what are the reusable tasks ur used?

TCS(white field,bangalore) Interview Questions (4-06-2011)

2011-06-06T10:38:00.000-07:00

1.explain ur project?

2.how to display duplicate records in a table by using sql query?
ex:100 aaa
   100 aaa
   100 aaa
   101 bbb
   102 ccc

i want to dispaly like this 100 aaa
                           100 aaa
                           100 aaa

3.explain scd type2 i have the data d1 1 mum
                                       d2 1 kol
                                     d3 1 hyd
how it will be inserted?

4.what is mapplet and expalin where u can use mapplet in realtime?

5.emp
boss
sales transaction
hra a/c

in which what are the fact and dimensional tables?

6.explain mapping parameters and variables?

7.what is factless fact table and explain where u can use in realtime?

8.diff b/w in and exist?(sub quaries)

9.i have 100 records in my target table and next day i want to insert 5 records,
and i want to update 10 records then what is tot no.of records in my target table?(by using scd2)

10.diff b/w connected and unconnected lkp?

11.by using which transformation u can capture the rejected records and where it will be stored?

12.what is the shortcut and reusable transformation?

13.in my table 1000 records are there i want to get 100 records what is the sqlquery?

14.diff b/w sourcequalifier and filter?

15.what is abort and stop where we can use abort?

16.what are types of tracing levels and what is the verbose initialization?

17. diff b/w union and joiner?

18. what are the reusable and nonreusabletasks?

CDM Project Configaration

2011-06-02T08:29:00.000-07:00

CDM Project Configaration

Database design :

Temparary Database(oracle)

username : scott

password : tiger

(storing the metadata for src files)

source Database(oracle)

username : source_stage

password : source

Target Database(oracle) -DWH

username : dim_target

password : target

Procedure for creating and grant the previliges to the users(Schemas)

sql >create user source_stage identified by source;

sql >create user dim_target identified by target;

sql >grant dba to source_stage;

sql >grant dba to dim_target;

1. procedure for creating source file structure in scott (schema)

connect to the scott schema (username = scott and password = tiger)

copy and paste the source table structures in the scott schema.

the source table structure is available on the below document file

Rafi\CDM_Project\FILE_Source_Data\Table_Structure.doc

2. procedure for creating source file structure in source_stage (schema)

connect to the source_stage schema (username = source_stage and password = source)

copy and paste the stage table structures in the source_stage schema.

the stage table structure is available on the below document file

Rafi\CDM_Project\FILE_Source_Data\Table_Structure.doc

3. procedure for converting source file structure (oracle) to flatfile

import all tables from scott schema (source analyser - in Informatica)

change all the tables, database type as oracle to database type as flatfiles.

4. create a Date table in the source_stage (schema)

Create Table T_DATE

(

FULL_DATE Date

);

procedure for loading the dates into T_DATE table (source_stage schema)

CREATE OR REPLACE procedure load_date

start_date date := to_date('01-01-2007','dd-mm-yyyy');

end_date date := to_date('31-12-2012','dd-mm-yyyy');

begin

for i in 1..2500 loop

insert into t_date values(start_date);

start_date:= start_date + 1;

end loop;

end;

5. Start maping for passing the soruce file data (scott schema) into stage tables (source_stage schema)

as follows

scott stage

account_src (flat file) t_account (oracle)

product_src (flat file) t_product (oracle)

market_src (flat file) t_market (oracle)

employee_src (flat file) t_employee(oracle)

client_order (flat file) client_order (oracle)

client_allocation (flat file) client_allocation (oracle)

client_execution (flat file) client_execution (oracle)

t_date(oracle)

6. procedure for creating Dimension table structure in dim_target (schema) - DWH

connect to the dim_target schema (username = dim_target and password = target)

copy and paste the Dimension table structures in the dim_target schema.

the Dimension table structure is available on the below document file

Rafi\CDM_Project\FILE_Source_Data\Dimension Table Structure.doc

7. Start maping for passing the stage table data (source_stage) into Dimension tables (dim_target schema)

as follows

stage target (DWH)

a) t_account (oracle) dm_t_account_dim (oracle)

dm_t_account_dim_except (oracle)

b) t_product (oracle) dm_t_product_dim (oracle)

dm_t_product_dim_excep (oracle)

c) t_market (oracle) dm_ t_market_dim (oracle)

dm_ t_market_dim_excep (oracle)

d) t_employee(oracle) dm_t_employee_dim (oracle)

dm_t_employee_dim (oracle)

a and d are SCD Type - 2

b and c are SCD Type - 1

8. FACT table loading (In progress)

Mapping Parameters and Variables Overview

2011-06-01T07:01:00.000-07:00

Mapping Parameters and Variables Overview

In the Designer, use mapping parameters and variables to make mappings more flexible. Mapping parameters and variables represent values in mappings and mapplets. If you declare mapping parameters and variables in a mapping, you can reuse a mapping by altering the parameter and variable values of the mapping in the session. This can reduce the overhead of creating multiple mappings when only certain attributes of a mapping need to be changed.

When you use a mapping parameter or variable in a mapping, first you declare the mapping parameter or variable for use in each mapplet or mapping. Then, you define a value for the mapping parameter or variable before you run the session.

Use mapping parameters and variables in a mapping to incrementally extract data. Use mapping parameters or variables in the source filter of a Source Qualifier transformation to determine the beginning timestamp and end timestamp for incrementally extracting data.

For example, you can create a user-defined mapping variable $$LastUpdateDateTime that saves the timestamp of the last row the Integration Service read in the previous session. Use $$LastUpdateDateTime for the beginning timestamp and the built-in variable $$$SessStartTime for the end timestamp in the source filter. Use the following filter to incrementally extract data based on the SALES.sales_datetime column in the source:

SALES.sales_datetime > TO_DATE (‘$$LastUpdateDateTime’) AND SALES.sales_datetime < TO_DATE (‘$$$SessStartTime’)

Mapping Parameters

A mapping parameter represents a constant value that you can define before running a session. A mapping parameter retains the same value throughout the entire session.

When you use a mapping parameter, you declare and use the parameter in a mapping or mapplet. Then define the value of the parameter in a parameter file. The Integration Service evaluates all references to the parameter to that value.

For example, you want to use the same session to extract transaction records for each of the customers individually. Instead of creating a separate mapping for each customer account, you can create a mapping parameter to represent a single customer account. Then use the parameter in a source filter to extract only data for that customer account. Before running the session, you enter the value of the parameter in the parameter file.

To reuse the same mapping to extract records for other customer accounts, you can enter a new value for the parameter in the parameter file and run the session. Or, you can create a parameter file for each customer account and start the session with a different parameter file each time using pmcmd. By using a parameter file, you reduce the overhead of creating multiple mappings and sessions to extract transaction records for different customer accounts.

When you want to use the same value for a mapping parameter each time you run the session, use the same parameter file for each session run. When you want to change the value of a mapping parameter between sessions you can perform one of the following tasks:

Update the parameter file between sessions.

Create a different parameter file and configure the session to use the new file.

Remove the parameter file from the session properties. The Integration Service uses the parameter value in the pre-session variable assignment. If there is no pre-session variable assignment, the Integration Service uses the configured initial value of the parameter in the mapping.

Mapping Variables

Unlike a mapping parameter, a mapping variable represents a value that can change through the session. The Integration Service saves the value of a mapping variable to the repository at the end of each successful session run and uses that value the next time you run the session.

When you use a mapping variable, you declare the variable in the mapping or mapplet, and then use a variable function in the mapping to change the value of the variable. At the beginning of a session, the Integration Service evaluates references to a variable to determine the start value. At the end of a successful session, the Integration Service saves the final value of the variable to the repository. The next time you run the session, the Integration Service evaluates references to the variable to the saved value. To override the saved value, define the start value of the variable in a parameter file or assign a value in the pre-session variable assignment in the session properties.

Use mapping variables to perform incremental reads of a source. For example, the customer accounts in the mapping parameter example above are numbered from 001 to 065, incremented by one. Instead of creating a mapping parameter, you can create a mapping variable with an initial value of 001. In the mapping, use a variable function to increase the variable value by one. The first time the Integration Service runs the session, it extracts the records for customer account 001. At the end of the session, it increments the variable by one and saves that value to the repository. The next time the Integration Service runs the session, it extracts the data for the next customer account, 002. It also increments the variable value so the next session extracts and looks up data for customer account 003.

Using Mapping Parameters and Variables

You can create mapping parameters and variables in the Mapping Designer or Mapplet Designer. Once created, mapping parameters and variables appear on the Variables tab of the Expression Editor. Use them in any expression in the mapplet or mapping. The Designer validates mapping parameters and variables in the Expression Editor of mapplets and mappings.

Use mapping parameters and variables in a source qualifier in a mapplet or mapping. When you use mapping parameters and variables in a Source Qualifier transformation, the Designer expands them before passing the query to the source database for validation. This allows the source database to validate the query.

When you create a reusable transformation in the Transformation Developer, use any mapping parameter or variable. Since a reusable transformation is not contained within any mapplet or mapping, the Designer validates the usage of any mapping parameter or variable in the expressions of reusable transformation. When you use the reusable transformation in a mapplet or mapping, the Designer validates the expression again. If the parameter or variable is not defined in the mapplet or mapping, or if it is used incorrectly in the reusable transformation, the Designer logs an error when you validate the mapplet or mapping.

When the Designer validates a mapping variable in a reusable transformation, it treats the variable as an Integer datatype.

You cannot use mapping parameters and variables interchangeably between a mapplet and a mapping. Mapping parameters and variables declared for a mapping cannot be used within a mapplet. Similarly, you cannot use a mapping parameter or variable declared for a mapplet in a mapping.

Initial and Default Values

When you declare a mapping parameter or variable in a mapping or a mapplet, you can enter an initial value. The Integration Service uses the configured initial value for a mapping parameter when the parameter is not defined in the parameter file. Similarly, the Integration Service uses the configured initial value for a mapping variable when the variable value is not defined in the parameter file, and there is no saved variable value in the repository.

When the Integration Service needs an initial value, and you did not declare an initial value for the parameter or variable, the Integration Service uses a default value based on the datatype of the parameter or variable.

Table 7-1 lists the default values the Integration Service uses for different types of data:

Table 7-1. Default Values for Mapping Parameters and Variables Based on Datatype
Data	Default Value
String	Empty string.
Numeric	0
Datetime	1/1/1753 A.D. or 1/1/1 when the Integration Service is configured for compatibility with 4.0.

For example, you create a new mapping using an Integer mapping variable, $$MiscellaneousExpenses. You do not configure an initial value for the variable or define it in a parameter file. The first time you run a session with the mapping, the Integration Service uses the default value for numeric datatypes, 0.

Or, if you create a mapping parameter $$MiscellaneousCosts to represent additional expenses that might become relevant in the future, but do not currently exist in source data. You configure the parameter for a Decimal datatype. Since you want $$MiscellaneousCosts to evaluate to 0 when you do not have additional expenses, you set the initial value to 0.

As long as you do not define the parameter value in the parameter file, the Integration Service replaces $$MiscellaneousCosts with 0. When you want to include miscellaneous expenses in mapping calculations, set $$MiscellaneousCosts to that value in the parameter file.

Using String Parameters and Variables

When you enter mapping parameters and variables of a string datatype in a Source Qualifier transformation, use a string identifier appropriate for the source database. When the Integration Service expands a parameter or variable in a Source Qualifier transformation, the Integration Service replaces it with its start value, and then passes the expanded query to the source database. Most databases require single quotation marks around string values.

When you enter string parameters or variables using the PowerCenter transformation language, do not use additional quotes. The Integration Service recognizes mapping parameter and variable naming syntax in the PowerCenter transformation language. For example, you might use a parameter named $$State in the filter for a Source Qualifier transformation to extract rows for a particular state:

STATE = ‘$$State’

During the session, the Integration Service replaces the parameter with a string. If $$State is defined as MD in the parameter file, the Integration Service replaces the parameter as follows:

STATE = ‘MD’

You can perform a similar filter in the Filter transformation using the PowerCenter transformation language as follows:

STATE = $$State

If you enclose the parameter in single quotes in the Filter transformation, the Integration Service reads it as the string literal “$$State” instead of replacing the parameter with “MD.”

Using Datetime Parameters and Variables

When you use a datetime parameter or variable in the Source Qualifier transformation, you might need to change the date format to the format used in the source.

Filter Transformation complete Overview

2011-05-29T09:47:00.000-07:00

Use the Filter transformation to filter out rows in a mapping. As an active transformation, the Filter transformation may change the number of rows passed through it. The Filter transformation allows rows that meet the specified filter condition to pass through. It drops rows that do not meet the condition. You can filter data based on one or more conditions.

A filter condition returns TRUE or FALSE for each row that the Integration Service evaluates, depending on whether a row meets the specified condition. For each row that returns TRUE, the Integration Services pass through the transformation. For each row that returns FALSE, the Integration Service drops and writes a message to the session log.

The following mapping passes the rows from a human resources table that contains employee data through a Filter transformation. The filter allows rows through for employees that make salaries of $30,000 or higher.

You cannot concatenate ports from more than one transformation into the Filter transformation. The input ports for the filter must come from a single transformation.

Tip: Place the Filter transformation as close to the sources in the mapping as possible to maximize session performance. Rather than passing rows you plan to discard through the mapping, you can filter out unwanted data early in the flow of data from sources to targets.

Filter Transformation Components

You can create a Filter transformation in the Transformation Developer or the Mapping Designer. A Filter transformation contains the following tabs:

Transformation. Enter the name and description of the transformation. The naming convention for a Filter transformation is FIL_TransformationName. You can also make the transformation reusable.

Ports. Create and configure ports.

Properties. Configure the filter condition to filter rows. Use the Expression Editor to enter the filter condition. You can also configure the tracing level to determine the amount of transaction detail reported in the session log file.

Metadata Extensions. Create a non-reusable metadata extension to extend the metadata of the transformation transformation. Configure the extension name, datatype, precision, and value. You can also promote metadata extensions to reusable extensions if you want to make it available to all transformation transformations.

Configuring Filter Transformation Ports

You can create and modify ports on the Ports tab.

You can configure the following properties on the Ports tab:

Port name. Name of the port.

Datatype, precision, and scale. Configure the datatype and set the precision and scale for each port.

Port type. All ports are input/output ports. The input ports receive data and output ports pass data.

Default values and description. Set default value for ports and add description.

Filter Condition

The filter condition is an expression that returns TRUE or FALSE. Enter conditions using the Expression Editor available on the Properties tab.

Any expression that returns a single value can be used as a filter. For example, if you want to filter out rows for employees whose salary is less than $30,000, you enter the following condition:

SALARY > 30000

You can specify multiple components of the condition, using the AND and OR logical operators. If you want to filter out employees who make less than $30,000 and more than $100,000, you enter the following condition:

SALARY > 30000 AND SALARY < 100000

You can also enter a constant for the filter condition. The numeric equivalent of FALSE is zero (0). Any non-zero value is the equivalent of TRUE. For example, the transformation contains a port named NUMBER_OF_UNITS with a numeric datatype. You configure a filter condition to return FALSE if the value of NUMBER_OF_UNITS equals zero. Otherwise, the condition returns TRUE.

You do not need to specify TRUE or FALSE as values in the expression. TRUE and FALSE are implicit return values from any condition you set. If the filter condition evaluates to NULL, the row is treated as FALSE.

Note: The filter condition is case sensitive.

Filtering Rows with Null Values

To filter rows containing null values or spaces, use the ISNULL and IS_SPACES functions to test the value of the port. For example, if you want to filter out rows that contain NULL value in the FIRST_NAME port, use the following condition:

IIF(ISNULL(FIRST_NAME),FALSE,TRUE)

This condition states that if the FIRST_NAME port is NULL, the return value is FALSE and the row should be discarded. Otherwise, the row passes through to the next transformation.

Performance Tuning

Use the Filter transformation early in the mapping.

To maximize session performance, keep the Filter transformation as close as possible to the sources in the mapping. Rather than passing rows that you plan to discard through the mapping, you can filter out unwanted data early in the flow of data from sources to targets.

Use the Source Qualifier transformation to filter.

The Source Qualifier transformation provides an alternate way to filter rows. Rather than filtering rows from within a mapping, the Source Qualifier transformation filters rows when read from a source. The main difference is that the source qualifier limits the row set extracted from a source, while the Filter transformation limits the row set sent to a target. Since a source qualifier reduces the number of rows used throughout the mapping, it provides better performance.

However, the Source Qualifier transformation only lets you filter rows from relational sources, while the Filter transformation filters rows from any type of source. Also, note that since it runs in the database, you must make sure that the filter condition in the Source Qualifier transformation only uses standard SQL. The Filter transformation can define a condition using any statement or transformation function that returns either a TRUE or FALSE value.

Source Qualifier Transformation Complete Overview

2011-05-29T09:36:00.000-07:00

Transformation type:

Active

Connected

For more information about Application Source Qualifier transformations, see the corresponding help topic for your PowerExchange product.

When you add a relational or a flat file source definition to a mapping, you need to connect it to a Source Qualifier transformation. The Source Qualifier transformation represents the rows that the Integration Service reads when it runs a session.

Use the Source Qualifier transformation to complete the following tasks:

Join data originating from the same source database. You can join two or more tables with primary key-foreign key relationships by linking the sources to one Source Qualifier transformation.

Filter rows when the Integration Service reads source data. If you include a filter condition, the Integration Service adds a WHERE clause to the default query.

Specify an outer join rather than the default inner join. If you include a user-defined join, the Integration Service replaces the join information specified by the metadata in the SQL query.

Specify sorted ports. If you specify a number for sorted ports, the Integration Service adds an ORDER BY clause to the default SQL query.

Select only distinct values from the source. If you choose Select Distinct, the Integration Service adds a SELECT DISTINCT statement to the default SQL query.

Create a custom query to issue a special SELECT statement for the Integration Service to read source data. For example, you might use a custom query to perform aggregate calculations.

Transformation Datatypes

The Source Qualifier transformation displays the transformation datatypes. The transformation datatypes determine how the source database binds data when the Integration Service reads it. Do not alter the datatypes in the Source Qualifier transformation. If the datatypes in the source definition and Source Qualifier transformation do not match, the Designer marks the mapping invalid when you save it.

Target Load Order

You specify a target load order based on the Source Qualifier transformations in a mapping. If you have multiple Source Qualifier transformations connected to multiple targets, you can designate the order in which the Integration Service loads data into the targets.

If one Source Qualifier transformation provides data for multiple targets, you can enable constraint-based loading in a session to have the Integration Service load data based on target table primary and foreign key relationships.

Datetime Values

When you use a datetime value or a datetime parameter or variable in the SQL query, change the date format to the format used in the source. The Integration Service passes datetime values to source systems as strings in the SQL query. The Integration Service converts a datetime value to a string, based on the source database.

The following table describes the datetime formats for each database type:


Source	Date Format
DB2	YYYY-MM-DD-HH24:MI:SS
Informix	YYYY-MM-DD HH24:MI:SS
Microsoft SQL Server	MM/DD/YYYY HH24:MI:SS
ODBC	YYYY-MM-DD HH24:MI:SS
Oracle	MM/DD/YYYY HH24:MI:SS
Sybase	MM/DD/YYYY HH24:MI:SS
Teradata	YYYY-MM-DD HH24:MI:SS

Some databases require you to identify datetime values with additional punctuation, such as single quotation marks or database specific functions. For example, to convert the $$$SessStartTime value for an Oracle source, use the following Oracle function in the SQL override:

to_date (‘$$$SessStartTime’, ‘mm/dd/yyyy hh24:mi:ss’)

For Informix, use the following Informix function in the SQL override to convert the $$$SessStartTime value:

DATETIME ($$$SessStartTime) YEAR TO SECOND

For information about database specific functions, see the database documentation.

Parameters and Variables

You can use parameters and variables in the SQL query, user-defined join, source filter, and pre- and post-session SQL commands of a Source Qualifier transformation. Use any parameter or variable type that you can define in the parameter file. You can enter a parameter or variable within the SQL statement, or you can use a parameter or variable as the SQL query. For example, you can use a session parameter, $ParamMyQuery, as the SQL query, and set $ParamMyQuery to the SQL statement in a parameter file.

The Integration Service first generates an SQL query and expands each parameter or variable. It replaces each mapping parameter, mapping variable, and workflow variable with its start value. Then it runs the query on the source database.

When you use a string mapping parameter or variable in the Source Qualifier transformation, use a string identifier appropriate to the source system. Most databases use a single quotation mark as a string identifier. For example, to use the string parameter $$IPAddress in a source filter for a Microsoft SQL Server database table, enclose the parameter in single quotes as follows: ‘$$IPAddress.’

When you use a datetime mapping parameter or variable, or when you use the built-in variable $$$SessStartTime, change the date format to the format used in the source. The Integration Service passes datetime values to source systems as strings in the SQL query.

Tip: To ensure the format of a datetime parameter or variable matches that used by the source, validate the SQL query.

Configure the following Source Qualifier transformation properties on the Properties tab:

Option

Description

SQL Query

Defines a custom query that replaces the default query the Integration Service uses to read data from sources represented in this Source Qualifier transformation. A custom query overrides entries for a custom join or a source filter.

User-Defined Join

Specifies the condition used to join data from multiple sources represented in the same Source Qualifier transformation.

Source Filter

Specifies the filter condition the Integration Service applies when querying rows.

Number of Sorted Ports

Indicates the number of columns used when sorting rows queried from relational sources. If you select this option, the Integration Service adds an ORDER BY to the default query when it reads source rows. The ORDER BY includes the number of ports specified, starting from the top of the transformation.
When selected, the database sort order must match the session sort order.

Tracing Level

Sets the amount of detail included in the session log when you run a session containing this transformation.

Select Distinct

Specifies if you want to select only unique rows. The Integration Service includes a SELECT DISTINCT statement if you choose this option.

Pre-SQL

Pre-session SQL commands to run against the source database before the Integration Service reads the source.

Post-SQL

Post-session SQL commands to run against the source database after the Integration Service writes to the target.

Output is Deterministic

Relational source or transformation output that does not change between session runs when the input data is consistent between runs. When you configure this property, the Integration Service does not stage source data for recovery if transformations in the pipeline always produce repeatable data.

Output is Repeatable

Relational source or transformation output that is in the same order between session runs when the order of the input data is consistent. When output is deterministic and output is repeatable, the Integration Service does not stage source data for recovery.

Warning: If you configure a transformation as repeatable and deterministic, it is your responsibility to ensure that the data is repeatable and deterministic. If you try to recover a session with transformations that do not produce the same data between the session and the recovery, the recovery process can result in corrupted data.

Default Query
For relational sources, the Integration Service generates a query for each Source Qualifier transformation when it runs a session. The default query is a SELECT statement for each source column used in the mapping. In other words, the Integration Service reads only the columns that are connected to another transformation.

Although there are many columns in the source definition, only three columns are connected to another transformation. In this case, the Integration Service generates a default query that selects only those three columns:
SELECT CUSTOMERS.CUSTOMER_ID, CUSTOMERS.COMPANY, CUSTOMERS.FIRST_NAME
FROM CUSTOMERS

If any table name or column name contains a database reserved word, you can create and maintain a file, reswords.txt, containing reserved words. When the Integration Service initializes a session, it searches for reswords.txt in the Integration Service installation directory. If the file exists, the Integration Service places quotes around matching reserved words when it executes SQL against the database. If you override the SQL, you must enclose any reserved word in quotes.
When generating the default query, the Designer delimits table and field names containing the following characters with double quotes:
/ + - = ~ ` ! % ^ & * ( ) [ ] { } ' ; ? , < > \ | <space>
Viewing the Default Query
You can view the default query in the Source Qualifier transformation.
To view the default query:

1.

From the Properties tab, select SQL Query.

The SQL Editor displays the default query the Integration Service uses to select source data.

2.

Click Generate SQL.

3.

Click Cancel to exit.

Note: If you do not cancel the SQL query, the Integration Service overrides the default query with the custom SQL query.
Do not connect to the source database. You only connect to the source database when you enter an SQL query that overrides the default query.
You must connect the columns in the Source Qualifier transformation to another transformation or target before you can generate the default query.
Overriding the Default Query
You can alter or override the default query in the Source Qualifier transformation by changing the default settings of the transformation properties. Do not change the list of selected ports or the order in which they appear in the query. This list must match the connected transformation output ports.
When you edit transformation properties, the Source Qualifier transformation includes these settings in the default query. However, if you enter an SQL query, the Integration Service uses only the defined SQL statement. The SQL Query overrides the User-Defined Join, Source Filter, Number of Sorted Ports, and Select Distinct settings in the Source Qualifier transformation.
Note: When you override the default SQL query, you must enclose all database reserved words in quotes.
Joining Source Data
Use one Source Qualifier transformation to join data from multiple relational tables. These tables must be accessible from the same instance or database server.
When a mapping uses related relational sources, you can join both sources in one Source Qualifier transformation. During the session, the source database performs the join before passing data to the Integration Service. This can increase performance when source tables are indexed.
Tip: Use the Joiner transformation for heterogeneous sources and to join flat files.
Default Join
When you join related tables in one Source Qualifier transformation, the Integration Service joins the tables based on the related keys in each table.
This default join is an inner equijoin, using the following syntax in the WHERE clause:
Source1.column_name = Source2.column_name
The columns in the default join must have:

A primary key-foreign key relationship

Matching datatypes

For example, you might see all the orders for the month, including order number, order amount, and customer name. The ORDERS table includes the order number and amount of each order, but not the customer name. To include the customer name, you need to join the ORDERS and CUSTOMERS tables. Both tables include a customer ID, so you can join the tables in one Source Qualifier transformation.

When you include multiple tables, the Integration Service generates a SELECT statement for all columns used in the mapping. In this case, the SELECT statement looks similar to the following statement:
SELECT CUSTOMERS.CUSTOMER_ID, CUSTOMERS.COMPANY, CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME, CUSTOMERS.ADDRESS1, CUSTOMERS.ADDRESS2, CUSTOMERS.CITY, CUSTOMERS.STATE, CUSTOMERS.POSTAL_CODE, CUSTOMERS.PHONE, CUSTOMERS.EMAIL, ORDERS.ORDER_ID, ORDERS.DATE_ENTERED, ORDERS.DATE_PROMISED, ORDERS.DATE_SHIPPED, ORDERS.EMPLOYEE_ID, ORDERS.CUSTOMER_ID, ORDERS.SALES_TAX_RATE, ORDERS.STORE_ID
FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.CUSTOMER_ID=ORDERS.CUSTOMER_ID
The WHERE clause is an equijoin that includes the CUSTOMER_ID from the ORDERS and CUSTOMER tables.
Custom Joins
If you need to override the default join, you can enter contents of the WHERE clause that specifies the join in the custom query. If the query performs an outer join, the Integration Service may insert the join syntax in the WHERE clause or the FROM clause, depending on the database syntax.
You might need to override the default join under the following circumstances:

Columns do not have a primary key-foreign key relationship.

The datatypes of columns used for the join do not match.

You want to specify a different type of join, such as an outer join.

Heterogeneous Joins
To perform a heterogeneous join, use the Joiner transformation. Use the Joiner transformation when you need to join the following types of sources:

Join data from different source databases

Join data from different flat file systems

Join relational sources and flat files

Creating Key Relationships
You can join tables in the Source Qualifier transformation if the tables have primary key-foreign key relationships. However, you can create primary key-foreign key relationships in the Source Analyzer by linking matching columns in different tables. These columns do not have to be keys, but they should be included in the index for each table.
Tip: If the source table has more than 1,000 rows, you can increase performance by indexing the primary key-foreign keys. If the source table has fewer than 1,000 rows, you might decrease performance if you index the primary key-foreign keys.
For example, the corporate office for a retail chain wants to extract payments received based on orders. The ORDERS and PAYMENTS tables do not share primary and foreign keys. Both tables, however, include a DATE_SHIPPED column. You can create a primary key-foreign key relationship in the metadata in the Source Analyzer.
Note, the two tables are not linked. Therefore, the Designer does not recognize the relationship on the DATE_SHIPPED columns.
You create a relationship between the ORDERS and PAYMENTS tables by linking the DATE_SHIPPED columns. The Designer adds primary and foreign keys to the DATE_SHIPPED columns in the ORDERS and PAYMENTS table definitions.
The following figure shows a relationship between two tables:

If you do not connect the columns, the Designer does not recognize the relationships.
The primary key-foreign key relationships exist in the metadata only. You do not need to generate SQL or alter the source tables.
Once the key relationships exist, use a Source Qualifier transformation to join the two tables. The default join is based on DATE_SHIPPED.

Adding an SQL Query

The Source Qualifier transformation provides the SQL Query option to override the default query. You can enter an SQL statement supported by the source database. Before entering the query, connect all the input and output ports you want to use in the mapping.

When you edit the SQL Query, you can generate and edit the default query. When the Designer generates the default query, it incorporates all other configured options, such as a filter or number of sorted ports. The resulting query overrides all other options you might subsequently configure in the transformation.

You can use a parameter or variable as the SQL query or include parameters and variables within the query. When including a string mapping parameter or variable, use a string identifier appropriate to the source system. For most databases, you need to enclose the name of a string parameter or variable in single quotes.

When you include a datetime value or a datetime mapping parameter or variable in the SQL query, change the date format to match the format used by the source. The Integration Service converts a datetime value to a string based on the source system. For more information about date conversion, see Datetime Values.

When creating a custom SQL query, the SELECT statement must list the port names in the order in which they appear in the transformation.

When you override the default SQL query for a session configured for pushdown optimization, the Integration Service creates a view to represent the SQL override. It then runs an SQL query against this view to push the transformation logic to the database.

If you edit the SQL query, you must enclose all database reserved words in quotes.

To override the default query:

1.	Open the Source Qualifier transformation, and click the Properties tab.

2.	Click the Open button in the SQL Query field.

The SQL Editor dialog box appears.

3.	Click Generate SQL.

The Designer displays the default query it generates when querying rows from all sources included in the Source Qualifier transformation.

4.	Enter a query in the space where the default query appears.

Every column name must be qualified by the name of the table, view, or synonym in which it appears. For example, if you want to include the ORDER_ID column from the ORDERS table, enter ORDERS.ORDER_ID. You can double-click column names appearing in the Ports window to avoid typing the name of every column.

You can use a parameter or variable as the query, or you can include parameters and variables in the query.

Enclose string mapping parameters and variables in string identifiers. Alter the date format for datetime mapping parameters and variables when necessary.

5.	Select the ODBC data source containing the sources included in the query.

6.	Enter the user name and password to connect to this database.

7.	Click Validate.

The Designer runs the query and reports whether its syntax was correct.

8.	Click OK to return to the Edit Transformations dialog box. Click OK again to return to the Designer.

Tip: You can resize the Expression Editor. Expand the dialog box by dragging from the borders. The Designer saves the new size for the dialog box as a client setting.

Entering a User-Defined Join

Entering a user-defined join is similar to entering a custom SQL query. However, you only enter the contents of the WHERE clause, not the entire query. When you perform an outer join, the Integration Service may insert the join syntax in the WHERE clause or the FROM clause of the query, depending on the database syntax.

When you add a user-defined join, the Source Qualifier transformation includes the setting in the default SQL query. However, if you modify the default query after adding a user-defined join, the Integration Service uses only the query defined in the SQL Query property of the Source Qualifier transformation.

You can use a parameter or variable as the user-defined join or include parameters and variables within the join. When including a string mapping parameter or variable, use a string identifier appropriate to the source system. For most databases, you need to enclose the name of a string parameter or variable in single quotes.

When you include a datetime parameter or variable, you might need to change the date format to match the format used by the source. The Integration Service converts a datetime parameter and variable to a string based on the source system. For more information about automatic date conversion, see Datetime Values.

To create a user-defined join:

1.	Create a Source Qualifier transformation containing data from multiple sources or associated sources.

2.	Open the Source Qualifier transformation, and click the Properties tab.

3.	Click the Open button in the User Defined Join field.

The SQL Editor dialog box appears.

4.	Enter the syntax for the join.

Do not enter the keyword WHERE at the beginning of the join. The Integration Service adds this keyword when it queries rows.

Enclose string mapping parameters and variables in string identifiers. Alter the date format for datetime mapping parameters and variables when necessary.

Click OK to return to the Edit Transformations dialog box, and then click OK to return to the Designer.

Using Sorted Ports
When you use sorted ports, the Integration Service adds the ports to the ORDER BY clause in the default query. The Integration Service adds the configured number of ports, starting at the top of the Source Qualifier transformation. You might use sorted ports to improve performance when you include any of the following transformations in a mapping:

Aggregator. When you configure an Aggregator transformation for sorted input, you can send sorted data by using sorted ports. The group by ports in the Aggregator transformation must match the order of the sorted ports in the Source Qualifier transformation.

Joiner. When you configure a Joiner transformation for sorted input, you can send sorted data by using sorted ports. Configure the order of the sorted ports the same in each Source Qualifier transformation.

Note: You can also use the Sorter transformation to sort relational and flat file data before Aggregator and Joiner transformations.
Use sorted ports for relational sources only. When using sorted ports, the sort order of the source database must match the sort order configured for the session. The Integration Service creates the SQL query used to extract source data, including the ORDER BY clause for sorted ports. The database server performs the query and passes the resulting data to the Integration Service. To ensure data is sorted as the Integration Service requires, the database sort order must be the same as the user-defined session sort order.
When you configure the Integration Service for data code page validation and run a workflow in Unicode data movement mode, the Integration Service uses the selected sort order to sort character data.
When you configure the Integration Service for relaxed data code page validation, the Integration Service uses the selected sort order to sort all character data that falls in the language range of the selected sort order. The Integration Service sorts all character data outside the language range of the selected sort order according to standard Unicode sort ordering.
When the Integration Service runs in ASCII mode, it ignores this setting and sorts all character data using a binary sort order. The default sort order depends on the code page of the Integration Service.
The Source Qualifier transformation includes the number of sorted ports in the default SQL query. However, if you modify the default query after choosing the Number of Sorted Ports, the Integration Service uses only the query defined in the SQL Query property.
To use sorted ports:

1.

In the Mapping Designer, open a Source Qualifier transformation, and click the Properties tab.

2.

Click in Number of Sorted Ports and enter the number of ports you want to sort.

The Integration Service adds the configured number of columns to an ORDER BY clause, starting from the top of the Source Qualifier transformation.
The source database sort order must correspond to the session sort order.
Tip: Sybase supports a maximum of 16 columns in an ORDER BY clause. If the source is Sybase, do not sort more than 16 columns.

3.

Click OK.

Select Distinct
If you want the Integration Service to select unique values from a source, use the Select Distinct option. You might use this feature to extract unique customer IDs from a table listing total sales. Using Select Distinct filters out unnecessary data earlier in the data flow, which might improve performance.
By default, the Designer generates a SELECT statement. If you choose Select Distinct, the Source Qualifier transformation includes the setting in the default SQL query.
For example, in the Source Qualifier transformation in Joining Source Data, you enable the Select Distinct option. The Designer adds SELECT DISTINCT to the default query as follows:
SELECT DISTINCT CUSTOMERS.CUSTOMER_ID, CUSTOMERS.COMPANY, CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME, CUSTOMERS.ADDRESS1, CUSTOMERS.ADDRESS2, CUSTOMERS.CITY, CUSTOMERS.STATE, CUSTOMERS.POSTAL_CODE, CUSTOMERS.EMAIL, ORDERS.ORDER_ID, ORDERS.DATE_ENTERED, ORDERS.DATE_PROMISED, ORDERS.DATE_SHIPPED, ORDERS.EMPLOYEE_ID, ORDERS.CUSTOMER_ID, ORDERS.SALES_TAX_RATE, ORDERS.STORE_ID
FROM
CUSTOMERS, ORDERS
WHERE
CUSTOMERS.CUSTOMER_ID=ORDERS.CUSTOMER_ID
However, if you modify the default query after choosing Select Distinct, the Integration Service uses only the query defined in the SQL Query property. In other words, the SQL Query overrides the Select Distinct setting.
To use Select Distinct:

1.

Open the Source Qualifier transformation in the mapping, and click on the Properties tab.

2.

Check Select Distinct, and Click OK.

Overriding Select Distinct in the Session
You can override the transformation level option to Select Distinct when you configure the session in the Workflow Manager.
To override the Select Distinct option:

1.

In the Workflow Manager, open the Session task, and click the Mapping tab.

2.

Click the Transformations view, and click the Source Qualifier transformation under the Sources node.

3.

In the Properties settings, enable Select Distinct, and click OK.

Adding Pre- and Post-Session SQL Commands

You can add pre- and post-session SQL commands on the Properties tab in the Source Qualifier transformation. You might want to use pre-session SQL to write a timestamp row to the source table when a session begins.

The Integration Service runs pre-session SQL commands against the source database before it reads the source. It runs post-session SQL commands against the source database after it writes to the target.

You can override the SQL commands in the Transformations view on the Mapping tab in the session properties. You can also configure the Integration Service to stop or continue when it encounters errors running pre- or post-session SQL commands.

Use the following guidelines when you enter pre- and post-session SQL commands in the Source Qualifier transformation:

Use any command that is valid for the database type. However, the Integration Service does not allow nested comments, even though the database might.

You can use parameters and variables in source pre- and post-session SQL commands, or you can use a parameter or variable as the command. Use any parameter or variable type that you can define in the parameter file.

Use a semicolon (;) to separate multiple statements. The Integration Service issues a commit after each statement.

The Integration Service ignores semicolons within /*...*/.

If you need to use a semicolon outside of comments, you can escape it with a backslash (\). When you escape the semicolon, the Integration Service ignores the backslash, and it does not use the semicolon as a statement separator.

The Designer does not validate the SQL.

Note: You can also enter pre- and post-session SQL commands on the Properties tab of the target instance in a mapping.

Interview Question&ANSWERS-2

2011-05-28T06:02:00.000-07:00

1.What is the advantages of converting stored procedures into Informatica mappings?
Ans:-
Informatica is intermediate tool which talks to database. If you use the stored proc , the informatica needs to talks to Database for each record since Informatica process record by record. Making a call to database every time is costly.It is good to avoid stored proc as much as possible unless it is very necessary.If you write the same code in informatica, the informatica server will execute the code.

2.How to list Top 10 salary, without using Rank Transmission?
Ans:-
BY USING SORTER TRANSFORMATION USING SORTED PORT AS SAL AND FILTER TRANSFORMATION TO GET FIRST 10 RECORDS

3.I want load the data into target with out 1st and last record.

BY USING FIRST FUNCTION, TO GET THE 1ST RECORD,TO GET THE LAST RECORD ,

SIMPLY USEAGGREGATE TRANSFORAMATION IN THIS DO NOT APPLY GROUP BY FUNCTION

BY DEFALUT IT WILL GENERATE LAST RECORD

OR

USE RANK TRANS

IN =>PROPERTIES  NO OF TOP RANKS:1

NO OF BOTTOM  RANKS:LAST RECORD

4. can u apply SCD2 on flat file tgt? if yes wat is the procedure?

No, you can't apply SCD2 on flat files.

5. we have 6 records in source , i need 2nd record in one target and 5th record in one target or 2nd & 5th record in same target.

Another way is to use rank t/r and load data to target by

filtering 2 and 5 rows.

or

We can use Router t/r also.

create as many conditions u want.

say, if rownum=2 then flow the data to target 1

if rownum=5 then flow to another target

6. Re: If u r giving different parameter file names in workflow and session for a single mapping parameter, then which one will be executed? The one u given in workflow or the one u given in the session?

workflow parameter file

7. How to update records in Target, without using Update Strategy?

YES WITHOUT A pk WE CANT UPDATE THE REC u can write sql query at targetlevel.

in target table properties there isa option updateoverride.

IN SESSION PROPERTIES WE HAVE ONE OPTION TREAT ALL ROWS AS... THERE U SELECT

UPDATE OPTION AND IN THE TARGET PROPERTIES SELECT UPDTAE OPTION.

8. A TABLE CONTAINS SOME NULL VALUES . HOW TO GET (NOT APPLICABLE(NA))

IN PLACE OF THAT NULL VALUE IN TARGET .?

in a ports tab select particular column write N/A

in the Default value text box for the particular column

9. How to load time dimension?

Run the procedure to load the ttime dimension.Its not loaded frequently,but

once or twice a year.

10. What is the main data object present inbetween sourceand target.

The main data object present inbetween source and target is staging layer only,

Staging layer will do eliminate the inconsistency data and gives the result data

object

11.Can we use unconnected lookup as dynamic lookup?

NO

Unconnected lookup will return one port only. But dynamiclookup will return

more than one port and it update andinsert the targt while session runs.

12. LOOKUP Condition is nothing but a Join condition? What type of join

condition it,by default ? Using the LookUP Condition How many types of

relational conditionswe can make ?

lkp is always behave like left outer join..it ll give you all matched

records as well as ummatched records which is not present in base table...and

those unmatched records is ll be null in case of unconnected lkp trn.....

13.  have two flat files.. containing same type of data i want to load it

to dwh..how many source qualifires i need

If the 2 flat files have the same structure,then we can go for filelist

concept ininformatica.

only one source qualifier is needed and the source should be either of the

flat files.

14.when will we use unconnected & connected lookup? Howit will effect on the

performance of mapping?

Connected Lookup

Receives input values directly from the pipeline.

We can use a dynamic or static cache

Supports user-defined default values

Unconnected Lookup

Receives input values from the result of a :LKP expression

in another transformation.

We can use a static cache

Does not support user-defined default values

When you compared both basically connected lookup will return more values

and unconnected returns one value. conn lookup is in the same pipeline of

source and it

will accept dynamic caching. Unconn lookup don't have that faclity but

in some special cases we can use Unconnected. if output of one lookup is going

as input of another lookup this unconnected lookups are favourableMoreover if the

mapping requires multiple lookup using samelookup conditions then its better

to use unconnected lookup& call the lookup wherever required.This also

prevents the mapping from being complex.

15. how do u use sequence created in oracle in informatica?

 Explain with an simple example

Using Stored Porcedure Transformation we can call the Sequence Generator

16. in which situations do u go for sequence generator ?

In the Fallowing Situations we use Sequence Generator

Transformation

1)Creating Primary Key Values

2)Replacing The Missing Key Values

3)When You Apply the Cycle Through a Sequential Range Of

Numbers

17. write a query for how to eliminate the duplicate rows

without using distinct?

DELETE FROM EMP WHERE ROWID NOT IN (SELECT MAX(ROWID)FROM

EMP)GROUP BY EMPNO;

18. source is a flat file empname, empno, sal ram, 101, 1,000 sam, 102, 2,000 ques: my target needs the data to be loaded as sal -1000 and 2000 excluding commas target empname, empno, sal ram, 101, 1000 sam, 102, 2000 how to implement this?

OPEN THE SOURCE FILE-->EDIT-->REPLACE ALL , to NULL.

USE FIXED WITH LENGTH WHILE IMPORTING TO SOURCE AND .....

HOPE IT WILL HELP

19. wht is cdc?how to use it in creation of mappings?

CDC stands for change data capture

CDC defines Whenever data is changed in OLTP Systems Only

that data Will be captured and loaded into our Target

SCD's works internally based on cdc logic

basically cdc's implented by using effective date

20. how can we store previous session logs

Go to Session-->right click -->Select Edit Task then Goto -->Config Object
then set the property

Save Session Log By --Runs

Save Session Log for These Runs --->To Number of Historical Session logs you want

21.How to read rejected data or bad data from bad file and reload it to target?

Correction the rejected data and send to target relational tables using loadorder utility. Find out the rejected data by using column indicatior and row indicator.

21. In my source table 1000 rec's are there. I want to load 501 rec to 1000 rec into my Target table ?

select * from tab_name where rownum<=1000

minus

select * from tab_name where rownum<=500;

hi connect a sequence generator next to source qualifier into the expression.Use the filter trans to filter rows like where nextval>500.

22. I have a cobol program with a sub program. How ca i find that it is a dynamic call? or static call..?

1st u check the stmt call 'sub-name' or call 'ws-name'...

if it is call 'sub-name' then it is STATIC CALL

OR

If it is call 'ws-name' then it is DYNAMIC CALL.

Interview Question&ANSWERS-1

2011-05-28T06:00:00.000-07:00

1.How could we generate the sequence of key values without using sequence generator transformation in the target ??

TAKE EXPRESSION TRANSFORMATION AND DOUBLE CLICK ON THE

EXPRESSION TRANSFORMATION AND ADD TWO NEW PORTS

1.SEQ

2.SEQ_OUT

1.IN THE SEQ PORT CHECK V=VARIABLEPORT THERE YOU MENTION

EXPRESSION SEQ+1.

2.IN THE SEQ_OUT PORT CHECK O=OUTPUTPORT THERE YOU MENTION

EXPRESSION IIF(ISNULL(SEQ),O,SEQ)

IT WILL GIVE CORRECT RESULT

2. Normalizer transformation is not involved in Mapplet.. why???

Mapplet is a reusable logic that you can use acrossdifferent mappings. Normalizer is a dynamic transformation

which converts rows to columns or vice-versa, so that is dependendent on the input to the normalizer, it is not

fixed logic that you can reuse in other mappings.

3. Re: i want to load data in to two targets..one is dimension table and the other is fact table?how can i load ata a time?

Using dynamic lookup, you can load both simultaneously.

4.  what is shortcut in informatica? difference between shortcut,reusable object?

Short cut is a referenced object.We cannot edit on short cut objects.Shortcut objects are present under
shared folder.Mostly sources and targets will be the shortcut objects.Reusable object can be used multiple
times in a mapping.short cuts can be used in all the folders available in that repository, but reusable objects
are limited to that folder.

5. can v update d records in target using update stargey

without generationg  primary key ? explain

 no using update strategy without primary keys update is not possible. try and read teh session log file once.

it will display a msg updates are not supported without primary keys.update override in the target is to update

 the function in the update strategy t/tion and it updates only on non-primary key columns like dname, loc but not on deptno.

6. What is the exact difference b/w IN and EXIST in Oracle..?

EXIST is used as conditional operator in which u can write a queryIN is also used as a conditional operator in

which we gives a list of elements

7. what is threshold error in informatica?

When the target is used by the update strategy DD_REJECT,DD_UPDATE and some limited count, then if it the number

of rejected records exceed the count then the session ends with failed status.  This error is called Threshold Error.

8. If the values in a row are 1,2,3,3,4,,4,5 then how a rank transformation can rank these values

   1,2,3,3,4,,4,5

ANS:1,2,3,3,5,5,7

9. In seqence generator transformation maximum limit is reached,after reaching maximum limit how will u insert the data

When a Seq Gen Transformation reaches its maximum value and the sequence is not configured to cycle, the session fails

.Either you need to cycle the sequence or increase the end value.

10. i have 2 flat files one table contains duplicates another one no duplicates.... so i need like this i don't want duplicates...pl's give me ans...

place sorter transformation after source qualifier of duplicates flatfile and enable property distinct

11. what is meant by data driven.. in which scenario we use that..?

data driven is available at session level.it says that when we r using update strategy t/r ,how the integration service

 fetches the data and how to update/insert row in the database log.

Or

you can select the data driven option, where the Integration Service follows instructions coded into Update Strategy

transformations within the session mapping.

12. Can we lookup a table from source qualifier transformation. ie. unconnected lookup

You cannot lookup from a source qualifier directly. However, you can override the SQL in the source qualifier to join with the lookup table to perform the lookup.

13.how can we stop batch

By using server manager or pmcmd.

14. Batches - It Provides A Way to Group Sessions For Either Serial Or Parallel Execution By The Informatica Server. There Are Two Types Of Batches :

15.Can u start a batches with in a batch?

U can not. If u want to start batch that resides in a batch,create a new independent batch and copy the necessary sessions into the new batch.

16.Can u start a session inside a batch idividually?

We can start our required session only in case of sequential batch.in case of concurrent batch
we cant do like this.

17. whether Sequence generater T/r uses Caches? then what type of Cache it is

the seq t/r uses index cache for the sequential range of numbers for the generated keys.

18. HOW DO YOU CONNECT TO REMOTE SERVER ?

USING FTP THRU TELNET/PUTTY/COMMAND PROMPT

 V CAN CONNECT TO REMOTE SERVERS..

19. What is a command that used to run a batch?

pmcmd is a command line programme. this can be used to stop,run and abort the session.

20. If i done any modifications for my table in back end does it reflect in informatca warehouse or maping desginer or source analyzer?

NO. Informatica is not at all concern with back end database.It displays you all the information that is to be

stored in repository.If want to reflect back end changes toinformatica screens, again you have to import from back endto

 informatica by valid connection.And you have to replacethe existing files with imported files.

21. how many repositories can v create in informatica .

2 types of repositories can be created

1. standalone

2. Shared or Global repository

22. in which situations we go for pesistent cache in lookup  and

which situations go for shared lookup cache?

If the lookup does not change between sessions, you can configure the transformation to use a persistent

 lookupcache. When you run the session, the Integration Service rebuilds the persistent cache if any cache file

is missing or invalid.

23. What are the data movement modes in informatcia?

Datamovement modes determines how informatcia server handlesthe charector data.yoU choose the datamovement in

the informatica server configuration settings.Two types ofdatamovement modes avialable in informatica.

 ASCII mode

Uni code mode

24. How many ways you create ports?

1.Drag the port from another transforamtion

 2.Click the add buttion on the ports tab.

25. How do you create single lookup transformation using multiple tables?

we have the lOOKUP OVERRIDE Query in the Lookup transformation. Use the SQl Query to join the tables

you lookup on.Thsi is similar to what yo do at the Source Qualifier

26.Discuss which is better among incremental load, Normal Load and Bulk load

It depends on the requirement. Otherwise Incremental load which can be better as it takes only that data which is not available previously on the target.

27. What is the difference between Informatica server, Repository Server and Power center Server ?

Informatica Server and PowerCenter Server both mean the same. It is concerned with running of sessions and workflows.
The Repository Server or the Informatica Rep Server is concerned with connecting to the repository through the Rep Agent. One Repository Server can connect to many repositories at a time.
For the PowerCenter Server to be up and working it is necessary that the Repository Server is running.

According to performence bulk is better than normal.
But bolh having some conditions in source data
Conditions are like
1)does not containn any constraint in data.
2)dont use the double datatype if neccesory to use then use it as last row of the table.
3)it does not support

28. ow can we access MAINFRAME tables in INFORMATICA as a source ?

We got mainframe source as VSAM (Virtual Storage Access Method) files mainframe developers
converts these files as Flat files to access data into Informatica for our convenience. The bottom line is
The mainframe data files are in .Dat files(Flat Files) .

Union Transformation

2011-05-26T10:35:00.000-07:00

The Union transformation is a multiple input group transformation that you use to merge data from multiple pipelines or pipeline branches into one pipeline branch. It merges data from multiple sources similar to the UNION ALL SQL statement to combine the results from two or more SQL statements. Similar to the UNION ALL statement, the Union transformation does not remove duplicate rows.

The Integration Service processes all input groups in parallel. It concurrently reads sources connected to the Union transformation and pushes blocks of data into the input groups of the transformation. The Union transformation processes the blocks of data based on the order it receives the blocks from the Integration Service.

You can connect heterogeneous sources to a Union transformation. The transformation merges sources with matching ports and outputs the data from one output group with the same ports as the input groups.

The Union transformation is developed using the Multigroup External Procedure transformation.

Union Transformation Rules and Guidelines

Use the following rules and guidelines when you work with a Union transformation:

You can create multiple input groups, but only one output group.

All input groups and the output group must have matching ports. The precision, datatype, and scale must be identical across all groups.

The Union transformation does not remove duplicate rows. To remove duplicate rows, you must add another transformation such as a Router or Filter transformation.

You cannot use a Sequence Generator or Update Strategy transformation upstream from a Union transformation.

The Union transformation does not generate transactions.

Transaction Control transformation limitations

2011-05-26T10:27:00.000-07:00

Use the following rules and guidelines when you create a mapping with a Transaction Control transformation:

If the mapping includes an XML target, and you choose to append or create a new document on commit, the input groups must receive data from the same transaction control point.

Transaction Control transformations connected to any target other than relational, XML, or dynamic MQSeries targets are ineffective for those targets.

You must connect each target instance to a Transaction Control transformation.

You can connect multiple targets to a single Transaction Control transformation.

You can connect only one effective Transaction Control transformation to a target.

You cannot place a Transaction Control transformation in a pipeline branch that starts with a Sequence Generator transformation.

If you use a dynamic Lookup transformation and a Transaction Control transformation in the same mapping, a rolled-back transaction might result in unsynchronized target data.

Stored Procedure Transformation

2011-05-26T09:49:00.000-07:00

Stored Procedure Transformation

Transformation type:

Passive

Connected/Unconnected

A Stored Procedure transformation is an important tool for populating and maintaining databases. Database administrators create stored procedures to automate tasks that are too complicated for standard SQL statements.

A stored procedure is a precompiled collection of Transact-SQL, PL-SQL or other database procedural statements and optional flow control statements, similar to an executable script. Stored procedures are stored and run within the database.

You can run a stored procedure with the EXECUTE SQL statement in a database client tool, just as you can run SQL statements. Unlike standard SQL, however, stored procedures allow user-defined variables, conditional statements, and other powerful programming features

You might use stored procedures to complete the following tasks:

ü	Check the status of a target database before loading data into it.
ü	Determine if enough space exists in a database.

ü	Perform a specialized calculation.
ü	Drop and recreate indexes.

The stored procedure must exist in the database before creating a Stored Procedure transformation, and the stored procedure can exist in a source, target, or any database with a valid connection to the Integration Service

You might use a stored procedure to perform a query or calculation that you would otherwise make part of a mapping. For example, if you already have a well-tested stored procedure for calculating sales tax, you can perform that calculation through the stored procedure instead of recreating the same calculation in an Expression transformation.

One of the most useful features of stored procedures is the ability to send data to the stored procedure, and receive data from the stored procedure.

Input/Output Parameters

For many stored procedures, you provide a value and receive a value in return. These values are known as input and output parameters.

For example, a sales tax calculation stored procedure can take a single input parameter, such as the price of an item. After performing the calculation, the stored procedure returns two output parameters, the amount of tax, and the total cost of the item including the tax.

The Stored Procedure transformation sends and receives input and output parameters using ports, variables, or by entering a value in an expression, such as 10 or SALES.

If a stored procedure returns a result set rather than a single return value, the Stored Procedure transformation takes only the first value returned from the procedure

Connected and Unconnected

Connected. The flow of data through a mapping in connected mode also passes through the Stored Procedure transformation. All data entering the transformation through the input ports affects the stored procedure. You should use a connected Stored Procedure transformation when you need data from an input port sent as an input parameter to the stored procedure, or the results of a stored procedure sent as an output parameter to another transformation.

Unconnected. The unconnected Stored Procedure transformation is not connected directly to the flow of the mapping. It either runs before or after the session, or is called by an expression in another transformation in the mapping.

The following table compares connected and unconnected transformations:

If you want to	Use this mode
Run a stored procedure before or after a session.	Unconnected
Run a stored procedure once during a mapping, such as pre- or post-session.	Unconnected
Run a stored procedure every time a row passes through the Stored Procedure transformation.	Connected or Unconnected
Run a stored procedure based on data that passes through the mapping, such as when a specific port does not contain a null value.	Unconnected
Pass parameters to the stored procedure and receive a single output parameter.	Connected or Unconnected
Pass parameters to the stored procedure and receive multiple output parameters. Note: To get multiple output parameters from an unconnected Stored Procedure transformation, you must create variables for each output parameter.	Connected or Unconnected
Run nested stored procedures.	Unconnected
Call multiple times within a mapping.	Unconnected

Connected stored procedures run only in normal mode.

	Normal. The stored procedure runs where the transformation exists in the mapping on a row-by-row basis. This is useful for calling the stored procedure for each row of data that passes through the mapping, such as running a calculation against an input port. Connected stored procedures run only in normal mode.
	Pre-load of the Source. Before the session retrieves data from the source, the stored procedure runs. This is useful for verifying the existence of tables or performing joins of data in a temporary table.

	Post-load of the Source. After the session retrieves data from the source, the stored procedure runs. This is useful for removing temporary tables.
	Pre-load of the Target. Before the session sends data to the target, the stored procedure runs. This is useful for verifying target tables or disk space on the target system.

Post-load of the Target. After the session sends data to the target, the stored procedure runs. This is useful for re-creating indexes on the database.

To run multiple stored procedures that use the same database connection, set these stored procedures to run consecutively. If you do not set them to run consecutively, you might have unexpected results in the target. For example, you have two stored procedures: Stored Procedure A and Stored Procedure B. Stored Procedure A begins a transaction, and Stored Procedure B commits the transaction. If you run Stored Procedure C before Stored Procedure B, using another database connection, Stored Procedure B cannot commit the transaction because the Integration Service closes the database connection when it runs Stored Procedure C.

Note: The Integration Service fails sessions that contain stored procedure arguments with large objects.

Notice that the return value is a string value (Success) with the datatype VARCHAR2. Oracle is the only database to allow return values with string datatypes.

There are three ways to import a stored procedure in the Mapping Designer:

	Select the stored procedure icon and add a Stored Procedure transformation.
	Click Transformation > Import Stored Procedure.

Click Transformation > Create, and then select Stored Procedure.

When you import a stored procedure containing a period (.) in the stored procedure name, the Designer substitutes an underscore (_) for the period in the Stored Procedure transformation name.

Factless Fact Table

2011-05-24T06:53:00.000-07:00

Factless Fact Table

A factless fact table is a table that contains nothing but dimensional keys. Ralph Kimball’s earlier article is still the best source to learn this.
There are two types of factless tables. One is for capturing the event. An event establishes the relationship among the dimension members from various dimension but there is no measured value. The existence of the relationship itself is the fact.
This type of fact table itself can be used to generate the useful reports. You can count the number of occurrences with various criteria. For example, you can have a factless fact table to capture the student attendance (the example used by Ralph). The following questions can be answered:

Which class has the least attendance?
Which teachers taugh the most students?
What is the average number of attendance of a given course?

All the queries are based on the COUNT() with the GROUP BY queries. I think that the interesting metrics are the nested GROUP BY so you can first count and then apply other aggregate functions such as AVERAGE, MAX, MIX.
The other type of factless table is called Coverage table by Ralph. It is used to support negative analysis report. For example a Store that did not sell a product for a given period. To produce such report, you need to have a fact table to capture all the possible combinations. You can then figure out what is missing.

Option	Description
SQL Query	Defines a custom query that replaces the default query the Integration Service uses to read data from sources represented in this Source Qualifier transformation. A custom query overrides entries for a custom join or a source filter.
User-Defined Join	Specifies the condition used to join data from multiple sources represented in the same Source Qualifier transformation.
Source Filter	Specifies the filter condition the Integration Service applies when querying rows.
Number of Sorted Ports	Indicates the number of columns used when sorting rows queried from relational sources. If you select this option, the Integration Service adds an ORDER BY to the default query when it reads source rows. The ORDER BY includes the number of ports specified, starting from the top of the transformation. When selected, the database sort order must match the session sort order.
Tracing Level	Sets the amount of detail included in the session log when you run a session containing this transformation.
Select Distinct	Specifies if you want to select only unique rows. The Integration Service includes a SELECT DISTINCT statement if you choose this option.
Pre-SQL	Pre-session SQL commands to run against the source database before the Integration Service reads the source.
Post-SQL	Post-session SQL commands to run against the source database after the Integration Service writes to the target.
Output is Deterministic	Relational source or transformation output that does not change between session runs when the input data is consistent between runs. When you configure this property, the Integration Service does not stage source data for recovery if transformations in the pipeline always produce repeatable data.
Output is Repeatable	Relational source or transformation output that is in the same order between session runs when the order of the input data is consistent. When output is deterministic and output is repeatable, the Integration Service does not stage source data for recovery.