101⟩ How can we transform row to column?
Through Normalizer Transformation we can do this.
Informatica Data Warehousing
Home Data Warehouse Informatica Data Warehousing
“Informatica Data Warehousing Frequently Asked Questions in various Informatica Data Warehousing Interviews asked by the interviewer. So learn Informatica Data Warehousing with the help of this Informatica Data Warehousing Interview questions and answers guide and feel free to comment as your suggestions, questions and answers on any Informatica Data Warehousing Interview Question or answer by the comment feature available on the page.”
128 Informatica Data Warehousing Questions And Answers
102⟩ What is a time dimension? give an example?
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 tables would be merged as a single table called TIME DIMENSION for performance and slicing data.
This dimensions helps to find the sales done on date, weekly, monthly and yearly basis. We can have a trend analysis by comparing this year sales with the previous year or this week sales with the previous week.
103⟩ Explain Why dimenstion tables are denormalized in nature?
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 details should be maintain in one table, if u maintain primary key it won't allow the duplicate records with same employee id. so to maintain historical data we are all going for concept data warehousing by using surrogate keys we can achieve the historical data(using oracle sequence for critical column).
so all the dimensions are marinating historical data, they are de normalized, because of duplicate entry means not exactly duplicate record with same employee number another record is maintaining in the table.
104⟩ How to eliminate duplicate rows from flat file?
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 during the sort operation
105⟩ How to load the data from people soft hrm to people soft erm using informatica?
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 people soft source/target in workflow manager. select the proper connection (people soft with oracle,sybase,db2 and informix) and execute like a normal session.
106⟩ Explain How to export mappings to the production environment?
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.
You will have to export as xml format using export option and then import in production environment.
107⟩ How Identifying bottlenecks in various components of Informatica and resolving them?
The best way to find out bottlenecks is writing to flat file and see where the bottle neck is .
108⟩ What is Recovering sessions?
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 use to complete the session depends on the properties of the mapping, session, and Informatica Server configuration.
Use one of the following methods to complete the session:
? Run the session again if the Informatica Server has not issued a commit.
? Truncate the target tables and run the session again if the session is not recoverable.
? Consider performing recovery if the Informatica Server has issued at least one commit.
109⟩ What is perform recovery?
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 all sources again and starts processing from the next row ID. For example, if the Informatica Server commits 10,000 rows before the
session fails, when you run recovery, the Informatica Server bypasses the rows up to 10,000 and starts loading with row 10,001.
By default, Perform Recovery is disabled in the Informatica Server setup. You must enable Recovery in the Informatica Server setup before you run a session so the
Informatica Server can create and/or write entries in the OPB_SRVR_RECOVERY table.
110⟩ Can we start and stop single session in concurent bstch?
ya shoor,Just right click on the particular session and going to recovery option
or
by using event wait and event rise
111⟩ Start a batches with in a batch?
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.
112⟩ Explain What are the different types of transformation available in informatica. and what are the mostly used ones among them?
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 meet the condition in transformation.2]Passive TransformationA passive transformation does not change the number of rows that pass through it i.e it passes all rows through the transformation.Transformations can be Connected or UnConnected. Connected TransformationConnected transformation is connected to other transformations or directly to target table in the mapping.UnConnected TransformationAn unconnected transformation is not connected to other transformations in the mapping. It is called within another transformation, and returns a value to that transformation.list of Transformations available in Informatica:1 source qualifier Tranformation2..Expression Transformation 3..Filter Transformation 4..Joiner Transformation 5..Lookup Transformation 6..Normalizer Transformation 7..Rank Transformation 8..Router Transformation 9..Sequence Generator Transformation 10..Stored Procedure Transformation 11..Sorter Transformation 12..Update Strategy Transformation .13...Aggregator Transformation 14..XML Source Qualifier Transformation 15..Advanced External Procedure Transformation 16..External Transformation 16.. custom tranformationMostly use of particular tranformation depend upon the requirement.In our project we are mostly using source qualifier ,aggregator,joiner,look up tranformation
113⟩ Explain Why we use partitioning the session in informatica?
Partitioning achieves the session performance by reducing the time period of reading the source and loading the data into target.
114⟩ How to join 3 database like Flat File, Oracle, Db2 in Informatrica?
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.
115⟩ Explain What is the difference between stop and abort?
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 bacth
Abort:----
You can issue the abort command , it is similar to stop command except it has 60 second time out .
If the server cannot finish processing and commiting data with in 60 sec
Here's the difference:
ABORT is equivalent to:
1. Kill -9 on Unix (NOT kill -7) but YES, Kill -9
2. SIGTERM ABEND (Force ABEND) on Mainframe
3. Windows FORCE QUIT on application.
What does this do?
Each session uses SHARED/LOCKED (semaphores) memory blocks. The ABORT function kills JUST THE CODE threads, leaving the memory LOCKED and SHARED and allocated. The good news: It appears as if AIX Operating system cleans up these lost memory blocks. The bad news? Most other operating systems DO NOT CLEAR THE MEMORY, leaving the memory "taken" from the system. The only way to clear this memory is to warm-boot/cold-boot (restart) the informatica SERVER machine, yes, the entire box must be re-started to get the memory back.
If you find your box running slower and slower over time, or not having enough memory to allocate new sessions, then I suggest that ABORT not be used.
So then the question is: When I ask for a STOP, it takes forever. How do I get the session to stop fast?
well, first things first. STOP is a REQUEST to stop. It fires a request (equivalent to a control-c in SQL*PLUS) to the source database, waits for the source database to clean up. The bigger the data in the source query, the more time it takes to "roll-back" the source query, to maintain transaction consistency in the source database. (ie: join of huge tables, big group by, big order by).
It then cleans up the buffers in memory by releasing the data (without writing to the target) but it WILL run the data all the way through to the target buffers, never sending it to the target DB. The bigger the session memory allocations, the longer it takes to clean up.
Then it fires a request to stop against the target DB, and waits for the target to roll-back. The higher the commit point, the more data the target DB has to "roll-back".
FINALLY, it shuts the session down.
WHAT IF I NEED THE SESSION STOPPED NOW?
Pick up the phone and call the source system DBA, have them KILL the source query IN THE DATABASE. This will send an EOF (end of file) downstream to Informatica, and Infa will take less time to stop the session.
If you use abort, be aware, you are choosing to "LOSE" memory on the server in which Informatica is running (except AIX).
If you use ABORT and you then re-start the session, chances are, not only have you lost memory - but now you have TWO competing queries on the source system after the same data, and you've locked out any hope of performance in the source database. You're competing for resources with a defunct query that's STILL rolling back.
116⟩ What is the Rankindex in Ranktransformation?
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, if you create a Rank transformation that ranks the top 5 salespersons for each quarter, the rank index numbers the salespeople from 1 to 5.
117⟩ How to improve session performance in aggregator transformation?
Use sorted input.
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 to allocate more space to support aggregation.
118⟩ What is the difference between connected and unconnected stored procedures?
Unconnected:
The unconnected Stored Procedure transformation is not connected directly to the flow of the mapping. It either runs before or after the session, or is called by an expression in another transformation in the mapping.
connected:
The flow of data through a mapping in connected mode also passes through the Stored Procedure transformation. All data entering the transformation through the input ports affects the stored procedure. You should use a connected Stored Procedure transformation when you need data from an input port sent as an input parameter to the stored procedure, or the results of a stored procedure sent as an output parameter to another transformation.
by using unconnected stored procedure reusability is possible in connected only one time is possible
119⟩ What is the difference between constraind base load ordering and target load plan?
Constraint based load ordering
example:
Table 1---Master
Tabke 2---Detail
If the data in table1 is dependent on the data in table2 then table2 should be loaded first.In such cases to control the load order of the tables we need some conditional loading which is nothing but constraint based load
In Informatica this feature is implemented by just one check box at the session level.
A CBl specifies the order in which data loads into the targets based on key constraints
A target load plan defines the order in which data being extracted from the source qualifier
120⟩ Is union Transormation is Active transformation?
By Definiation, 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 from the actual number of rows.
As we are combining results of two select queries using Union Tr Most probably no of rows increases.So it is An Active Tr.
