Explore topic-wise InterviewSolutions in Current Affairs.

- Abinitio
- DATASTAGE
- Informatica
- COGNOS Decision Stream
- Oracle Warehouse BUILDER
- Business Objects XI (Extreme Insight)
- SAP Business Warehouse
- SAS Enterprise ETL Server

- Abinitio
- DataStage
- Informatica
- Cognos Decision Stream
- Oracle Warehouse Builder
- Business Objects XI (Extreme Insight)
- SAP Business Warehouse
- SAS Enterprise ETL Server

If RETURN PORT only ONE then we can go for unconnected. More than one return port is not POSSIBLE with Unconnected. If more than one return port then go for CONNECTED.

If return port only one then we can go for unconnected. More than one return port is not possible with Unconnected. If more than one return port then go for Connected.

1.Use filter condition in source QUALIFIES WITHOUT USING filter
2.use persistence and shared cache in look up t/r
3.use in aggregations t/r in sorted i/p, group by ports
4.in expression use OPERATORS instead of FUNCTIONS
5.increase the cache size
6. increase the commit interval

1.Use filter condition in source qualifies without using filter
2.use persistence and shared cache in look up t/r
3.use in aggregations t/r in sorted i/p, group by ports
4.in expression use operators instead of functions
5.increase the cache size
6. increase the commit interval

While the selection of a database and a hardware platform is a MUST, the selection of an ETL tool is highly recommended, but it's not a must. When you evaluate ETL tools, it pays to LOOK for the following characteristics:

Functional capability: This includes both the 'transformation' piece and the 'cleansing' piece. In general, the typical ETL tools are either geared towards having strong transformation capabilities or having strong cleansing capabilities, but they are seldom very strong in both. As a result, if you know your data is going to be dirty coming in, make sure your ETL tool has strong cleansing capabilities. If you know there are going to be a lot of different data transformations, it then makes sense to pick a tool that is strong in transformation.

Ability to read directly from your data source: For each ORGANIZATION, there is a different set of data sources. Make sure the ETL tool you select can connect directly to your source data.

Metadata support: The ETL tool plays a key role in your metadata because it maps the source data to the destination, which is an important piece of the metadata. In fact, some organizations have come to rely on the documentation of their ETL tool as their metadata source. As a result, it is very important to select an ETL tool that WORKS with your overall metadata strategy.

While the selection of a database and a hardware platform is a must, the selection of an ETL tool is highly recommended, but it's not a must. When you evaluate ETL tools, it pays to look for the following characteristics:

Functional capability: This includes both the 'transformation' piece and the 'cleansing' piece. In general, the typical ETL tools are either geared towards having strong transformation capabilities or having strong cleansing capabilities, but they are seldom very strong in both. As a result, if you know your data is going to be dirty coming in, make sure your ETL tool has strong cleansing capabilities. If you know there are going to be a lot of different data transformations, it then makes sense to pick a tool that is strong in transformation.

Ability to read directly from your data source: For each organization, there is a different set of data sources. Make sure the ETL tool you select can connect directly to your source data.

Metadata support: The ETL tool plays a key role in your metadata because it maps the source data to the destination, which is an important piece of the metadata. In fact, some organizations have come to rely on the documentation of their ETL tool as their metadata source. As a result, it is very important to select an ETL tool that works with your overall metadata strategy.

Popular Tools:
IBM WebSphere INFORMATION INTEGRATION (Accentual DATASTAGE)
Ab Initio
Informatica
TALEND

Popular Tools:
IBM WebSphere Information Integration (Accentual DataStage)
Ab Initio
Informatica
Talend

Materialized view is a view in which DATA is also STORED in some temp table. i.e if we will go with the View concept in DB in that we only store QUERY and once we call View it extract data from DB. But In materialized View data is stored in some temp tables.

Materialized view is a view in which data is also stored in some temp table. i.e if we will go with the View concept in DB in that we only store query and once we call View it extract data from DB. But In materialized View data is stored in some temp tables.

