Important Short Questions and Answers: Data Warehousing

DATA WAREHOUSING

1. Define Data warehouse.

A data warehouse is a repository of multiple heterogeneous data sources organized under a unified schema at a single site to facilitate management decision making . (or) A data warehouse is a subject-oriented, time-variant and nonvolatile collection of data in support of management’s decision-making process.

Subject Oriented: Data that gives information about a particular subject instead of about a company’s ongoing operations.

Integrated: Data that is gathered into the data warehouse from a variety of sources and merged into a coherent whole.

Time-variant: All data in the data warehouse is identified with a particular time period. Non-volatile: Data is stable in a data warehouse. More data is added but data is never removed.

2. Define Metadata.

It is data about data. It is used for maintaining, managing and using the data warehouse. It is classified into two:

Technical Meta data: It contains information about data warehouse data used by warehouse designer, administrator to carry out development and management

Business Meta data: It contains info that gives info stored in data warehouse to users.

3.What is virtual warehouse?

A virtual warehouse is a set of views over operational databases. For efficient query processing, only some of the possible summary views may be materialized. A virtual warehouse is easy to build but requires excess capability on operational database servers.

4.What are the steps for design of data warehouse?

The following nine-step method is followed in the design of a data warehouse:

Choosing the subject matter

Deciding what a fact table represents

Identifying and conforming the dimensions

Choosing the facts

Storing pre calculations in the fact table

Rounding out the dimension table

Choosing the duration of the db

The need to track slowly changing dimensions

Deciding the query priorities and query models

5. What is Operational and informational Data?

Operational Data:

Focusing on transactional function such as bank card withdrawals and deposits

Detailed

 Updateable

Reflects current data

Informational Data:

 Focusing on providing answers to problems posed by decision makers

Summarized

Non updateable

6.List the Data Warehouse Characteristics

A data warehouse can be viewed as an information system with the following attributes:

It is a database designed for analytical tasks

It’s content is periodically updated

It contains current and historical data to provide a historical perspective of information

What are the seven components of Data warehouse Architecture?

Data sourcing, cleanup, transformation, and migration tools

Metadata repository

Warehouse/database technology

Data marts

Data query, reporting, analysis, and mining tools

Data warehouse administration and management

Information delivery system

7. Define a data mart?

Data mart is a pragmatic collection of related facts, but does not have to be exhaustive or exclusive. A data mart is both a kind of subject area and an application. Data mart is a collection of numeric facts.

8. What are the advantages of a data modeling tool?

Integrates the data warehouse model with other corporate data models.

Helps assure consistency in naming.

Creates good documentation in a variety of useful formats.

Provides a reasonably intuitive user interface for entering comments about objects.

9. Merits of Data Warehouse.

Ability to make effective decisions from database

Better analysis of data and decision support

Discover trends and correlations that benefits business

Handle huge amount of data.

10. What are the characteristics of data warehouse?

Separate

Available

Integrated

Subject Oriented

Not Dynamic

Consistency

Iterative Development

Aggregation Performance

11. List some of the Data Warehouse tools?

OLAP(OnLine Analytic Processing)

ROLAP(Relational OLAP)

End User Data Access tool

Ad Hoc Query tool

Data Transformation services

Replication

12. Why organizations consider data warehousing a critical need?

Business factors:

Business users want to make decision quickly and correctly using all available data.

Technological factors:

To address the incompatibility of operational data stores

IT infrastructure is changing rapidly. Its capacity is increasing and cost is decreasing so that building a data warehouse is easy

13. What are the two approaches of bulding the data warehouse?

 Top – Down Approach (Suggested by Bill Inmon)

Bottom – Up Approach (Suggested by Ralph Kimball)

14. What is the reason for building of Data warehouse is difficult?

 Heterogeneity of data sources

 Use of historical data

Growing nature of data base

15. List the classification of data warehouse user.

Casual users: are most comfortable in retrieving info from warehouse in pre defined formats and running pre existing queries and reports. These users do not need tools that allow for building standard and ad hoc reports

Power Users: can use pre defined as well as user defined queries to create simple and ad hoc reports. These users can engage in drill down operations. These users may have the experience of using reporting and query tools.

Expert users: These users tend to create their own complex queries and perform standard analysis on the info they retrieve. These users have the knowledge about the use of query and report tools

16. What are two types of parallelism?

Inter query Parallelism: In which different server threads or processes handle multiple requests at the same time.

Intra query Parallelism: This form of parallelism decomposes the serial SQL query into lower level operations such as scan, join, sort etc. Then these lower level operations are executed concurrently in parallel.

17.What are three DBMS software architecture styles for parallel processing:

Shared memory or shared everything Architecture

Shared disk architecture

Shred nothing architecture

18.List out the views in the design of a data warehouse?

Top-down view

Data source view

Data warehouse view

Business query view

19. List out the steps of the data warehouse design process?

Choose a business process to model.

Choose the grain of the business process

Choose the dimensions that will apply to each fact table record.

Choose the measures that will populate each fact table record.

20. What is enterprise warehouse?

An enterprise warehouse collects all the information’s about subjects spanning the entire organization. It provides corporate-wide data integration, usually from one (or) more operational systems (or) external information providers. It contains detailed data as well as summarized data and can range in size from a few giga bytes to hundreds of giga bytes, tera bytes (or) beyond. An enterprise data warehouse may be implemented on traditional mainframes, UNIX super servers (or) parallel architecture platforms. It requires business modeling and may take years to design and build.

21. Why we need separate data warehouse? Different functions and different data:

missing data: Decision support requires historical data which operational DBs do not typically maintain data consolidation: DS requires consolidation (aggregation, summarization) of data from heterogeneous sources data quality: different sources typically use inconsistent data representations, codes and formats which have to be reconciled

22.What are the benefits of data warehouse

The benefits can be classified into two:

Tangible benefits (quantified / measureable):Itincludes,

Improvement in product inventory

Decrement in production cost

Improvement in selection of target markets

Enhancement in asset and liability management

Intangible benefits (not easy to quantified): It includes,

Improvement in productivity by keeping all data in single location and eliminating rekeying of data

Reduced redundant processing

Enhanced customer relation