FINAL INTERVIEW QUESTIONS ( Data warehouse) Part-1 - ETL- iNFORMATICA DEVELOPER

Monday, June 17, 2019

FINAL INTERVIEW QUESTIONS ( Data warehouse) Part-1


ktnewslive



Data warehousing Basics

  1. Definition of data warehousing?
   Data warehouse is a Subject oriented, Integrated, Time variant, Non volatile collection of data in support of management's decision making process.

                                                                                                                        
Subject Oriented

Data warehouses are designed to help you analyze data. For example, to learn more about your company's sales data, you can build a warehouse that concentrates on sales. Using this warehouse, you can answer questions like "Who was our best customer for this item last year?" This ability to define a data warehouse by subject matter, sales in this case makes the data warehouse subject oriented.

Integrated

Integration is closely related to subject orientation. Data warehouses must put data from disparate sources into a consistent format. They must resolve such problems as naming conflicts and inconsistencies among units of measure. When they achieve this, they are said to be integrated.

Nonvolatile

Nonvolatile means that, once entered into the warehouse, data should not change. This is logical because the purpose of a warehouse is to enable you to analyze what has occurred.

Time Variant

In order to discover trends in business, analysts need large amounts of data. This is very much in contrast to online transaction processing (OLTP) systems, where performance requirements demand that historical data be moved to an archive. A data warehouse's focus on change over time is what is meant by the term time variant.

  1. How many stages in Datawarehousing?

Data warehouse generally includes two stages
   ETL
   Report Generation


ETL
Short for extract, transform, load, three database functions that are combined into one tool
  • Extract -- the process of reading data from a source database.
  • Transform -- the process of converting the extracted data from its previous form into required form
  • Load -- the process of writing the data into the target database.

ETL is used to
migrate data from one database to another, to form data marts and data warehouses and also to convert databases from one format to another format.
It is used to retrieve the data from various operational databases and is transformed into useful information and finally loaded into Datawarehousing system.

1 INFORMATICA
2 ABINITO
3 DATASTAGE
4. BODI
5 ORACLE WAREHOUSE BUILDERS


Report generation

          In report generation, OLAP is used (i.e.) online analytical processing. It is a set of specification which allows the client applications in retrieving the data for analytical processing.
It is a specialized tool that sits between a database and user in order to provide various analyses of the data stored in the database.
OLAP Tool is a reporting tool which generates the reports that are useful for Decision support for top level management.

  1. Business Objects
  2. Cognos
  3. Micro strategy
  4. Hyperion
  5. Oracle Express

6.    Microsoft Analysis Services



  • Different Between OLTP and OLAP
OLTP
OLAP
1
Application Oriented (e.g., purchase order it is functionality of an application)
Subject Oriented (subject in the sense customer, product, item, time)
2
Used to run business
Used to analyze business
3
Detailed data   
Summarized data
4
Repetitive access
Ad-hoc access
5
Few Records accessed at a time (tens), simple query
Large volumes accessed at a time(millions), complex query
6
Small database
Large Database
7
Current data
Historical data
8
Clerical User
Knowledge User
9
Row by Row Loading
Bulk Loading
10
Time invariant
Time variant
11
Normalized data
De-normalized data
12
E – R schema
Star schema




No comments:

Post a Comment