Sunday, October 10, 2010

INFORMATICA QUESTION AND ANSWERS PART 5


169)Can we lookup a table from a source qualifer transformation-unconnected lookup
No. we can't do.
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) source qualifier don't have any variables feilds to utalize as expression.
170)If i done any modifications for my table in back end does it reflect in informatca
warehouse or maping desginer or source analyzer?
NO. Informatica is not at all concern with back end 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 have to import from back end to informatica by valid connection.And u have to replace the existing files with imported files.
171)Can you copy the batches? No
Can you use the mapping parameters or variables created in one maping into another r mapping? No
What are main advantages and purpose of using Normalizer Transformation in
Informatica?
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 single row of data
172)Can any one explain real time complain mappings or complex transformations in
Informatica. Specially in Sales Domain.
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 of the complexicity in expression transformation involving lot of nested IIF's and Decode statements...another one is the union tranformation and joiner.
173)What is Micro Strategy? Why is it used for? Can any one explain in detail about it?
Micro strategy is again an BI tool whicl 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 WINDOWS.
174)What are the real time problems generally come up while doing/running
mapping/any transformation?can any body explain with example.
May be you will encounter with connection faliure, other then that i don't think so,
cuzserver will handle all the syntex errors and Invalid mappings.
175)what are the difference between view and materialized view?
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 table data by storing the results of a query
in a separate schema object. Unlike an ordinary view, which does not take up any storage
space or contain any data
176)What are the different threads in DTM process?
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 will be created to perform pre and post session operations.
Reader thread: One thread will be created for each partition of a source.It reads data from source.
Writer thread: It will be created to load data to the target.
Transformation thread: It will be created to tranform data.
177)what r the settiings that u use to cofigure the joiner transformation?
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
Detail Outer
Full Outer
178)What is difference between maplet and reusable transformation?
Maplet: one or more transformations Reusable transformation: only one transformation
179)Define maping and sessions?
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 move data from source to targets.
180)What are the mapping paramaters and maping variables?
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 ,U declare and use the parameter in a maping or maplet.Then define the value of parameter in a parameter file for the session. Unlike a mapping parameter,a maping variable represents a value that can change
throughout the session.The informatica server saves the value of maping variable to the repository at the end of session run and uses that value next time you run the session.
181)What are the tasks that Loadmanger process will do?
Manages the session and batch scheduling: Whe you start the informatica server the load maneger launches and queries the repository for a list of sessions configured to run on the informatica server.When you configure the session the loadmanager maintains list of list of sessions and session start times.When you sart a session loadmanger fetches the session information from the repository to perform the validations and verifications prior to starting DTM process. Locking and reading the session: When the informatica server starts a session loadmanger locks the session from the repository.Locking prevents you starting the session again and again. Reading the parameter file: If the session uses a parameter files,loadmanager reads the parameter file and verifies that the session level parematers are declared in the file Verifies permission and privelleges: When the sesson starts load manger checks whether or not the user have privelleges to run the session. Creating log files: Loadmanger creates logfile contains the status of session.

182)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.1Active 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,lookup transformation
183)What are two types of processes that informatica runs the session?
Load manager Process: Starts the session, creates the DTM process, and sends postsession email when the session completes. The DTM process. Creates threads to initialize the session, read, write, and transform data, and handle pre- and post-session operations.
184)How can we store previous session logs
Just run the session in time stamp mode then automatically session log will not overwrite
current session log.
185)What is meant by Junk Attribute in Informatica?
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 dimensions like from the Overall_Transaction_master table tput flag tcmp flag del flag advance flag all these attributes can be a part of a junk dimensions
186)In a joiner trasformation, you should specify the source with fewer rows as the
master source. Why?
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 process.
187)What r the tasks that source qualifier performs?
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 Informatica Server reads source data. If you include a filter condition, the Informatica Server adds a WHERE clause to the default query. Specify an outer join rather than the default inner join. If you include a user-defined join, the Informatica Server replaces the join information specified by the metadata in the SQL query. Specify sorted ports. If you specify a number for sorted ports, the Informatica Server adds an ORDER BY clause to the default SQL query. Select only distinct values from the source. If you choose Select Distinct, the Informatica Server adds a SELECT DISTINCT statement to the default SQL query. Create a custom query to issue a special SELECT statement for the Informatica Server to read source data. For example, you might use a custom query to perform aggregate calculations or execute a stored procedure.

