Informatica Interview Questions with Answers
Q3) Tell me what exactly, what was your role ?
A3) I worked as ETL Developer. I was also involved in requirement gathering, developing mappings,
checking source data. I did Unit testing (using TOAD), helped in User Acceptance Testing.
Q4) What kind of challenges did you come across in your project ?
A4) Mostly the challenges were to finalize the requirements in such a way so that different stakeholders
come to a common agreement about the scope and expectations from the project.
Q5) Tell me what was the size of your database ?
A5) Around 3 TB. There were other separate systems, but the one I was mainly using was around 3 TB.
Q6) what was the daily volume of records ?
A6) It used to vary, We processed around 100K-200K records on a daily basis, on weekends, it used to be
higher sometimes around 1+ Million records.
Q7) So tell me what were your sources ?
A7) Our Sources were mainly flat files, relational databases.
Q What tools did you use for FTP/UNIX ?
A For UNIX, I used Open Source tool called Putty and for FTP, I used WINSCP, Filezilla.
Q9) Tell me how did you gather requirements?
A9) We used to have meetings and design sessions with end users. The users used to give us sketchy
requirements and after that we used to do further analysis and used to create detailed Requirement
Specification Documents (RSD).
Q10) Did you follow any formal process or methodology for Requirement gathering ?
A10) As such we did not follow strict SDLC approach because requirement gathering is an iterative process.
But after creating the detailed Requirement Specification Documents, we used to take User signoff.
Q11) Tell me what are the steps involved in Application Development ?
A11) In Application Development, we usually follow following steps:
ADDTIP.
a) A - Analysis or User Requirement Gathering
b) D - Designing and Architecture
c) D - Development
d) T - Testing (which involves Unit Testing,System Integration Testing,
UAT - User Acceptance Testing
)
e) I - Implementation (also called deployment to production)
f) P - Production Support / Warranty
Q12) What are the drawbacks of Waterfall Approach ?
A12) This approaches assumes that all the User Requirements will be perfect before start of design
and development. That is not the case most of the time.Users can change their mind to add few more detailed
requirements or worse change the requirements drastically. So in those cases this approach (waterfall) is
likely to cause a delay in project which is a RISK to the project.
Q13) what is mapping design document ?
A13) In a mapping design document, we map source to target field, also document any special business logic
that needs to be implemented in the mapping.
Q14) What are diferent Data Warehousing Methodologies that you are familiar with ?
A14) In Data Warehousing, two methodologies are poopulare, 1st one is Ralph Kimbal and 2nd one is Bill Inmon.
We mainly followed Ralph Kimball's methodlogy in my last project.
In this methodlogy, we have a fact tables in the middle, surrounded by dimension tables.
This is also a basic STAR Schema which is the basic dimensional model.
A Snowflake schema. In a snowflake schema, we normalize one of the dimension tables.
Q15) What do you do in Bill Inmon Approach ?
A15) In Bill Inmon's approach, we try to create an Enterprise Data Warehouse using 3rd NF, and then
Data Marts are mainly STAR Schemas in 2nd NF.
Q16) How many mappings have you done ?
A16) I did over 35+ mappings, around 10+ were complex mappings.
Q17) What are Test cases or how did you do testing of Informatica Mappings ?
A17) Basically we take the SQL from Source Qualifier and check the source / target data in Toad.
Then we try to spot check data for various conditions according to mapping document and look for
any error in mappings.
For example, there may be a condition that if customer account does not exist then filter out that record
and write it to a reject file.
Q18) What are the other error handlings that you did in mappings?
A18) I mainly looked for non-numeric data in numeric fields, layout of a flat file may be different.
Also dates from flat file come as a string
Q19) How did you debug your mappings ?
A19) I used Informatica Debugger to check for any flags being set incorrectly. We see if the
logic / expressions are working or not. We may be expecting data
We use Wizard to configure the debugger.
Q20) Give me an example of a tough situation that you came across in Informatica Mappings
and how did you handle it ?
A20) Basically one of our colleagues had created a mapping that was using Joiner and mapping was taking a lot
of time to run, but the Join was in such a way that we could do the Join at Database Level (Oracle Level).
So I suggested and implemented that change and it reduced the run time by 40%.
Q21) Tell me what are various transformations that you have used ?
A21) I have used Lookup, Joiner, Update Strategy, Aggregator, Sorter etc.
Q22) How will you categorize various types of transformation ?
A22) Transformations can be connected or unconnected. Active or passive.
Q23) What are the different types of Transformations ?
A23) Transformations can be active transformation or passive transformations. If the number of output
rows are different than number of input rows then the transformation is an active transformation.
Like a Filter / Aggregator Transformation. Filter Transformation can filter out some records based
on condition defined in filter transformation.
Similarly, in an aggregator transformation, number of output rows can be less than input rows as
after applying the aggregate function like SUM, we could have less records.
Q24) What is a lookup transformation ?
A24) We can use a Lookup transformation to look up data in a flat file or a relational table,
view, or synonym.
We can use multiple Lookup transformations in a mapping.
The PowerCenter Server queries the lookup source based on the lookup ports in the
transformation. It compares Lookup transformation port values to lookup source column
values based on the lookup condition.
We can use the Lookup transformation to perform many tasks, including:
1) Get a related value.
2) Perform a calculation.
3) Update slowly changing dimension tables.
Q25) Did you use unconnected Lookup Transformation ? If yes, then explain.
A25) Yes. An Unconnected Lookup receives input value as a result of :LKP Expression in another
transformation. It is not connected to any other transformation. Instead, it has input ports,
output ports and a Return Port.
An Unconnected Lookup can have ONLY ONE Return PORT.
Q26) What is Lookup Cache ?
A26) The PowerCenter Server builds a cache in memory when it processes the first row of data in a
cached Lookup transformation.
It allocates the memory based on amount configured in the session. Default is
2M Bytes for Data Cache and 1M bytes for Index Cache.
We can change the default Cache size if needed.
Condition values are stored in Index Cache and output values in Data cache.
Q27) What happens if the Lookup table is larger than the Lookup Cache ?
A27) If the data does not fit in the memory cache, the PowerCenter Server stores the overflow values
in the cache files.
To avoid writing the overflow values to cache files, we can increase the default cache size.
When the session completes, the PowerCenter Server releases cache memory and deletes the cache files.
If you use a flat file lookup, the PowerCenter Server always caches the lookup source.
Q28) What is meant by "Lookup caching enabled" ?
A28) By checking "Lookup caching enabled" option, we are instructing Informatica Server to Cache lookup
values during the session.
Q29) What are the different types of Lookup ?
A29) When configuring a lookup cache, you can specify any of the following options:
a) Persistent cache.You can save the lookup cache files and reuse them the next time the
PowerCenter Server processes a Lookup transformation configured to use the cache.
b) Recache from source. If the persistent cache is not synchronized with the lookup table,
you can configure the Lookup transformation to rebuild the lookup cache.
c) Static cache. You can configure a static, or read-only, cache for any lookup source.
By default, the PowerCenter Server creates a static cache. It caches the lookup file or table
and looks up values in the cache for each row that comes into the transformation.
When the lookup condition is true, the PowerCenter Server returns a value from the lookup
cache. The PowerCenter Server does not update the cache while it processes the Lookup
transformation.
d) Dynamic cache. If you want to cache the target table and insert new rows or update
existing rows in the cache and the target, you can create a Lookup transformation to
use a dynamic cache.
The PowerCenter Server dynamically inserts or updates data in the lookup cache and passes
data to the target table.
You cannot use a dynamic cache with a flat file lookup.
e) Shared cache. You can share the lookup cache between multiple transformations. You can
share an unnamed cache between transformations in the same mapping. You can share a
named cache between transformations in the same or different mappings.
Q30) What is a Router Transformation ?
A30) A Router transformation is similar to a Filter transformation because both transformations
allow you to use a condition to test data. A Filter transformation tests data for one condition
and drops the rows of data that do not meet the condition.
However, a Router transformation tests data for one or more conditions and gives you the
option to route rows of data that do not meet any of the conditions to a default output group.
Q31) What is a sorter transformation ?
A31) The Sorter transformation allows you to sort data. You can sort data in ascending or
descending order according to a specified sort key. You can also configure the Sorter
transformation for case-sensitive sorting, and specify whether the output rows should be
distinct. The Sorter transformation is an active transformation.
It must be connected to the data flow.
Q32) What is a UNION Transformation ?
A32) The Union transformation is a multiple input group transformation that you can 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.
You can connect heterogeneous sources to a Union transformation. The Union
transformation merges sources with matching ports and outputs the data from one output
group with the same ports as the input groups.
Q33) What is Update Strategy ?
A33) Update strategy is used to decide on how you will handle updates in your project.
When you design your data warehouse, you need to decide what type of information to store
in targets. As part of your target table design, you need to determine whether to maintain all
the historic data or just the most recent changes.
For example, you might have a target table, T_CUSTOMERS, that contains customer data.
When a customer address changes, you may want to save the original address in the table
instead of updating that portion of the customer row. In this case, you would create a new row
containing the updated address, and preserve the original row with the old customer address.
This illustrates how you might store historical information in a target table. However, if you
want the T_CUSTOMERS table to be a snapshot of current customer data, you would
update the existing customer row and lose the original address.
The model you choose determines how you handle changes to existing rows.
In PowerCenter, you set your update strategy at two different levels:
1) Within a session. When you configure a session, you can instruct the PowerCenter Server
to either treat all rows in the same way (for example, treat all rows as inserts), or use
instructions coded into the session mapping to flag rows for different database operations.
2) Within a mapping. Within a mapping, you use the Update Strategy transformation to flag
rows for insert, delete, update, or reject.
Note: You can also use the Custom transformation to flag rows for insert, delete, update, or reject.
Q34) Joiner transformation?
A34) A Joiner transformation joins two related heterogenous sources residing in different location. The combination of
sources can be varied like
- two relational tables existing in seperate database.
- two flat files in potentially different file systems.
- two different ODBC sources.
- two instances of the same XML sources.
- a relational table and a flat file source.
- a relational table ans a XML source.
Q35) How many types of Joins can you use in a Joiner ?
A35) There can be 4 types of joins
a) Normal Join (Equi Join)
b) Master Outer Join - In master outer join you get all rows from Detail table
c) Detail Outer Join - In Detail Outer Join you get all rows from Master table
d) FULL Outer Join
Q36) What are Mapping Parameter & variables ?
A36) We Use Mapping parameter and variables to make mappings more flexible.
Value of a parameter does not change during session, whereas the value stored in a variable can change.
Q37) TELL ME ABOUT PERFORMANCE TUNING IN INFORMATICA?
A37) Basically Performance Tuning is an Iterative process, we can do lot of tuning at database level and
if database queries are faster then Informatica workflows will be automatically faster.
For Performance tuning, first we try to identify the source / target bottlenecks. Meaning that first we
see what can be do so that Source data is being retrieved as fast possible.
We try to filter as much data in SOURCE QUALIFIER as possible. If we have to use a filter then filtering
records should be done as early in the mapping as possible.
If we are using an aggregator transformation then we can pass the sorted input to aggregator. We need
to ideally sort the ports on which the GROUP BY is being done.
Depending on data an unconnected Lookup can be faster than a connected Lookup.
Also there should be as less transformations as possible. Also in Source Qualifier, we should bring only
the ports which are being used.
For optimizing the TARGET, we can disable the constraints in PRE-SESSION SQL and use BULK LOADING.
IF the TARGET Table has any indexes like primary key or any other indexes / constraints then BULK Loading
will fail. So in order to utilize the BULK Loading, we need to disable the indexes.
In case of Aggregator transformation, we can use incremental loading depending on requirements.
Q18) How did you do Error handling in Informatica ?
A18) Typically we can set the error flag in mapping based on business requirements and for each type of error,
we can associate an error code and error description and write all errors to a separate error table so that
we capture all rejects correctly.
Also we need to capture all source fields in a ERR_DATA table so that if we need to correct the erroneous
data fields and Re-RUN the corrected data if needed.
Usually there could be a separate mapping to handle such error data file.
Typical errors that we come across are
1) Non Numeric data in Numeric fields.
2) Incorrect Year / Months in Date fields from Flat files or varchar2 fields.
Q19) Did you work in Team Based environments ?
A19) Yes, we had versioning enabled in Repository.
Q12) What kind of workflows or tasks have you used ?
A12) I have used session, email task, command task, event wait tasks.
Q21) Explain the process that happens when a WORKFLOW Starts ?
A21) when a workflow starts, the informatica server retrieves mappings, workflows & session metadata from the
repository to extract the data from the source, transform it & load it into Target.
- it also runs the task in the workflow.
- The informatica server uses load manager & Data Transformation manager (DTM) process to run the workflow.
- The informatica server can combine data from different platforms & source types. For ex. joins data from
flat file & an oracle source. It can also load data to different platforms & target types. For ex. can
load, transform data to both a FF target & a MS SQL server db in same session.
Q27) What all tasks can we perform in a Repository Manager ?
A27) The Repository Manager allows you to navigate through multiple folders & repositories & perform basic
repository tasks.
Some examples of these tasks are:
- Add or remove a repository
- work with repository connections : can connect to one repo or multiple repositories.
- view object dependencies : b4 you remove or change an object can view dependencies to see
the impact on other objects.
- terminate user connections : can use the repo manager to view & terminate residual user connections
- Exchange metadata with other BI tools : can export & import metadata from other BI tools like cognos, BO..
IN REPOSITORY MANAGER NAVIGATOR WINDOW, WE FIND OBJECTS LIKE :
_ Repositories : can be standalone, local or global.
- Deployment groups : contain collections of objects for deployment to another repository in the domain.
- Folders : can be non-shared.
- Nodes : can inlcude sessions, sources, targets, transformation, mapplets, workflows, tasks, worklets & mappings.
- Repository objects : same as nodes along with workflow logs & sessions logs.
Q) Did you work on ETL strategy ?
A) Yes, my Data modeler & ETL lead along with developers analysed & worked on dependencies between tasks(workflows).
well there are Push & Pull strategies which is used to determine how the data comes from source systems to ETL server.
Push strategy : with this strategy, the source system pushes data ( or send the data ) to the ETL server.
Pull strategy : with this strategy, the ETL server pull the data(or gets the data) from the source system.
Q20) How did you migrate from Dev environment to UAT / PROD Environment ?
A20) We can do a folder copy or export the mapping in XML Format and then Import it another Repository or folder.
In my last project we used Deployment groups.
Q) External Scheduler ?
A) with exteranal schedulers, we used to run informatica jobs like workflows using pmcmd command in parallel with
some oracle jobs like stored procedures. there were variuos kinds of external schedulers available in market
like AUtosys, Maestro, Control M . So we can use for mix & match for informatica & oracle jobs using external schedulers.
Q10) What is a Slowly Changing Dimension ?
A10) In a Data Warehouse, usually the updates in Dimension tables don't happen frequently.
So if we want to capture changes to a dimension, we usually resolve it with Type 2 or
Type 3 SCD. So basically we keep historical data with SCD.
Q11) Explain SLOWLY CHANGING DIMENSION (SCD) Type, which one did you use ?
A11) There are 3 ways to resolve SCD. First one is Type 1, in which we overwrite the
changes, so we loose history.
Type 1
OLD RECORD
==========
Surr Dim Cust_Id Cust Name
Key (Natural Key)
======== =============== =========================
1 C01 ABC Roofing
NEW RECORD
==========
Surr Dim Cust_Id Cust Name
Key (Natural Key)
======== =============== =========================
1 C01 XYZ Roofing
I mainly used Type 2 SCD.
In Type 2 SCD, we keep effective date and expiration date.
For older record, we update the exp date as the The current Date - 1, if the changes
happened today.
In the current Record, we keep Current Date as
Surr Dim Cust_Id Cust Name Eff Date Exp Date
Key (Natural Key)
======== =============== ========================= ========== =========
1 C01 ABC Roofing 1/1/0001 12/31/9999
Suppose on 1st Oct, 2007 a small business name changes from ABC Roofing to XYZ Roofing, so if we want
to store the old name, we will store data as below:
Surr Dim Cust_Id Cust Name Eff Date Exp Date
Key (Natural Key)
======== =============== ========================= ========== =========
1 C01 ABC Roofing 1/1/0001 09/30/2007
101 C01 XYZ Roofing 10/1/2007 12/31/9999
We can implment TYPE 2 as a CURRENT RECORD FLAG Also
In the current Record, we keep Current Date as
Surr Dim Cust_Id Cust Name Current_Record
Key (Natural Key) Flag
======== =============== ========================= ==============
1 C01 ABC Roofing Y
Suppose on 1st Oct, 2007 a small business name changes from ABC Roofing to XYZ Roofing, so if we want
to store the old name, we will store data as below:
Surr Dim Cust_Id Cust Name Current_Record
Key (Natural Key) Flag
======== =============== ========================= ==============
1 C01 ABC Roofing N
101 C01 XYZ Roofing Y
Q3) What is a Mapplets? Can you use an active transformation in a
Mapplet?
A3) A mapplet has one input and output transformation and in between we
can have various mappings.
A mapplet is a reusable object that you create in the Mapplet
Designer. It contains a set of transformations and allows you to reuse
that transformation logic in multiple mappings.
Yes we can use active transformation in a Mapplet.
1)A data warehouse is a relational database that is designed for query and analysis
rather than for transaction processing. It usually contains historical data derived
from transaction data, but it can include data from other sources. It separates
analysis workload from transaction workload and enables an organization to
consolidate data from several sources.
In addition to a relational database, a data warehouse environment includes an
extraction, transportation, transformation, and loading (ETL) solution, an online
analytical processing (OLAP) engine, client analysis tools, and other applications
that manage the process of gathering data and delivering it to business users.
A common way of introducing data warehousing is to refer to the characteristics of
a data warehouse as set forth by William Inmon:
Subject Oriented
Integrated
Nonvolatile
Time Variant
2)Surrogate Key
Data warehouses typically use a surrogate, (also known as artificial or identity key), key for the dimension tables primary keys. They can use Infa sequence generator, or Oracle sequence, or SQL Server Identity values for the surrogate key.
There are actually two cases where the need for a "dummy" dimension key arises:
1) the fact row has no relationship to the dimension (as in your example), and
2) the dimension key cannot be derived from the source system data.
3)Facts & Dimensions form the heart of a data warehouse. Facts are the metrics that business users would use for making business decisions. Generally, facts are mere numbers. The facts cannot be used without their dimensions. Dimensions are those attributes that qualify facts. They give structure to the facts. Dimensions give different views of the facts. In our example of employee expenses, the employee expense forms a fact. The Dimensions like department, employee, and location qualify it. This was mentioned so as to give an idea of what facts are.
Facts are like skeletons of a body.
Skin forms the dimensions. The dimensions give structure to the facts.
The fact tables are normalized to the maximum extent.
Whereas the Dimension tables are de-normalized since their growth would be very less.
4)Type 2 Slowly Changing Dimension
In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The newe record gets its own primary key.Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.
SCD Type 2
Slowly changing dimension Type 2 is a model where the whole history is stored in the database. An additional dimension record is created and the segmenting between the old record values and the new (current) value is easy to extract and the history is clear.
The fields 'effective date' and 'current indicator' are very often used in that dimension and the fact table usually stores dimension key and version number.
4)CRC Key
Cyclic redundancy check, or CRC, is a data encoding method (noncryptographic) originally developed for detecting errors or corruption in data that has been transmitted over a data communications line.
During ETL processing for the dimension table, all relevant columns needed to determine change of content from the source system (s) are combined and encoded through use of a CRC algorithm. The encoded CRC value is stored in a column on the dimension table as operational meta data. During subsequent ETL processing cycles, new source system(s) records have their relevant data content values combined and encoded into CRC values during ETL processing. The source system CRC values are compared against CRC values already computed for the same production/natural key on the dimension table. If the production/natural key of an incoming source record are the same but the CRC values are different, the record is processed as a new SCD record on the dimension table. The advantage here is that CRCs are small, usually 16 or 32 bytes in length, and easier to compare during ETL processing versus the contents of numerous data columns or large variable length columns.
5)Data partitioning, a new feature added to SQL Server 2005, provides a way to divide large tables and indexes into smaller parts. By doing so, it makes the life of a database administrator easier when doing backups, loading data, recovery and query processing.
Data partitioning improves the performance, reduces contention and increases availability of data.
Objects that may be partitioned are:
• Base tables
• Indexes (clustered and nonclustered)
• Indexed views
Q)Why we use stored procedure transformation?
A Stored Procedure transformation is an important tool for populating and maintaining databases
. Database administrators create stored procedures to automate time-consuming tasks that are too
complicated for standard SQL statements.
You might use stored procedures to do 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.
Q)What r the types of data that passes between informatica server and stored procedure?
types of data
Input/Out put parameters
Return Values
Status code.
Q) What is source qualifier transformation?
A) 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 represents the rows that the
Informatica Server reads when it executes a session.
The Transformation which Converts the source(relational or flat) datatype to
Informatica datatype.So it works as an intemediator between and source and informatica server.
Tasks performed by qualifier transformation:-
1. Join data originating from the same source database.
2. Filter records when the Informatica Server reads source data.
3. Specify an outer join rather than the default inner join.
4. Specify sorted ports.
5. Select only distinct values from the source.
6. Create a custom query to issue a special SELECT statement for the Informatica Server to read source data.
============================================================================================================
============================================================================================================
informatica interview questions and answers
1] HOW CAN YOU RECOGNIZE WHETHER OR NOT THE NEWLY ADDED ROWS IN THE SOURCE ARE GETS INSERT IN THE TARGET?
Answer: In the Type2 maping we have three options to recognise the newly added rows Version number Flagvalue Effective date Range.
2] WHAT IS THE DIFFERENCE BETWEEN INFORMATICA 7.0 AND 8.0?
Answer: The basic diff b/w in between informatica 8.0 and informatica7.0 is that in 8.0series informatica corp has introduces powerexchnage concept
3] PERFORMANCE TUNING IN INFORMATICA?
Answer: The goal of performance tuning is optimize session performance so sessions run during the available load window for the Informatica Server.Increase the session performance by following. The performa
4] DIFFERENCES BETWEEN NORMALIZER AND NORMALIZER TRANSFORMATION.
Answer: Normalizer: It is a transormation mainly using for cobol sources, it's change the rows into coloums and columns into rows Normalization:To remove the retundancy and inconsitecy
5] WHAT IS THE TARGET LOAD ORDER?
Answer: You specify the target loadorder based on source qualifiers in a maping.If you have the multiple source qualifiers connected to the multiple targets,You can designatethe order in which informatica ser
6] DIFF BETWEEN STATIC AND DYNAMIC CACHE? AND PLEASE EXPLAIN WITH ONE EXAMPLE?
Answer: Difference between static and dynamic cache- Static- Once the data is cached , it will not change. example unconnected lookup uses static cache. Dynamic- The cache is updated as to reflect the upda
7] WHAT IS THE USE OF INCREMENTAL AGGREGATION? EXPLAIN ME IN BRIEF WITH AN EXAMPLE.
Answer: Its a session option. When the informatica server performs incremental aggr. it passes new source data through the mapping and uses historical chache data to perform new aggregation caluculations incr
8] WHAT IS THE DIFFERENCE BETWEEN INFORMATICS 7X AND 8X AND WHAT IS LATEST VERSION?
Answer: Java Transformation available in the 8x version and it is not available in 7x version.
9] HOW DO WE DO UNIT TESTING IN INFORMATICA? HOW DO WE LOAD DATA IN INFORMATICA?
Answer: Unit testing are of two types 1. Quantitaive testing 2.Qualitative testing Steps. 1.First validate the mapping 2.Create session on themapping and then run workflow. Once the session
10] IS SORTER AN ACTIVE OR PASSIVE TRANSFORMATION? WHAT HAPPENS IF WE UNCHECK THE DISTINCT OPTION IN SORTER? WILL IT BE UNDER ACTIVE OR PASSIVE TRANSFORMATION?
Answer: Sorter is an active transformation. if you don't check the distinct option it is considered as a passive transformation. becos this distinct option eliminates the duplicate records from the table.
11] HOW THE INFORMATICA SERVER SORTS THE STRING VALUES IN RANK TRANSFORMATION?
Answer: When Informatica Server runs in UNICODE data movement mode ,then it uses the sort order configured in session properties.
12] EXPLAIN ABOUT INFORMATICA SERVER ARCHITECTURE?
Answer: Informatica server,load manager/rs,data transfer manager,reader,temp server and writer are the components of informatica server. first load manager sends a request to the reader if the reader is ready
13] HOW CAN YOU CREATE OR IMPORT FLAT FILE DEFINITION IN TO THE WAREHOUSE DESIGNER?
Answer: You can not create or import flat file defintion in to warehouse designer directly.Instead you must analyze the file in source analyzer,then drag it into the warehouse designer. When you drag the fl
14] HOW CAN YOU IMPROVE SESSION PERFORMANCE IN AGGREGATOR TRANSFORMATION?
Answer: One way is supplying the sorted input to aggregator transformation. In situations where sorted input cannot be supplied, we need to configure data cache and index cache at session/transformation level
15] WHAT IS THE DIFFERENCE BETWEEN STOP AND ABORT?
Answer: stop: _______If the session u want to stop is a part of batch you must stop the batch, if the batch is part of nested batch, Stop the outer most batch Abort:---- You can issue the abort comma
16] IN UPDATE STRATEGY TARGET TABLE OR FLAT FILE WHICH GIVES MORE PERFORMANCE? WHY?
Answer: Pros: Loading, Sorting, Merging operations will be faster as there is no index concept and Data will be in ASCII mode. Cons: There is no concept of updating existing records in flat file. As the
17] HOW MANY TYPES OF DIMENSIONS ARE AVAILABLE IN INFORMATICA?
Answer: There r 3 types of dimensions 1.star schema 2.snowflake schema 3.glaxy schema
18] WHAT IS DIFFERENCE B/W INFORMATICA 7.1 AND ABINITIO
Answer: There is a lot of diffrence between informatica an Ab Initio In Ab Initio we r using 3 parllalisim but Informatica using 1 parllalisim In Ab Initio no scheduling option we can scheduled manul
19] WHEN WE CREATE A TARGET AS FLAT FILE AND SOURCE AS ORACLE. HOW CAN I SPECIFY FIRST ROWS AS COLUMN NAMES IN FLAT FILES.
Answer: Use a pre sql statement....but this is a hardcoding method...if you change the column names or put in extra columns in the flat file, you will have to change the insert statement
20] WHAT IS THE DIFFERENCE BETWEEN FILTER AND LOOKUP TRANSFORMATION?
Answer: 1) Filter transformation is an Active transformation and Lookup is a Passive transformation 2) Filter transformation is used to Filter rows based on condition and Lookup is used to to look up data
21] HOW DO YOU CONFIGURE MAPPING IN INFORMATICA
Answer: You should configure the mapping with the least number of transformations and expressions to do the most amount of work possible. You should minimize the amount of data moved by deleting unnecessary l
22] HOW CAN WE USE PMCMD COMMAND IN A WORKFLOW OR TO RUN A SESSION?
Answer: in the command task there is a option pression. we can write appropriate command of pmcmd to run workflow.
23] WHAT ARE THE OUTPUT FILES THAT THE INFORMATICA SERVER CREATES DURING THE SESSION RUNNING?
Answer: Informatica server log: Informatica server(on unix) creates a log for all status and error messages(default name: pm.server.log). It also creates an error log for error messages. These files will b
24] IN DIMENSIONAL MODELING FACT TABLE IS NORMALIZED OR DENORMALIZED? IN CASE OF STAR SCHEMA AND INCASE OF SNOW FLAKE SCHEMA?
Answer: In Dimensional modeling, Star Schema: A Single Fact table will be surrounded by a group of Dimensional tables comprise of de- normalized data Snowflake Schema: A Single Fact table will be surrounded b
25] DISCUSS THE ADVANTAGES & DISADVANTAGES OF STAR & SNOWFLAKE SCHEMA?
Answer: In a STAR schema there is no relation between any two dimension tables, whereas in a SNOWFLAKE schema there is a possible relation between the dimension tables.
26] HOW MANY TYPES OF FACTS AND WHAT ARE THEY?
Answer: There are Factless Facts:Facts without any measures. Additive Facts:Fact data that can be additive/aggregative. Non-Additive facts: Facts that are result of non-additon Semi-Additive Facts: Only
27] WHAT IS SOURCE QUALIFIER TRANSFORMATION?
Answer: SQ transformation is a transformation which is automatically generated to read data from source tables into informatica designer.
28] WHEN DO U USE A UNCONNECTED LOOKUP AND CONNECTED LOOKUP.... WHAT IS THE DIFFERENCE BETWEEN DYNAMIC AND STATIC LOOKUP...Y AND WHEN DO V USE THESE TYPES OF LOOKUPS (I.E. DYNAMIC AND STATIC)
Answer: In static lookup cache, you cache all the lookup data at the starting of the session. in dynamic lookup cache, you go and query the database to get the lookup value for each record which needs the loo
29] WHICH TASKS CAN BE PERFORMED ON PORT LEVEL (USING ONE SPECIFIC PORT)?
Answer: I think unconnected Lookup or expression transformation can be used for single port for a row.
30] HOW CAN U WORK WITH REMOTE DATABASE IN INFORMATICA? DID U WORK DIRECTLY BY USING REMOTE CONNECTIONS?
Answer: To work with remote datasource u need to connect it with remote connections.But it is not preferable to work with that remote source directly by using remote connections .Instead u bring that source
31] CAN ANYONE EXPLAIN ERROR HANDLING IN INFORMATICA WITH EXAMPLES SO THAT IT WILL BE EASY TO EXPLAIN THE SAME IN THE INTERVIEW?
Answer: Go to the session log file there we will find the information regarding to the session initiation process, errors encountered. load summary. so by seeing the errors encountered during the
32] WHAT IS DIFFERENCE BETWEEN IIF AND DECODE FUNCTION
Answer: You can use nested IIF statements to test multiple conditions. The following example tests for various conditions and returns 0 if sales is zero or negative: IIF( SALES > 0, IIF( SALES < 50, SALAR
33] DIFFERENCE BETWEEN RANK AND DENSE RANK?
Answer: Rank: 1 2<--2nd position 2<--3rd position 4 5 Same Rank is assigned to same totals/numbers. Rank is followed by the Position. Golf game ususally Ranks this way. This is usually a Gold Ranking.
34] WHAT IS MEANT BY AGGREGATE FACT TABLE AND WHERE IS IT USED?
Answer: Basically fact tables are two kinds 1. Aggregated factable and Factless fact table. Agregated factable has aggregarted columns. for eg. Total-Sal, Dep-Sal. where as in factless factable will not have
35] WHAT IS THE DIFFERENCE BETWEEN CONSTRAIND BASE LOAD ORDERING AND TARGET LOAD PLAN
Answer: Constraint based load ordering example: Table 1---Master Table 2---Detail If the data in table1 is dependent on the data in table2 then table2 should be loaded first.In such cases to con
36] WHAT IS THE DIFFERENCE BETWEEN CONNECTED AND UNCONNECTED STORED PROCEDURES?
Answer: 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
37] WHAT ARE MAIN ADVANTAGES AND PURPOSE OF USING NORMALIZER TRANSFORMATION IN INFORMATICA?
Answer: Narmalizer Transformation is used mainly with COBOL sources where most of the time data is stored in de-normalized format. Also, Normalizer transformation can be used to create multiple rows from a si
38] HOW CAN WE PARTITION A SESSION IN INFORMATICA?
Answer: The Informatica PowerCenter Partitioning option optimizes parallel processing on multiprocessor hardware by providing a thread-based architecture and built-in data partitioning. GUI-based tools redu
39] WHAT IS UPDATE STRATEGY TRANSFORMATION?
Answer: The model you choose constitutes your update strategy, how to handle changes to existing rows. In PowerCenter and PowerMart, you set your update strategy at two different levels: Within a session.
40] WHEN DO U WE USE DYNAMIC CACHE AND WHEN DO WE USE STATIC CACHE IN AN CONNECTED AND UNCONNECTED LOOKUP TRANSFORMATION
Answer: We use dynamic cache only for connected lookup. We use dynamic cache to check whether the record already exists in the target table are not. And depending on that, we insert,update or delete the recor
41] TO ACHIEVE THE SESSION PARTITION WHAT ARE THE NECESSARY TASKS YOU HAVE TO DO?
Answer: Configure the session to partition source data. Install the informatica server on a machine with multiple CPU’s.
42] WHAT ARE THE TYPES OF METADATA THAT STORES IN REPOSITORY?
Answer: Following are the types of metadata that stores in the repository:- Database connections Global objects Mappings Mapplets Multidimensional metadata Reusable transformations Sessions and batches
43] HOW TO RECOVER SESSIONS IN CONCURRENT BATCHES?
Answer: If multiple sessions in a concurrent batch fail, you might want to truncate all targets and run the batch again. However, if a session in a concurrent batch fails and the rest of the sessions complet
44] WHAT ARE THE NEW FEATURES IN INFORMATICA 5.0?
Answer: You can Debug u r maping in maping designer You can view the work space over the entire screen The designer displays a new icon for a invalid mapings in the navigator window You can use a dynamic l
45] WHAT ARE TWO TYPES OF PROCESSES THAT INFORMATICA RUNS THE SESSION?
Answer: Load manager Process: Starts the session, creates the DTM process, and sends post-session email when the session completes. The DTM process. Creates threads to initialize the session, read, write, an
46] ON A DAY, I LOAD 10 ROWS IN MY TARGET AND ON NEXT DAY IF I GET 10 MORE ROWS TO BE ADDED TO MY TARGET OUT OF WHICH 5 ARE UPDATED ROWS HOW CAN I SEND THEM TO TARGET? HOW CAN I INSERT AND UPDATE THE RECORD?
Answer: We can use do this by identifying the granularity of the target table . We can use CRC external procedure after that to compare newly generated CRC no. with the old one and if they do not match then
47] WHAT IS THE DEFAULT JOIN THAT SOURCE QUALIFIER PROVIDES?
Answer: Inner equi join.
48] WHAT ARE THE JOIN TYPES IN JOINER TRANSFORMATION?
Answer: Normal (Default) Master outer Detail outer Full outer.
49] WHAT ARE THE METHODS FOR CREATING REUSABLE TRANSFORMATIONS?
Answer: Two methods:- 1.Design it in the transformation developer. 2.Promote a standard transformation from the mapping designer.After you add a transformation to the mapping , You can promote it to the sta
50] IF YOU HAVE FOUR LOOKUP TABLES IN THE WORKFLOW. HOW DO YOU TROUBLESHOOT TO IMPROVE PERFORMANCE?
Answer: There r many ways to improve the mapping which has multiple lookups. 1) we can create an index for the lookup table if we have permissions(staging area). 2) divide the lookup mapping into two (a
51] SCD MAPPINGS ARE MOSTLY USED UNCONNECTED LOOKUP TRANSFORMATION. UNCONNECTED LOOKUP USED STATIC CACHE ONLY. AT THIS TIME HOW CAN YOU INSERT OR UPDATE DATE IN TARGET BY USING STATIC CACHE?
Answer: There is no connected lookup t/r n by default it takes static cache.For insert n update operations we use dynamic cache.
52] WHAT ARE THE JOINER CACHES?
Answer: When a Joiner transformation occurs in a session, the Informatica Server reads all the records from the master source and builds index and data caches based on the master rows. After building the ca
53] WHY DID U USE UPDATE STATEGY IN YOUR APPLICATION?
Answer: Update Strategy is used to drive the data to be Inert, Update and Delete depending upon some condition. You can do this on session level tooo but there you cannot define any condition.For eg: If you w
54] WHAT IS DIFFERENCE BETWEEN PARTIONING OF RELATONAL TARGET AND PARTITIONING OF FILE TARGETS?
Answer: Partition's can be done on both relational and flat files. Informatica supports following partitions 1.Database partitioning 2.RoundRobin 3.Pass-through 4.Hash-Key partitioning 5.Key
55] WHAT ARE THE NEW FEATURES OF THE SERVER MANAGER IN THE INFORMATICA 5.0?
Answer: You can use command line arguments for a session or batch.This allows you to change the values of session parameters,and mapping parameters and maping variables. Parallel data processing: This featur
56] WHAT IS THE DIFFERENCE BETWEEN SUMMARY FILTER AND DETAIL FILTER?
Answer: Summary filter can be applieid on a group of rows that contain a common value.where as detail filters can be applied on each and every rec of the data base.
57] WHAT ARE THE CONNECTED OR UNCONNECTED TRANSFORMATIONS?
Answer: An unconnected transforamtion is not connected to other transformations in the mapping.Connected transforamation is connected to other transforamtions in the mapping.
58] HOW TO IMPORT ORACLE SEQUENCE INTO INFORMATICA.
Answer: CREATE ONE PROCEDURE AND DECLARE THE SEQUENCE INSIDE THE PROCEDURE,FINALLY CALL THE PROCEDURE IN INFORMATICA WITH THE HELP OF STORED PROCEDURE TRANSFORMATION.
59] WHAT ARE THE REAL TIMES PROBLEMS GENERALLY COME UP WHILE DOING/RUNNING MAPPING/ANY TRANSFORMATION? CAN ANYBODY EXPLAIN WITH EXAMPLE.
Answer: Here are few real time examples of problems while running informatica mappings: 1) Informatica uses OBDC connections to connect to the databases. The database passwords (production ) is changed in
60] IF A SESSION FAILS AFTER LOADING OF 10,000 RECORDS IN TO THE TARGET. HOW CAN U LOAD THE RECORDS FROM 10001 THE RECORD WHEN U RUN THE SESSION NEXT TIME?
Answer: As explained above informatcia server has 3 methods to recovering the sessions.Use performing recovery to load the records from where the session fails.
61] WHY DID YOU USE STORED PROCEDURE IN YOUR ETL APPLICATION?
Answer: Usage of stored procedure has the following advantages 1 checks the status of the target database 2 drops and recreates indexes 3 determines if enough space exists in the database 4 perfor
62] WHAT ARE THE DIFFERENCE BETWEEN JOINER TRANSFORMATION AND SOURCE QUALIFIER TRANSFORMATION?
Answer: You can join hetrogenious data sources in joiner transformation which we can not achieve in source qualifier transformation. You need matching keys to join two relational sources in source qualifier
63] EXPLAIN USE OF UPDATE STRATEGY TRANSFORMATION
Answer: This is the important transformation,is used to maintain the history data or just most recent changes into the target table. We can set or flag the records by using these two levels. 1) Within a ses
64] WHAT ARE THE DIFFERENCE BETWEEN VIEW AND MATERIALIZED VIEW?
Answer: Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse. A materialized view provides indirect access to
65] IDENTIFYING BOTTLENECKS IN VARIOUS COMPONENTS OF INFORMATICA AND RESOLVING THEM.
Answer: The best way to find out bottlenecks is writing to flat file and see where the bottle neck is .
66] WHAT ARE THE BASIC NEEDS TO JOIN TWO SOURCES IN A SOURCE QUALIFIER?
Answer: Basic need to join two sources using source qualifier: 1) Both sources should be in same database 2) The should have at least one column in common with same data types
67] THIS IS A SCENARIO IN WHICH THE SOURCE HAS 2 COLS 10 A 10 A 20 C 30 D 40 E 20 C AND THERE SHOULD BE 2 TARGETS ONE TO SHOW THE DUPLICATE VALUES AND ANOTHER TARGET FOR DISTINCT ROWS. T1 T2 10 A 10 A 20 C 20 C 30 D WHICH TRANSFORMATION CAN BE USED TO LOAD DATA INTO TARGET? 40 E
Answer: Sorter Transformation to Target 2. Source qualifier transformation to Target 1. Check the Distinct option in Sorter transformation.
68] WHAT ARE TWO MODES OF DATA MOVEMENT IN INFORMATICA SERVER?
Answer: The data movement mode depends on whether Informatica Server should process single byte or multi-byte character data. This mode selection can affect the enforcement of code page relationships and cod
69] WHAT IS PUSHDOWN OPTIMIZATIONS IN PC 8.X WITH EXAMPLE?
Answer: Use pushdown optimization to push transformation logic to the source or target database. The Integration Service analyzes the transformation logic, mapping, and session configuration to determine the
70] I HAVE WINDOWS XP. I WAS TOLD A PERSON COULD FIND OUT WHAT SITES A PERSON IS LOOKING ON YOUR COMPUTER. HOW DO YOU DO THIS? I HAVE A PERSON WHO USES MY COMPUTER, AND I THINK HE IS LOOKING AT SITES HE SHOULD NOT BE VIEWING.
Answer: Open the browser, click the history button.
71] WHAT ARE THE TYPES OF MAPING IN GETTING STARTED WIZARD?
Answer: Simple Pass through maping : Loads a static fact or dimension table by inserting all rows. Use this mapping when you want to drop all existing data from your table before loading new data. Slowly Gr
72] IN A SCENARIO I HAVE COL1, COL2, COL3, UNDER THAT 1,X,Y, AND 2,A,B AND I WANT IN THIS FORM COL1, COL2 AND 1,X AND 1,Y AND 2,A AND 2,B, WHAT IS THE PROCEDURE?
Answer: Use Normalizer : create two ports - first port occurs = 1 second make occurs = 2 two output ports are created and connect to target
73] HOW CAN YOU IMPROVE THE PERFORMANCE OF AGGREGATE TRANSFORMATION?
Answer: We can improve the aggregator performance in the following ways 1.send sorted input. 2.increase aggregator cache size. i.e. Index cache and data cache. 3.Give input/output what you need in th
74] WHERE TO STORE INFORMATICA REJECTED DATA? HOW TO EXTRACT THE INFORMATICA REJECTED DATA?
Answer: The reject rows say for example due to unique key constrain is all pushed by session into the $PMBadFileDir (default relative path is
75] IN A SEQUENTIAL BATCH HOW CAN WE STOP SINGLE SESSION?
Answer: We can stop it using PMCMD command or in the monitor right click on that perticular session and select stop.this will stop the current session and the sessions next to it.
76] HOW DO YOU CREATE SINGLE LOOKUP TRANSFORMATION USING MULTIPLE TABLES?
Answer: Write a override sql query. Adjust the ports as per the sql query.
77] WHAT R THE MAPINGS THAT WE USE FOR SLOWLY CHANGING DIMENSION TABLE?
Answer: We can use the following Mapping for slowly Changing dimension table. Expression Lookup Filter Sequence Generator Update Strategy
78] IN WHICH CONDITIONS WE CANNOT USE JOINER TRANSFORMATION (LIMITATIONS OF JOINER TRANSFORMATION)?
Answer: Both pipelines begin with the same original data source. Both input pipelines originate from the same Source Qualifier transformation. Both input pipelines originate from the same Normalizer transfo
79] WHAT IS BATCH AND DESCRIBE ABOUT TYPES OF BATCHES?
Answer: There are two types of batches 1. Concurrent 2. Sequential
80] WHAT IS THE PROCEDURE TO LOAD THE FACT TABLE? GIVE IN DETAIL?
Answer: Based on the requirement to your fact table, choose the sources and data and transform it based on your business needs. For the fact table, you need a primary key so use a sequence generator transfo
81] HOW TO MOVE THE MAPPING FROM ONE DATABASE TO ANOTHER?
Answer: 1. Open the mapping you want to migrate. Go to File Menu - Select 'Export Objects' and give a name - an XML file will be generated. Connect to the repository where you want to migrate and then select
82] HOW DO U CHECK THE SOURCE FOR THE LATEST RECORDS THAT ARE TO BE LOADED INTO THE TARGET. I.E. I HAVE LOADED SOME RECORDS YESTERDAY, TODAY AGAIN THE FILE HAS BEEN POPULATED WITH SOME MORE RECORDS TODAY, SO HOW DO I FIND THE RECORDS POPULATED TODAY.
Answer: a) Create a lookup to target table from Source Qualifier based on primary Key. b) Use and expression to evaluate primary key from target look-up. ( If a new source record look-up primary key port f
83] WHAT ARE VARIABLE PORTS AND LIST TWO SITUATIONS WHEN THEY CAN BE USED?
Answer: We have mainly tree ports Inport, Outport, Variable port. Inport represents data is flowing into transformation. Outport is used when data is mapped to next transformation. Variable port is used when
84] CAN YOU USE THE MAPING PARAMETERS OR VARIABLES CREATED IN ONE MAPING INTO ANY OTHER REUSABLE TRANSFORMATION?
Answer: Yes.Because reusable tranformation is not contained with any maplet or maping.
85] PARTITIONING, BITMAP INDEXING (WHEN TO USE), HOW WILL THE BITMAP INDEXING WILL EFFECT THE PERFORMANCE
Answer: Bitmap indexing a indexing technique to tune the performance of SQL queries. The default type is B-Tree indexers which is of high cardinality (normalized data). You can use bitmap indexers for de-norm
86] HOW A TWO DIMENSION ARRAY IS INITIALIZE WITH POINTER?
Answer: #include void main() { int size; cout<<"Size "; cin>>size; int **p=new int *[size]; for(int i=0;i
87] WHAT R THE TYPES OF GROUPS IN ROUTER TRANSFORMATION?
Answer: A Router transformation has the following types of groups: Input Output Input Group The Designer copies property information from the input ports of the input group to create a set of output
88] THERE ARE 1000 SOURCE TABLES CONTAINING THE SAME DATA WITH DIFFERENT FILE FORMATS, NOW I WANT TO LOAD INTO A SINGLE TARGET TABLE. HOW TO ACHIEVE ?
Answer: first u should convert diff. file format to one format then create 1 to 1 mapping,run it and see the o/p in unix whether file is posted or not.
89] HOW CAN U COMPLETE UNRECOVERABLE SESSIONS?
Answer: Under certain circumstances, when a session does not complete, you need to truncate the target tables and run the session from the beginning. Run the session from the beginning when the Informatica S
90] HOW U WILL CREATE HEADER AND FOOTER IN TARGET USING INFORMATICA?
Answer: If you are focus is about the flat files then one can set it in file properties while creating a mapping or at the session level in session properties
91] HOW CAN YOU SAY THAT UNION TRANSFORMATION IS ACTIVE TRANSFORMATION?
Answer: By Definition, Active transformation is the transformation that changes the number of rows that pass through it...in union transformation the number of rows resulting from union can be (are) different
92] WHAT IS THE DIFFERENCE BETWEEN POWERCENTER 6 AND POWERCENTER 7?
Answer: 1)lookup the flat files in informatica 7.X but we cann't lookup flat files in informatica 6.X 2) External Stored Procedure Transformation is not available in informatica 7.X but this transformation
93] WHAT ARE LOAD TYPES IN INFORMATICA AND WHAT IS DELTA LOAD
Answer: There are two types of load bulk and normal.we ll use normal type almost, bulk is used in for the data which is huge to insert to the target
94] HOW CAN I TRANSFORM ROW TO COLUMN?
Answer: Through Normalizer Transformation we can do this.
95] 1. CAN U EXPLAIN ONE CRITICAL MAPPING? 2. PERFORMANCE ISSUE WHICH ONE IS BETTER? WHETHER CONNECTED LOOKUP TRANSFORMATION OR UNCONNECTED ONE?
Answer: it depends on your data and the type of operation u r doing. If u need to calculate a value for all the rows or for the maximum rows coming out of the source then go for a connected lookup. Or,i
96] DIFFERENCE BETWEEN STATIC CACHE AND DYNAMIC CACHE
Answer: Static cache Dynamic cache U can not insert or update the cache U can insert rows into the cache as u pass to the target The informatic server returns a value from the lookup table or cache w
97] WHAT IS TRANSACTION?
Answer: Transaction is a logical unit of work that comprises one or more sql statements executed by a single user
98] WHAT IS THE DIFFERENCE BETWEEN NORMAL LOAD AND BULK LOAD?
Answer: Normal Load: Normal load will write information to the database log file so that if any recorvery is needed it is will be helpful. when the source file is a text file and loading data to a table,in su
99] WHY SORTER TRANSFORMATION IS AN ACTIVE TRANSFORMATION?
Answer: This is type of active transformation which is responsible for sorting the data either in the ascending order or descending order according to the key specifier. the port on which the sorting takes pl
100] WHAT IS THE LOGIC WILL YOU IMPLEMENT TO LOAD THE DATA IN TO ONE FACTV FROM 'N' NUMBER OF DIMENSION TABLES.
Answer: Normally every one use 1) slowly changing dimensions 2) slowly growing dimensions
101] CAN YOU START A SESSION INSIDE A BATCH INDIVIDUALLY?
Answer: We can start our required session only in case of sequential batch.in case of concurrent batch we cant do like this.
102] WHAT R THE TASKS THAT LOAD MANGER PROCESS WILL DO?
Answer: Manages the session and batch scheduling: Whe u start the informatica server the load maneger launches and queries the repository for a list of sessions configured to run on the informatica server.Whe
103] WHAT R THE TYPES OF LOOKUP CACHES?
Answer: 1) Static Cache 2) Dynamic Cache 3) Persistent Cache 4) Reusable Cache 5) Shared Cache
104] WHAT IS HASH TABLE INFORMATICA?
Answer: In hash partitioning, the Informatica Server uses a hash function to group rows of data among partitions. The Informatica Server groups the data based on a partition key. Use hash partitioning when yo
105] IDENTIFYING BOTTLENECKS IN VARIOUS COMPONENTS OF INFORMATICA AND RESOLVING THEM.
Answer: The best way to find out bottlenecks is writing to flat file and see where the bottle neck is.
106] WHAT IS THE NEED AND WHEN DO WE USE A DYNAMIC LOOKUP CACHE?
Answer: Implementing Dynamic cache in the lookup transformation comes under performance tuning side. If your mapping has lookup on target u can implement dynamic cache.
107] WHAT IS THE STATUS CODE?
Answer: Status code provides error handling for the informatica server during the session. The stored procedure issues a status code that notifies whether or not stored procedure completed sucessfully. This
108] WHAT IS THE RANK INDEX IN RANK TRANSFORMATION?
Answer: The Designer automatically creates a RANKINDEX port for each Rank transformation. The Informatica Server uses the Rank Index port to store the ranking position for each record in a group. For example,
109] WHAT ARE THE UNSUPPORTED REPOSITORY OBJECTS FOR A MAPPLET?
Answer: COBOL source definition Joiner transformations Normalizer transformations Non reusable sequence generator transformations. Pre or post session stored procedures Target definitions Power mart 3.5
110] WHAT IS DATADRIVEN?
Answer: The Informatica Server follows instructions coded into Update Strategy transformations within the session mapping to determine how to flag rows for insert, delete, update, or reject. If the mapping
111] HOW TO DEFINE INFORMATICA SERVER?
Answer: Informatica server is the main server component in informatica product family..Which is resonsible for reads the data from various source system and tranforms the data according to business rule and l
112] WHAT IS THE METHOD OF LOADING 5 FLAT FILES OF HAVING SAME STRUCTURE TO A SINGLE TARGET AND WHICH TRANSFORMATIONS I CAN USE?
Answer: Use file repository concept.
113] WITHOUT USING UPDATESTRETAGY AND SESSIONS OPTIONS, HOW WE CAN DO THE UPDATE OUR TARGET TABLE?
Answer: In session properties, There is an option insert update insert as update update as update like that by using this we will easily solve
114] IN WHICH CIRCUMSTANCES THAT INFORMATICA SERVER CREATES REJECT FILES?
Answer: When it encounters the DD_Reject in update strategy transformation. Violates database constraint Filed in the rows was truncated or overflowed.
115] TO PROVIDE SUPPORT FOR MAINFRAMES SOURCE DATA, WHICH FILES ARE USED AS A SOURCE DEFINITIONS?
Answer: COBOL files.
116] IN A SCENARIO I WANT TO CHANGE THE DIMENSIONS OF A TABLE AND NORMALIZE THE RENORMALIZED TABLE WHICH TRANSFORMATION CAN I USE?
Answer: You can use normalizer transformation .It will normalize the records.
117] WHAT ARE MAPPING PARAMETERS AND VARIABLES IN WHICH SITUATION WE CAN USE IT
Answer: If we need to change certain attributes of a mapping after every time the session is run, it will be very difficult to edit the mapping and then change the attribute. So we use mapping parameters and
118] WHAT R THE UNSUPPORTED REPOSITORY OBJECTS FOR A MAPPLET?
Answer: Source definitions. Definitions of database objects (tables, views, synonyms) or files that provide source data. Target definitions. Definitions of database objects or files that contain the target
119] CAN INFORMATICA LOAD HETEROGENEOUS TARGETS FROM HETEROGENEOUS SOURCES?
Answer: Yes it can. For example...Flat File and Relations sources are joined in the mapping, and later, Flat File and relational targets are loaded.
120] CAN YOU START A BATCHES WITH IN A BATCH?
Answer: You cannot. If you want to start batch that resides in a batch,create a new independent batch and copy the necessary sessions into the new batch.
121] WHAT R THE ACTIVE AND PASSIVE TRANSFORMATIONS?
Answer: Transformations can be active or passive. An active transformation can change the number of rows that pass through it, such as a Filter transformation that removes rows that do not meet the filter con
122] WHAT ARE DIMENSIONS AND VARIOUS TYPES OF DIMENSIONS?
Answer: Set of level properties that describe a specific aspect of a business, used for analyzing the factual measures of one or more cubes, which use that dimension. Egs. Geography, time, customer and produc
123] EXPLAIN ABOUT RECOVERING SESSIONS?
Answer: If you stop a session or if an error causes a session to stop, refer to the session and error logs to determine the cause of failure. Correct the errors, and then complete the session. The method you
124] CAN WE ELIMINATE DUPLICATE ROWS BY USING FILTER AND ROUTER TRANSFORMATION? IF SO EXPLAIN ME IN DETAIL.
Answer: U can use SQL query for uniqness if the source is Relational But if the source is Flat file then u should use Shorter or Aggregatot transformation
125] THERE ARE 3 DEPTS. IN DEPT TABLE AND ONE WITH 100 PEOPLE AND 2ND WITH 5 AND 3RD WITH SOME 30 AND SO. I WANT TO DISPLAY THOSE DEPTNO WHERE MORE THAN 10 PEOPLE EXISTS
Answer: Yes! the answer provided is absolutely right. by an SQL application(Oracle). If you want to perform it thru informatica, the Fire the same query in the SQL Override of Source qualifier transformati
126] 1) WHAT ARE THE VARIOUS TEST PROCEDURES USED TO CHECK WHETHER THE DATA IS LOADED IN THE BACKEND, PERFORMANCE OF THE MAPPING, AND QUALITY OF THE DATA LOADED IN INFORMATICA. 2) WHAT ARE THE COMMON PROBLEMS DEVELOPERS FACE WHILE ETL DEVELOPMENT
Answer: If you want to know the performance of a mapping at transformation level, then select the option in the session properties-> collect performance data. At the run time in the monitor you can see it in
127] WHAT IS DIFFERENCE BETWEEN LOOKUP CACHE AND UNCHACHED LOOKUP? CAN I RUN THE MAPPING WITHOUT STARTING THE INFORMATICA SERVER?
Answer: The difference between cache and uncacheed lookup is when you configure the lookup transformation cache lookup it stores all the lookup table data in the cache when the first input record enter into t
128] WHAT IS PARAMETER FILE?
Answer: Parameter file is to define the values for parameters and variables used in a session. A parameter file is a file created by text editor such as word pad or notepad. You can define the following val
129] WHAT ARE THE MAPINGS THAT WE USE FOR SLOWLY CHANGING DIMENSION TABLE?
Answer: Type1: Rows containing changes to existing dimensions are updated in the target by overwriting the existing dimension. In the Type 1 Dimension mapping, all rows contain current dimension data. Use t
130] WHAT IS MYSTERY DIMENTION?
Answer: Using Mystery Dimension ur maitaining the mystery data in ur Project.
131] HOW DO YOU HANDLE DECIMAL PLACES WHILE IMPORTING A FLATFILE INTO INFORMATICA?
Answer: While importing flat file definetion just specify the scale for a neumaric data type. in the mapping, the flat file source supports only number datatype(no decimal and integer). In the SQ associated w
132] WHAT ARE THE DIFFERENT THREADS IN DTM PROCESS?
Answer: Master thread: Creates and manages all other threads Maping thread: One maping thread will be creates for each session. Fectchs session and maping information. Pre and post session threads: This wil
133] WHICH IS BETTER AMONG CONNECTED LOOKUP AND UNCONNECTED LOOKUP TRANSFORMATIONS IN INFORMATICA OR ANY OTHER ETL TOOL?
Answer: If you are having defined source you can use connected, source is not well defined or from different database you can go for unconnected We are using like that only
134] WHAT IS METADATA REPORTER?
Answer: It is a web based application that enables you to run reports against repository metadata. With a meta data reporter, You can access information about u r repository with out having knowledge of sql,
135] WHICH TRANSFORMATION SHOULD WE USE TO NORMALIZE THE COBOL AND RELATIONAL SOURCES?
Answer: Normalizer Transformation. When you drag the COBOL source in to the mapping Designer workspace,the normalizer transformation automatically appears,creating input and output ports for every column in
136] WHAT IS LOAD MANAGER?
Answer: While running a Workflow,the PowerCenter Server uses the Load Manager process and the Data Transformation Manager Process (DTM) to run the workflow and carry out workflow tasks.When the PowerCenter Se
137] WHAT IS CODE PAGE COMPATIBILITY?
Answer: Compatibility between code pages is used for accurate data movement when the Informatica Sever runs in the Unicode data movement mode. If the code pages are identical, then there will not be any data
138] WHAT R THE BASIC NEEDS TO JOIN TWO SOURCES IN A SOURCE QUALIFIER?
Answer: The both the table should have a common field with same data type. Its not necessary both should follow primary and foreign relationship. If any relation ship exists that will help u in performance
139] WHAT R THE TYPES OF MAPING WIZARDS THAT R TO BE PROVIDED IN INFORMATICA?
Answer: Simple Pass through Slowly Growing Target Slowly Changing the Dimension Type1 Most recent values Type2 Full History Version Flag Date Type3 Current and one previous
140] WHICH TOOL U USE TO CREATE AND MANAGE SESSIONS AND BATCHES AND TO MONITOR AND STOP THE INFORMATICA SERVER?
Answer: Informatica Workflow Managar and Informatica Worlflow Monitor
141] WHY WE USE PARTITIONING THE SESSION IN INFORMATICA?
Answer: Performance can be improved by processing data in parallel in a single session by creating multiple partitions of the pipeline. Informatica server can achieve high performance by partitioning the p
142] EXPLAIN ABOUT PERFORM RECOVERY?
Answer: When the Informatica Server starts a recovery session, it reads the OPB_SRVR_RECOVERY table and notes the row ID of the last row committed to the target database. The Informatica Server then reads al
143] WHY AND WHERE WE ARE USING FACTLESS FACT TABLE?
Answer: Factless Fact Tables are the fact tables with no facts or measures (numerical data). It contains only the foriegn keys of corresponding Dimensions.
144] IN CERTAIN MAPPING THERE ARE FOUR TARGETS TG1,TG2,TG3 AND TG4. TG1 HAS A PRIMARY KEY,TG2 FOREIGN KEY REFERENCING THE TG1'S PRIMARY KEY,TG3 HAS PRIMARY KEY THAT TG2 AND TG4 REFERS AS FOREIGN KEY,TG2 HAS FOREIGN KEY REFERENCING PRIMARY KEY OF TG4 ,THE ORDER IN WHICH THE INFORMATICA WILL LOAD THE TARGET? 2]HOW CAN I DETECT AGGREGATE TRANSFORMATION CAUSING LOW PERFORMANCE?
Answer: To get performance details for any aggregator transformation, we have to check some parameters in the .perf file named as Transformationname_writetodisk and Transformationname_readfromdisk. If these t
145] WHAT ARE THE PROPERTIES SHOULD BE NOTIFIED WHEN WE CONNECT THE FLAT FILE SOURCE DEFINITION TO RELATIONAL DATABASE TARGET DEFINITION?
Answer: 1.File is fixed width or delimited 2.Size of the file. If its can be executed without performance issues then normal load will work If its huge in GB they NWAY partitions can be specified at
146] HOW DO YOU LOAD THE TIME DIMENSION?
Answer: Time Dimension will generally load manually by using PL/SQL , shell scripts, proc C etc......
147] WHAT IS THE MAPPING FOR UNIT TESTING IN INFORMATICA, ARE THERE ANY OTHER TESTING’S IN INFORMATICA, AND HOW WE WILL DO THEM AS A ETL DEVELOPER. HOW DO THE TESTING PEOPLE WILL DO TESTING ARE THERE ANY SPECIFIC TOOLS FOR TESTING
Answer: In informatica there is no method for unit testing. There are two methods to test the mapping. 1. But we have data sampling. set the ata sampling properties for session in workflow manager for spec
148] WHAT IS THE MAPLET?
Answer: Maplet is a set of transformations that you build in the maplet designer and You can use in multiple mapings.
149] CAN YOU USE THE MAPING PARAMETERS OR VARIABLES CREATED IN ONE MAPING INTO ANOTHER MAPING?
Answer: No.
150] WHAT ARE THE RANK CACHES?
Answer: During the session, the informatica server compares an input row with rows in the datacache. If the input row out-ranks a stored row, the informatica server replaces the stored row with the input row
151] WHAT ARE THE TYPES OF DATA THAT PASSES BETWEEN INFORMATICA SERVER AND STORED PROCEDURE?
Answer: Three types of data:- Input/Out put parameters Return Values Status code.
152] AT THE MAX HOW MANY TRANSFORMATIONS CAN BE US IN A MAPPING?
Answer: In a mapping we can use any number of transformations depending on the project, and the included transformations in the perticular related transformatons.
153] WHAT ARE THE TYPES OF MAPING WIZARDS THAT ARE TO BE PROVIDED IN INFORMATICA?
Answer: The Designer provides two mapping wizards to help you create mappings quickly and easily. Both wizards are designed to create mappings for loading and maintaining star schemas, a series of dimensions
154] WHILE IMPORTING THE RELATIONAL SOURCE DEFINITION FROM DATABASE, WHAT ARE THE META DATA OF SOURCE YOU IMPORT?
Answer: Source name Database location Column names Datatypes Key constraints.
155] WHY USE THE LOOKUP TRANSFORMATION?
Answer: To perform the following tasks:- Get a related value. For example, if your source table includes employee ID, but you want to include the employee name in your target table to make your summary data
156] WHAT ARE THE DIFFERENT OPTIONS USED TO CONFIGURE THE SEQUENTIAL BATCHES?
Answer: Two options Run the session only if previous session completes sucessfully. Always runs the session.
157] WHICH TOOL YOU USE TO CREATE AND MANAGE SESSIONS AND BATCHES AND TO MONITOR AND STOP THE INFORMATICA SERVER?
Answer: Informatica Server Manager.
158] WHAT IS A SOURCE QUALIFIER?
Answer: 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 represents the rows that the Informatica Serv
159] IN THE SOURCE, IF WE ALSO HAVE DUPLICATE RECORDS AND WE HAVE 2 TARGETS, T1- FOR UNIQUE VALUES AND T2- ONLY FOR DUPLICATE VALUES. HOW DO WE PASS THE UNIQUE VALUES TO T1 AND DUPLICATE VALUES TO T2 FROM THE SOURCE TO THESE 2 DIFFERENT TARGETS IN A SINGLE MAPPING?
Answer: source--->sq--->exp-->sorter(with enable select distinct check box)--->t1 --->aggregator(with enabling group by and write count function)--->t2 If u want only duplicates to t2 u can follow t
160] EXPLAIN ME THE 3 POINTS: I WANT TO KNOW : 1) THE DIFFERENCES BETWEEN USING NATIVE AND ODBC SERVER-SIDE DATABASE CONNECTIONS 2)KNOW THE REASON WHY TO REGISTER A SERVER TO THE REPOSITORY IS NECESSARY 3)KNOW THE RULES ASSOCIATED WITH TRANSFERRING AND SHARING OBJECTS BETWEEN FOLDERS. 4) KNOW THE RULES ASSOCIATED WITH TRANSFERRING AND SHARING OBJECTS BETWEEN REPOSITORIES
Answer: 1> Native connection is something which is provided by the same vendor for that tool. eg: oracle warehouse builder has its own driver to connect to oracle DB which does not use a ODBC driver. here con
161] WHAT IS THE HIERARCHIES IN DWH
Answer: Data sources ---> Data acquisition ---> Warehouse ---> Front end tools ---> Metadata management ---> Data warehouse operation management
162] HOW DO YOU CREATE A MAPPING USING MULTIPLE LOOKUP TRANSFORMATION?
Answer: Use unconnected lookup if same lookup repeats multiple times.
163] DEFINE INFORMATICA REPOSITORY?
Answer: The Informatica repository is a relational database that stores information, or metadata, used by the Informatica Server and Client tools. Metadata can include information such as mappings describing
164] WHAT IS DIFFERENCE BETWEEN MAPLET AND REUSABLE TRANSFORMATION?
Answer: Maplet consists of set of transformations that is reusable.A reusable transformation is a single transformation that can be reusable. If u create a variables or parameters in maplet that can not be
165] WHAT TRANSFORMATION YOU CAN USE INPLACE OF LOOKUP?
Answer: Look-up transformation can serve in so many situations. So, if you can a bit particular about the scenarioo that you are talking about, it will be easy to interpret.
166] HOW CAN WE STORE PREVIOUS SESSION LOGS
Answer: Just run the session in time stamp mode then automatically session log will not overwrite current session log.
167] HOW TO USE THE UNCONNECTED LOOKUP I.E., FROM WHERE THE INPUT HAS TO BE TAKEN AND THE OUTPUT IS LINKED? WHAT CONDITION IS TO BE GIVEN?
Answer: The unconnected lookup is used just like a function call. in an expression output/variable port or any place where an expression is accepted(like condition in update strategy etc..), call the unconnec
168] IS A FACT TABLE NORMALIZED OR DE-NORMALIZED?
Answer: A fact table is always DENORMALISED table. It consists of data from dimension table (Primary Key's) and Fact table has Foreign keys and measures.
169] HOW CAN WE JOIN 3 DATABASE LIKE FLAT FILE, ORACLE, DB2 IN INFORMATRICA..
Answer: You have to use two joiner transformations.fIRST one will join two tables and the next one will join the third with the resultant of the first joiner.
170] HOW TO EXPORT MAPPINGS TO THE PRODUCTION ENVIRONMENT?
Answer: In the designer go to the main menu and one can see the export/import options. Import the exported mapping in to the production repository with replace options.
171] HOW TO RUN SCD1 BEC IT CREATE TWO TARGET TABLES IN MAPPING WINDOW AND THERE ARE ONLY ONE TABLE IN WAREHOUSE DESIGNER(MEANS TARGET).. SO IF WE CREATE ONE NEW TABLE IN TARGET IT GIVES ERROR..
Answer: If so, create the target with the name u have given in wizard for target(table). No't create the target again for the second instance. It is just the virtual copy of the same target. i.e in warehouse
172] WHAT ARE THE DIFFERENT TYPES OF TRANSFORMATION AVAILABLE IN INFORMATICA. AND WHAT ARE THE MOSTLY USED ONES AMONG THEM
Answer: Mainly there are two types of tranformation.1]Active TransformationAn active transformation can change the number of rows that pass through it from source to target i.e it eliminates rows that do not
173] HOW TO GENERATE THE METADATA REPORTS IN INFORMATICA?
Answer: You can generate PowerCenter Metadata Reporter from a browser on any workstation, even a workstation that does not have PowerCenter tools installed.
174] IN MY SOURCE TABLE 1000 REC'S R THERE.I WANT TO LOAD 501 REC TO 1000 REC INTO MY TARGET TABLE ? HOW CAN U DO THIS ?
Answer: You can overide the sql Query in Wofkflow Manager. LIke select * from tab_name where rownum<=1000 minus select * from tab_name where rownum<=500; This will work fine. Try it and get back t
175] WHAT IS SURROGATEKEY ? IN UR PROJECT IN WHICH SITUATION U HAS USED ? EXPLAIN WITH EXAMPLE ?
Answer: A surrogate key is system genrated/artificial key /sequence number or A surrogate key is a substitution for the natural primary key.It is just a unique identifier or number for each row that can be us
176] IF THE WORKFLOW HAS 5 SESSION AND RUNNING SEQUENTIALLY AND 3RD SESSION HAS BEEN FAILED HOW CAN WE RUN AGAIN FROM ONLY 3RD TO 5TH SESSION?
Answer: If multiple sessions in a concurrent batch fail, you might want to truncate all targets and run the batch again. However, if a session in a concurrent batch fails and the rest of the sessions complete
177] HOW TO LOAD THE DATA FROM PEOPLE SOFT HRM TO PEOPLE SOFT ERM USING INFORMATICA?
Answer: Following are necessary 1.Power Connect license 2.Import the source and target from people soft using ODBC connections 3.Define connection under "Application Connection Browser" for the peop
178] WHAT ARE THE PROPERTIES SHOULD BE NOTIFIED WHEN WE CONNECT THE FLAT FILE SOURCE DEFINITION TO RELATIONAL DATABASE TARGET DEFINITION?
Answer: 1.File is fixed width or delimited 2.Size of the file. If its can be executed without performance issues then normal load will work If its huge in GB they NWAY partitions can be specified at
179] WHAT IS THE TARGET LOAD ORDER?
Answer: A target load order group is the collection of source qualifiers, transformations, and targets linked together in a mapping.
180] WHAT ARE AGGREGATE TRANSFORMATION?
Answer: Aggregator transformation allows you to perform aggregate calculations, such as averages and sums.
181] WHAT ARE TARGET TYPES ON THE SERVER?
Answer: Target Types are File, Relational and ERP.
182] WHAT IS THE DEFAULT SOURCE OPTION FOR UPDATE STRATGEY TRANSFORMATION?
Answer: DATA DRIVEN
183] WHAT R THE OPTIONS IN THE TARGET SESSION OF UPDATE STRATEGY TRANSSFORMATIOIN?
Answer: Update as Insert: This option specified all the update records from source to be flagged as inserts in the target. In other words, instead of updating the records in the target they are inserted as
184] WHAT R THE TYPES OF MAPING IN GETTING STARTED WIZARD?
Answer: 1. Simple Pass through 2. Slowly Growing Target
185] WHAT R THE DIFFERENT TYPES OF TYPE2 DIMENSION MAPING?
Answer: Type2 1. Version number 2. Flag 3.Date
186] IF I DONE ANY MODIFICATIONS FOR MY TABLE IN BACK END DOES IT REFLECT IN INFORMATCA WAREHOUSE OR MAPING DESGINER OR SOURCE ANALYZER?
Answer: NO. Informatica is not at all concern with back end data base.It displays u all the information that is to be stored in repository.If want to reflect back end changes to informatica screens, again u h
187] HOW CAN U RECOGNISE WHETHER OR NOT THE NEWLY ADDED ROWS IN THE SOURCE R GETS INSERT IN THE TARGET ?
Answer: If it is Type 2 Dimension the abouve answer is fine, but if u want to get the info of all the insert statements and Updates you need to use session log file where you configure it to verbose. You
188] WHAT R THE CIRCUMSTANCES THAT INFROMATICA SERVER RESULTS AN UNRECIVERABLE SESSION?
Answer: The source qualifier transformation does not use sorted ports. If u change the partition information after the initial session fails. Perform recovery is disabled in the informatica server configura
189] CAN U GENERATE REPORTS IN INFORMATCIA?
Answer: It is a ETL tool, you could not make reports from here, but you can generate metadata report, that is not going to be used for business analysis
190] HOW CAN U RECOVER THE SESSION IN SEQUENTIAL BATCHES?
Answer: If you configure a session in a sequential batch to stop on failure, you can run recovery starting with the failed session. The Informatica Server completes the session and then runs the rest of the
191] HOW TO RECOVER THE STANDALONE SESSION?
Answer: A standalone session is a session that is not nested in a batch. If a standalone session fails, you can run recovery using a menu command or pmcmd. These options are not available for batched session
192] CAN U COPY THE SESSION TO A DIFFERENT FOLDER OR REPOSITORY?
Answer: In addition, you can copy the workflow from the Repository manager. This will automatically copy the mapping, associated source,targets and session to the target folder.
193] WHAT IS DIFFERENCE BETWEEN STORED PROCEDURE TRANSFORMATION AND EXTERNAL PROCEDURE TRANSFORMATION?
Answer: In case of stored procedure transformation procedure will be compiled and executed in a relational data source. U need data base connection to import the stored procedure in to u r maping. Where as i
194] WHAT IS TRACING LEVEL AND WHAT R THE TYPES OF TRACING LEVEL?
Answer: Tracing level represents the amount of information that informatcia server writes in a log file. Types of tracing level:- Normal Verbose Verbose init Verbose data
195] WHAT IS POWER CENTER REPOSITORY?
Answer: The PowerCenter repository allows you to share metadata across repositories to create a data mart domain. In a data mart domain, yoYou can create a single global repository to store metadata used acr
196] AFTER DRAGING THE PORTS OF THREE SOURCES(SQL SERVER,ORACLE,INFORMIX) TO A SINGLE SOURCE QUALIFIER, CAN U MAP THESE THREE PORTS DIRECTLY TO TARGET?
Answer: if u drag three hetrogenous sources and populated to target without any join means you are entertaining Carteisn product. If you don't use join means not only diffrent sources but homegeous sources ar
197] WHAT IS DATA CLEANSING..?
Answer: Data cleansing is a two step process including DETECTION and then CORRECTION of errors in a data set.
198] TO PROVIDE SUPPORT FOR MAINFRAMES SOURCE DATA,WHICH FILES R USED AS A SOURCE DEFINITIONS?
Answer: COBOL Copy-book files
199] WHERE SHOULD U PLACE THE FLAT FILE TO IMPORT THE FLAT FILE DEFINITION TO THE DESIGNER?
Answer: There is no such restriction to place the source file. In performance point of view its better to place the file in server local src folder. if you need path please check the server properties availab
200] WHAT ARE THE MAPPING PARAMATERS AND MAPING VARIABLES?
Answer: Maping 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 the maping parameter ,
201] WHAT ARE THE REUSABLE TRANSFORAMTIONS?
Answer: Reusable transformations can be used in multiple mappings.When you need to incorporate this transformation into maping,U add an instance of it to maping.Later if you change the definition of the trans
202] WHAT ARE THE ACTIVE AND PASSIVE TRANSFORAMTIONS?
Answer: An active transforamtion can change the number of rows that pass through it.A passive transformation does not change the number of rows that pass through it.
203] THE DESIGNER INCLUDES A "FIND" SEARCH TOOL AS PART OF THE STANDARD TOOL BAR. WHAT CAN IT BE USED TO FIND?
Answer: This is used for finding source columns in workspace.
204] CAN U USE THE MAPING PARAMETERS OR VARIABLES CREATED IN ONE MAPING INTO ANOTHER MAPING?
Answer: NO. You might want to use a workflow parameter/variable if you want it to be visible with other mappings/sessions
205] WHICH TRANSFORMATION SHOULD YOU NEED WHILE USING THE COBOL SOURCES AS SOURCE DEFINITIONS?
Answer: Normalizer transformation which is used to normalize the data. Since cobol sources are oftenly consists of Denormailzed data.
206] WHAT R THE SETTINGS THAT U USE TO COFIGURE THE JOINER TRANSFORMATION?
Answer: Master and detail source Type of join Condition of the join the Joiner transformation supports the following join types, which you set in the Properties tab: Normal (Default) Master Outer
207] WHERE SHOULD YOU PLACE THE FLAT FILE TO IMPORT THE FLAT FILE DEFINITION TO THE DESIGNER?
Answer: Place it in local folder.
208] WHAT IS THE LOOK UP TRANSFORMATION?
Answer: Using it we can access the data from a relational table which is not a source in the mapping. For Ex:Suppose the source contains only Empno, but we want Empname also in the mapping.Then instead of
209] DIFFERENCES BETWEEN CONNECTED AND UNCONNECTED LOOKUP?
Answer: Connected lookup Unconnected lookup Receives input values diectly from the pipe line. Receives input values from the result of a lkp expression in a another transformation. U can use a dynamic
210] WHAT IS DATA TRANSFORMATION MANAGER?
Answer: A Router transformation is similar to a Filter transformation because both transformations allow you to use a condition to test data. A Filter transformation tests data for one condition and drops the
211] WHAT IS THE ROUTER TRANSFORMATION?
Answer: A Router transformation is similar to a Filter transformation because both transformations allow you to use a condition to test data. A Filter transformation tests data for one condition and drops the
212] WHY WE USE STORED PROCEDURE TRANSFORMATION?
Answer: A Stored Procedure transformation is an important tool for populating and maintaining databases. Database administrators create stored procedures to automate time-consuming tasks that are too complica
213] IF YOU ARE WORKFLOW IS RUNNING SLOW IN INFORMATICA. WHERE DO YOU START TROUBLE SHOOTING AND WHAT ARE THE STEPS YOU FOLLOW?
Answer: When the work flow is running slowly u have to find out the bottlenecks in this order target source mapping session system
214] WHAT DOES THE EXPRESSION N FILTER TRANSFORMATIONS DO IN INFORMATICA SLOWLY GROWING TARGET WIZARD?
Answer: EXPESSION transformation detects and flags the rows from source. Filter transformation filters the rows that are not flagged and passes the flagged rows to the Update strategy transformation
215] HOW THE INFORMATICA SERVER INCREASES THE SESSION PERFORMANCE THROUGH PARTITIONING THE SOURCE?
Answer: For a relational sources informatica server creates multiple connections for each parttion of a single source and extracts seperate range of data for each connection.Informatica server reads multiple
216] WHAT R THE TYPES OF METADATA THAT STORES IN REPOSITORY?
Answer: Source definitions. Definitions of database objects (tables, views, synonyms) or files that provide source data. Target definitions. Definitions of database objects or files that contain the target
217] WHAT IS THE DIFF B/W STORED PROC (DB LEVEL) & STORED PROC TRANS (INFORMATICA LEVEL) ? AGAIN WHY SHOULD WE USE SP TRANS ?
Answer: First of all stored procedures (at DB level) are series of SQL statement. And those are stored and compiled at the server side.In the Informatica it is a transformation that uses same stored procedure
218] HOW CAN WE JOIN THE TABLES IF THE TABLES HAVE NO PRIMARY AND FORIEN KEY RELATION AND NO MATCHIG PORT TO JOIN?
Answer: without common column or common data type we can join two sources using dummy ports. 1.Add one dummy port in two sources. 2.In the expression trans assing '1' to each port. 2.Use Joiner trans
219] IN THE CONCEPT OF MAPPING PARAMETERS AND VARIABLES, THE VARIABLE VALUE WILL BE SAVED TO THE REPOSITORY AFTER THE COMPLETION OF THE SESSION AND THE NEXT TIME WHEN U RUN THE SESSION, THE SERVER TAKES THE SAVED VARIABLE VALUE IN THE REPOSITORY AND STARTS ASSIGNING THE NEXT VALUE OF THE SAVED VALUE. FOR EXAMPLE I RAN A SESSION AND IN THE END IT STORED A VALUE OF 50 TO THE REPOSITORY.NEXT TIME WHEN I RUN THE SESSION, IT SHOULD START WITH THE VALUE OF 70. NOT WITH THE VALUE OF 51. HOW TO DO THIS.
Answer: u can do onething after running the mapping,, in workflow manager start-------->session. right clickon the session u will get a menu, in that go for persistant values, there u will find the last
220] WHAT IS POLLING?
Answer: It displays the updated information about the session in the monitor window. The monitor window displays the status of each session when you poll the informatica server.
221] WHY YOU USE REPOSITORY CONNECTIVITY?
Answer: When you edit,schedule the sesion each time,informatica server directly communicates the repository to check whether or not the session and users are valid.All the metadata of sessions and mappings wi
222] WHAT IS CODE PAGE USED FOR?
Answer: Code Page is used to identify characters that might be in different languages. If you are importing Japanese data into mapping, you must select the Japanese code page of source data.
223] WHAT ARE VARIOUS TYPES OF AGGREGATION?
Answer: Various types of aggregation are SUM, AVG, COUNT, MAX, MIN, FIRST, LAST, MEDIAN, PERCENTILE, STDDEV, and VARIANCE.
224] WHAT ARE THE SCHEDULING OPTIONS TO RUN A SESION?
Answer: A session can be scheduled to run at a given time or intervel,or u can manually run the session. Different options of scheduling Run only on demand: server runs the session only when user starts ses
225] WHAT IS CHANGE DATA CAPTURE?
Answer: Change data capture (CDC) is a set of software design patterns used to determine the data that has changed in a database so that action can be taken using the changed data.
226] CAN U TELL ME HOW TO GO FOR SCD'S AND ITS TYPES.WHERE DO WE USE THEM MOSTLY
Answer: The "Slowly Changing Dimension" problem is a common one particular to data warehousing. In a nutshell, this applies to cases where the attribute for a record varies over time. We give an example below
227] WHAT IS MEANT BY EDW?
Answer: EDW is Enterprise Datawarehouse which means that its a centralised DW for the whole organization. This apporach is the apporach on Imon which relies on the point of having a single warehouse/centra
228] IF YOU WANT TO CREATE INDEXES AFTER THE LOAD PROCESS WHICH TRANSFORMATION YOU CHOOSE?
Answer: Its usually not done in the mapping(transformation) level. Its done in session level. Create a command task which will execute a shell script (if Unix) or any other scripts which contains the create i
229] HOW I CAN DO INCREMENTAL AGGREGATION IN REAL TIME?
Answer: For incremental Aggregation.. We need to use Aggregations + Look up on Target + Expression to SUM up Count obtained from New Aggregations and Lookup on target. For one record already present in Aggre
230] WHAT IS WORKLET AND WHAT USE OF WORKLET AND IN WHICH SITUATION WE CAN USE IT
Answer: A set of worlflow tasks is called worklet, Workflow tasks means 1)timer2)decesion3)command4)eventwait5)eventrise6)mail etc...... But we r use diffrent situations by using this only
231] CAN YOU COPY THE BATCHES?
Answer: NO.
232] WHAT R THE JOINER CACHES?
Answer: Specifies the directory used to cache master records and the index to these records. By default, the cached files are created in a directory specified by the server variable $PMCacheDir. If you overri
233] CAN WE USE AGGREGATOR/ACTIVE TRANSFORMATION AFTER UPDATE STRATEGY TRANSFORMATION
Answer: You can use aggregator after update strategy. The problem will be, once you perform the update strategy, say you had flagged some rows to be deleted and you had performed aggregator transformation for
234] WHAT IS DIFFERENCE BETWEEN DIMENTION TABLE AND FACT TABLE AND WHAT ARE DIFFERENT DIMENTION TABLES AND FACT TABLES
Answer: In the fact table contain measurable data and less columns and meny rows, It's contain primarykey Diffrent types of fact tables: additive,non additive, semi additive In the dimensions tabl
235] WHAT IS DATA MERGING, DATA CLEANSING, SAMPLING?
Answer: Data merging: It is a process of combining the data with similar structures in to a single output. Data Cleansing: It is a process of identifying and rectifying the inconsistent and inaccurate data
236] WHAT IS IQD FILE?
Answer: IQD file is nothing but Impromptu Query Definetion,This file is maily used in Cognos Impromptu tool after creating a imr( report) we save the imr as IQD file which is used while creating a cube in pow
237] DESCRIBE TWO LEVELS IN WHICH UPDATE STRATEGY TRANSFORMATION SETS?
Answer: Within a session: When you configure a session, yoYou can instruct the Informatica Server to either treat all records in the same way (for example, treat all records as inserts), or use instructions c
238] WHAT ARE THE TASKS THAT SOURCE QUALIFIER PERFORMS?
Answer: Join data originating from same source data base. Filter records when the informatica server reads source data. Specify an outer join rather than the default inner join specify sorted records. Sele
239] WHICH TRANSFORMATION SHOULD WE USE TO NORMALIZE THE COBOL AND RELATIONAL SOURCES?
Answer: The Normalizer transformation normalizes records from COBOL and relational sources, allowing you to organize the data according to your own needs. A Normalizer transformation can appear anywhere in a
240] WHEN WE CREATE A TARGET AS FLAT FILE AND SOURCE AS ORACLE.. HOW CAN I SPECIFY FIRST ROWS AS COLUMN NAMES IN FLAT FILES...
Answer: Use a pre sql statement....but this is a hardcoding method...if you change the column names or put in extra columns in the flat file, you will have to change the insert statement
241] WHY IS MEANT BY DIRECT AND INDIRECT LOADING OPTIONS IN SESSIONS?
Answer: Direct loading can be used to Single transformation where as indirect transformation can be used to multiple transformations or files In the direct we can perform recovery process but in Indirect
242] IN A JOINER TRANSFORMATION, YOU SHOULD SPECIFY THE SOURCE WITH FEWER ROWS AS THE MASTER SOURCE. WHY?
Answer: Joiner transformation compares each row of the master source against the detail source. The fewer unique rows in the master, the fewer iterations of the join comparison occur, which speeds the join pr
243] WHAT IS THE EXACT MEANING OF DOMAIN?
Answer: The PowerCenter domain is the fundamental administrative unit in PowerCenter. The domain supports the administration of the distributed services. A domain is a collection of nodes and services that yo
244] I HAVE AN REQUIREMENT WHERE IN THE COLUMNS NAMES IN A TABLE (TABLE A) SHOULD APPEAR IN ROWS OF TARGET TABLE (TABLE B) I.E. CONVERTING COLUMNS TO ROWS. IS IT POSSIBLE THROUGH INFORMATICA? IF SO, HOW?
Answer: if data in tables as follows Table A Key-1 char(3); table A values _______ 1 2 3 Table B bkey-a char(3); bcode char(1); table b values 1 T 1 A 1 G 2 A 2 T 2 L 3 A and output re
245] WHAT ARE COST BASED AND RULE BASED APPROACHES AND THE DIFFERENCE
Answer: Cost based and rule based approaches are the optimization techniques which are used in related to databases, where we need to optimize a sql query. Basically Oracle provides Two types of Optimizer
246] CAN YOU COPY THE SESSION TO A DIFFERENT FOLDER OR REPOSITORY?
Answer: Yes. By using copy session wizard You can copy a session in a different folder or repository. But that target folder or repository should consists of mapping of that session. If target folder or repo
247] DIFFERENCES BETWEEN INFORMATICA 6.2 AND INFORMATICA 7.0 YOURS SINCERELY
Answer: Features in 7.1 are : 1.union and custom transformation 2.lookup on flat file 3.grid servers working on different operating systems can coexist on same server 4.we can use pmcmdrep 5.we
248] WHAT IS MEANT BY LOOKUP CACHES?
Answer: The informatica server builds a cache in memory when it processes the first row af a data in a cached look up transformation.It allocates memory for the cache based on the amount you configure in the
249] HOW MANY WAYS YOU CAN UPDATE A RELATIONAL SOURCE DEFINTION AND WHAT R THEY?
Answer: Two ways 1. Edit the definition 2. Reimport the defintion
250] HOW MANY WAYS YOU CREATE PORTS?
Answer: Two ways:- 1.Drag the port from another transforamtion 2.Click the add buttion on the ports tab.
251] WHAT R THE METHODS FOR CREATING REUSABLE TRANSFORAMTIONS?
Answer: A transformation can reused, that is known as reusable transformation You can design using 2 methods using transformation developer create normal one and promote it to reusable
252] HOW MANY WAYS YOU CAN UPDATE A RELATIONAL SOURCE DEFINITION AND WHAT ARE THEY?
Answer: joinercondition exciuted with in the infoematica staging layer source qualifier condition exciuted with in the database level
253] WHAT IS SESSION AND BATCHES?
Answer: Session - A Session Is A set of instructions that tells the Informatica Server How And When To Move Data From Sources To Targets. After creating the session, we can use either the server manager or t
254] WHAT R THE TASKS THAT SOURCE QUALIFIER PERFORMS?
Answer: Join data originating from the same source database. You can join two or more tables with primary-foreign key relationships by linking the sources to one Source Qualifier. Filter records when the In
255] WHAT IS A VIEW? HOW IT IS RELATED TO DATA INDEPENDENCE? AND WHAT ARE THE DIFFERENT TYPES OF VIEWS, AND WHAT IS MATERIALIZE VIEW
Answer: views view is a combination of one or more table.view does not stores the data,it just store the query in file format.If we excutes the query the query will fetch the data from the tables and just
256] WHAT IS MEANT BY JUNK ATTRIBUTE IN INFORMATICA?
Answer: Junk Dimension A Dimension is called junk dimension if it contains attribute which are rarely changed ormodified. example In Banking Domain , we can fetch four attributes accounting to a junk dimensio
257] WHAT IS LOOKUP TRANSFORMATION AND UPDATE STRATEGY TRANSFORMATION AND EXPLAIN WITH AN EXAMPLE.
Answer: Look up transformation is used to lookup the data in a relationa table,view,Synonym and Flat file. The informatica server queries the lookup table based on the lookup ports used in the transformati
258] CAN I USE A SESSION BULK LOADING OPTIONS THAT TIME CAN I MAKE A RECOVERY TO THE SESSION?
Answer: If the session is configured to use in bulk mode it will not write recovery information to recovery tables. So Bulk loading will not perform the recovery as required.
259] WHAT IS THE BEST WAY TO SHOW METADATA (NUMBER OF ROWS AT SOURCE, TARGET AND EACH TRANSFORMATION LEVEL, ERROR RELATED DATA) IN A REPORT FORMAT?
Answer: You can select these details from the repository table. you can use the view REP_SESS_LOG to get these data
260] HOW TO JOIN TWO TABLES WITHOUT USING THE JOINER TRANSFORMATION.
Answer: It’s possible to join the two or more tables by using source qualifier.But provided the tables should have relationship. When u drag n drop the tables u will getting the source qualifier for each t
261] WHAT IS INCREMANTAL AGGREGATION?
Answer: When using incremental aggregation, you apply captured changes in the source to aggregate calculations in a session. If the source changes only incrementally and you can capture changes, you can conf
262] WHAT IS TRACING LEVEL?
Answer: Ya its the level of information storage in session log. The option comes in the properties tab of transformations. By default it remains "Normal". Can be Verbose Initialisation Verbose Data
263] HOW CAN WE ELIMINATE DUPLICATE ROWS FROM FLAT FILE?
Answer: Use Sorter Transformation. When you configure the Sorter Transformation to treat output rows as distinct, it configures all ports as part of the sort key. It therefore discards duplicate rows compared
264] WHAT ARE THE MEASURE OBJECTS
Answer: Use Sorter Transformation. When you configure the Sorter Transformation to treat output rows as distinct, it configures all ports as part of the sort key. It therefore discards duplicate rows compared
265] WHAT IS RANK TRANSFORMATION?WHERE CAN WE USE THIS TRANSFORMATION?
Answer: Rank transformation is used to find the status.ex if we have one sales table and in this if we find more employees selling the same product and we are in need to find the first 5 0r 10 employee who is
266] HOW TO GET TWO TARGETS T1 CONTAINING DISTINCT VALUES AND T2 CONTAINING DUPLICATE VALUES FROM ONE SOURCE S1.
Answer: Use filter transformation for loading the target with no duplicates. and for the other transformation load it directly from source.
267] CAN ANYBODY WRITE A SESSION PARAMETER FILE WHICH WILL CHANGE THE SOURCE AND TARGETS FOR EVERY SESSION. I.E DIFFERENT SOURCE AND TARGETS FOR EACH SESSION RUN.
Answer: You are supposed to define a parameter file. And then in the Parameter file, you can define two parameters, one for source and one for target. Give like this for example: $Src_file = c:program f
268] WHAT IS THE DIFFERENCE BETWEEN POWER CENTRE AND POWER MART? WHAT IS THE PROCEDURE FOR CREATING INDEPENDENT DATA MARTS FROM INFORMATICA 7.1?
Answer: power center powermart no.of repository n No. n No. aplicability high end WH low&mid range WH global repository supported not supported local repository supported supported ERP support
269] WHAT IS DIFFERENCE BETWEEN PARTIONING OF RELATONAL TARGET AND PARTITIONING OF FILE TARGETS?
Answer: If u parttion a session with a relational target informatica server creates multiple connections to the target database to write target data concurently.If u partition a session with a file target the
270] HOW TO DELETE DUPLICATE ROWS IN FLAT FILES SOURCE IS ANY OPTION IN INFORMATICA
Answer: Use a sorter transformation , in that u will have a "distinct" option make use of it .
271] IF I GET A FLAT FILE AS SOURCE, I NEED TO GET DISTINCT DATA IN THE TARGET. HOW DO I ACCOMPLISH THIS? PLEASE GIVE A DETAILED ANSWER OF WHAT ALL TRASFORMATIONS WE HAVE TO USE AND HOW THE DATA FLOW WILL BE, AND WHAT SQL STATEMENT WE HAVE TO USE.
Answer: when we have a flat file as a source we need to use the sorter transformation to get the distinct rows in the target we have an option distinct in sorter transformation which is eliminated the dupl
272] WHY WE USE STORED PROCEDURE TRANSFORMATION?
Answer: For populating and maintaining data bases.
273] WHAT IS AGGREGATE CACHE IN AGGREGATOR TRANSFORAMTION?
Answer: The aggregator stores data in the aggregate cache until it completes aggregate calculations.When you run a session that uses an aggregator transformation,the informatica server creates index and data
274] WHAT IS THE DIFF B/W STOP & ABORT IN INFORMATICA SESS LEVEL ?
Answer: Stop:We can Restart the session Abort:WE cant restart the session.We should truncate all the pipeline after that start the session
275] CAN YOU GENERATE REPORTS IN INFORMATCIA?
Answer: Yes. By using Metadata reporter we can generate reports in informatica.
276] HOW THE INFORMATICA SERVER SORTS THE STRING VALUES IN RANKTRANSFORMATION?
Answer: When the informatica server runs in the ASCII data movement mode it sorts session data using Binary sortorder.If you configure the seeion to use a binary sort order,the informatica server caluculates
277] HOW DO WE ANALYSE THE DATA AT DATABASE LEVEL?
Answer: Data can be viewed using Informatica's designer tool. If you want to view the data on source/target we can preview the data but with some limitations. We can use data profiling too.
278] CAN BATCHES BE COPIED/STOPPED FROM SERVER MANAGER?
Answer: Yes, we can stop the batches using server manager or pmcmd commnad
279] IF U HAD TO SPLIT THE SOURCE LEVEL KEY GOING INTO TWO SEPERATE TABLES. ONE AS SURROGATE AND OTHER AS PRIMARY. SINCE INFORMATICA DOES NOT GURANTEE KEYS ARE LOADED PROPERLY(ORDER!) INTO THOSE TABLES. WHAT ARE THE DIFFERENT WAYS YOU COULD HANDLE THIS TYPE OF SITUATION?
Answer: Foreign key
280] HOW TO RETRIEVE THE RECORDS FROM A REJECTED FILE. EXPLAIN WITH SYNTAX OR EXAMPLE
Answer: During the execution of workflow all the rejected rows will be stored in bad files(where your informatica server get installed;C:Program FilesInformatica PowerCenter 7.1Server) These bad files can be
281] DIFFERENCE BETWEEN SUMMARY FILTER AND DETAILS FILTER?
Answer: Summary Filter --- we can apply records group by that contain common values. Detail Filter --- we can apply to each and every record in a database.
282] WHAT IS A JUNK DIMENSION?
Answer: A "junk" dimension is a collection of random transactional codes, flags and/or text attributes that are unrelated to any particular dimension. The junk dimension is simply a structure that provides a
283] WHAT ARE THE ACTIVE AND PASSIVE TRANSFORMATIONS?
Answer: An active transforamtion can change the number of rows that pass through it.A passive transformation does not change the number of rows that pass through it.
284] WHAT R THE MAPPING PARAMETERS AND MAPING VARIABLES?
Answer: Please refer to the documentation for more understanding. Mapping variables have two identities: Start value and Current value Start value = Current value ( when the session starts the execut
285] WHY WE USE LOOKUP TRANSFORMATIONS?
Answer: Lookup Transformations can access data from relational tables that are not sources in mapping. With Lookup transformation, we can acomplish the following tasks:- Get a related value-Get the Employee
286] HOW CAN I GET DISTINCT VALUES WHILE MAPPING IN INFORMATICA IN INSERTION?
Answer: You can add an aggregator before insert and group by the feilds that need to be distinct.
287] WHAT IS PARTITIONING? WHERE WE CAN USE PARTITION? WHAT IS ADVANTAGES? IS IT NECESSARY?
Answer: Partitions are used to optimize the session performance we can select in sesstion propetys for partiotions types default----passthrough partition key range partion round robin partion
288] IN REALTIME WHICH ONE IS BETTER STAR SCHEMA OR SNOWFLAKE STAR SCHEMA THE SURROGATE WILL BE LINKED TO WHICH COLUMNS IN THE DIMENSION TABLE.
Answer: In real time only star schema will implement because it will take less time and surrogate key will there in each and every dimension table in star schema and this surrogate key will assign as foreign
289] HOW CAN YOU DELETE DUPLICATE ROWS WITHOUT USING DYNAMIC LOOKUP? TELL ME ANY OTHER WAYS USING LOOKUP DELETE THE DUPLICATE ROWS?
Answer: For example u have a table Emp_Name and it has two columns Fname, Lname in the source table which has douplicate rows. In the mapping Create Aggregator transformation. Edit the aggregator transformati
290] WHAT IS EXACT USE OF 'ONLINE' AND 'OFFLINE' SERVER CONNECT OPTIONS WHILE DEFINING WORK FLOW IN WORK FLOW MONITOR? . THE SYSTEM HANGS WHEN 'ONLINE' SERVER CONNECT OPTION. THE INFORMATICA IS INSTALLED ON A PERSONAL LAPTOP.
Answer: When the repo is up and the PMSERVER is also up, workflow monitor always will be connected on-line. When PMserver is down and the repo is still up we will be prompted for an off-line connection wit
291] WHAT WILL HAPPEN IF YOU ARE USING UPDATE STRATEGY TRANSFORMATION AND YOUR SESSION IS CONFIGURED FOR "INSERT"? WHAT ARE THE TYPES OF EXTERNAL LOADER AVAILABLE WITH INFORMATICA? IF YOU HAVE RANK INDEX FOR TOP 10. HOWEVER IF YOU PASS ONLY 5 RECORDS, WHAT WILL BE THE OUTPUT OF SUCH A RANK TRANSFORMATION?
Answer: if u r using a update strategy in any of ur mapping, then in session properties u have to set treat source rows as Data Driven. if u select insert or udate or delete, then the info server will not con
292] WHERE IS THE CACHE STORED IN INFORMATICA?
Answer: Cache is stored in the Informatica server memory and over flowed data is stored on the disk in file format which will be automatically deleted after the successful completion of the session run. If yo
293] WHAT HAPPENS IF YOU TRY TO CREATE A SHORTCUT TO A NON-SHARED FOLDER?
Answer: It only creates a copy of it..
294] INFORMATICA LIVE INTERVIEW QUESTIONS
Answer: confirmed dimension == one dimension that shares with two fact table factless means ,fact table without measures only contains foreign keys-two types of factless table ,one is event tracking and ot
295] WHERE DO WE USE MQ SERIES SOURCE QUALIFIER, APPLICATION MULTI GROUP SOURCE QUALIFIER. JUST GIVE AN EXAMPLE FOR A BETTER UNDERSTANDING
Answer: We can use a MQSeries SQ when we have a MQ messaging system as source(queue). When there is need to extract data from a Queue, which will basically have messages in XML format, we will use a JMS or
296] CAN ANYONE EXPLAIN ABOUT INCREMENTAL AGGREGATION WITH AN EXAMPLE?
Answer: Incremental aggregation is specially used for tune the performance of the aggregator. It captures the change each time (incrementally) you run the transformation and then performs the aggregation func
297] ABOUT INFORMATICA POWER CENTER 7: 1) I WANT TO KNOW WHICH MAPPING PROPERTIES CAN BE OVERRIDDEN ON A SESSION TASK LEVEL. 2) KNOW WHAT TYPES OF PERMISSIONS ARE NEEDED TO RUN AND SCHEDULE WORK FLOWS.
Answer: 1.(Ans) You can override any properties other than the source and targets. Make sure the source and targets exists in ur db if it is a relational db. If it is a flat file, you can override its propert
298] CAN ANY ONE EXPLAIN REAL TIME COMPLAIN MAPPINGS OR COMPLEX TRANSFORMATIONS IN INFORMATICA. SPECIALLY IN SALES DOMAIN.
Answer: Most complex logic we use is denormalization. We dont have any Denormalizer transformation in INformatica. So we will have to use an aggregator followed by an expression. Apart from this, we use most
299] WHAT ARE THE ENHANCEMENTS MADE TO INFORMATICA 7.1.1 VERSION WHEN COMPARED TO 6.2.2 VERSION?
Answer: 1.union & custom transformation 2.lookup on flatfile 3.we can use pmcmd command 4.we can export independent&dependent repository objects 5.version controlling 6.data proffiling 7.sup
300] WHAT IS MEANT BY COMPLEX MAPPING?
Answer: Complex maping means involved in more logic and more business rules. Actually in my project complex mapping is In my bank project, I involved in construct a 1 dataware house Meny customer is
301] IF A SESSION FAILS AFTER LOADING OF 10,000 RECORDS IN TO THE TARGET.HOW CAN U LOAD THE RECORDS FROM 10001 TH RECORD WHEN U RUN THE SESSION NEXT TIME IN INFORMATICA 6.1?
Answer: Running the session in recovery mode will work, but the target load type should be normal. If its bulk then recovery wont work as expected
302] CAN I START AND STOP SINGLE SESSION IN CONCURENT BSTCH?
Answer: ya shoor,Just right click on the particular session and going to recovery option or by using event wait and event rise
303] WHAT IS MICRO STRATEGY? WHY IS IT USED FOR? CAN ANY ONE EXPLAIN IN DETAIL ABOUT IT?
Answer: Micro strategy is again an BI tool which is a HOLAP... u can create 2 dimensional report and also cubes in here.......basically a reporting tool. IT HAS A FULL RANGE OF REPORTING ON WEB ALSO IN WINDOW
304] WHAT ARE PARTITION POINTS?
Answer: Partition points mark the thread boundaries in a pipeline and divide the pipeline into stages. The Informatica Server sets partition points at several transformations in a pipeline by default. If yo
305] HOW TO APPEND THE RECORDS IN FLAT FILE(INFORMATICA) ? WHERE AS IN DATASTAGE WE HAVE THE OPTIONS I) OVERWRITE THE EXISTING FILE II) APPEND EXISTING FILE
Answer: This is not there in Informatica v 7. but heard that its included in the latest version 8.0 where u can append to a flat file. Its about to be shipping in the market.
306] TWO RELATIONAL TABLES ARE CONNECTED TO SQ TRANS,WHAT ARE THE POSSIBLE ERRORS IT WILL BE THROWN?
Answer: The only two possibilities as of I know is Both the table should have primary key/foreign key relation ship Both the table should be available in the same schema or same database
307] WHAT ARE THE STEPS REQUIRED FOR TYPE2 DIMENSION/VERSION DATA MAPPING. HOW CAN WE IMPLEMENT IT
Answer: 1. Determine if the incoming row is 1) a new record 2) an updated record or 3) a record that already exists in the table using two lookup transformations. Split the mapping into 3 seperate flows using
308] WHAT ARE THE TRANSFORMATIONS THAT RESTRICTS THE PARTITIONING OF SESSIONS?
Answer: Advanced External procedure tranformation and External procedure transformation: This transformation contains a check box on the properties tab to allow partitioning. Aggregator Transformation: If u
309] HOW CAN YOU ACCESS THE REMOTE SOURCE INTO YOUR SESSION?
Answer: Relational source: To acess relational source which is situated in a remote place ,u need to configure database connection to the datasource. FileSource : To access the remote source file you must co
310] WHAT IS THE PROCEDURE OR STEPS IMPLEMENTING VERSIONING IF YOU ARE ALREADY IN VERSION7.X. ANY GOTCHA'S OR PRECAUTIONS..
Answer: For version control in ETL layer using informatica, first of all after doing anything in your designer mode or workflow manager, do the following steps..... 1> First save the changes or new impleme
311] HOW CAN YOU STOP A BATCH?
Answer: By using server manager or pmcmd.
312] WHAT IS A COMMAND THAT USED TO RUN A BATCH?
Answer: pmcmd is used to start a batch.
313] WHAT IS DIMENSION TABLE EXACTLY?
Answer: Dimension tables gives description about something. for eg. If we take Student as a dimention table, we have various attributes like college name, age, gender,etc which gives some description about
314] WHEN THE INFORMATICA SERVER MARKS THAT A BATCH IS FAILED?
Answer: If one of session is configured to "run if previous completes" and that previous session fails.
315] WHY DIMENSTION TABLES ARE DENORMALIZED IN NATURE ?
Answer: Because in Data warehousing historical data should be maintained, to maintain historical data means suppose one employee details like where previously he worked, and now where he is working, all detai
316] HOW MANY NUMBER OF SESSIONS THAT YOU CAN CREATE IN A BATCH?
Answer: It depends on the config settings of informatica server. The parameters for the maximum connections cant be exceeded. It depends on the overall sessions running per the server at a time. For eg, if th
317] CAN ANY ONE COMMENT ON SIGNIFICANCE OF ORACLE 9I IN INFORMATICA WHEN COMPARED TO ORACLE 8 OR 8I. I MEAN HOW IS ORACLE 9I ADVANTAGEOUS WHEN COMPARED TO ORACLE 8 OR 8I WHEN USED IN INFORMATICA
Answer: it's very easy Actually oracle 8i not allowed userdefined data types but 9i allows and then blob,clob allow only 9i not 8i and more over list partinition is there in 9i only
318] WHAT IS BATCH AND DESCRIBE ABOUT TYPES OF BATCHES?
Answer: Grouping of session is known as batch. Batches are two types:- Sequential: Runs sessions one after the other Concurrent: Runs session at same time. If you have sessions with source-target dependenc
319] HOW TO USE MAPPING PARAMETERS AND WHAT IS THEIR USE
Answer: In designer u will find the mapping parameters and variables options.u can assign a value to them in designer. comming to there uses suppose u r doing incremental extractions daily. suppose ur source
320] WHAT ARE THE DATA MOVEMENT MODES IN INFORMATCIA?
Answer: Datamovement modes determines how informatcia server handles the charector data.U choose the datamovement in the informatica server configuration settings. Two types of datamovement modes avialable i
321] HOW TO LOOKUP THE DATA ON MULTIPLE TABELS.
Answer: if the two tables are relational, then u can use the SQL lookup over ride option to join the two tables in the lookup properties.u cannot join a flat file and a relatioanl table. eg: lookup default
322] WHAT IS DTM PROCESS?
Answer: DTM means data transformation manager.in informatica this is main back ground process.it run after complition of load manager.in this process informatica server search source and tgt connection in rep
323] HOW DOES THE SERVER RECOGNISE THE SOURCE AND TARGET DATABASES?
Answer: By using ODBC connection.if it is relational.if is flat file FTP connection..see we can make sure with connection in the properties of session both sources && targets.
324] WHAT IS THE LIMIT TO THE NUMBER OF SOURCES AND TARGETS YOU CAN HAVE IN A MAPPING
Answer: There is no such restriction to use this number of sources or targets inside a mapping.
325] WHICH OBJECTS ARE REQUIRED BY THE DEBUGGER TO CREATE A VALID DEBUG SESSION?
Answer: Intially the session should be valid session. source, target, lookups, expressions should be availble, min 1 break point should be available for debugger to debug your session.
326] DEFINE MAPING AND SESSIONS?
Answer: Maping: It is a set of source and target definitions linked by transformation objects that define the rules for transformation. Session : It is a set of instructions that describe how and when to mov
327] WHAT IS THE PROCEDURE TO WRITE THE QUERY TO LIST THE HIGHEST SALARY OF THREE EMPLOYEES?
Answer: The following is the query to find out the top three salaries in ORACLE:--(take emp table) select * from emp e where 3>(select count (*) from emp where e.sal>emp.sal) order by sal desc. i
328] WE ARE USING UPDATE STRATEGY TRANSFORMATION IN MAPPING HOW CAN WE KNOW WHETHER INSERT OR UPDATE OR REJECT OR DELETE OPTION HAS BEEN SELECTED DURING RUNNING OF SESSIONS IN INFORMATICA.
Answer: In Designer while creating Update Strategy Transformation uncheck "forward to next transformation". If any rejected rows are there automatically it will be updated to the session log file. Update
329] SUPPOSE SESSION IS CONFIGURED WITH COMMIT INTERVAL OF 10,000 ROWS AND SOURCE HAS 50,000 ROWS. EXPLAIN THE COMMIT POINTS FOR SOURCE BASED COMMIT AND TARGET BASED COMMIT. ASSUME APPROPRIATE VALUE WHEREVER REQUIRED.
Answer: Source based commit will commit the data into target based on commit interval.so,for every 10,000 rows it will commit into target. Target based commit will commit the data into target based on buff
330] HOW DO WE ESTIMATE THE NUMBER OF PARTITONS THAT A MAPPING REALLY REQUIRES? IS IT DEPENDENT ON THE MACHINE CONFIGURATION?
Answer: It depends upon the informatica version we r using. suppose if we r using informatica 6 it supports only 32 partitions where as informatica 7 supports 64 partitions.
331] HOW DO WE ESTIMATE THE DEPTH OF THE SESSION SCHEDULING QUEUE? WHERE DO WE SET THE NUMBER OF MAXIMUM CONCURRENT SESSIONS THAT INFORMATICA CAN RUN AT A GIVEN TIME?
Answer: U set the max no of concurrent sessions in the info server. By default its 10. u can set to any no.
332] HOW DO YOU DECIDE WHETHER YOU NEED TO DO AGGREGATIONS AT DATABASE LEVEL OR AT INFORMATICA LEVEL?
Answer: It depends upon our requirment only.If you have good processing database you can create aggregation table or view at database level else its better to use informatica. Here i'm explaing why we need to
333] WHAT ARE THE DIFFERENT TYPES OF TYPE2 DIMENSION MAPING?
Answer: Type2 Dimension/Version Data Maping: In this maping the updated dimension in the source will gets inserted in target along with a new version number. Newly added dimension in source will inserted int
334] CAN INFORMATICA BE USED AS A CLEANSING TOOL? IF YES, GIVE EXAMPLE OF TRANSFORMATIONS THAT CAN IMPLEMENT A DATA CLEANSING ROUTINE.
Answer: Yes, we can use Informatica for cleansing data. some time we use stages to cleansing the data. It depends upon performance again else we can use expression to cleasing data. For example an feild X
335] BRIEFLY EXPLAIN THE VERSIONING CONCEPT IN POWER CENTER 7.1.
Answer: When you create a version of a folder referenced by shortcuts, all shortcuts continue to reference their original object in the original version. They do not automatically update to the current folder
336] IN A FILTER EXPRESSION WE WANT TO COMPARE ONE DATE FIELD WITH A DB2 SYSTEM FIELD CURRENT DATE. OUR SYNTAX: DATEFIELD = CURRENT DATE (WE DIDN'T DEFINE IT BY PORTS, ITS A SYSTEM FIELD ), BUT THIS IS NOT VALID (PMPARSER: MISSING OPERATOR).. CAN SOMEONE HELP US. THANKS
Answer: The db2 date formate is "yyyymmdd" where as sysdate in oracle will give "dd-mm-yy" so conversion of db2 date formate to local database date formate is compulsary. other wise u will get that type of er
337] HOW TO CREATE THE STAGING AREA IN YOUR DATABASE
Answer: A Staging area in a DW is used as a temporary space to hold all the records from the source system. So more or less it should be exact replica of the source systems except for the laod startegy where
338] WHATS THE DIFF BETWEEN INFORMATICA POWERCENTER SERVER, REPOSITORYSERVER AND REPOSITORY?
Answer: Repository is a database in which all informatica componets are stored in the form of tables. The reposiitory server controls the repository and maintains the data integrity and Consistency across the
339] WHAT ARE THE DIFFERENCES BETWEEN INFORMATICA POWER CENTER VERSIONS 6.2 AND 7.1, ALSO BETWEEN VERSIONS 6.2 AND 5.1?
Answer: The main difference between informatica 5.1 and 6.1 is that in 6.1 they introduce a new thing called repository server and in place of server manager(5.1), they introduce workflow manager and workflow
340] DISCUSS WHICH IS BETTER AMONG INCREMENTAL LOAD, NORMAL LOAD AND BULK LOAD
Answer: 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. According to performance bulk is better than
341] COMPARE DATA WAREHOUSING TOP-DOWN APPROACH WITH BOTTOM-UP APPROACH
Answer: Top down ODS-->ETL-->Datawarehouse-->Datamart-->OLAP Bottom up ODS-->ETL-->Datamart-->Datawarehouse-->OLAP
342] WHAT ARE THE TYPES OF GROUPS IN ROUTER TRANSFORMATION?
Answer: Input group Output group The designer copies property information from the input ports of the input group to create a set of output ports for each output group. Two types of output groups:- User de
343] HOW TO GET THE FIRST 100 ROWS FROM THE FLAT FILE INTO THE TARGET?
Answer: 1. Use test download option if you want to use it for testing. 2. Put counter/sequence generator in mapping and perform it.
344] CAN WE LOOKUP A TABLE FROM A SOURCE QUALIFER TRANSFORMATION-UNCONNECTED LOOKUP
Answer: No. we can't do. I will explain you why. 1) Unless you assign the output of the source qualifier to another transformation or to target no way it will include the feild in the query. 2) sou
345] WHAT IS A TIME DIMENSION? GIVE AN EXAMPLE.
Answer: In a relational data model, for normalization purposes, year lookup, quarter lookup, month lookup, and week lookups are not merged as a single table. In a dimensional data modeling(star schema), these
346] HOW CAN U CREATE OR IMPORT FLAT FILE DEFINITION IN TO THE WAREHOUSE DESIGNER?
Answer: U can create flat file definition in warehouse designer.in the warehouse designer,u can create new target: select the type as flat file. save it and u can enter various columns for that created target
Q3) Tell me what exactly, what was your role ?
A3) I worked as ETL Developer. I was also involved in requirement gathering, developing mappings,
checking source data. I did Unit testing (using TOAD), helped in User Acceptance Testing.
Q4) What kind of challenges did you come across in your project ?
A4) Mostly the challenges were to finalize the requirements in such a way so that different stakeholders
come to a common agreement about the scope and expectations from the project.
Q5) Tell me what was the size of your database ?
A5) Around 3 TB. There were other separate systems, but the one I was mainly using was around 3 TB.
Q6) what was the daily volume of records ?
A6) It used to vary, We processed around 100K-200K records on a daily basis, on weekends, it used to be
higher sometimes around 1+ Million records.
Q7) So tell me what were your sources ?
A7) Our Sources were mainly flat files, relational databases.
Q What tools did you use for FTP/UNIX ?
A For UNIX, I used Open Source tool called Putty and for FTP, I used WINSCP, Filezilla.
Q9) Tell me how did you gather requirements?
A9) We used to have meetings and design sessions with end users. The users used to give us sketchy
requirements and after that we used to do further analysis and used to create detailed Requirement
Specification Documents (RSD).
Q10) Did you follow any formal process or methodology for Requirement gathering ?
A10) As such we did not follow strict SDLC approach because requirement gathering is an iterative process.
But after creating the detailed Requirement Specification Documents, we used to take User signoff.
Q11) Tell me what are the steps involved in Application Development ?
A11) In Application Development, we usually follow following steps:
ADDTIP.
a) A - Analysis or User Requirement Gathering
b) D - Designing and Architecture
c) D - Development
d) T - Testing (which involves Unit Testing,System Integration Testing,
UAT - User Acceptance Testing
)
e) I - Implementation (also called deployment to production)
f) P - Production Support / Warranty
Q12) What are the drawbacks of Waterfall Approach ?
A12) This approaches assumes that all the User Requirements will be perfect before start of design
and development. That is not the case most of the time.Users can change their mind to add few more detailed
requirements or worse change the requirements drastically. So in those cases this approach (waterfall) is
likely to cause a delay in project which is a RISK to the project.
Q13) what is mapping design document ?
A13) In a mapping design document, we map source to target field, also document any special business logic
that needs to be implemented in the mapping.
Q14) What are diferent Data Warehousing Methodologies that you are familiar with ?
A14) In Data Warehousing, two methodologies are poopulare, 1st one is Ralph Kimbal and 2nd one is Bill Inmon.
We mainly followed Ralph Kimball's methodlogy in my last project.
In this methodlogy, we have a fact tables in the middle, surrounded by dimension tables.
This is also a basic STAR Schema which is the basic dimensional model.
A Snowflake schema. In a snowflake schema, we normalize one of the dimension tables.
Q15) What do you do in Bill Inmon Approach ?
A15) In Bill Inmon's approach, we try to create an Enterprise Data Warehouse using 3rd NF, and then
Data Marts are mainly STAR Schemas in 2nd NF.
Q16) How many mappings have you done ?
A16) I did over 35+ mappings, around 10+ were complex mappings.
Q17) What are Test cases or how did you do testing of Informatica Mappings ?
A17) Basically we take the SQL from Source Qualifier and check the source / target data in Toad.
Then we try to spot check data for various conditions according to mapping document and look for
any error in mappings.
For example, there may be a condition that if customer account does not exist then filter out that record
and write it to a reject file.
Q18) What are the other error handlings that you did in mappings?
A18) I mainly looked for non-numeric data in numeric fields, layout of a flat file may be different.
Also dates from flat file come as a string
Q19) How did you debug your mappings ?
A19) I used Informatica Debugger to check for any flags being set incorrectly. We see if the
logic / expressions are working or not. We may be expecting data
We use Wizard to configure the debugger.
Q20) Give me an example of a tough situation that you came across in Informatica Mappings
and how did you handle it ?
A20) Basically one of our colleagues had created a mapping that was using Joiner and mapping was taking a lot
of time to run, but the Join was in such a way that we could do the Join at Database Level (Oracle Level).
So I suggested and implemented that change and it reduced the run time by 40%.
Q21) Tell me what are various transformations that you have used ?
A21) I have used Lookup, Joiner, Update Strategy, Aggregator, Sorter etc.
Q22) How will you categorize various types of transformation ?
A22) Transformations can be connected or unconnected. Active or passive.
Q23) What are the different types of Transformations ?
A23) Transformations can be active transformation or passive transformations. If the number of output
rows are different than number of input rows then the transformation is an active transformation.
Like a Filter / Aggregator Transformation. Filter Transformation can filter out some records based
on condition defined in filter transformation.
Similarly, in an aggregator transformation, number of output rows can be less than input rows as
after applying the aggregate function like SUM, we could have less records.
Q24) What is a lookup transformation ?
A24) We can use a Lookup transformation to look up data in a flat file or a relational table,
view, or synonym.
We can use multiple Lookup transformations in a mapping.
The PowerCenter Server queries the lookup source based on the lookup ports in the
transformation. It compares Lookup transformation port values to lookup source column
values based on the lookup condition.
We can use the Lookup transformation to perform many tasks, including:
1) Get a related value.
2) Perform a calculation.
3) Update slowly changing dimension tables.
Q25) Did you use unconnected Lookup Transformation ? If yes, then explain.
A25) Yes. An Unconnected Lookup receives input value as a result of :LKP Expression in another
transformation. It is not connected to any other transformation. Instead, it has input ports,
output ports and a Return Port.
An Unconnected Lookup can have ONLY ONE Return PORT.
Q26) What is Lookup Cache ?
A26) The PowerCenter Server builds a cache in memory when it processes the first row of data in a
cached Lookup transformation.
It allocates the memory based on amount configured in the session. Default is
2M Bytes for Data Cache and 1M bytes for Index Cache.
We can change the default Cache size if needed.
Condition values are stored in Index Cache and output values in Data cache.
Q27) What happens if the Lookup table is larger than the Lookup Cache ?
A27) If the data does not fit in the memory cache, the PowerCenter Server stores the overflow values
in the cache files.
To avoid writing the overflow values to cache files, we can increase the default cache size.
When the session completes, the PowerCenter Server releases cache memory and deletes the cache files.
If you use a flat file lookup, the PowerCenter Server always caches the lookup source.
Q28) What is meant by "Lookup caching enabled" ?
A28) By checking "Lookup caching enabled" option, we are instructing Informatica Server to Cache lookup
values during the session.
Q29) What are the different types of Lookup ?
A29) When configuring a lookup cache, you can specify any of the following options:
a) Persistent cache.You can save the lookup cache files and reuse them the next time the
PowerCenter Server processes a Lookup transformation configured to use the cache.
b) Recache from source. If the persistent cache is not synchronized with the lookup table,
you can configure the Lookup transformation to rebuild the lookup cache.
c) Static cache. You can configure a static, or read-only, cache for any lookup source.
By default, the PowerCenter Server creates a static cache. It caches the lookup file or table
and looks up values in the cache for each row that comes into the transformation.
When the lookup condition is true, the PowerCenter Server returns a value from the lookup
cache. The PowerCenter Server does not update the cache while it processes the Lookup
transformation.
d) Dynamic cache. If you want to cache the target table and insert new rows or update
existing rows in the cache and the target, you can create a Lookup transformation to
use a dynamic cache.
The PowerCenter Server dynamically inserts or updates data in the lookup cache and passes
data to the target table.
You cannot use a dynamic cache with a flat file lookup.
e) Shared cache. You can share the lookup cache between multiple transformations. You can
share an unnamed cache between transformations in the same mapping. You can share a
named cache between transformations in the same or different mappings.
Q30) What is a Router Transformation ?
A30) A Router transformation is similar to a Filter transformation because both transformations
allow you to use a condition to test data. A Filter transformation tests data for one condition
and drops the rows of data that do not meet the condition.
However, a Router transformation tests data for one or more conditions and gives you the
option to route rows of data that do not meet any of the conditions to a default output group.
Q31) What is a sorter transformation ?
A31) The Sorter transformation allows you to sort data. You can sort data in ascending or
descending order according to a specified sort key. You can also configure the Sorter
transformation for case-sensitive sorting, and specify whether the output rows should be
distinct. The Sorter transformation is an active transformation.
It must be connected to the data flow.
Q32) What is a UNION Transformation ?
A32) The Union transformation is a multiple input group transformation that you can 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.
You can connect heterogeneous sources to a Union transformation. The Union
transformation merges sources with matching ports and outputs the data from one output
group with the same ports as the input groups.
Q33) What is Update Strategy ?
A33) Update strategy is used to decide on how you will handle updates in your project.
When you design your data warehouse, you need to decide what type of information to store
in targets. As part of your target table design, you need to determine whether to maintain all
the historic data or just the most recent changes.
For example, you might have a target table, T_CUSTOMERS, that contains customer data.
When a customer address changes, you may want to save the original address in the table
instead of updating that portion of the customer row. In this case, you would create a new row
containing the updated address, and preserve the original row with the old customer address.
This illustrates how you might store historical information in a target table. However, if you
want the T_CUSTOMERS table to be a snapshot of current customer data, you would
update the existing customer row and lose the original address.
The model you choose determines how you handle changes to existing rows.
In PowerCenter, you set your update strategy at two different levels:
1) Within a session. When you configure a session, you can instruct the PowerCenter Server
to either treat all rows in the same way (for example, treat all rows as inserts), or use
instructions coded into the session mapping to flag rows for different database operations.
2) Within a mapping. Within a mapping, you use the Update Strategy transformation to flag
rows for insert, delete, update, or reject.
Note: You can also use the Custom transformation to flag rows for insert, delete, update, or reject.
Q34) Joiner transformation?
A34) A Joiner transformation joins two related heterogenous sources residing in different location. The combination of
sources can be varied like
- two relational tables existing in seperate database.
- two flat files in potentially different file systems.
- two different ODBC sources.
- two instances of the same XML sources.
- a relational table and a flat file source.
- a relational table ans a XML source.
Q35) How many types of Joins can you use in a Joiner ?
A35) There can be 4 types of joins
a) Normal Join (Equi Join)
b) Master Outer Join - In master outer join you get all rows from Detail table
c) Detail Outer Join - In Detail Outer Join you get all rows from Master table
d) FULL Outer Join
Q36) What are Mapping Parameter & variables ?
A36) We Use Mapping parameter and variables to make mappings more flexible.
Value of a parameter does not change during session, whereas the value stored in a variable can change.
Q37) TELL ME ABOUT PERFORMANCE TUNING IN INFORMATICA?
A37) Basically Performance Tuning is an Iterative process, we can do lot of tuning at database level and
if database queries are faster then Informatica workflows will be automatically faster.
For Performance tuning, first we try to identify the source / target bottlenecks. Meaning that first we
see what can be do so that Source data is being retrieved as fast possible.
We try to filter as much data in SOURCE QUALIFIER as possible. If we have to use a filter then filtering
records should be done as early in the mapping as possible.
If we are using an aggregator transformation then we can pass the sorted input to aggregator. We need
to ideally sort the ports on which the GROUP BY is being done.
Depending on data an unconnected Lookup can be faster than a connected Lookup.
Also there should be as less transformations as possible. Also in Source Qualifier, we should bring only
the ports which are being used.
For optimizing the TARGET, we can disable the constraints in PRE-SESSION SQL and use BULK LOADING.
IF the TARGET Table has any indexes like primary key or any other indexes / constraints then BULK Loading
will fail. So in order to utilize the BULK Loading, we need to disable the indexes.
In case of Aggregator transformation, we can use incremental loading depending on requirements.
Q18) How did you do Error handling in Informatica ?
A18) Typically we can set the error flag in mapping based on business requirements and for each type of error,
we can associate an error code and error description and write all errors to a separate error table so that
we capture all rejects correctly.
Also we need to capture all source fields in a ERR_DATA table so that if we need to correct the erroneous
data fields and Re-RUN the corrected data if needed.
Usually there could be a separate mapping to handle such error data file.
Typical errors that we come across are
1) Non Numeric data in Numeric fields.
2) Incorrect Year / Months in Date fields from Flat files or varchar2 fields.
Q19) Did you work in Team Based environments ?
A19) Yes, we had versioning enabled in Repository.
Q12) What kind of workflows or tasks have you used ?
A12) I have used session, email task, command task, event wait tasks.
Q21) Explain the process that happens when a WORKFLOW Starts ?
A21) when a workflow starts, the informatica server retrieves mappings, workflows & session metadata from the
repository to extract the data from the source, transform it & load it into Target.
- it also runs the task in the workflow.
- The informatica server uses load manager & Data Transformation manager (DTM) process to run the workflow.
- The informatica server can combine data from different platforms & source types. For ex. joins data from
flat file & an oracle source. It can also load data to different platforms & target types. For ex. can
load, transform data to both a FF target & a MS SQL server db in same session.
Q27) What all tasks can we perform in a Repository Manager ?
A27) The Repository Manager allows you to navigate through multiple folders & repositories & perform basic
repository tasks.
Some examples of these tasks are:
- Add or remove a repository
- work with repository connections : can connect to one repo or multiple repositories.
- view object dependencies : b4 you remove or change an object can view dependencies to see
the impact on other objects.
- terminate user connections : can use the repo manager to view & terminate residual user connections
- Exchange metadata with other BI tools : can export & import metadata from other BI tools like cognos, BO..
IN REPOSITORY MANAGER NAVIGATOR WINDOW, WE FIND OBJECTS LIKE :
_ Repositories : can be standalone, local or global.
- Deployment groups : contain collections of objects for deployment to another repository in the domain.
- Folders : can be non-shared.
- Nodes : can inlcude sessions, sources, targets, transformation, mapplets, workflows, tasks, worklets & mappings.
- Repository objects : same as nodes along with workflow logs & sessions logs.
Q) Did you work on ETL strategy ?
A) Yes, my Data modeler & ETL lead along with developers analysed & worked on dependencies between tasks(workflows).
well there are Push & Pull strategies which is used to determine how the data comes from source systems to ETL server.
Push strategy : with this strategy, the source system pushes data ( or send the data ) to the ETL server.
Pull strategy : with this strategy, the ETL server pull the data(or gets the data) from the source system.
Q20) How did you migrate from Dev environment to UAT / PROD Environment ?
A20) We can do a folder copy or export the mapping in XML Format and then Import it another Repository or folder.
In my last project we used Deployment groups.
Q) External Scheduler ?
A) with exteranal schedulers, we used to run informatica jobs like workflows using pmcmd command in parallel with
some oracle jobs like stored procedures. there were variuos kinds of external schedulers available in market
like AUtosys, Maestro, Control M . So we can use for mix & match for informatica & oracle jobs using external schedulers.
Q10) What is a Slowly Changing Dimension ?
A10) In a Data Warehouse, usually the updates in Dimension tables don't happen frequently.
So if we want to capture changes to a dimension, we usually resolve it with Type 2 or
Type 3 SCD. So basically we keep historical data with SCD.
Q11) Explain SLOWLY CHANGING DIMENSION (SCD) Type, which one did you use ?
A11) There are 3 ways to resolve SCD. First one is Type 1, in which we overwrite the
changes, so we loose history.
Type 1
OLD RECORD
==========
Surr Dim Cust_Id Cust Name
Key (Natural Key)
======== =============== =========================
1 C01 ABC Roofing
NEW RECORD
==========
Surr Dim Cust_Id Cust Name
Key (Natural Key)
======== =============== =========================
1 C01 XYZ Roofing
I mainly used Type 2 SCD.
In Type 2 SCD, we keep effective date and expiration date.
For older record, we update the exp date as the The current Date - 1, if the changes
happened today.
In the current Record, we keep Current Date as
Surr Dim Cust_Id Cust Name Eff Date Exp Date
Key (Natural Key)
======== =============== ========================= ========== =========
1 C01 ABC Roofing 1/1/0001 12/31/9999
Suppose on 1st Oct, 2007 a small business name changes from ABC Roofing to XYZ Roofing, so if we want
to store the old name, we will store data as below:
Surr Dim Cust_Id Cust Name Eff Date Exp Date
Key (Natural Key)
======== =============== ========================= ========== =========
1 C01 ABC Roofing 1/1/0001 09/30/2007
101 C01 XYZ Roofing 10/1/2007 12/31/9999
We can implment TYPE 2 as a CURRENT RECORD FLAG Also
In the current Record, we keep Current Date as
Surr Dim Cust_Id Cust Name Current_Record
Key (Natural Key) Flag
======== =============== ========================= ==============
1 C01 ABC Roofing Y
Suppose on 1st Oct, 2007 a small business name changes from ABC Roofing to XYZ Roofing, so if we want
to store the old name, we will store data as below:
Surr Dim Cust_Id Cust Name Current_Record
Key (Natural Key) Flag
======== =============== ========================= ==============
1 C01 ABC Roofing N
101 C01 XYZ Roofing Y
Q3) What is a Mapplets? Can you use an active transformation in a
Mapplet?
A3) A mapplet has one input and output transformation and in between we
can have various mappings.
A mapplet is a reusable object that you create in the Mapplet
Designer. It contains a set of transformations and allows you to reuse
that transformation logic in multiple mappings.
Yes we can use active transformation in a Mapplet.
1)A data warehouse is a relational database that is designed for query and analysis
rather than for transaction processing. It usually contains historical data derived
from transaction data, but it can include data from other sources. It separates
analysis workload from transaction workload and enables an organization to
consolidate data from several sources.
In addition to a relational database, a data warehouse environment includes an
extraction, transportation, transformation, and loading (ETL) solution, an online
analytical processing (OLAP) engine, client analysis tools, and other applications
that manage the process of gathering data and delivering it to business users.
A common way of introducing data warehousing is to refer to the characteristics of
a data warehouse as set forth by William Inmon:
Subject Oriented
Integrated
Nonvolatile
Time Variant
2)Surrogate Key
Data warehouses typically use a surrogate, (also known as artificial or identity key), key for the dimension tables primary keys. They can use Infa sequence generator, or Oracle sequence, or SQL Server Identity values for the surrogate key.
There are actually two cases where the need for a "dummy" dimension key arises:
1) the fact row has no relationship to the dimension (as in your example), and
2) the dimension key cannot be derived from the source system data.
3)Facts & Dimensions form the heart of a data warehouse. Facts are the metrics that business users would use for making business decisions. Generally, facts are mere numbers. The facts cannot be used without their dimensions. Dimensions are those attributes that qualify facts. They give structure to the facts. Dimensions give different views of the facts. In our example of employee expenses, the employee expense forms a fact. The Dimensions like department, employee, and location qualify it. This was mentioned so as to give an idea of what facts are.
Facts are like skeletons of a body.
Skin forms the dimensions. The dimensions give structure to the facts.
The fact tables are normalized to the maximum extent.
Whereas the Dimension tables are de-normalized since their growth would be very less.
4)Type 2 Slowly Changing Dimension
In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The newe record gets its own primary key.Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.
SCD Type 2
Slowly changing dimension Type 2 is a model where the whole history is stored in the database. An additional dimension record is created and the segmenting between the old record values and the new (current) value is easy to extract and the history is clear.
The fields 'effective date' and 'current indicator' are very often used in that dimension and the fact table usually stores dimension key and version number.
4)CRC Key
Cyclic redundancy check, or CRC, is a data encoding method (noncryptographic) originally developed for detecting errors or corruption in data that has been transmitted over a data communications line.
During ETL processing for the dimension table, all relevant columns needed to determine change of content from the source system (s) are combined and encoded through use of a CRC algorithm. The encoded CRC value is stored in a column on the dimension table as operational meta data. During subsequent ETL processing cycles, new source system(s) records have their relevant data content values combined and encoded into CRC values during ETL processing. The source system CRC values are compared against CRC values already computed for the same production/natural key on the dimension table. If the production/natural key of an incoming source record are the same but the CRC values are different, the record is processed as a new SCD record on the dimension table. The advantage here is that CRCs are small, usually 16 or 32 bytes in length, and easier to compare during ETL processing versus the contents of numerous data columns or large variable length columns.
5)Data partitioning, a new feature added to SQL Server 2005, provides a way to divide large tables and indexes into smaller parts. By doing so, it makes the life of a database administrator easier when doing backups, loading data, recovery and query processing.
Data partitioning improves the performance, reduces contention and increases availability of data.
Objects that may be partitioned are:
• Base tables
• Indexes (clustered and nonclustered)
• Indexed views
Q)Why we use stored procedure transformation?
A Stored Procedure transformation is an important tool for populating and maintaining databases
. Database administrators create stored procedures to automate time-consuming tasks that are too
complicated for standard SQL statements.
You might use stored procedures to do 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.
Q)What r the types of data that passes between informatica server and stored procedure?
types of data
Input/Out put parameters
Return Values
Status code.
Q) What is source qualifier transformation?
A) 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 represents the rows that the
Informatica Server reads when it executes a session.
The Transformation which Converts the source(relational or flat) datatype to
Informatica datatype.So it works as an intemediator between and source and informatica server.
Tasks performed by qualifier transformation:-
1. Join data originating from the same source database.
2. Filter records when the Informatica Server reads source data.
3. Specify an outer join rather than the default inner join.
4. Specify sorted ports.
5. Select only distinct values from the source.
6. Create a custom query to issue a special SELECT statement for the Informatica Server to read source data.
============================================================================================================
============================================================================================================
informatica interview questions and answers
1] HOW CAN YOU RECOGNIZE WHETHER OR NOT THE NEWLY ADDED ROWS IN THE SOURCE ARE GETS INSERT IN THE TARGET?
Answer: In the Type2 maping we have three options to recognise the newly added rows Version number Flagvalue Effective date Range.
2] WHAT IS THE DIFFERENCE BETWEEN INFORMATICA 7.0 AND 8.0?
Answer: The basic diff b/w in between informatica 8.0 and informatica7.0 is that in 8.0series informatica corp has introduces powerexchnage concept
3] PERFORMANCE TUNING IN INFORMATICA?
Answer: The goal of performance tuning is optimize session performance so sessions run during the available load window for the Informatica Server.Increase the session performance by following. The performa
4] DIFFERENCES BETWEEN NORMALIZER AND NORMALIZER TRANSFORMATION.
Answer: Normalizer: It is a transormation mainly using for cobol sources, it's change the rows into coloums and columns into rows Normalization:To remove the retundancy and inconsitecy
5] WHAT IS THE TARGET LOAD ORDER?
Answer: You specify the target loadorder based on source qualifiers in a maping.If you have the multiple source qualifiers connected to the multiple targets,You can designatethe order in which informatica ser
6] DIFF BETWEEN STATIC AND DYNAMIC CACHE? AND PLEASE EXPLAIN WITH ONE EXAMPLE?
Answer: Difference between static and dynamic cache- Static- Once the data is cached , it will not change. example unconnected lookup uses static cache. Dynamic- The cache is updated as to reflect the upda
7] WHAT IS THE USE OF INCREMENTAL AGGREGATION? EXPLAIN ME IN BRIEF WITH AN EXAMPLE.
Answer: Its a session option. When the informatica server performs incremental aggr. it passes new source data through the mapping and uses historical chache data to perform new aggregation caluculations incr
8] WHAT IS THE DIFFERENCE BETWEEN INFORMATICS 7X AND 8X AND WHAT IS LATEST VERSION?
Answer: Java Transformation available in the 8x version and it is not available in 7x version.
9] HOW DO WE DO UNIT TESTING IN INFORMATICA? HOW DO WE LOAD DATA IN INFORMATICA?
Answer: Unit testing are of two types 1. Quantitaive testing 2.Qualitative testing Steps. 1.First validate the mapping 2.Create session on themapping and then run workflow. Once the session
10] IS SORTER AN ACTIVE OR PASSIVE TRANSFORMATION? WHAT HAPPENS IF WE UNCHECK THE DISTINCT OPTION IN SORTER? WILL IT BE UNDER ACTIVE OR PASSIVE TRANSFORMATION?
Answer: Sorter is an active transformation. if you don't check the distinct option it is considered as a passive transformation. becos this distinct option eliminates the duplicate records from the table.
11] HOW THE INFORMATICA SERVER SORTS THE STRING VALUES IN RANK TRANSFORMATION?
Answer: When Informatica Server runs in UNICODE data movement mode ,then it uses the sort order configured in session properties.
12] EXPLAIN ABOUT INFORMATICA SERVER ARCHITECTURE?
Answer: Informatica server,load manager/rs,data transfer manager,reader,temp server and writer are the components of informatica server. first load manager sends a request to the reader if the reader is ready
13] HOW CAN YOU CREATE OR IMPORT FLAT FILE DEFINITION IN TO THE WAREHOUSE DESIGNER?
Answer: You can not create or import flat file defintion in to warehouse designer directly.Instead you must analyze the file in source analyzer,then drag it into the warehouse designer. When you drag the fl
14] HOW CAN YOU IMPROVE SESSION PERFORMANCE IN AGGREGATOR TRANSFORMATION?
Answer: One way is supplying the sorted input to aggregator transformation. In situations where sorted input cannot be supplied, we need to configure data cache and index cache at session/transformation level
15] WHAT IS THE DIFFERENCE BETWEEN STOP AND ABORT?
Answer: stop: _______If the session u want to stop is a part of batch you must stop the batch, if the batch is part of nested batch, Stop the outer most batch Abort:---- You can issue the abort comma
16] IN UPDATE STRATEGY TARGET TABLE OR FLAT FILE WHICH GIVES MORE PERFORMANCE? WHY?
Answer: Pros: Loading, Sorting, Merging operations will be faster as there is no index concept and Data will be in ASCII mode. Cons: There is no concept of updating existing records in flat file. As the
17] HOW MANY TYPES OF DIMENSIONS ARE AVAILABLE IN INFORMATICA?
Answer: There r 3 types of dimensions 1.star schema 2.snowflake schema 3.glaxy schema
18] WHAT IS DIFFERENCE B/W INFORMATICA 7.1 AND ABINITIO
Answer: There is a lot of diffrence between informatica an Ab Initio In Ab Initio we r using 3 parllalisim but Informatica using 1 parllalisim In Ab Initio no scheduling option we can scheduled manul
19] WHEN WE CREATE A TARGET AS FLAT FILE AND SOURCE AS ORACLE. HOW CAN I SPECIFY FIRST ROWS AS COLUMN NAMES IN FLAT FILES.
Answer: Use a pre sql statement....but this is a hardcoding method...if you change the column names or put in extra columns in the flat file, you will have to change the insert statement
20] WHAT IS THE DIFFERENCE BETWEEN FILTER AND LOOKUP TRANSFORMATION?
Answer: 1) Filter transformation is an Active transformation and Lookup is a Passive transformation 2) Filter transformation is used to Filter rows based on condition and Lookup is used to to look up data
21] HOW DO YOU CONFIGURE MAPPING IN INFORMATICA
Answer: You should configure the mapping with the least number of transformations and expressions to do the most amount of work possible. You should minimize the amount of data moved by deleting unnecessary l
22] HOW CAN WE USE PMCMD COMMAND IN A WORKFLOW OR TO RUN A SESSION?
Answer: in the command task there is a option pression. we can write appropriate command of pmcmd to run workflow.
23] WHAT ARE THE OUTPUT FILES THAT THE INFORMATICA SERVER CREATES DURING THE SESSION RUNNING?
Answer: Informatica server log: Informatica server(on unix) creates a log for all status and error messages(default name: pm.server.log). It also creates an error log for error messages. These files will b
24] IN DIMENSIONAL MODELING FACT TABLE IS NORMALIZED OR DENORMALIZED? IN CASE OF STAR SCHEMA AND INCASE OF SNOW FLAKE SCHEMA?
Answer: In Dimensional modeling, Star Schema: A Single Fact table will be surrounded by a group of Dimensional tables comprise of de- normalized data Snowflake Schema: A Single Fact table will be surrounded b
25] DISCUSS THE ADVANTAGES & DISADVANTAGES OF STAR & SNOWFLAKE SCHEMA?
Answer: In a STAR schema there is no relation between any two dimension tables, whereas in a SNOWFLAKE schema there is a possible relation between the dimension tables.
26] HOW MANY TYPES OF FACTS AND WHAT ARE THEY?
Answer: There are Factless Facts:Facts without any measures. Additive Facts:Fact data that can be additive/aggregative. Non-Additive facts: Facts that are result of non-additon Semi-Additive Facts: Only
27] WHAT IS SOURCE QUALIFIER TRANSFORMATION?
Answer: SQ transformation is a transformation which is automatically generated to read data from source tables into informatica designer.
28] WHEN DO U USE A UNCONNECTED LOOKUP AND CONNECTED LOOKUP.... WHAT IS THE DIFFERENCE BETWEEN DYNAMIC AND STATIC LOOKUP...Y AND WHEN DO V USE THESE TYPES OF LOOKUPS (I.E. DYNAMIC AND STATIC)
Answer: In static lookup cache, you cache all the lookup data at the starting of the session. in dynamic lookup cache, you go and query the database to get the lookup value for each record which needs the loo
29] WHICH TASKS CAN BE PERFORMED ON PORT LEVEL (USING ONE SPECIFIC PORT)?
Answer: I think unconnected Lookup or expression transformation can be used for single port for a row.
30] HOW CAN U WORK WITH REMOTE DATABASE IN INFORMATICA? DID U WORK DIRECTLY BY USING REMOTE CONNECTIONS?
Answer: To work with remote datasource u need to connect it with remote connections.But it is not preferable to work with that remote source directly by using remote connections .Instead u bring that source
31] CAN ANYONE EXPLAIN ERROR HANDLING IN INFORMATICA WITH EXAMPLES SO THAT IT WILL BE EASY TO EXPLAIN THE SAME IN THE INTERVIEW?
Answer: Go to the session log file there we will find the information regarding to the session initiation process, errors encountered. load summary. so by seeing the errors encountered during the
32] WHAT IS DIFFERENCE BETWEEN IIF AND DECODE FUNCTION
Answer: You can use nested IIF statements to test multiple conditions. The following example tests for various conditions and returns 0 if sales is zero or negative: IIF( SALES > 0, IIF( SALES < 50, SALAR
33] DIFFERENCE BETWEEN RANK AND DENSE RANK?
Answer: Rank: 1 2<--2nd position 2<--3rd position 4 5 Same Rank is assigned to same totals/numbers. Rank is followed by the Position. Golf game ususally Ranks this way. This is usually a Gold Ranking.
34] WHAT IS MEANT BY AGGREGATE FACT TABLE AND WHERE IS IT USED?
Answer: Basically fact tables are two kinds 1. Aggregated factable and Factless fact table. Agregated factable has aggregarted columns. for eg. Total-Sal, Dep-Sal. where as in factless factable will not have
35] WHAT IS THE DIFFERENCE BETWEEN CONSTRAIND BASE LOAD ORDERING AND TARGET LOAD PLAN
Answer: Constraint based load ordering example: Table 1---Master Table 2---Detail If the data in table1 is dependent on the data in table2 then table2 should be loaded first.In such cases to con
36] WHAT IS THE DIFFERENCE BETWEEN CONNECTED AND UNCONNECTED STORED PROCEDURES?
Answer: 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
37] WHAT ARE MAIN ADVANTAGES AND PURPOSE OF USING NORMALIZER TRANSFORMATION IN INFORMATICA?
Answer: Narmalizer Transformation is used mainly with COBOL sources where most of the time data is stored in de-normalized format. Also, Normalizer transformation can be used to create multiple rows from a si
38] HOW CAN WE PARTITION A SESSION IN INFORMATICA?
Answer: The Informatica PowerCenter Partitioning option optimizes parallel processing on multiprocessor hardware by providing a thread-based architecture and built-in data partitioning. GUI-based tools redu
39] WHAT IS UPDATE STRATEGY TRANSFORMATION?
Answer: The model you choose constitutes your update strategy, how to handle changes to existing rows. In PowerCenter and PowerMart, you set your update strategy at two different levels: Within a session.
40] WHEN DO U WE USE DYNAMIC CACHE AND WHEN DO WE USE STATIC CACHE IN AN CONNECTED AND UNCONNECTED LOOKUP TRANSFORMATION
Answer: We use dynamic cache only for connected lookup. We use dynamic cache to check whether the record already exists in the target table are not. And depending on that, we insert,update or delete the recor
41] TO ACHIEVE THE SESSION PARTITION WHAT ARE THE NECESSARY TASKS YOU HAVE TO DO?
Answer: Configure the session to partition source data. Install the informatica server on a machine with multiple CPU’s.
42] WHAT ARE THE TYPES OF METADATA THAT STORES IN REPOSITORY?
Answer: Following are the types of metadata that stores in the repository:- Database connections Global objects Mappings Mapplets Multidimensional metadata Reusable transformations Sessions and batches
43] HOW TO RECOVER SESSIONS IN CONCURRENT BATCHES?
Answer: If multiple sessions in a concurrent batch fail, you might want to truncate all targets and run the batch again. However, if a session in a concurrent batch fails and the rest of the sessions complet
44] WHAT ARE THE NEW FEATURES IN INFORMATICA 5.0?
Answer: You can Debug u r maping in maping designer You can view the work space over the entire screen The designer displays a new icon for a invalid mapings in the navigator window You can use a dynamic l
45] WHAT ARE TWO TYPES OF PROCESSES THAT INFORMATICA RUNS THE SESSION?
Answer: Load manager Process: Starts the session, creates the DTM process, and sends post-session email when the session completes. The DTM process. Creates threads to initialize the session, read, write, an
46] ON A DAY, I LOAD 10 ROWS IN MY TARGET AND ON NEXT DAY IF I GET 10 MORE ROWS TO BE ADDED TO MY TARGET OUT OF WHICH 5 ARE UPDATED ROWS HOW CAN I SEND THEM TO TARGET? HOW CAN I INSERT AND UPDATE THE RECORD?
Answer: We can use do this by identifying the granularity of the target table . We can use CRC external procedure after that to compare newly generated CRC no. with the old one and if they do not match then
47] WHAT IS THE DEFAULT JOIN THAT SOURCE QUALIFIER PROVIDES?
Answer: Inner equi join.
48] WHAT ARE THE JOIN TYPES IN JOINER TRANSFORMATION?
Answer: Normal (Default) Master outer Detail outer Full outer.
49] WHAT ARE THE METHODS FOR CREATING REUSABLE TRANSFORMATIONS?
Answer: Two methods:- 1.Design it in the transformation developer. 2.Promote a standard transformation from the mapping designer.After you add a transformation to the mapping , You can promote it to the sta
50] IF YOU HAVE FOUR LOOKUP TABLES IN THE WORKFLOW. HOW DO YOU TROUBLESHOOT TO IMPROVE PERFORMANCE?
Answer: There r many ways to improve the mapping which has multiple lookups. 1) we can create an index for the lookup table if we have permissions(staging area). 2) divide the lookup mapping into two (a
51] SCD MAPPINGS ARE MOSTLY USED UNCONNECTED LOOKUP TRANSFORMATION. UNCONNECTED LOOKUP USED STATIC CACHE ONLY. AT THIS TIME HOW CAN YOU INSERT OR UPDATE DATE IN TARGET BY USING STATIC CACHE?
Answer: There is no connected lookup t/r n by default it takes static cache.For insert n update operations we use dynamic cache.
52] WHAT ARE THE JOINER CACHES?
Answer: When a Joiner transformation occurs in a session, the Informatica Server reads all the records from the master source and builds index and data caches based on the master rows. After building the ca
53] WHY DID U USE UPDATE STATEGY IN YOUR APPLICATION?
Answer: Update Strategy is used to drive the data to be Inert, Update and Delete depending upon some condition. You can do this on session level tooo but there you cannot define any condition.For eg: If you w
54] WHAT IS DIFFERENCE BETWEEN PARTIONING OF RELATONAL TARGET AND PARTITIONING OF FILE TARGETS?
Answer: Partition's can be done on both relational and flat files. Informatica supports following partitions 1.Database partitioning 2.RoundRobin 3.Pass-through 4.Hash-Key partitioning 5.Key
55] WHAT ARE THE NEW FEATURES OF THE SERVER MANAGER IN THE INFORMATICA 5.0?
Answer: You can use command line arguments for a session or batch.This allows you to change the values of session parameters,and mapping parameters and maping variables. Parallel data processing: This featur
56] WHAT IS THE DIFFERENCE BETWEEN SUMMARY FILTER AND DETAIL FILTER?
Answer: Summary filter can be applieid on a group of rows that contain a common value.where as detail filters can be applied on each and every rec of the data base.
57] WHAT ARE THE CONNECTED OR UNCONNECTED TRANSFORMATIONS?
Answer: An unconnected transforamtion is not connected to other transformations in the mapping.Connected transforamation is connected to other transforamtions in the mapping.
58] HOW TO IMPORT ORACLE SEQUENCE INTO INFORMATICA.
Answer: CREATE ONE PROCEDURE AND DECLARE THE SEQUENCE INSIDE THE PROCEDURE,FINALLY CALL THE PROCEDURE IN INFORMATICA WITH THE HELP OF STORED PROCEDURE TRANSFORMATION.
59] WHAT ARE THE REAL TIMES PROBLEMS GENERALLY COME UP WHILE DOING/RUNNING MAPPING/ANY TRANSFORMATION? CAN ANYBODY EXPLAIN WITH EXAMPLE.
Answer: Here are few real time examples of problems while running informatica mappings: 1) Informatica uses OBDC connections to connect to the databases. The database passwords (production ) is changed in
60] IF A SESSION FAILS AFTER LOADING OF 10,000 RECORDS IN TO THE TARGET. HOW CAN U LOAD THE RECORDS FROM 10001 THE RECORD WHEN U RUN THE SESSION NEXT TIME?
Answer: As explained above informatcia server has 3 methods to recovering the sessions.Use performing recovery to load the records from where the session fails.
61] WHY DID YOU USE STORED PROCEDURE IN YOUR ETL APPLICATION?
Answer: Usage of stored procedure has the following advantages 1 checks the status of the target database 2 drops and recreates indexes 3 determines if enough space exists in the database 4 perfor
62] WHAT ARE THE DIFFERENCE BETWEEN JOINER TRANSFORMATION AND SOURCE QUALIFIER TRANSFORMATION?
Answer: You can join hetrogenious data sources in joiner transformation which we can not achieve in source qualifier transformation. You need matching keys to join two relational sources in source qualifier
63] EXPLAIN USE OF UPDATE STRATEGY TRANSFORMATION
Answer: This is the important transformation,is used to maintain the history data or just most recent changes into the target table. We can set or flag the records by using these two levels. 1) Within a ses
64] WHAT ARE THE DIFFERENCE BETWEEN VIEW AND MATERIALIZED VIEW?
Answer: Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse. A materialized view provides indirect access to
65] IDENTIFYING BOTTLENECKS IN VARIOUS COMPONENTS OF INFORMATICA AND RESOLVING THEM.
Answer: The best way to find out bottlenecks is writing to flat file and see where the bottle neck is .
66] WHAT ARE THE BASIC NEEDS TO JOIN TWO SOURCES IN A SOURCE QUALIFIER?
Answer: Basic need to join two sources using source qualifier: 1) Both sources should be in same database 2) The should have at least one column in common with same data types
67] THIS IS A SCENARIO IN WHICH THE SOURCE HAS 2 COLS 10 A 10 A 20 C 30 D 40 E 20 C AND THERE SHOULD BE 2 TARGETS ONE TO SHOW THE DUPLICATE VALUES AND ANOTHER TARGET FOR DISTINCT ROWS. T1 T2 10 A 10 A 20 C 20 C 30 D WHICH TRANSFORMATION CAN BE USED TO LOAD DATA INTO TARGET? 40 E
Answer: Sorter Transformation to Target 2. Source qualifier transformation to Target 1. Check the Distinct option in Sorter transformation.
68] WHAT ARE TWO MODES OF DATA MOVEMENT IN INFORMATICA SERVER?
Answer: The data movement mode depends on whether Informatica Server should process single byte or multi-byte character data. This mode selection can affect the enforcement of code page relationships and cod
69] WHAT IS PUSHDOWN OPTIMIZATIONS IN PC 8.X WITH EXAMPLE?
Answer: Use pushdown optimization to push transformation logic to the source or target database. The Integration Service analyzes the transformation logic, mapping, and session configuration to determine the
70] I HAVE WINDOWS XP. I WAS TOLD A PERSON COULD FIND OUT WHAT SITES A PERSON IS LOOKING ON YOUR COMPUTER. HOW DO YOU DO THIS? I HAVE A PERSON WHO USES MY COMPUTER, AND I THINK HE IS LOOKING AT SITES HE SHOULD NOT BE VIEWING.
Answer: Open the browser, click the history button.
71] WHAT ARE THE TYPES OF MAPING IN GETTING STARTED WIZARD?
Answer: Simple Pass through maping : Loads a static fact or dimension table by inserting all rows. Use this mapping when you want to drop all existing data from your table before loading new data. Slowly Gr
72] IN A SCENARIO I HAVE COL1, COL2, COL3, UNDER THAT 1,X,Y, AND 2,A,B AND I WANT IN THIS FORM COL1, COL2 AND 1,X AND 1,Y AND 2,A AND 2,B, WHAT IS THE PROCEDURE?
Answer: Use Normalizer : create two ports - first port occurs = 1 second make occurs = 2 two output ports are created and connect to target
73] HOW CAN YOU IMPROVE THE PERFORMANCE OF AGGREGATE TRANSFORMATION?
Answer: We can improve the aggregator performance in the following ways 1.send sorted input. 2.increase aggregator cache size. i.e. Index cache and data cache. 3.Give input/output what you need in th
74] WHERE TO STORE INFORMATICA REJECTED DATA? HOW TO EXTRACT THE INFORMATICA REJECTED DATA?
Answer: The reject rows say for example due to unique key constrain is all pushed by session into the $PMBadFileDir (default relative path is
75] IN A SEQUENTIAL BATCH HOW CAN WE STOP SINGLE SESSION?
Answer: We can stop it using PMCMD command or in the monitor right click on that perticular session and select stop.this will stop the current session and the sessions next to it.
76] HOW DO YOU CREATE SINGLE LOOKUP TRANSFORMATION USING MULTIPLE TABLES?
Answer: Write a override sql query. Adjust the ports as per the sql query.
77] WHAT R THE MAPINGS THAT WE USE FOR SLOWLY CHANGING DIMENSION TABLE?
Answer: We can use the following Mapping for slowly Changing dimension table. Expression Lookup Filter Sequence Generator Update Strategy
78] IN WHICH CONDITIONS WE CANNOT USE JOINER TRANSFORMATION (LIMITATIONS OF JOINER TRANSFORMATION)?
Answer: Both pipelines begin with the same original data source. Both input pipelines originate from the same Source Qualifier transformation. Both input pipelines originate from the same Normalizer transfo
79] WHAT IS BATCH AND DESCRIBE ABOUT TYPES OF BATCHES?
Answer: There are two types of batches 1. Concurrent 2. Sequential
80] WHAT IS THE PROCEDURE TO LOAD THE FACT TABLE? GIVE IN DETAIL?
Answer: Based on the requirement to your fact table, choose the sources and data and transform it based on your business needs. For the fact table, you need a primary key so use a sequence generator transfo
81] HOW TO MOVE THE MAPPING FROM ONE DATABASE TO ANOTHER?
Answer: 1. Open the mapping you want to migrate. Go to File Menu - Select 'Export Objects' and give a name - an XML file will be generated. Connect to the repository where you want to migrate and then select
82] HOW DO U CHECK THE SOURCE FOR THE LATEST RECORDS THAT ARE TO BE LOADED INTO THE TARGET. I.E. I HAVE LOADED SOME RECORDS YESTERDAY, TODAY AGAIN THE FILE HAS BEEN POPULATED WITH SOME MORE RECORDS TODAY, SO HOW DO I FIND THE RECORDS POPULATED TODAY.
Answer: a) Create a lookup to target table from Source Qualifier based on primary Key. b) Use and expression to evaluate primary key from target look-up. ( If a new source record look-up primary key port f
83] WHAT ARE VARIABLE PORTS AND LIST TWO SITUATIONS WHEN THEY CAN BE USED?
Answer: We have mainly tree ports Inport, Outport, Variable port. Inport represents data is flowing into transformation. Outport is used when data is mapped to next transformation. Variable port is used when
84] CAN YOU USE THE MAPING PARAMETERS OR VARIABLES CREATED IN ONE MAPING INTO ANY OTHER REUSABLE TRANSFORMATION?
Answer: Yes.Because reusable tranformation is not contained with any maplet or maping.
85] PARTITIONING, BITMAP INDEXING (WHEN TO USE), HOW WILL THE BITMAP INDEXING WILL EFFECT THE PERFORMANCE
Answer: Bitmap indexing a indexing technique to tune the performance of SQL queries. The default type is B-Tree indexers which is of high cardinality (normalized data). You can use bitmap indexers for de-norm
86] HOW A TWO DIMENSION ARRAY IS INITIALIZE WITH POINTER?
Answer: #include void main() { int size; cout<<"Size "; cin>>size; int **p=new int *[size]; for(int i=0;i
87] WHAT R THE TYPES OF GROUPS IN ROUTER TRANSFORMATION?
Answer: A Router transformation has the following types of groups: Input Output Input Group The Designer copies property information from the input ports of the input group to create a set of output
88] THERE ARE 1000 SOURCE TABLES CONTAINING THE SAME DATA WITH DIFFERENT FILE FORMATS, NOW I WANT TO LOAD INTO A SINGLE TARGET TABLE. HOW TO ACHIEVE ?
Answer: first u should convert diff. file format to one format then create 1 to 1 mapping,run it and see the o/p in unix whether file is posted or not.
89] HOW CAN U COMPLETE UNRECOVERABLE SESSIONS?
Answer: Under certain circumstances, when a session does not complete, you need to truncate the target tables and run the session from the beginning. Run the session from the beginning when the Informatica S
90] HOW U WILL CREATE HEADER AND FOOTER IN TARGET USING INFORMATICA?
Answer: If you are focus is about the flat files then one can set it in file properties while creating a mapping or at the session level in session properties
91] HOW CAN YOU SAY THAT UNION TRANSFORMATION IS ACTIVE TRANSFORMATION?
Answer: By Definition, Active transformation is the transformation that changes the number of rows that pass through it...in union transformation the number of rows resulting from union can be (are) different
92] WHAT IS THE DIFFERENCE BETWEEN POWERCENTER 6 AND POWERCENTER 7?
Answer: 1)lookup the flat files in informatica 7.X but we cann't lookup flat files in informatica 6.X 2) External Stored Procedure Transformation is not available in informatica 7.X but this transformation
93] WHAT ARE LOAD TYPES IN INFORMATICA AND WHAT IS DELTA LOAD
Answer: There are two types of load bulk and normal.we ll use normal type almost, bulk is used in for the data which is huge to insert to the target
94] HOW CAN I TRANSFORM ROW TO COLUMN?
Answer: Through Normalizer Transformation we can do this.
95] 1. CAN U EXPLAIN ONE CRITICAL MAPPING? 2. PERFORMANCE ISSUE WHICH ONE IS BETTER? WHETHER CONNECTED LOOKUP TRANSFORMATION OR UNCONNECTED ONE?
Answer: it depends on your data and the type of operation u r doing. If u need to calculate a value for all the rows or for the maximum rows coming out of the source then go for a connected lookup. Or,i
96] DIFFERENCE BETWEEN STATIC CACHE AND DYNAMIC CACHE
Answer: Static cache Dynamic cache U can not insert or update the cache U can insert rows into the cache as u pass to the target The informatic server returns a value from the lookup table or cache w
97] WHAT IS TRANSACTION?
Answer: Transaction is a logical unit of work that comprises one or more sql statements executed by a single user
98] WHAT IS THE DIFFERENCE BETWEEN NORMAL LOAD AND BULK LOAD?
Answer: Normal Load: Normal load will write information to the database log file so that if any recorvery is needed it is will be helpful. when the source file is a text file and loading data to a table,in su
99] WHY SORTER TRANSFORMATION IS AN ACTIVE TRANSFORMATION?
Answer: This is type of active transformation which is responsible for sorting the data either in the ascending order or descending order according to the key specifier. the port on which the sorting takes pl
100] WHAT IS THE LOGIC WILL YOU IMPLEMENT TO LOAD THE DATA IN TO ONE FACTV FROM 'N' NUMBER OF DIMENSION TABLES.
Answer: Normally every one use 1) slowly changing dimensions 2) slowly growing dimensions
101] CAN YOU START A SESSION INSIDE A BATCH INDIVIDUALLY?
Answer: We can start our required session only in case of sequential batch.in case of concurrent batch we cant do like this.
102] WHAT R THE TASKS THAT LOAD MANGER PROCESS WILL DO?
Answer: Manages the session and batch scheduling: Whe u start the informatica server the load maneger launches and queries the repository for a list of sessions configured to run on the informatica server.Whe
103] WHAT R THE TYPES OF LOOKUP CACHES?
Answer: 1) Static Cache 2) Dynamic Cache 3) Persistent Cache 4) Reusable Cache 5) Shared Cache
104] WHAT IS HASH TABLE INFORMATICA?
Answer: In hash partitioning, the Informatica Server uses a hash function to group rows of data among partitions. The Informatica Server groups the data based on a partition key. Use hash partitioning when yo
105] IDENTIFYING BOTTLENECKS IN VARIOUS COMPONENTS OF INFORMATICA AND RESOLVING THEM.
Answer: The best way to find out bottlenecks is writing to flat file and see where the bottle neck is.
106] WHAT IS THE NEED AND WHEN DO WE USE A DYNAMIC LOOKUP CACHE?
Answer: Implementing Dynamic cache in the lookup transformation comes under performance tuning side. If your mapping has lookup on target u can implement dynamic cache.
107] WHAT IS THE STATUS CODE?
Answer: Status code provides error handling for the informatica server during the session. The stored procedure issues a status code that notifies whether or not stored procedure completed sucessfully. This
108] WHAT IS THE RANK INDEX IN RANK TRANSFORMATION?
Answer: The Designer automatically creates a RANKINDEX port for each Rank transformation. The Informatica Server uses the Rank Index port to store the ranking position for each record in a group. For example,
109] WHAT ARE THE UNSUPPORTED REPOSITORY OBJECTS FOR A MAPPLET?
Answer: COBOL source definition Joiner transformations Normalizer transformations Non reusable sequence generator transformations. Pre or post session stored procedures Target definitions Power mart 3.5
110] WHAT IS DATADRIVEN?
Answer: The Informatica Server follows instructions coded into Update Strategy transformations within the session mapping to determine how to flag rows for insert, delete, update, or reject. If the mapping
111] HOW TO DEFINE INFORMATICA SERVER?
Answer: Informatica server is the main server component in informatica product family..Which is resonsible for reads the data from various source system and tranforms the data according to business rule and l
112] WHAT IS THE METHOD OF LOADING 5 FLAT FILES OF HAVING SAME STRUCTURE TO A SINGLE TARGET AND WHICH TRANSFORMATIONS I CAN USE?
Answer: Use file repository concept.
113] WITHOUT USING UPDATESTRETAGY AND SESSIONS OPTIONS, HOW WE CAN DO THE UPDATE OUR TARGET TABLE?
Answer: In session properties, There is an option insert update insert as update update as update like that by using this we will easily solve
114] IN WHICH CIRCUMSTANCES THAT INFORMATICA SERVER CREATES REJECT FILES?
Answer: When it encounters the DD_Reject in update strategy transformation. Violates database constraint Filed in the rows was truncated or overflowed.
115] TO PROVIDE SUPPORT FOR MAINFRAMES SOURCE DATA, WHICH FILES ARE USED AS A SOURCE DEFINITIONS?
Answer: COBOL files.
116] IN A SCENARIO I WANT TO CHANGE THE DIMENSIONS OF A TABLE AND NORMALIZE THE RENORMALIZED TABLE WHICH TRANSFORMATION CAN I USE?
Answer: You can use normalizer transformation .It will normalize the records.
117] WHAT ARE MAPPING PARAMETERS AND VARIABLES IN WHICH SITUATION WE CAN USE IT
Answer: If we need to change certain attributes of a mapping after every time the session is run, it will be very difficult to edit the mapping and then change the attribute. So we use mapping parameters and
118] WHAT R THE UNSUPPORTED REPOSITORY OBJECTS FOR A MAPPLET?
Answer: Source definitions. Definitions of database objects (tables, views, synonyms) or files that provide source data. Target definitions. Definitions of database objects or files that contain the target
119] CAN INFORMATICA LOAD HETEROGENEOUS TARGETS FROM HETEROGENEOUS SOURCES?
Answer: Yes it can. For example...Flat File and Relations sources are joined in the mapping, and later, Flat File and relational targets are loaded.
120] CAN YOU START A BATCHES WITH IN A BATCH?
Answer: You cannot. If you want to start batch that resides in a batch,create a new independent batch and copy the necessary sessions into the new batch.
121] WHAT R THE ACTIVE AND PASSIVE TRANSFORMATIONS?
Answer: Transformations can be active or passive. An active transformation can change the number of rows that pass through it, such as a Filter transformation that removes rows that do not meet the filter con
122] WHAT ARE DIMENSIONS AND VARIOUS TYPES OF DIMENSIONS?
Answer: Set of level properties that describe a specific aspect of a business, used for analyzing the factual measures of one or more cubes, which use that dimension. Egs. Geography, time, customer and produc
123] EXPLAIN ABOUT RECOVERING SESSIONS?
Answer: If you stop a session or if an error causes a session to stop, refer to the session and error logs to determine the cause of failure. Correct the errors, and then complete the session. The method you
124] CAN WE ELIMINATE DUPLICATE ROWS BY USING FILTER AND ROUTER TRANSFORMATION? IF SO EXPLAIN ME IN DETAIL.
Answer: U can use SQL query for uniqness if the source is Relational But if the source is Flat file then u should use Shorter or Aggregatot transformation
125] THERE ARE 3 DEPTS. IN DEPT TABLE AND ONE WITH 100 PEOPLE AND 2ND WITH 5 AND 3RD WITH SOME 30 AND SO. I WANT TO DISPLAY THOSE DEPTNO WHERE MORE THAN 10 PEOPLE EXISTS
Answer: Yes! the answer provided is absolutely right. by an SQL application(Oracle). If you want to perform it thru informatica, the Fire the same query in the SQL Override of Source qualifier transformati
126] 1) WHAT ARE THE VARIOUS TEST PROCEDURES USED TO CHECK WHETHER THE DATA IS LOADED IN THE BACKEND, PERFORMANCE OF THE MAPPING, AND QUALITY OF THE DATA LOADED IN INFORMATICA. 2) WHAT ARE THE COMMON PROBLEMS DEVELOPERS FACE WHILE ETL DEVELOPMENT
Answer: If you want to know the performance of a mapping at transformation level, then select the option in the session properties-> collect performance data. At the run time in the monitor you can see it in
127] WHAT IS DIFFERENCE BETWEEN LOOKUP CACHE AND UNCHACHED LOOKUP? CAN I RUN THE MAPPING WITHOUT STARTING THE INFORMATICA SERVER?
Answer: The difference between cache and uncacheed lookup is when you configure the lookup transformation cache lookup it stores all the lookup table data in the cache when the first input record enter into t
128] WHAT IS PARAMETER FILE?
Answer: Parameter file is to define the values for parameters and variables used in a session. A parameter file is a file created by text editor such as word pad or notepad. You can define the following val
129] WHAT ARE THE MAPINGS THAT WE USE FOR SLOWLY CHANGING DIMENSION TABLE?
Answer: Type1: Rows containing changes to existing dimensions are updated in the target by overwriting the existing dimension. In the Type 1 Dimension mapping, all rows contain current dimension data. Use t
130] WHAT IS MYSTERY DIMENTION?
Answer: Using Mystery Dimension ur maitaining the mystery data in ur Project.
131] HOW DO YOU HANDLE DECIMAL PLACES WHILE IMPORTING A FLATFILE INTO INFORMATICA?
Answer: While importing flat file definetion just specify the scale for a neumaric data type. in the mapping, the flat file source supports only number datatype(no decimal and integer). In the SQ associated w
132] WHAT ARE THE DIFFERENT THREADS IN DTM PROCESS?
Answer: Master thread: Creates and manages all other threads Maping thread: One maping thread will be creates for each session. Fectchs session and maping information. Pre and post session threads: This wil
133] WHICH IS BETTER AMONG CONNECTED LOOKUP AND UNCONNECTED LOOKUP TRANSFORMATIONS IN INFORMATICA OR ANY OTHER ETL TOOL?
Answer: If you are having defined source you can use connected, source is not well defined or from different database you can go for unconnected We are using like that only
134] WHAT IS METADATA REPORTER?
Answer: It is a web based application that enables you to run reports against repository metadata. With a meta data reporter, You can access information about u r repository with out having knowledge of sql,
135] WHICH TRANSFORMATION SHOULD WE USE TO NORMALIZE THE COBOL AND RELATIONAL SOURCES?
Answer: Normalizer Transformation. When you drag the COBOL source in to the mapping Designer workspace,the normalizer transformation automatically appears,creating input and output ports for every column in
136] WHAT IS LOAD MANAGER?
Answer: While running a Workflow,the PowerCenter Server uses the Load Manager process and the Data Transformation Manager Process (DTM) to run the workflow and carry out workflow tasks.When the PowerCenter Se
137] WHAT IS CODE PAGE COMPATIBILITY?
Answer: Compatibility between code pages is used for accurate data movement when the Informatica Sever runs in the Unicode data movement mode. If the code pages are identical, then there will not be any data
138] WHAT R THE BASIC NEEDS TO JOIN TWO SOURCES IN A SOURCE QUALIFIER?
Answer: The both the table should have a common field with same data type. Its not necessary both should follow primary and foreign relationship. If any relation ship exists that will help u in performance
139] WHAT R THE TYPES OF MAPING WIZARDS THAT R TO BE PROVIDED IN INFORMATICA?
Answer: Simple Pass through Slowly Growing Target Slowly Changing the Dimension Type1 Most recent values Type2 Full History Version Flag Date Type3 Current and one previous
140] WHICH TOOL U USE TO CREATE AND MANAGE SESSIONS AND BATCHES AND TO MONITOR AND STOP THE INFORMATICA SERVER?
Answer: Informatica Workflow Managar and Informatica Worlflow Monitor
141] WHY WE USE PARTITIONING THE SESSION IN INFORMATICA?
Answer: Performance can be improved by processing data in parallel in a single session by creating multiple partitions of the pipeline. Informatica server can achieve high performance by partitioning the p
142] EXPLAIN ABOUT PERFORM RECOVERY?
Answer: When the Informatica Server starts a recovery session, it reads the OPB_SRVR_RECOVERY table and notes the row ID of the last row committed to the target database. The Informatica Server then reads al
143] WHY AND WHERE WE ARE USING FACTLESS FACT TABLE?
Answer: Factless Fact Tables are the fact tables with no facts or measures (numerical data). It contains only the foriegn keys of corresponding Dimensions.
144] IN CERTAIN MAPPING THERE ARE FOUR TARGETS TG1,TG2,TG3 AND TG4. TG1 HAS A PRIMARY KEY,TG2 FOREIGN KEY REFERENCING THE TG1'S PRIMARY KEY,TG3 HAS PRIMARY KEY THAT TG2 AND TG4 REFERS AS FOREIGN KEY,TG2 HAS FOREIGN KEY REFERENCING PRIMARY KEY OF TG4 ,THE ORDER IN WHICH THE INFORMATICA WILL LOAD THE TARGET? 2]HOW CAN I DETECT AGGREGATE TRANSFORMATION CAUSING LOW PERFORMANCE?
Answer: To get performance details for any aggregator transformation, we have to check some parameters in the .perf file named as Transformationname_writetodisk and Transformationname_readfromdisk. If these t
145] WHAT ARE THE PROPERTIES SHOULD BE NOTIFIED WHEN WE CONNECT THE FLAT FILE SOURCE DEFINITION TO RELATIONAL DATABASE TARGET DEFINITION?
Answer: 1.File is fixed width or delimited 2.Size of the file. If its can be executed without performance issues then normal load will work If its huge in GB they NWAY partitions can be specified at
146] HOW DO YOU LOAD THE TIME DIMENSION?
Answer: Time Dimension will generally load manually by using PL/SQL , shell scripts, proc C etc......
147] WHAT IS THE MAPPING FOR UNIT TESTING IN INFORMATICA, ARE THERE ANY OTHER TESTING’S IN INFORMATICA, AND HOW WE WILL DO THEM AS A ETL DEVELOPER. HOW DO THE TESTING PEOPLE WILL DO TESTING ARE THERE ANY SPECIFIC TOOLS FOR TESTING
Answer: In informatica there is no method for unit testing. There are two methods to test the mapping. 1. But we have data sampling. set the ata sampling properties for session in workflow manager for spec
148] WHAT IS THE MAPLET?
Answer: Maplet is a set of transformations that you build in the maplet designer and You can use in multiple mapings.
149] CAN YOU USE THE MAPING PARAMETERS OR VARIABLES CREATED IN ONE MAPING INTO ANOTHER MAPING?
Answer: No.
150] WHAT ARE THE RANK CACHES?
Answer: During the session, the informatica server compares an input row with rows in the datacache. If the input row out-ranks a stored row, the informatica server replaces the stored row with the input row
151] WHAT ARE THE TYPES OF DATA THAT PASSES BETWEEN INFORMATICA SERVER AND STORED PROCEDURE?
Answer: Three types of data:- Input/Out put parameters Return Values Status code.
152] AT THE MAX HOW MANY TRANSFORMATIONS CAN BE US IN A MAPPING?
Answer: In a mapping we can use any number of transformations depending on the project, and the included transformations in the perticular related transformatons.
153] WHAT ARE THE TYPES OF MAPING WIZARDS THAT ARE TO BE PROVIDED IN INFORMATICA?
Answer: The Designer provides two mapping wizards to help you create mappings quickly and easily. Both wizards are designed to create mappings for loading and maintaining star schemas, a series of dimensions
154] WHILE IMPORTING THE RELATIONAL SOURCE DEFINITION FROM DATABASE, WHAT ARE THE META DATA OF SOURCE YOU IMPORT?
Answer: Source name Database location Column names Datatypes Key constraints.
155] WHY USE THE LOOKUP TRANSFORMATION?
Answer: To perform the following tasks:- Get a related value. For example, if your source table includes employee ID, but you want to include the employee name in your target table to make your summary data
156] WHAT ARE THE DIFFERENT OPTIONS USED TO CONFIGURE THE SEQUENTIAL BATCHES?
Answer: Two options Run the session only if previous session completes sucessfully. Always runs the session.
157] WHICH TOOL YOU USE TO CREATE AND MANAGE SESSIONS AND BATCHES AND TO MONITOR AND STOP THE INFORMATICA SERVER?
Answer: Informatica Server Manager.
158] WHAT IS A SOURCE QUALIFIER?
Answer: 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 represents the rows that the Informatica Serv
159] IN THE SOURCE, IF WE ALSO HAVE DUPLICATE RECORDS AND WE HAVE 2 TARGETS, T1- FOR UNIQUE VALUES AND T2- ONLY FOR DUPLICATE VALUES. HOW DO WE PASS THE UNIQUE VALUES TO T1 AND DUPLICATE VALUES TO T2 FROM THE SOURCE TO THESE 2 DIFFERENT TARGETS IN A SINGLE MAPPING?
Answer: source--->sq--->exp-->sorter(with enable select distinct check box)--->t1 --->aggregator(with enabling group by and write count function)--->t2 If u want only duplicates to t2 u can follow t
160] EXPLAIN ME THE 3 POINTS: I WANT TO KNOW : 1) THE DIFFERENCES BETWEEN USING NATIVE AND ODBC SERVER-SIDE DATABASE CONNECTIONS 2)KNOW THE REASON WHY TO REGISTER A SERVER TO THE REPOSITORY IS NECESSARY 3)KNOW THE RULES ASSOCIATED WITH TRANSFERRING AND SHARING OBJECTS BETWEEN FOLDERS. 4) KNOW THE RULES ASSOCIATED WITH TRANSFERRING AND SHARING OBJECTS BETWEEN REPOSITORIES
Answer: 1> Native connection is something which is provided by the same vendor for that tool. eg: oracle warehouse builder has its own driver to connect to oracle DB which does not use a ODBC driver. here con
161] WHAT IS THE HIERARCHIES IN DWH
Answer: Data sources ---> Data acquisition ---> Warehouse ---> Front end tools ---> Metadata management ---> Data warehouse operation management
162] HOW DO YOU CREATE A MAPPING USING MULTIPLE LOOKUP TRANSFORMATION?
Answer: Use unconnected lookup if same lookup repeats multiple times.
163] DEFINE INFORMATICA REPOSITORY?
Answer: The Informatica repository is a relational database that stores information, or metadata, used by the Informatica Server and Client tools. Metadata can include information such as mappings describing
164] WHAT IS DIFFERENCE BETWEEN MAPLET AND REUSABLE TRANSFORMATION?
Answer: Maplet consists of set of transformations that is reusable.A reusable transformation is a single transformation that can be reusable. If u create a variables or parameters in maplet that can not be
165] WHAT TRANSFORMATION YOU CAN USE INPLACE OF LOOKUP?
Answer: Look-up transformation can serve in so many situations. So, if you can a bit particular about the scenarioo that you are talking about, it will be easy to interpret.
166] HOW CAN WE STORE PREVIOUS SESSION LOGS
Answer: Just run the session in time stamp mode then automatically session log will not overwrite current session log.
167] HOW TO USE THE UNCONNECTED LOOKUP I.E., FROM WHERE THE INPUT HAS TO BE TAKEN AND THE OUTPUT IS LINKED? WHAT CONDITION IS TO BE GIVEN?
Answer: The unconnected lookup is used just like a function call. in an expression output/variable port or any place where an expression is accepted(like condition in update strategy etc..), call the unconnec
168] IS A FACT TABLE NORMALIZED OR DE-NORMALIZED?
Answer: A fact table is always DENORMALISED table. It consists of data from dimension table (Primary Key's) and Fact table has Foreign keys and measures.
169] HOW CAN WE JOIN 3 DATABASE LIKE FLAT FILE, ORACLE, DB2 IN INFORMATRICA..
Answer: You have to use two joiner transformations.fIRST one will join two tables and the next one will join the third with the resultant of the first joiner.
170] HOW TO EXPORT MAPPINGS TO THE PRODUCTION ENVIRONMENT?
Answer: In the designer go to the main menu and one can see the export/import options. Import the exported mapping in to the production repository with replace options.
171] HOW TO RUN SCD1 BEC IT CREATE TWO TARGET TABLES IN MAPPING WINDOW AND THERE ARE ONLY ONE TABLE IN WAREHOUSE DESIGNER(MEANS TARGET).. SO IF WE CREATE ONE NEW TABLE IN TARGET IT GIVES ERROR..
Answer: If so, create the target with the name u have given in wizard for target(table). No't create the target again for the second instance. It is just the virtual copy of the same target. i.e in warehouse
172] WHAT ARE THE DIFFERENT TYPES OF TRANSFORMATION AVAILABLE IN INFORMATICA. AND WHAT ARE THE MOSTLY USED ONES AMONG THEM
Answer: Mainly there are two types of tranformation.1]Active TransformationAn active transformation can change the number of rows that pass through it from source to target i.e it eliminates rows that do not
173] HOW TO GENERATE THE METADATA REPORTS IN INFORMATICA?
Answer: You can generate PowerCenter Metadata Reporter from a browser on any workstation, even a workstation that does not have PowerCenter tools installed.
174] IN MY SOURCE TABLE 1000 REC'S R THERE.I WANT TO LOAD 501 REC TO 1000 REC INTO MY TARGET TABLE ? HOW CAN U DO THIS ?
Answer: You can overide the sql Query in Wofkflow Manager. LIke select * from tab_name where rownum<=1000 minus select * from tab_name where rownum<=500; This will work fine. Try it and get back t
175] WHAT IS SURROGATEKEY ? IN UR PROJECT IN WHICH SITUATION U HAS USED ? EXPLAIN WITH EXAMPLE ?
Answer: A surrogate key is system genrated/artificial key /sequence number or A surrogate key is a substitution for the natural primary key.It is just a unique identifier or number for each row that can be us
176] IF THE WORKFLOW HAS 5 SESSION AND RUNNING SEQUENTIALLY AND 3RD SESSION HAS BEEN FAILED HOW CAN WE RUN AGAIN FROM ONLY 3RD TO 5TH SESSION?
Answer: If multiple sessions in a concurrent batch fail, you might want to truncate all targets and run the batch again. However, if a session in a concurrent batch fails and the rest of the sessions complete
177] HOW TO LOAD THE DATA FROM PEOPLE SOFT HRM TO PEOPLE SOFT ERM USING INFORMATICA?
Answer: Following are necessary 1.Power Connect license 2.Import the source and target from people soft using ODBC connections 3.Define connection under "Application Connection Browser" for the peop
178] WHAT ARE THE PROPERTIES SHOULD BE NOTIFIED WHEN WE CONNECT THE FLAT FILE SOURCE DEFINITION TO RELATIONAL DATABASE TARGET DEFINITION?
Answer: 1.File is fixed width or delimited 2.Size of the file. If its can be executed without performance issues then normal load will work If its huge in GB they NWAY partitions can be specified at
179] WHAT IS THE TARGET LOAD ORDER?
Answer: A target load order group is the collection of source qualifiers, transformations, and targets linked together in a mapping.
180] WHAT ARE AGGREGATE TRANSFORMATION?
Answer: Aggregator transformation allows you to perform aggregate calculations, such as averages and sums.
181] WHAT ARE TARGET TYPES ON THE SERVER?
Answer: Target Types are File, Relational and ERP.
182] WHAT IS THE DEFAULT SOURCE OPTION FOR UPDATE STRATGEY TRANSFORMATION?
Answer: DATA DRIVEN
183] WHAT R THE OPTIONS IN THE TARGET SESSION OF UPDATE STRATEGY TRANSSFORMATIOIN?
Answer: Update as Insert: This option specified all the update records from source to be flagged as inserts in the target. In other words, instead of updating the records in the target they are inserted as
184] WHAT R THE TYPES OF MAPING IN GETTING STARTED WIZARD?
Answer: 1. Simple Pass through 2. Slowly Growing Target
185] WHAT R THE DIFFERENT TYPES OF TYPE2 DIMENSION MAPING?
Answer: Type2 1. Version number 2. Flag 3.Date
186] IF I DONE ANY MODIFICATIONS FOR MY TABLE IN BACK END DOES IT REFLECT IN INFORMATCA WAREHOUSE OR MAPING DESGINER OR SOURCE ANALYZER?
Answer: NO. Informatica is not at all concern with back end data base.It displays u all the information that is to be stored in repository.If want to reflect back end changes to informatica screens, again u h
187] HOW CAN U RECOGNISE WHETHER OR NOT THE NEWLY ADDED ROWS IN THE SOURCE R GETS INSERT IN THE TARGET ?
Answer: If it is Type 2 Dimension the abouve answer is fine, but if u want to get the info of all the insert statements and Updates you need to use session log file where you configure it to verbose. You
188] WHAT R THE CIRCUMSTANCES THAT INFROMATICA SERVER RESULTS AN UNRECIVERABLE SESSION?
Answer: The source qualifier transformation does not use sorted ports. If u change the partition information after the initial session fails. Perform recovery is disabled in the informatica server configura
189] CAN U GENERATE REPORTS IN INFORMATCIA?
Answer: It is a ETL tool, you could not make reports from here, but you can generate metadata report, that is not going to be used for business analysis
190] HOW CAN U RECOVER THE SESSION IN SEQUENTIAL BATCHES?
Answer: If you configure a session in a sequential batch to stop on failure, you can run recovery starting with the failed session. The Informatica Server completes the session and then runs the rest of the
191] HOW TO RECOVER THE STANDALONE SESSION?
Answer: A standalone session is a session that is not nested in a batch. If a standalone session fails, you can run recovery using a menu command or pmcmd. These options are not available for batched session
192] CAN U COPY THE SESSION TO A DIFFERENT FOLDER OR REPOSITORY?
Answer: In addition, you can copy the workflow from the Repository manager. This will automatically copy the mapping, associated source,targets and session to the target folder.
193] WHAT IS DIFFERENCE BETWEEN STORED PROCEDURE TRANSFORMATION AND EXTERNAL PROCEDURE TRANSFORMATION?
Answer: In case of stored procedure transformation procedure will be compiled and executed in a relational data source. U need data base connection to import the stored procedure in to u r maping. Where as i
194] WHAT IS TRACING LEVEL AND WHAT R THE TYPES OF TRACING LEVEL?
Answer: Tracing level represents the amount of information that informatcia server writes in a log file. Types of tracing level:- Normal Verbose Verbose init Verbose data
195] WHAT IS POWER CENTER REPOSITORY?
Answer: The PowerCenter repository allows you to share metadata across repositories to create a data mart domain. In a data mart domain, yoYou can create a single global repository to store metadata used acr
196] AFTER DRAGING THE PORTS OF THREE SOURCES(SQL SERVER,ORACLE,INFORMIX) TO A SINGLE SOURCE QUALIFIER, CAN U MAP THESE THREE PORTS DIRECTLY TO TARGET?
Answer: if u drag three hetrogenous sources and populated to target without any join means you are entertaining Carteisn product. If you don't use join means not only diffrent sources but homegeous sources ar
197] WHAT IS DATA CLEANSING..?
Answer: Data cleansing is a two step process including DETECTION and then CORRECTION of errors in a data set.
198] TO PROVIDE SUPPORT FOR MAINFRAMES SOURCE DATA,WHICH FILES R USED AS A SOURCE DEFINITIONS?
Answer: COBOL Copy-book files
199] WHERE SHOULD U PLACE THE FLAT FILE TO IMPORT THE FLAT FILE DEFINITION TO THE DESIGNER?
Answer: There is no such restriction to place the source file. In performance point of view its better to place the file in server local src folder. if you need path please check the server properties availab
200] WHAT ARE THE MAPPING PARAMATERS AND MAPING VARIABLES?
Answer: Maping 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 the maping parameter ,
201] WHAT ARE THE REUSABLE TRANSFORAMTIONS?
Answer: Reusable transformations can be used in multiple mappings.When you need to incorporate this transformation into maping,U add an instance of it to maping.Later if you change the definition of the trans
202] WHAT ARE THE ACTIVE AND PASSIVE TRANSFORAMTIONS?
Answer: An active transforamtion can change the number of rows that pass through it.A passive transformation does not change the number of rows that pass through it.
203] THE DESIGNER INCLUDES A "FIND" SEARCH TOOL AS PART OF THE STANDARD TOOL BAR. WHAT CAN IT BE USED TO FIND?
Answer: This is used for finding source columns in workspace.
204] CAN U USE THE MAPING PARAMETERS OR VARIABLES CREATED IN ONE MAPING INTO ANOTHER MAPING?
Answer: NO. You might want to use a workflow parameter/variable if you want it to be visible with other mappings/sessions
205] WHICH TRANSFORMATION SHOULD YOU NEED WHILE USING THE COBOL SOURCES AS SOURCE DEFINITIONS?
Answer: Normalizer transformation which is used to normalize the data. Since cobol sources are oftenly consists of Denormailzed data.
206] WHAT R THE SETTINGS THAT U USE TO COFIGURE THE JOINER TRANSFORMATION?
Answer: Master and detail source Type of join Condition of the join the Joiner transformation supports the following join types, which you set in the Properties tab: Normal (Default) Master Outer
207] WHERE SHOULD YOU PLACE THE FLAT FILE TO IMPORT THE FLAT FILE DEFINITION TO THE DESIGNER?
Answer: Place it in local folder.
208] WHAT IS THE LOOK UP TRANSFORMATION?
Answer: Using it we can access the data from a relational table which is not a source in the mapping. For Ex:Suppose the source contains only Empno, but we want Empname also in the mapping.Then instead of
209] DIFFERENCES BETWEEN CONNECTED AND UNCONNECTED LOOKUP?
Answer: Connected lookup Unconnected lookup Receives input values diectly from the pipe line. Receives input values from the result of a lkp expression in a another transformation. U can use a dynamic
210] WHAT IS DATA TRANSFORMATION MANAGER?
Answer: A Router transformation is similar to a Filter transformation because both transformations allow you to use a condition to test data. A Filter transformation tests data for one condition and drops the
211] WHAT IS THE ROUTER TRANSFORMATION?
Answer: A Router transformation is similar to a Filter transformation because both transformations allow you to use a condition to test data. A Filter transformation tests data for one condition and drops the
212] WHY WE USE STORED PROCEDURE TRANSFORMATION?
Answer: A Stored Procedure transformation is an important tool for populating and maintaining databases. Database administrators create stored procedures to automate time-consuming tasks that are too complica
213] IF YOU ARE WORKFLOW IS RUNNING SLOW IN INFORMATICA. WHERE DO YOU START TROUBLE SHOOTING AND WHAT ARE THE STEPS YOU FOLLOW?
Answer: When the work flow is running slowly u have to find out the bottlenecks in this order target source mapping session system
214] WHAT DOES THE EXPRESSION N FILTER TRANSFORMATIONS DO IN INFORMATICA SLOWLY GROWING TARGET WIZARD?
Answer: EXPESSION transformation detects and flags the rows from source. Filter transformation filters the rows that are not flagged and passes the flagged rows to the Update strategy transformation
215] HOW THE INFORMATICA SERVER INCREASES THE SESSION PERFORMANCE THROUGH PARTITIONING THE SOURCE?
Answer: For a relational sources informatica server creates multiple connections for each parttion of a single source and extracts seperate range of data for each connection.Informatica server reads multiple
216] WHAT R THE TYPES OF METADATA THAT STORES IN REPOSITORY?
Answer: Source definitions. Definitions of database objects (tables, views, synonyms) or files that provide source data. Target definitions. Definitions of database objects or files that contain the target
217] WHAT IS THE DIFF B/W STORED PROC (DB LEVEL) & STORED PROC TRANS (INFORMATICA LEVEL) ? AGAIN WHY SHOULD WE USE SP TRANS ?
Answer: First of all stored procedures (at DB level) are series of SQL statement. And those are stored and compiled at the server side.In the Informatica it is a transformation that uses same stored procedure
218] HOW CAN WE JOIN THE TABLES IF THE TABLES HAVE NO PRIMARY AND FORIEN KEY RELATION AND NO MATCHIG PORT TO JOIN?
Answer: without common column or common data type we can join two sources using dummy ports. 1.Add one dummy port in two sources. 2.In the expression trans assing '1' to each port. 2.Use Joiner trans
219] IN THE CONCEPT OF MAPPING PARAMETERS AND VARIABLES, THE VARIABLE VALUE WILL BE SAVED TO THE REPOSITORY AFTER THE COMPLETION OF THE SESSION AND THE NEXT TIME WHEN U RUN THE SESSION, THE SERVER TAKES THE SAVED VARIABLE VALUE IN THE REPOSITORY AND STARTS ASSIGNING THE NEXT VALUE OF THE SAVED VALUE. FOR EXAMPLE I RAN A SESSION AND IN THE END IT STORED A VALUE OF 50 TO THE REPOSITORY.NEXT TIME WHEN I RUN THE SESSION, IT SHOULD START WITH THE VALUE OF 70. NOT WITH THE VALUE OF 51. HOW TO DO THIS.
Answer: u can do onething after running the mapping,, in workflow manager start-------->session. right clickon the session u will get a menu, in that go for persistant values, there u will find the last
220] WHAT IS POLLING?
Answer: It displays the updated information about the session in the monitor window. The monitor window displays the status of each session when you poll the informatica server.
221] WHY YOU USE REPOSITORY CONNECTIVITY?
Answer: When you edit,schedule the sesion each time,informatica server directly communicates the repository to check whether or not the session and users are valid.All the metadata of sessions and mappings wi
222] WHAT IS CODE PAGE USED FOR?
Answer: Code Page is used to identify characters that might be in different languages. If you are importing Japanese data into mapping, you must select the Japanese code page of source data.
223] WHAT ARE VARIOUS TYPES OF AGGREGATION?
Answer: Various types of aggregation are SUM, AVG, COUNT, MAX, MIN, FIRST, LAST, MEDIAN, PERCENTILE, STDDEV, and VARIANCE.
224] WHAT ARE THE SCHEDULING OPTIONS TO RUN A SESION?
Answer: A session can be scheduled to run at a given time or intervel,or u can manually run the session. Different options of scheduling Run only on demand: server runs the session only when user starts ses
225] WHAT IS CHANGE DATA CAPTURE?
Answer: Change data capture (CDC) is a set of software design patterns used to determine the data that has changed in a database so that action can be taken using the changed data.
226] CAN U TELL ME HOW TO GO FOR SCD'S AND ITS TYPES.WHERE DO WE USE THEM MOSTLY
Answer: The "Slowly Changing Dimension" problem is a common one particular to data warehousing. In a nutshell, this applies to cases where the attribute for a record varies over time. We give an example below
227] WHAT IS MEANT BY EDW?
Answer: EDW is Enterprise Datawarehouse which means that its a centralised DW for the whole organization. This apporach is the apporach on Imon which relies on the point of having a single warehouse/centra
228] IF YOU WANT TO CREATE INDEXES AFTER THE LOAD PROCESS WHICH TRANSFORMATION YOU CHOOSE?
Answer: Its usually not done in the mapping(transformation) level. Its done in session level. Create a command task which will execute a shell script (if Unix) or any other scripts which contains the create i
229] HOW I CAN DO INCREMENTAL AGGREGATION IN REAL TIME?
Answer: For incremental Aggregation.. We need to use Aggregations + Look up on Target + Expression to SUM up Count obtained from New Aggregations and Lookup on target. For one record already present in Aggre
230] WHAT IS WORKLET AND WHAT USE OF WORKLET AND IN WHICH SITUATION WE CAN USE IT
Answer: A set of worlflow tasks is called worklet, Workflow tasks means 1)timer2)decesion3)command4)eventwait5)eventrise6)mail etc...... But we r use diffrent situations by using this only
231] CAN YOU COPY THE BATCHES?
Answer: NO.
232] WHAT R THE JOINER CACHES?
Answer: Specifies the directory used to cache master records and the index to these records. By default, the cached files are created in a directory specified by the server variable $PMCacheDir. If you overri
233] CAN WE USE AGGREGATOR/ACTIVE TRANSFORMATION AFTER UPDATE STRATEGY TRANSFORMATION
Answer: You can use aggregator after update strategy. The problem will be, once you perform the update strategy, say you had flagged some rows to be deleted and you had performed aggregator transformation for
234] WHAT IS DIFFERENCE BETWEEN DIMENTION TABLE AND FACT TABLE AND WHAT ARE DIFFERENT DIMENTION TABLES AND FACT TABLES
Answer: In the fact table contain measurable data and less columns and meny rows, It's contain primarykey Diffrent types of fact tables: additive,non additive, semi additive In the dimensions tabl
235] WHAT IS DATA MERGING, DATA CLEANSING, SAMPLING?
Answer: Data merging: It is a process of combining the data with similar structures in to a single output. Data Cleansing: It is a process of identifying and rectifying the inconsistent and inaccurate data
236] WHAT IS IQD FILE?
Answer: IQD file is nothing but Impromptu Query Definetion,This file is maily used in Cognos Impromptu tool after creating a imr( report) we save the imr as IQD file which is used while creating a cube in pow
237] DESCRIBE TWO LEVELS IN WHICH UPDATE STRATEGY TRANSFORMATION SETS?
Answer: Within a session: When you configure a session, yoYou can instruct the Informatica Server to either treat all records in the same way (for example, treat all records as inserts), or use instructions c
238] WHAT ARE THE TASKS THAT SOURCE QUALIFIER PERFORMS?
Answer: Join data originating from same source data base. Filter records when the informatica server reads source data. Specify an outer join rather than the default inner join specify sorted records. Sele
239] WHICH TRANSFORMATION SHOULD WE USE TO NORMALIZE THE COBOL AND RELATIONAL SOURCES?
Answer: The Normalizer transformation normalizes records from COBOL and relational sources, allowing you to organize the data according to your own needs. A Normalizer transformation can appear anywhere in a
240] WHEN WE CREATE A TARGET AS FLAT FILE AND SOURCE AS ORACLE.. HOW CAN I SPECIFY FIRST ROWS AS COLUMN NAMES IN FLAT FILES...
Answer: Use a pre sql statement....but this is a hardcoding method...if you change the column names or put in extra columns in the flat file, you will have to change the insert statement
241] WHY IS MEANT BY DIRECT AND INDIRECT LOADING OPTIONS IN SESSIONS?
Answer: Direct loading can be used to Single transformation where as indirect transformation can be used to multiple transformations or files In the direct we can perform recovery process but in Indirect
242] IN A JOINER TRANSFORMATION, YOU SHOULD SPECIFY THE SOURCE WITH FEWER ROWS AS THE MASTER SOURCE. WHY?
Answer: Joiner transformation compares each row of the master source against the detail source. The fewer unique rows in the master, the fewer iterations of the join comparison occur, which speeds the join pr
243] WHAT IS THE EXACT MEANING OF DOMAIN?
Answer: The PowerCenter domain is the fundamental administrative unit in PowerCenter. The domain supports the administration of the distributed services. A domain is a collection of nodes and services that yo
244] I HAVE AN REQUIREMENT WHERE IN THE COLUMNS NAMES IN A TABLE (TABLE A) SHOULD APPEAR IN ROWS OF TARGET TABLE (TABLE B) I.E. CONVERTING COLUMNS TO ROWS. IS IT POSSIBLE THROUGH INFORMATICA? IF SO, HOW?
Answer: if data in tables as follows Table A Key-1 char(3); table A values _______ 1 2 3 Table B bkey-a char(3); bcode char(1); table b values 1 T 1 A 1 G 2 A 2 T 2 L 3 A and output re
245] WHAT ARE COST BASED AND RULE BASED APPROACHES AND THE DIFFERENCE
Answer: Cost based and rule based approaches are the optimization techniques which are used in related to databases, where we need to optimize a sql query. Basically Oracle provides Two types of Optimizer
246] CAN YOU COPY THE SESSION TO A DIFFERENT FOLDER OR REPOSITORY?
Answer: Yes. By using copy session wizard You can copy a session in a different folder or repository. But that target folder or repository should consists of mapping of that session. If target folder or repo
247] DIFFERENCES BETWEEN INFORMATICA 6.2 AND INFORMATICA 7.0 YOURS SINCERELY
Answer: Features in 7.1 are : 1.union and custom transformation 2.lookup on flat file 3.grid servers working on different operating systems can coexist on same server 4.we can use pmcmdrep 5.we
248] WHAT IS MEANT BY LOOKUP CACHES?
Answer: The informatica server builds a cache in memory when it processes the first row af a data in a cached look up transformation.It allocates memory for the cache based on the amount you configure in the
249] HOW MANY WAYS YOU CAN UPDATE A RELATIONAL SOURCE DEFINTION AND WHAT R THEY?
Answer: Two ways 1. Edit the definition 2. Reimport the defintion
250] HOW MANY WAYS YOU CREATE PORTS?
Answer: Two ways:- 1.Drag the port from another transforamtion 2.Click the add buttion on the ports tab.
251] WHAT R THE METHODS FOR CREATING REUSABLE TRANSFORAMTIONS?
Answer: A transformation can reused, that is known as reusable transformation You can design using 2 methods using transformation developer create normal one and promote it to reusable
252] HOW MANY WAYS YOU CAN UPDATE A RELATIONAL SOURCE DEFINITION AND WHAT ARE THEY?
Answer: joinercondition exciuted with in the infoematica staging layer source qualifier condition exciuted with in the database level
253] WHAT IS SESSION AND BATCHES?
Answer: Session - A Session Is A set of instructions that tells the Informatica Server How And When To Move Data From Sources To Targets. After creating the session, we can use either the server manager or t
254] WHAT R THE TASKS THAT SOURCE QUALIFIER PERFORMS?
Answer: Join data originating from the same source database. You can join two or more tables with primary-foreign key relationships by linking the sources to one Source Qualifier. Filter records when the In
255] WHAT IS A VIEW? HOW IT IS RELATED TO DATA INDEPENDENCE? AND WHAT ARE THE DIFFERENT TYPES OF VIEWS, AND WHAT IS MATERIALIZE VIEW
Answer: views view is a combination of one or more table.view does not stores the data,it just store the query in file format.If we excutes the query the query will fetch the data from the tables and just
256] WHAT IS MEANT BY JUNK ATTRIBUTE IN INFORMATICA?
Answer: Junk Dimension A Dimension is called junk dimension if it contains attribute which are rarely changed ormodified. example In Banking Domain , we can fetch four attributes accounting to a junk dimensio
257] WHAT IS LOOKUP TRANSFORMATION AND UPDATE STRATEGY TRANSFORMATION AND EXPLAIN WITH AN EXAMPLE.
Answer: Look up transformation is used to lookup the data in a relationa table,view,Synonym and Flat file. The informatica server queries the lookup table based on the lookup ports used in the transformati
258] CAN I USE A SESSION BULK LOADING OPTIONS THAT TIME CAN I MAKE A RECOVERY TO THE SESSION?
Answer: If the session is configured to use in bulk mode it will not write recovery information to recovery tables. So Bulk loading will not perform the recovery as required.
259] WHAT IS THE BEST WAY TO SHOW METADATA (NUMBER OF ROWS AT SOURCE, TARGET AND EACH TRANSFORMATION LEVEL, ERROR RELATED DATA) IN A REPORT FORMAT?
Answer: You can select these details from the repository table. you can use the view REP_SESS_LOG to get these data
260] HOW TO JOIN TWO TABLES WITHOUT USING THE JOINER TRANSFORMATION.
Answer: It’s possible to join the two or more tables by using source qualifier.But provided the tables should have relationship. When u drag n drop the tables u will getting the source qualifier for each t
261] WHAT IS INCREMANTAL AGGREGATION?
Answer: When using incremental aggregation, you apply captured changes in the source to aggregate calculations in a session. If the source changes only incrementally and you can capture changes, you can conf
262] WHAT IS TRACING LEVEL?
Answer: Ya its the level of information storage in session log. The option comes in the properties tab of transformations. By default it remains "Normal". Can be Verbose Initialisation Verbose Data
263] HOW CAN WE ELIMINATE DUPLICATE ROWS FROM FLAT FILE?
Answer: Use Sorter Transformation. When you configure the Sorter Transformation to treat output rows as distinct, it configures all ports as part of the sort key. It therefore discards duplicate rows compared
264] WHAT ARE THE MEASURE OBJECTS
Answer: Use Sorter Transformation. When you configure the Sorter Transformation to treat output rows as distinct, it configures all ports as part of the sort key. It therefore discards duplicate rows compared
265] WHAT IS RANK TRANSFORMATION?WHERE CAN WE USE THIS TRANSFORMATION?
Answer: Rank transformation is used to find the status.ex if we have one sales table and in this if we find more employees selling the same product and we are in need to find the first 5 0r 10 employee who is
266] HOW TO GET TWO TARGETS T1 CONTAINING DISTINCT VALUES AND T2 CONTAINING DUPLICATE VALUES FROM ONE SOURCE S1.
Answer: Use filter transformation for loading the target with no duplicates. and for the other transformation load it directly from source.
267] CAN ANYBODY WRITE A SESSION PARAMETER FILE WHICH WILL CHANGE THE SOURCE AND TARGETS FOR EVERY SESSION. I.E DIFFERENT SOURCE AND TARGETS FOR EACH SESSION RUN.
Answer: You are supposed to define a parameter file. And then in the Parameter file, you can define two parameters, one for source and one for target. Give like this for example: $Src_file = c:program f
268] WHAT IS THE DIFFERENCE BETWEEN POWER CENTRE AND POWER MART? WHAT IS THE PROCEDURE FOR CREATING INDEPENDENT DATA MARTS FROM INFORMATICA 7.1?
Answer: power center powermart no.of repository n No. n No. aplicability high end WH low&mid range WH global repository supported not supported local repository supported supported ERP support
269] WHAT IS DIFFERENCE BETWEEN PARTIONING OF RELATONAL TARGET AND PARTITIONING OF FILE TARGETS?
Answer: If u parttion a session with a relational target informatica server creates multiple connections to the target database to write target data concurently.If u partition a session with a file target the
270] HOW TO DELETE DUPLICATE ROWS IN FLAT FILES SOURCE IS ANY OPTION IN INFORMATICA
Answer: Use a sorter transformation , in that u will have a "distinct" option make use of it .
271] IF I GET A FLAT FILE AS SOURCE, I NEED TO GET DISTINCT DATA IN THE TARGET. HOW DO I ACCOMPLISH THIS? PLEASE GIVE A DETAILED ANSWER OF WHAT ALL TRASFORMATIONS WE HAVE TO USE AND HOW THE DATA FLOW WILL BE, AND WHAT SQL STATEMENT WE HAVE TO USE.
Answer: when we have a flat file as a source we need to use the sorter transformation to get the distinct rows in the target we have an option distinct in sorter transformation which is eliminated the dupl
272] WHY WE USE STORED PROCEDURE TRANSFORMATION?
Answer: For populating and maintaining data bases.
273] WHAT IS AGGREGATE CACHE IN AGGREGATOR TRANSFORAMTION?
Answer: The aggregator stores data in the aggregate cache until it completes aggregate calculations.When you run a session that uses an aggregator transformation,the informatica server creates index and data
274] WHAT IS THE DIFF B/W STOP & ABORT IN INFORMATICA SESS LEVEL ?
Answer: Stop:We can Restart the session Abort:WE cant restart the session.We should truncate all the pipeline after that start the session
275] CAN YOU GENERATE REPORTS IN INFORMATCIA?
Answer: Yes. By using Metadata reporter we can generate reports in informatica.
276] HOW THE INFORMATICA SERVER SORTS THE STRING VALUES IN RANKTRANSFORMATION?
Answer: When the informatica server runs in the ASCII data movement mode it sorts session data using Binary sortorder.If you configure the seeion to use a binary sort order,the informatica server caluculates
277] HOW DO WE ANALYSE THE DATA AT DATABASE LEVEL?
Answer: Data can be viewed using Informatica's designer tool. If you want to view the data on source/target we can preview the data but with some limitations. We can use data profiling too.
278] CAN BATCHES BE COPIED/STOPPED FROM SERVER MANAGER?
Answer: Yes, we can stop the batches using server manager or pmcmd commnad
279] IF U HAD TO SPLIT THE SOURCE LEVEL KEY GOING INTO TWO SEPERATE TABLES. ONE AS SURROGATE AND OTHER AS PRIMARY. SINCE INFORMATICA DOES NOT GURANTEE KEYS ARE LOADED PROPERLY(ORDER!) INTO THOSE TABLES. WHAT ARE THE DIFFERENT WAYS YOU COULD HANDLE THIS TYPE OF SITUATION?
Answer: Foreign key
280] HOW TO RETRIEVE THE RECORDS FROM A REJECTED FILE. EXPLAIN WITH SYNTAX OR EXAMPLE
Answer: During the execution of workflow all the rejected rows will be stored in bad files(where your informatica server get installed;C:Program FilesInformatica PowerCenter 7.1Server) These bad files can be
281] DIFFERENCE BETWEEN SUMMARY FILTER AND DETAILS FILTER?
Answer: Summary Filter --- we can apply records group by that contain common values. Detail Filter --- we can apply to each and every record in a database.
282] WHAT IS A JUNK DIMENSION?
Answer: A "junk" dimension is a collection of random transactional codes, flags and/or text attributes that are unrelated to any particular dimension. The junk dimension is simply a structure that provides a
283] WHAT ARE THE ACTIVE AND PASSIVE TRANSFORMATIONS?
Answer: An active transforamtion can change the number of rows that pass through it.A passive transformation does not change the number of rows that pass through it.
284] WHAT R THE MAPPING PARAMETERS AND MAPING VARIABLES?
Answer: Please refer to the documentation for more understanding. Mapping variables have two identities: Start value and Current value Start value = Current value ( when the session starts the execut
285] WHY WE USE LOOKUP TRANSFORMATIONS?
Answer: Lookup Transformations can access data from relational tables that are not sources in mapping. With Lookup transformation, we can acomplish the following tasks:- Get a related value-Get the Employee
286] HOW CAN I GET DISTINCT VALUES WHILE MAPPING IN INFORMATICA IN INSERTION?
Answer: You can add an aggregator before insert and group by the feilds that need to be distinct.
287] WHAT IS PARTITIONING? WHERE WE CAN USE PARTITION? WHAT IS ADVANTAGES? IS IT NECESSARY?
Answer: Partitions are used to optimize the session performance we can select in sesstion propetys for partiotions types default----passthrough partition key range partion round robin partion
288] IN REALTIME WHICH ONE IS BETTER STAR SCHEMA OR SNOWFLAKE STAR SCHEMA THE SURROGATE WILL BE LINKED TO WHICH COLUMNS IN THE DIMENSION TABLE.
Answer: In real time only star schema will implement because it will take less time and surrogate key will there in each and every dimension table in star schema and this surrogate key will assign as foreign
289] HOW CAN YOU DELETE DUPLICATE ROWS WITHOUT USING DYNAMIC LOOKUP? TELL ME ANY OTHER WAYS USING LOOKUP DELETE THE DUPLICATE ROWS?
Answer: For example u have a table Emp_Name and it has two columns Fname, Lname in the source table which has douplicate rows. In the mapping Create Aggregator transformation. Edit the aggregator transformati
290] WHAT IS EXACT USE OF 'ONLINE' AND 'OFFLINE' SERVER CONNECT OPTIONS WHILE DEFINING WORK FLOW IN WORK FLOW MONITOR? . THE SYSTEM HANGS WHEN 'ONLINE' SERVER CONNECT OPTION. THE INFORMATICA IS INSTALLED ON A PERSONAL LAPTOP.
Answer: When the repo is up and the PMSERVER is also up, workflow monitor always will be connected on-line. When PMserver is down and the repo is still up we will be prompted for an off-line connection wit
291] WHAT WILL HAPPEN IF YOU ARE USING UPDATE STRATEGY TRANSFORMATION AND YOUR SESSION IS CONFIGURED FOR "INSERT"? WHAT ARE THE TYPES OF EXTERNAL LOADER AVAILABLE WITH INFORMATICA? IF YOU HAVE RANK INDEX FOR TOP 10. HOWEVER IF YOU PASS ONLY 5 RECORDS, WHAT WILL BE THE OUTPUT OF SUCH A RANK TRANSFORMATION?
Answer: if u r using a update strategy in any of ur mapping, then in session properties u have to set treat source rows as Data Driven. if u select insert or udate or delete, then the info server will not con
292] WHERE IS THE CACHE STORED IN INFORMATICA?
Answer: Cache is stored in the Informatica server memory and over flowed data is stored on the disk in file format which will be automatically deleted after the successful completion of the session run. If yo
293] WHAT HAPPENS IF YOU TRY TO CREATE A SHORTCUT TO A NON-SHARED FOLDER?
Answer: It only creates a copy of it..
294] INFORMATICA LIVE INTERVIEW QUESTIONS
Answer: confirmed dimension == one dimension that shares with two fact table factless means ,fact table without measures only contains foreign keys-two types of factless table ,one is event tracking and ot
295] WHERE DO WE USE MQ SERIES SOURCE QUALIFIER, APPLICATION MULTI GROUP SOURCE QUALIFIER. JUST GIVE AN EXAMPLE FOR A BETTER UNDERSTANDING
Answer: We can use a MQSeries SQ when we have a MQ messaging system as source(queue). When there is need to extract data from a Queue, which will basically have messages in XML format, we will use a JMS or
296] CAN ANYONE EXPLAIN ABOUT INCREMENTAL AGGREGATION WITH AN EXAMPLE?
Answer: Incremental aggregation is specially used for tune the performance of the aggregator. It captures the change each time (incrementally) you run the transformation and then performs the aggregation func
297] ABOUT INFORMATICA POWER CENTER 7: 1) I WANT TO KNOW WHICH MAPPING PROPERTIES CAN BE OVERRIDDEN ON A SESSION TASK LEVEL. 2) KNOW WHAT TYPES OF PERMISSIONS ARE NEEDED TO RUN AND SCHEDULE WORK FLOWS.
Answer: 1.(Ans) You can override any properties other than the source and targets. Make sure the source and targets exists in ur db if it is a relational db. If it is a flat file, you can override its propert
298] CAN ANY ONE EXPLAIN REAL TIME COMPLAIN MAPPINGS OR COMPLEX TRANSFORMATIONS IN INFORMATICA. SPECIALLY IN SALES DOMAIN.
Answer: Most complex logic we use is denormalization. We dont have any Denormalizer transformation in INformatica. So we will have to use an aggregator followed by an expression. Apart from this, we use most
299] WHAT ARE THE ENHANCEMENTS MADE TO INFORMATICA 7.1.1 VERSION WHEN COMPARED TO 6.2.2 VERSION?
Answer: 1.union & custom transformation 2.lookup on flatfile 3.we can use pmcmd command 4.we can export independent&dependent repository objects 5.version controlling 6.data proffiling 7.sup
300] WHAT IS MEANT BY COMPLEX MAPPING?
Answer: Complex maping means involved in more logic and more business rules. Actually in my project complex mapping is In my bank project, I involved in construct a 1 dataware house Meny customer is
301] IF A SESSION FAILS AFTER LOADING OF 10,000 RECORDS IN TO THE TARGET.HOW CAN U LOAD THE RECORDS FROM 10001 TH RECORD WHEN U RUN THE SESSION NEXT TIME IN INFORMATICA 6.1?
Answer: Running the session in recovery mode will work, but the target load type should be normal. If its bulk then recovery wont work as expected
302] CAN I START AND STOP SINGLE SESSION IN CONCURENT BSTCH?
Answer: ya shoor,Just right click on the particular session and going to recovery option or by using event wait and event rise
303] WHAT IS MICRO STRATEGY? WHY IS IT USED FOR? CAN ANY ONE EXPLAIN IN DETAIL ABOUT IT?
Answer: Micro strategy is again an BI tool which is a HOLAP... u can create 2 dimensional report and also cubes in here.......basically a reporting tool. IT HAS A FULL RANGE OF REPORTING ON WEB ALSO IN WINDOW
304] WHAT ARE PARTITION POINTS?
Answer: Partition points mark the thread boundaries in a pipeline and divide the pipeline into stages. The Informatica Server sets partition points at several transformations in a pipeline by default. If yo
305] HOW TO APPEND THE RECORDS IN FLAT FILE(INFORMATICA) ? WHERE AS IN DATASTAGE WE HAVE THE OPTIONS I) OVERWRITE THE EXISTING FILE II) APPEND EXISTING FILE
Answer: This is not there in Informatica v 7. but heard that its included in the latest version 8.0 where u can append to a flat file. Its about to be shipping in the market.
306] TWO RELATIONAL TABLES ARE CONNECTED TO SQ TRANS,WHAT ARE THE POSSIBLE ERRORS IT WILL BE THROWN?
Answer: The only two possibilities as of I know is Both the table should have primary key/foreign key relation ship Both the table should be available in the same schema or same database
307] WHAT ARE THE STEPS REQUIRED FOR TYPE2 DIMENSION/VERSION DATA MAPPING. HOW CAN WE IMPLEMENT IT
Answer: 1. Determine if the incoming row is 1) a new record 2) an updated record or 3) a record that already exists in the table using two lookup transformations. Split the mapping into 3 seperate flows using
308] WHAT ARE THE TRANSFORMATIONS THAT RESTRICTS THE PARTITIONING OF SESSIONS?
Answer: Advanced External procedure tranformation and External procedure transformation: This transformation contains a check box on the properties tab to allow partitioning. Aggregator Transformation: If u
309] HOW CAN YOU ACCESS THE REMOTE SOURCE INTO YOUR SESSION?
Answer: Relational source: To acess relational source which is situated in a remote place ,u need to configure database connection to the datasource. FileSource : To access the remote source file you must co
310] WHAT IS THE PROCEDURE OR STEPS IMPLEMENTING VERSIONING IF YOU ARE ALREADY IN VERSION7.X. ANY GOTCHA'S OR PRECAUTIONS..
Answer: For version control in ETL layer using informatica, first of all after doing anything in your designer mode or workflow manager, do the following steps..... 1> First save the changes or new impleme
311] HOW CAN YOU STOP A BATCH?
Answer: By using server manager or pmcmd.
312] WHAT IS A COMMAND THAT USED TO RUN A BATCH?
Answer: pmcmd is used to start a batch.
313] WHAT IS DIMENSION TABLE EXACTLY?
Answer: Dimension tables gives description about something. for eg. If we take Student as a dimention table, we have various attributes like college name, age, gender,etc which gives some description about
314] WHEN THE INFORMATICA SERVER MARKS THAT A BATCH IS FAILED?
Answer: If one of session is configured to "run if previous completes" and that previous session fails.
315] WHY DIMENSTION TABLES ARE DENORMALIZED IN NATURE ?
Answer: Because in Data warehousing historical data should be maintained, to maintain historical data means suppose one employee details like where previously he worked, and now where he is working, all detai
316] HOW MANY NUMBER OF SESSIONS THAT YOU CAN CREATE IN A BATCH?
Answer: It depends on the config settings of informatica server. The parameters for the maximum connections cant be exceeded. It depends on the overall sessions running per the server at a time. For eg, if th
317] CAN ANY ONE COMMENT ON SIGNIFICANCE OF ORACLE 9I IN INFORMATICA WHEN COMPARED TO ORACLE 8 OR 8I. I MEAN HOW IS ORACLE 9I ADVANTAGEOUS WHEN COMPARED TO ORACLE 8 OR 8I WHEN USED IN INFORMATICA
Answer: it's very easy Actually oracle 8i not allowed userdefined data types but 9i allows and then blob,clob allow only 9i not 8i and more over list partinition is there in 9i only
318] WHAT IS BATCH AND DESCRIBE ABOUT TYPES OF BATCHES?
Answer: Grouping of session is known as batch. Batches are two types:- Sequential: Runs sessions one after the other Concurrent: Runs session at same time. If you have sessions with source-target dependenc
319] HOW TO USE MAPPING PARAMETERS AND WHAT IS THEIR USE
Answer: In designer u will find the mapping parameters and variables options.u can assign a value to them in designer. comming to there uses suppose u r doing incremental extractions daily. suppose ur source
320] WHAT ARE THE DATA MOVEMENT MODES IN INFORMATCIA?
Answer: Datamovement modes determines how informatcia server handles the charector data.U choose the datamovement in the informatica server configuration settings. Two types of datamovement modes avialable i
321] HOW TO LOOKUP THE DATA ON MULTIPLE TABELS.
Answer: if the two tables are relational, then u can use the SQL lookup over ride option to join the two tables in the lookup properties.u cannot join a flat file and a relatioanl table. eg: lookup default
322] WHAT IS DTM PROCESS?
Answer: DTM means data transformation manager.in informatica this is main back ground process.it run after complition of load manager.in this process informatica server search source and tgt connection in rep
323] HOW DOES THE SERVER RECOGNISE THE SOURCE AND TARGET DATABASES?
Answer: By using ODBC connection.if it is relational.if is flat file FTP connection..see we can make sure with connection in the properties of session both sources && targets.
324] WHAT IS THE LIMIT TO THE NUMBER OF SOURCES AND TARGETS YOU CAN HAVE IN A MAPPING
Answer: There is no such restriction to use this number of sources or targets inside a mapping.
325] WHICH OBJECTS ARE REQUIRED BY THE DEBUGGER TO CREATE A VALID DEBUG SESSION?
Answer: Intially the session should be valid session. source, target, lookups, expressions should be availble, min 1 break point should be available for debugger to debug your session.
326] DEFINE MAPING AND SESSIONS?
Answer: Maping: It is a set of source and target definitions linked by transformation objects that define the rules for transformation. Session : It is a set of instructions that describe how and when to mov
327] WHAT IS THE PROCEDURE TO WRITE THE QUERY TO LIST THE HIGHEST SALARY OF THREE EMPLOYEES?
Answer: The following is the query to find out the top three salaries in ORACLE:--(take emp table) select * from emp e where 3>(select count (*) from emp where e.sal>emp.sal) order by sal desc. i
328] WE ARE USING UPDATE STRATEGY TRANSFORMATION IN MAPPING HOW CAN WE KNOW WHETHER INSERT OR UPDATE OR REJECT OR DELETE OPTION HAS BEEN SELECTED DURING RUNNING OF SESSIONS IN INFORMATICA.
Answer: In Designer while creating Update Strategy Transformation uncheck "forward to next transformation". If any rejected rows are there automatically it will be updated to the session log file. Update
329] SUPPOSE SESSION IS CONFIGURED WITH COMMIT INTERVAL OF 10,000 ROWS AND SOURCE HAS 50,000 ROWS. EXPLAIN THE COMMIT POINTS FOR SOURCE BASED COMMIT AND TARGET BASED COMMIT. ASSUME APPROPRIATE VALUE WHEREVER REQUIRED.
Answer: Source based commit will commit the data into target based on commit interval.so,for every 10,000 rows it will commit into target. Target based commit will commit the data into target based on buff
330] HOW DO WE ESTIMATE THE NUMBER OF PARTITONS THAT A MAPPING REALLY REQUIRES? IS IT DEPENDENT ON THE MACHINE CONFIGURATION?
Answer: It depends upon the informatica version we r using. suppose if we r using informatica 6 it supports only 32 partitions where as informatica 7 supports 64 partitions.
331] HOW DO WE ESTIMATE THE DEPTH OF THE SESSION SCHEDULING QUEUE? WHERE DO WE SET THE NUMBER OF MAXIMUM CONCURRENT SESSIONS THAT INFORMATICA CAN RUN AT A GIVEN TIME?
Answer: U set the max no of concurrent sessions in the info server. By default its 10. u can set to any no.
332] HOW DO YOU DECIDE WHETHER YOU NEED TO DO AGGREGATIONS AT DATABASE LEVEL OR AT INFORMATICA LEVEL?
Answer: It depends upon our requirment only.If you have good processing database you can create aggregation table or view at database level else its better to use informatica. Here i'm explaing why we need to
333] WHAT ARE THE DIFFERENT TYPES OF TYPE2 DIMENSION MAPING?
Answer: Type2 Dimension/Version Data Maping: In this maping the updated dimension in the source will gets inserted in target along with a new version number. Newly added dimension in source will inserted int
334] CAN INFORMATICA BE USED AS A CLEANSING TOOL? IF YES, GIVE EXAMPLE OF TRANSFORMATIONS THAT CAN IMPLEMENT A DATA CLEANSING ROUTINE.
Answer: Yes, we can use Informatica for cleansing data. some time we use stages to cleansing the data. It depends upon performance again else we can use expression to cleasing data. For example an feild X
335] BRIEFLY EXPLAIN THE VERSIONING CONCEPT IN POWER CENTER 7.1.
Answer: When you create a version of a folder referenced by shortcuts, all shortcuts continue to reference their original object in the original version. They do not automatically update to the current folder
336] IN A FILTER EXPRESSION WE WANT TO COMPARE ONE DATE FIELD WITH A DB2 SYSTEM FIELD CURRENT DATE. OUR SYNTAX: DATEFIELD = CURRENT DATE (WE DIDN'T DEFINE IT BY PORTS, ITS A SYSTEM FIELD ), BUT THIS IS NOT VALID (PMPARSER: MISSING OPERATOR).. CAN SOMEONE HELP US. THANKS
Answer: The db2 date formate is "yyyymmdd" where as sysdate in oracle will give "dd-mm-yy" so conversion of db2 date formate to local database date formate is compulsary. other wise u will get that type of er
337] HOW TO CREATE THE STAGING AREA IN YOUR DATABASE
Answer: A Staging area in a DW is used as a temporary space to hold all the records from the source system. So more or less it should be exact replica of the source systems except for the laod startegy where
338] WHATS THE DIFF BETWEEN INFORMATICA POWERCENTER SERVER, REPOSITORYSERVER AND REPOSITORY?
Answer: Repository is a database in which all informatica componets are stored in the form of tables. The reposiitory server controls the repository and maintains the data integrity and Consistency across the
339] WHAT ARE THE DIFFERENCES BETWEEN INFORMATICA POWER CENTER VERSIONS 6.2 AND 7.1, ALSO BETWEEN VERSIONS 6.2 AND 5.1?
Answer: The main difference between informatica 5.1 and 6.1 is that in 6.1 they introduce a new thing called repository server and in place of server manager(5.1), they introduce workflow manager and workflow
340] DISCUSS WHICH IS BETTER AMONG INCREMENTAL LOAD, NORMAL LOAD AND BULK LOAD
Answer: 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. According to performance bulk is better than
341] COMPARE DATA WAREHOUSING TOP-DOWN APPROACH WITH BOTTOM-UP APPROACH
Answer: Top down ODS-->ETL-->Datawarehouse-->Datamart-->OLAP Bottom up ODS-->ETL-->Datamart-->Datawarehouse-->OLAP
342] WHAT ARE THE TYPES OF GROUPS IN ROUTER TRANSFORMATION?
Answer: Input group Output group The designer copies property information from the input ports of the input group to create a set of output ports for each output group. Two types of output groups:- User de
343] HOW TO GET THE FIRST 100 ROWS FROM THE FLAT FILE INTO THE TARGET?
Answer: 1. Use test download option if you want to use it for testing. 2. Put counter/sequence generator in mapping and perform it.
344] CAN WE LOOKUP A TABLE FROM A SOURCE QUALIFER TRANSFORMATION-UNCONNECTED LOOKUP
Answer: No. we can't do. I will explain you why. 1) Unless you assign the output of the source qualifier to another transformation or to target no way it will include the feild in the query. 2) sou
345] WHAT IS A TIME DIMENSION? GIVE AN EXAMPLE.
Answer: In a relational data model, for normalization purposes, year lookup, quarter lookup, month lookup, and week lookups are not merged as a single table. In a dimensional data modeling(star schema), these
346] HOW CAN U CREATE OR IMPORT FLAT FILE DEFINITION IN TO THE WAREHOUSE DESIGNER?
Answer: U can create flat file definition in warehouse designer.in the warehouse designer,u can create new target: select the type as flat file. save it and u can enter various columns for that created target