When addressing a table some DIMENSION key must REFLECT the NEED for a record to get extracted. Mostly it will be from time dimension (e.g. date >= 1ST of current month) or a transaction flag (e.g. Order Invoiced Stat). Foolproof would be adding an archive flag to record which GETS reset when record changes.

When addressing a table some dimension key must reflect the need for a record to get extracted. Mostly it will be from time dimension (e.g. date >= 1st of current month) or a transaction flag (e.g. Order Invoiced Stat). Foolproof would be adding an archive flag to record which gets reset when record changes.

• Aggregator Transformation
• EXPRESSION Transformation
• Filter Transformation
• Joiner Transformation
• Lookup Transformation
• Normalizer Transformation
• RANK Transformation
• Router Transformation
• Sequence Generator Transformation
• Stored Procedure Transformation
• Sorter Transformation
• Update STRATEGY Transformation
• XML Source QUALIFIER Transformation
• Advanced External Procedure Transformation
• External Transformation

Parameter FILE defines the value for parameter and VARIABLE USED in a workflow, work let or SESSION.

Parameter file defines the value for parameter and variable used in a workflow, work let or session.

1. Connected lookup
2. UNCONNECTED lookup

Connected lookup will receive input from the PIPELINE and sends OUTPUT to the pipeline and can return any number of values. it does not contain return port.

Unconnected lookup can return only one column. it contain return port.

1. Connected lookup
2. Unconnected lookup

Connected lookup will receive input from the pipeline and sends output to the pipeline and can return any number of values. it does not contain return port.

Unconnected lookup can return only one column. it contain return port.

ACTIVE transformation can change the number of rows that pass through it. (Decrease or INCREASE rows)
PASSIVE transformation cannot change the number of rows that pass through it.

Active transformation can change the number of rows that pass through it. (Decrease or increase rows)
Passive transformation cannot change the number of rows that pass through it.

ETL tool is meant for EXTRACTION data from the legacy systems and load into specified data BASE with some process of cleansing data.
ex: Informatica, data stage ....etc
OLAP is meant for Reporting purpose. in OLAP data available in MULTIDIRECTIONAL MODEL. so that u can write simple query to extract data from the data base.
ex: Business objects, Cognos....etc

ETL tool is meant for extraction data from the legacy systems and load into specified data base with some process of cleansing data.
ex: Informatica, data stage ....etc
OLAP is meant for Reporting purpose. in OLAP data available in Multidirectional model. so that u can write simple query to extract data from the data base.
ex: Business objects, Cognos....etc

The best procedure to take a help of debugger where we monitor each and EVERY PROCESS of mappings and how data is LOADING based on CONDITIONS breaks.

The best procedure to take a help of debugger where we monitor each and every process of mappings and how data is loading based on conditions breaks.

joiner is USED to join two or more tables to retrieve DATA from tables (just like JOINS in sql).
Look up is used to check and compare source table and target table . (just like CORRELATED sub-query in sql).

joiner is used to join two or more tables to retrieve data from tables (just like joins in sql).
Look up is used to check and compare source table and target table . (just like correlated sub-query in sql).

SAP DATA can be loaded into INFORMATICA in the form of FLAT files.
Condition:
Informatica source qualifier column sequence must match the SAP source FILE.

SAP Data can be loaded into Informatica in the form of Flat files.
Condition:
Informatica source qualifier column sequence must match the SAP source file.

we can do LTRIM, rtrim in the EXPRESSION or can have CHECK for NULL and then insert the data.

we can do ltrim, rtrim in the expression or can have check for null and then insert the data.

By using AGGREGATOR TRANSFORMATION with first and last FUNCTIONS we can GET first and last record.

By using Aggregator transformation with first and last functions we can get first and last record.

1. PowerMart DESIGNER
2. SERVER
3. Server MANAGER
4. Repository
5. Repository Manager

1. PowerMart Designer
2. Server
3. Server Manager
4. Repository
5. Repository Manager

Granularity, is the level of DETAIL in which the fact table is DESCRIBING, for example if we are making TIME analysis so the granularity maybe day BASED - MONTH based or year based

Granularity, is the level of detail in which the fact table is describing, for example if we are making time analysis so the granularity maybe day based - month based or year based