188)How to join two tables without using the Joiner Transformation.
Itz 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 table.Delete all
the source qualifiers.Add a common source qualifier for all.Right click on the source qualifier u will find EDIT click on it.Click on the properties tab,u will find sql query in that u can write ur sqls
189)If you want to create indexes after the load process which transformation you
choose?
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 index command. Use this command task in the workflow after the session or else, You can create it with a post session command.
190)What is the use of incremental aggregation? Explain me in brief with an example.
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 incrementaly. for performance we will use it.
191)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
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
192)What happens if you try to create a shortcut to a non-shared folder?
It only creates a copy of it..
193)What is metadata reporter?
It is a web based application that enables you to run reports againist repository metadata.
With a meta data reporter,You can access information about U’r repository with out
having knowledge of sql,transformation language or underlying tables in the repository.
194)What is polling?
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.
195)How do we estimate the number of partitons that a mapping really requires? Is it
dependent on the machine configuration?
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.
196)How do you decide whether you need ti do aggregations at database level or at
Informatica level?
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 use informatica. what ever it may be informatica is a thrid party tool, so it will take more time to process
aggregation compared to the database, but in Informatica an option we called "Incremental aggregation" which will help you to update the current values with current values +new values. No necessary to process entire values again and again. Unless this can be done if nobody deleted that cache files. If that happend total aggregation we need to execute on informatica also. In database we don't have Incremental aggregation facility.
197)
1.can u explain one critical mapping?
2.performance issue which one is better? whether connected lookup tranformation
or unconnected one?
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,if it is not so then go for unconnectd lookup. Specially in conditional case like, we have to get value for a field 'customer' from order tabel or from customer_data table,on the basis of following rule: If customer_name is null then ,customer=customer_data.ustomer_Id otherwise customer=order.customer_name. so in this case we will go for unconnected lookup
198)Discuss which is better among incremental load, Normal Load and Bulk load
It depends on the requirement. Otherwise Incremental load which can be better as it takes
onle that data which is not available previously on the target.
200)What is IQD file?
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 power play transformer.In data source type we select Impromptu Query Definetion.
201)Which objects are required by the debugger to create a valid debug session?
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.
202)What are the options in the target session of update strategy transsformatioin?
Insert
Delete
Update
Update as update
Update as insert
Update esle insert
Truncate table
203)What are the types of groups in Router transformation?
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 defined groups
Default group.
You can not modify or delete default groups.
204)What is the default join that source qualifier provides?
Inner equi join.
205)Define informatica repository?
Infromatica Repository:The informatica repository is at the center of the informatica suite. You create a set of metadata tables within the repository database that the informatica application and tools access. The informatica client and server access the repository to save and retrieve metadata.
206)Which tool U use to create and manage sessions and batches and to monitor and
stop the informatica server?
Informatica Workflow Managar and Informatica Worlflow Monitor
207)How to define Informatica server?
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 loads the data into the target table
208)Explain the informatica Architecture in detail
informatica server connects source data and target data using native odbc drivers again it connect to the repository for running sessions and retriveing metadata  information
source------>informatica server--------->target
|
|
REPOSITORY
209)What is the process flow of informatica,
Informatica is a ETL tool.used for the Extraction,Transformation and Loadind of data.This tool is used to Extract the data from different Data Bases and then we can dothe required transfermation like data type conversions,doing some aggregations,ordering,filtering and so on.After that we can load the transformed data into our database,which will be used for the Bussiness Decissions.
210)Can anyone explain about incremental aggregation with an example?
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 function to the changed rows and not to the entire rows. This improves the performance because you are not reading the entire source, each time you run the session.
211)How can you recognise whether or not the newly added rows in the source are gets
insert in the target?

In the Type2 maping we have three options to recognise the newly added rows
Version number
Flagvalue
Effective date Range.


Print this post

No comments: