data warehousing inteview qa-5

40. How do u qry the Metadata tables for Informatica?

41(i). When do u use connected lookup n when do u use unconnected lookup?
Ans:
Connected Lookups : -
A connected Lookup transformation is part of the mapping data flow. With connected lookups, you can have multiple return values. That is, you can pass multiple values from the same row in the lookup table out of the Lookup transformation.
Common uses for connected lookups include:
=> Finding a name based on a number ex. Finding a Dname based on deptno
=> Finding a value based on a range of dates
=> Finding a value based on multiple conditions
Unconnected Lookups : -
An unconnected Lookup transformation exists separate from the data flow in the mapping. You write an expression using
the :LKP reference qualifier to call the lookup within another transformation.
Some common uses for unconnected lookups include:
=> Testing the results of a lookup in an expression
=> Filtering records based on the lookup results
=> Marking records for update based on the result of a lookup (for example, updating slowly changing dimension tables)
=> Calling the same lookup multiple times in one mapping



41(ii). What r the differences between Connected lookups and Unconnected lookups?
Ans: Although both types of lookups perform the same basic task, there are some important differences:
--------------------------------------------------------------- ---------------------------------------------------------------
Connected Lookup Unconnected Lookup
--------------------------------------------------------------- ---------------------------------------------------------------
Part of the mapping data flow. Separate from the mapping data flow.
Can return multiple values from the same row. Returns one value from each row.
You link the lookup/output ports to another You designate the return value with the Return port (R).
transformation.
Supports default values. Does not support default values.
If there's no match for the lookup condition, the If there's no match for the lookup condition, the server
server returns the default value for all output ports. returns NULL.
More visible. Shows the data passing in and out Less visible. You write an expression using :LKP to tell
of the lookup. the server when to perform the lookup.
Cache includes all lookup columns used in the Cache includes lookup/output ports in the Lookup condition
mapping (that is, lookup table columns included and lookup/return port.
in the lookup condition and lookup table
columns linked as output ports to other
transformations).

42. What u need concentrate after getting explain plan?
Ans: The 3 most significant columns in the plan table are named OPERATION,OPTIONS, and OBJECT_NAME.For each step,
these tell u which operation is going to be performed and which object is the target of that operation.
Ex:-
**************************
TO USE EXPLAIN PLAN FOR A QRY...
**************************
SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = 'PKAR02'
3 FOR
4 SELECT JOB,MAX(SAL)
5 FROM EMP
6 GROUP BY JOB
7 HAVING MAX(SAL) >= 5000;

Explained.

**************************
TO QUERY THE PLAN TABLE :-
**************************
SQL> SELECT RTRIM(ID)||' '||
2 LPAD(' ', 2*(LEVEL-1))||OPERATION
3 ||' '||OPTIONS
4 ||' '||OBJECT_NAME STEP_DESCRIPTION
5 FROM PLAN_TABLE
6 START WITH ID = 0 AND STATEMENT_ID = 'PKAR02'
7 CONNECT BY PRIOR ID = PARENT_ID
8 AND STATEMENT_ID = 'PKAR02'
9 ORDER BY ID;

STEP_DESCRIPTION
----------------------------------------------------
0 SELECT STATEMENT
1 FILTER
2 SORT GROUP BY
3 TABLE ACCESS FULL EMP






43. How components are interfaced in Psoft?
Ans:

44. How do u do the analysis of an ETL?
Ans:

==============================================================

45. What is Standard, Reusable Transformation and Mapplet?
Ans: Mappings contain two types of transformations, standard and reusable. Standard transformations exist within a single
mapping. You cannot reuse a standard transformation you created in another mapping, nor can you create a shortcut to that transformation. However, often you want to create transformations that perform common tasks, such as calculating the average salary in a department. Since a standard transformation cannot be used by more than one mapping, you have to set up the same transformation each time you want to calculate the average salary in a department.
Mapplet: A mapplet is a reusable object that represents a set of transformations. It allows you to reuse transformation logic
and can contain as many transformations as you need. A mapplet can contain transformations, reusable transformations, and
shortcuts to transformations.

46. How do u copy Mapping, Repository, Sessions?
Ans: To copy an object (such as a mapping or reusable transformation) from a shared folder, press the Ctrl key and drag and drop
the mapping into the destination folder.

To copy a mapping from a non-shared folder, drag and drop the mapping into the destination folder.
In both cases, the destination folder must be open with the related tool active.
For example, to copy a mapping, the Mapping Designer must be active. To copy a Source Definition, the Source Analyzer must be active.

Copying Mapping:
• To copy the mapping, open a workbook.
• In the Navigator, click and drag the mapping slightly to the right, not dragging it to the workbook.
• When asked if you want to make a copy, click Yes, then enter a new name and click OK.
• Choose Repository-Save.

Repository Copying: You can copy a repository from one database to another. You use this feature before upgrading, to
preserve the original repository. Copying repositories provides a quick way to copy all metadata you want to use as a basis for
a new repository.
If the database into which you plan to copy the repository contains an existing repository, the Repository Manager deletes the existing repository. If you want to preserve the old repository, cancel the copy. Then back up the existing repository before copying the new repository.
To copy a repository, you must have one of the following privileges:
• Administer Repository privilege
• Super User privilege