The ANALYZE statement allows you to VALIDATE and compute statistics for an index, table, or cluster. These statistics are used by the cost-based optimizer when it calculates the most efficient PLAN for retrieval. In addition to its ROLE in statement optimization, ANALYZE also helps in validating object structures and in managing space in your system. You can choose the following operations: COMPUTER, ESTIMATE, and DELETE. EARLY version of Oracle7 produced unpredictable results when the ESTIMATE operation was used. It is best to compute your statistics.

The ANALYZE statement allows you to validate and compute statistics for an index, table, or cluster. These statistics are used by the cost-based optimizer when it calculates the most efficient plan for retrieval. In addition to its role in statement optimization, ANALYZE also helps in validating object structures and in managing space in your system. You can choose the following operations: COMPUTER, ESTIMATE, and DELETE. Early version of Oracle7 produced unpredictable results when the ESTIMATE operation was used. It is best to compute your statistics.

ETL - The process of extracting data from MULTIPLE sources.(ex. flat files, XML, COBOL, SAP etc) is more simpler with the help of tools.
Manual - LOADING the data other than flat files and oracle table need more effort.
ETL - High and clear visibility of logic.
Manual - COMPLEX and not so user friendly visibility of logic.
ETL - Contains META data and changes can be done easily.
Manual - No Meta data concept and changes needs more effort.
ETL- Error handling, log summary and load progress makes life easier for developer and maintainer.
Manual - need maximum effort from maintenance POINT of view.
ETL - Can handle Historic data very well.
Manual - as data grows the processing time degrades.

These are some differences b/w manual and ETL development.

ETL - The process of extracting data from multiple sources.(ex. flat files, XML, COBOL, SAP etc) is more simpler with the help of tools.
Manual - Loading the data other than flat files and oracle table need more effort.
ETL - High and clear visibility of logic.
Manual - complex and not so user friendly visibility of logic.
ETL - Contains Meta data and changes can be done easily.
Manual - No Meta data concept and changes needs more effort.
ETL- Error handling, log summary and load progress makes life easier for developer and maintainer.
Manual - need maximum effort from maintenance point of view.
ETL - Can handle Historic data very well.
Manual - as data grows the processing time degrades.

These are some differences b/w manual and ETL development.

- Cognos DECISION Stream
- ORACLE WAREHOUSE Builder
- Business Objects XI (EXTREME Insight)
- SAP Business Warehouse
- SAS Enterprise ETL Server

- Cognos Decision Stream
- Oracle Warehouse Builder
- Business Objects XI (Extreme Insight)
- SAP Business Warehouse
- SAS Enterprise ETL Server

The LATEST VERSION is 7.2

The Latest Version is 7.2

ODS - Operational DATA Store.
ODS Comes between staging AREA & Data Warehouse. The data is ODS will be at the low level of granularity.
Once data was populated in ODS aggregated data will be LOADED into into EDW through ODS.

ODS - Operational Data Store.
ODS Comes between staging area & Data Warehouse. The data is ODS will be at the low level of granularity.
Once data was populated in ODS aggregated data will be loaded into into EDW through ODS.

Here are some POPULAR VERSIONS of INFORMATICA.
Informatica POWERCENTER 4.1,
Informatica Powercenter 5.1,
Powercenter Informatica 6.1.2,
Informatica Powercenter 7.1.2,
Informatica Powercenter 8.1,
Informatica Powercenter 8.5,
Informatica Powercenter 8.6.

Here are some popular versions of Informatica.
Informatica Powercenter 4.1,
Informatica Powercenter 5.1,
Powercenter Informatica 6.1.2,
Informatica Powercenter 7.1.2,
Informatica Powercenter 8.1,
Informatica Powercenter 8.5,
Informatica Powercenter 8.6.

One foolproof method is to maintain a field CALLED 'Last Extraction Date' and then IMPOSE a condition in the CODE SAYING 'current_extraction_date > last_extraction_date'.

One foolproof method is to maintain a field called 'Last Extraction Date' and then impose a condition in the code saying 'current_extraction_date > last_extraction_date'.

