ETL Datawarehousing Interview questions 2

What is a staging area? Do we need it? What is the purpose of a staging area?

Staging area is place where you hold temporary tables on data warehouse server. Staging tables are connected to work area or fact tables. We basically need staging area to hold the data , and perform data cleansing and merging , before loading the data into warehouse.

What are the modules in Power Mart?

1. PowerMart Designer
   2. Server
   3. Server Manager
   4. Repository
   5. Repository Manager 
2. What is a three tier data warehouse?
   The 3 tiers are:
   1. Data tier – bottom tier – consists of the database
   2. Application tier – middle tier – consists of the analytical server
   3. Presentation tier – tier that interacts with the end-user 
   
   
   What are the various transformation available?

   The various type of transformation in informatica source qualifier aggregate sequence generator sorter router filter lookup update strategy joiner normalizer expression

   How to determine what records to extract?
   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 mth) or a transaction flag (e.g. Order Invoiced Stat). Foolproof would be adding an archive flag to record which gets reset when record changes.