1. Explain What is ODS (operation data source)?

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 poopulated in ODS aggregated data will be loaded into into EDW through ODS.

ODS: ODS is also a simalar small DWH which will help analyst to analysis the bussiness.It will have data for less number of days. generally it will be around 30-45 days. Like DWH here also we will primary keys will be genrated, error and reject handling will be done.

2. Explain if a flat file cotains 1000 records how can i get first and last records only?

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

3. What are the various tools? Explain Name a few?

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

Tools of SAS are :-
- SAS Information delivery portal.
- SAS data integration studio.
- Business Intelligence.
-SAS Enterprise Guide.

4. What is latest version of Power Center / Power Mart?

he Latest Version is 7.2

informatica 8.0

5. How to calculate fact table granularity?

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

6. Explain What are the modules in Power Mart?

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

7. How to extract SAP data Using Informatica? What is ABAP? What are IDOCS?

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.

8. Explain the difference between etl tool and olap tools?

ETL tool is ment for extraction data from the legecy systems and load into specified data base with some process of cleansing data.

ex: Informatica,data stage ....etc

OLAP is ment for Reporting purpose.in OLAP data avaliable in Mulitidimectional model. so that u can write smple query to extract data fro the data base.

ex: Businee objects,Cognos....etc

9. Explain What are the various transformation available?

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

10. Explain the process of extracting data from source systems,storing in ODS and how data modeling is done?

There are various ways of Extracting Data from Source Systems.For example , You can use a DATA step, an Import Process .It depends with your input data styles. What kind of File/database it is residing in. Storing ur data in an ODS can be done thru an ODS stmt/export stmt/FILE stmt, again which depends on the file & data format ,You want your output to be in.

Download Interview PDF