Rejection of records either at the database due to constraint key VIOLATION or the informatica server when writing data into target table. These rejected records we can find in the BAD files FOLDER where a reject file will be created for a session. we can CHECK why a record has been rejected. And this bad file contains first column a row indicator and second column a column indicator.

These row indicators or of four types:
D-valid data,
O-overflowed data,
N-null data,
T- Truncated data,
And depending on these indicators we can changes to load data SUCCESSFULLY to target.

Rejection of records either at the database due to constraint key violation or the informatica server when writing data into target table. These rejected records we can find in the bad files folder where a reject file will be created for a session. we can check why a record has been rejected. And this bad file contains first column a row indicator and second column a column indicator.

These row indicators or of four types:
D-valid data,
O-overflowed data,
N-null data,
T- Truncated data,
And depending on these indicators we can changes to load data successfully to target.

INFORMATICA METADATA is DATA about data which STORES in Informatica REPOSITORIES.

Informatica Metadata is data about data which stores in Informatica repositories.

ETL Tool:
It is used to Extract(E) data from multiple source systems(like RDBMS, Flat FILES, Mainframes, SAP,XML ETC) transform(T) them based on Business requirements and Load(L) in target locations.(like tables, files etc).
Need of ETL Tool:
An ETL tool is TYPICALLY required when data SCATTERED ACROSS different systems. (like RDBMS, Flat files, Mainframes, SAP,XML etc).

ETL Tool:
It is used to Extract(E) data from multiple source systems(like RDBMS, Flat files, Mainframes, SAP,XML etc) transform(T) them based on Business requirements and Load(L) in target locations.(like tables, files etc).
Need of ETL Tool:
An ETL tool is typically required when data scattered across different systems. (like RDBMS, Flat files, Mainframes, SAP,XML etc).

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

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

POWERCENTER - ability to organize repositories into a data mart DOMAIN and SHARE METADATA across repositories.
PowerMart - only local repository can be created.

PowerCenter - ability to organize repositories into a data mart domain and share metadata across repositories.
PowerMart - only local repository can be created.

Specify the FULL PATH of the Shell SCRIPT the "Post session properties of session/workflow".

Specify the Full path of the Shell script the "Post session properties of session/workflow".

Data STAGING is actually a COLLECTION of processes used to prepare SOURCE system data for loading a data warehouse. Staging includes the following steps:
Source data extraction, Data transformation (restructuring),
Data transformation (data cleansing, value transformations),
Surrogate key assignments

Data staging is actually a collection of processes used to prepare source system data for loading a data warehouse. Staging includes the following steps:
Source data extraction, Data transformation (restructuring),
Data transformation (data cleansing, value transformations),
Surrogate key assignments

we can’t directly import the xml file in informatica.
we have to define the MICROSOFT EXCEL odbc driver on our system and define the name in exce sheet by defining ranges then in informatica open the folder using SOURCES ->import from DATABASE->SELECT excel odbc driver->connect->select the excel sheet name .

we can’t directly import the xml file in informatica.
we have to define the Microsoft excel odbc driver on our system and define the name in exce sheet by defining ranges then in informatica open the folder using sources ->import from database->select excel odbc driver->connect->select the excel sheet name .

FULL Load: completely erasing the contents of one or more tables and reloading with fresh data.
INCREMENTAL Load: applying ONGOING CHANGES to one or more tables based on a PREDEFINED schedule.

Full Load: completely erasing the contents of one or more tables and reloading with fresh data.
Incremental Load: applying ongoing changes to one or more tables based on a predefined schedule.

ETL is extraction , transforming , loading process , you will extract data from the source and apply the business role on it then you will LOAD it in the TARGET
The steps are :
1-DEFINE the source (create the odbc and the connection to the source DB)
2-define the target (create the odbc and the connection to the target DB)
3-create the mapping ( you will apply the business role here by adding transformations , and define how the data flow will go from the source to the target )
4-create the SESSION (its a set of instruction that run the mapping )
5-create the work flow (instruction that run the session)

