1. Define Data warehouse.
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
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.
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?
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?
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
is Operational and informational Data?
Focusing on transactional function such as bank
card withdrawals and deposits
Reflects current data
providing answers to problems posed by decision makers
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
Data sourcing, cleanup, transformation, and
Data query, reporting, analysis, and mining tools
Data warehouse administration and management
Information delivery system
7. Define a 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.
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
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
Handle huge amount of data.
10. What are the characteristics
of data warehouse?
11. List some of the Data
OLAP(OnLine Analytic Processing)
End User Data Access tool
Ad Hoc Query tool
Data Transformation services
12. Why organizations consider
data warehousing a critical need?
Business users want to make decision quickly and
correctly using all available data.
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
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
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
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?
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?
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
benefits can be classified into two:
benefits (quantified / measureable):Itincludes,
Improvement in product inventory
Decrement in production cost
Improvement in selection of target markets
Enhancement in asset and liability management
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