To copy a repository:
1. In the Repository Manager, choose Repository-Copy Repository.
2. Select a repository you wish to copy, then enter the following information:
-------------------------------- --------------------------- -------------------------------------------------
Copy Repository Field Required/ Optional Description
-------------------------------- --------------------------- -------------------------------------------------
Repository Required Name for the repository copy. Each repository name must be unique within
the domain and should be easily distinguished from all other repositories.
Database Username Required Username required to connect to the database. This login must have the
appropriate database permissions to create the repository.
Database Password Required Password associated with the database username.Must be in US-ASCII.
ODBC Data Source Required Data source used to connect to the database.
Native Connect String Required Connect string identifying the location of the database.
Code Page Required Character set associated with the repository. Must be a superset of the code
page of the repository you want to copy.

If you are not connected to the repository you want to copy, the Repository Manager asks you to log in.
3. Click OK.
5. If asked whether you want to delete an existing repository data in the second repository, click OK to delete it. Click Cancel to preserve the existing repository.

Copying Sessions:
In the Server Manager, you can copy stand-alone sessions within a folder, or copy sessions in and out of batches.
To copy a session, you must have one of the following:
• Create Sessions and Batches privilege with read and write permission
• Super User privilege
To copy a session:
1. In the Server Manager, select the session you wish to copy.
2. Click the Copy Session button or choose Operations-Copy Session.
The Server Manager makes a copy of the session. The Informatica Server names the copy after the original session, appending a number, such as session_name1.

47. What are shortcuts, and what is advantage?
Ans: Shortcuts allow you to use metadata across folders without making copies, ensuring uniform metadata. A shortcut inherits all
properties of the object to which it points. Once you create a shortcut, you can configure the shortcut name and description.

When the object the shortcut references changes, the shortcut inherits those changes. By using a shortcut instead of a copy,
you ensure each use of the shortcut exactly matches the original object. For example, if you have a shortcut to a target
definition, and you add a column to the definition, the shortcut automatically inherits the additional column.

Shortcuts allow you to reuse an object without creating multiple objects in the repository. For example, you use a source
definition in ten mappings in ten different folders. Instead of creating 10 copies of the same source definition, one in each
folder, you can create 10 shortcuts to the original source definition.
You can create shortcuts to objects in shared folders. If you try to create a shortcut to a non-shared folder, the Designer
creates a copy of the object instead.

You can create shortcuts to the following repository objects:
• Source definitions
• Reusable transformations
• Mapplets
• Mappings
• Target definitions
• Business components

You can create two types of shortcuts:
Local shortcut. A shortcut created in the same repository as the original object.
Global shortcut. A shortcut created in a local repository that references an object in a global repository.

Advantages: One of the primary advantages of using a shortcut is maintenance. If you need to change all instances of an
object, you can edit the original repository object. All shortcuts accessing the object automatically inherit the changes.
Shortcuts have the following advantages over copied repository objects:
• You can maintain a common repository object in a single location. If you need to edit the object, all shortcuts immediately inherit the changes you make.
• You can restrict repository users to a set of predefined metadata by asking users to incorporate the shortcuts into their work instead of developing repository objects independently.
• You can develop complex mappings, mapplets, or reusable transformations, then reuse them easily in other folders.
• You can save space in your repository by keeping a single repository object and using shortcuts to that object, instead of creating copies of the object in multiple folders or multiple repositories.

48. What are Pre-session and Post-session Options?
(Plzz refer Help Using Shell Commands n Post-Session Commands and Email)
Ans: The Informatica Server can perform one or more shell commands before or after the session runs. Shell commands are
operating system commands. You can use pre- or post- session shell commands, for example, to delete a reject file or
session log, or to archive target files before the session begins.

The status of the shell command, whether it completed successfully or failed, appears in the session log file.
To call a pre- or post-session shell command you must:
1. Use any valid UNIX command or shell script for UNIX servers, or any valid DOS or batch file for Windows NT servers.
2. Configure the session to execute the pre- or post-session shell commands.

You can configure a session to stop if the Informatica Server encounters an error while executing pre-session shell commands.

For example, you might use a shell command to copy a file from one directory to another. For a Windows NT server you would use the following shell command to copy the SALES_ ADJ file from the target directory, L, to the source, H:
copy L:\sales\sales_adj H:\marketing
For a UNIX server, you would use the following command line to perform a similar operation:
cp sales/sales_adj marketing/

Tip: Each shell command runs in the same environment (UNIX or Windows NT) as the Informatica Server. Environment settings in one shell command script do not carry over to other scripts. To run all shell commands in the same environment, call a single shell script that in turn invokes other scripts.

49. What are Folder Versions?
Ans: In the Repository Manager, you can create different versions within a folder to help you archive work in development. You can copy versions to other folders as well. When you save a version, you save all metadata at a particular point in development. Later versions contain new or modified metadata, reflecting work that you have completed since the last version.

Maintaining different versions lets you revert to earlier work when needed. By archiving the contents of a folder into a version each time you reach a development landmark, you can access those versions if later edits prove unsuccessful.

You create a folder version after completing a version of a difficult mapping, then continue working on the mapping. If you are unhappy with the results of subsequent work, you can revert to the previous version, then create a new version to continue development. Thus you keep the landmark version intact, but available for regression.

Note: You can only work within one version of a folder at a time.