ETL is extraction , transforming , loading process , you will extract data from the source and apply the business role on it then you will load it in the target
The steps are :
1-define the source (create the odbc and the connection to the source DB)
2-define the target (create the odbc and the connection to the target DB)
3-create the mapping ( you will apply the business role here by adding transformations , and define how the data flow will go from the source to the target )
4-create the session (its a set of instruction that run the mapping )
5-create the work flow (instruction that run the session)

No, In INFORMATICA 5.2 and
YES, in Informatica 6.1 and LATER.

No, In Informatica 5.2 and
Yes, in Informatica 6.1 and later.

Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to REFLECT changes MADE to the master table. Snapshots are mirror or REPLICAS of tables.

VIEWS are built using the columns from one or more tables. The SINGLE Table View can be updated but the view with multi table cannot be updated.

A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.
Materialized view:
A pre-computed table comprising aggregated or joined data from fact and possibly dimension tables. Also known as a summary or aggregate table.

Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table. Snapshots are mirror or replicas of tables.

Views are built using the columns from one or more tables. The Single Table View can be updated but the view with multi table cannot be updated.

A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.
Materialized view:
A pre-computed table comprising aggregated or joined data from fact and possibly dimension tables. Also known as a summary or aggregate table.

Yes. we can USE mapping variable in INFORMATICA.

The Informatica server saves the VALUE of mapping variable to the repository at the END of SESSION run and uses that value next time we run the session.

Yes. we can use mapping variable in Informatica.

The Informatica server saves the value of mapping variable to the repository at the end of session run and uses that value next time we run the session.

Yes,we can override a native sql query in SOURCE QUALIFIER and lookup transformation.

In lookup transformation we can FIND "Sql override" in lookup PROPERTIES. by using this option we can do this.

Yes,we can override a native sql query in source qualifier and lookup transformation.

In lookup transformation we can find "Sql override" in lookup properties. by using this option we can do this.

Informatica allows END users and partners to extend the metadata stored in the repository by associating information with individual OBJECTS in the repository. For example, when you create a mapping, you can store your CONTACT information with the mapping. You associate information with repository metadata using metadata extensions.

Informatica Client applications can contain the FOLLOWING types of metadata extensions:
Vendor-defined: Third-party application vendors create vendor-defined metadata extensions. You can view and change the values of vendor-defined metadata extensions, but you cannot create, DELETE, or redefine them.

User-defined: You create user-defined metadata extensions using PowerCenter/PowerMart. You can create, edit, delete, and view user-defined metadata extensions. You can also change the values of user-defined extensions.

Informatica allows end users and partners to extend the metadata stored in the repository by associating information with individual objects in the repository. For example, when you create a mapping, you can store your contact information with the mapping. You associate information with repository metadata using metadata extensions.

Informatica Client applications can contain the following types of metadata extensions:
Vendor-defined: Third-party application vendors create vendor-defined metadata extensions. You can view and change the values of vendor-defined metadata extensions, but you cannot create, delete, or redefine them.

User-defined: You create user-defined metadata extensions using PowerCenter/PowerMart. You can create, edit, delete, and view user-defined metadata extensions. You can also change the values of user-defined extensions.

A DATA warehouse can be thought of as a three-tier SYSTEM in which a middle system PROVIDES usable data in a secure way to END users. On either side of this middle system are the end users and the back-end data stores.

A data warehouse can be thought of as a three-tier system in which a middle system provides usable data in a secure way to end users. On either side of this middle system are the end users and the back-end data stores.

ETL stands for EXTRACTION transformation and loading
ETL provide developers with an INTERFACE for designing source-to-target mappings, transformation and JOB control parameter
* Extraction
Take data from an external source and move it to the warehouse pre-processor DATABASE
* Transformation
Transform data task allows point-to-point generating, modifying and transforming data
* Loading
Load data task adds records to a database table in a warehouse.

ETL stands for extraction transformation and loading
ETL provide developers with an interface for designing source-to-target mappings, transformation and job control parameter
* Extraction
Take data from an external source and move it to the warehouse pre-processor database
* Transformation
Transform data task allows point-to-point generating, modifying and transforming data
* Loading
Load data task adds records to a database table in a warehouse.