DWDM

UNIT- 1

How is a DW different from a DB? How are they similar?

Answer: Differences between a DW and a DB:
A DW is a repository of information collected from multiple sources, over a history
of time, stored under a unified schema, and used for data analysis and decision
support; whereas

A DB, is a collection of interrelated data that represents the current status of the
stored data. There could be multiple heterogeneous DB where the schema of one DB
may not agree with the schema of another.
- A DBS supports ad-hoc query and on-line transaction processing.

Key Difference

 A DB stores current data while a DW stores historical data.

 A DB often changes due to frequent updates done on it, and hence, it cannot be
used for analysis or reaching decision. A DW extracts data and reports them to
analyze and reach decisions.
 A general DB is used for OLTP while a DW is used for OLAP.
 Tables in a DB are normalized to achieve efficient storage while a DW is
usually demoralized to achieve faster querying.
 Analytical queries are much faster on a DW than on a DB.
 A DB provides a detailed relational view while a DW provides a summarized
multidimensional view.
 A DB can do a lot of concurrent transactions while a DW is not designed for
such tasks.

Similarities between a DW and a DB: Both are repositories of information,

storing huge amounts of persistent data.
OLAP vs. OLTP

OLTP OLAP

It works on operational data & It works on data collected from operational system &
provides operational information. provides straight information.

used to run day to day business used to analyse how well business is running & what
operation. to do to improve business.

It uses traditional DBMS. It uses data warehousing.

It’s also called as OLTP assuming It is also called as OLAP assuming that information
that operation system. system.

It performs normal operation, It performs complex analysis on collected data.

insert, delete, update etc.

The size of the DB is The size of the data base is more compared to
comparatively small(100MB- OLTP(100GB-1TB)
1GB).

It contains current data. It contains current as well as historic data.

OLTP OLAP

It has more no. of users. It has less no. of users.

Access frequency of data is high. Access frequency of data is low.

Type of use is predictive and Type of use is ad-hoc.

repetitive.

It may update or delete data. It rarely updates, never deletes data.

Response time is in milli-second. Response time in is terms of seconds to minutes.

Types of users are clerk, DBA & Types of users are knowledge workers, managers
Data Base professionals. and CEO.

DB design is application oriented. DB design is subject oriented.

DB design is subject oriented. It provides query throughput.

At a time, limited no. of records At a time, millions of records can be retrieved.

can be retrieved.
OLTP OLAP

It is used to control and run It is used to help in planning, problem solving

fundamental tasks. decision support and reporting.

ER model is used. Dimensional modelling is used.

Allows read/write operations. Only read and rarely write.

Provides high performance. Provides high flexibility.

Briefly describe the following advanced DB Systems and applications: object-

relational DBs, spatial DBs, text DBs, multimedia DBs, WWW
Answer:
a) An objected-oriented DB is designed based on the OOP paradigm
where data are a large number of objects organized into classes and class hierarchies.
Each entity in the DB is considered as an object. The object contains a set of
variables that describe the object, a set of messages that the object can use to
communicate with other objects or with the rest of the DB system, and a set of
methods where each method holds the code to implement a message.
b) A spatial DB contains spatial-related data, which may be represented in the form
of raster or vector data. Raster data consists of n-dimensional bit maps or pixel maps,
and vector data are represented by lines, points, polygons or other kinds of processed
primitives, Some examples of spatial DB include geographical (map) DBs, VLSI
chip designs, and medical and satellite images DBs.
c) A text DB is a DB that contains text documents or other word descriptions in the
form of long sentences or paragraphs, such as product specifications, error or bug
reports, warning messages, summary reports, notes, or other documents.
d) A multimedia DB stores images, audio, and video data, and is used in applications
such as picture content-based retrieval, voice-mail systems, video-on-demand
systems, the WWW, and speech-based UI.
e) The WWW provides rich, world-wide, on-line information services, where data
objects are linked together to facilitate interactive access. Some examples of
distributed information services associated with the WWW include America Online,
Yahoo!, AltaVista, and Prodigy.

Data quality can be assessed in terms of accuracy, completeness, and

consistency. Propose two other dimensions of data quality.
Answer:
Other dimensions that can be used to assess the quality of data include timeliness,
believability, value added, interpretability and accessability, described as follows:
a) Timeliness: Data must be available within a time frame that allows it to be useful
for decision making.
b) Believability: Data values must be within the range of possible results in order to
be useful for decision making.
c) Value added: Data must provide additional value in terms of information that
offsets the cost of collecting and accessing it.
d) Interpretability: Data must not be complex. Information must be easily
understood.
e) Accessability: Data must be used with no or less effort so that we can collect
most benefit from it.
What is Data Warehousing?
DWg is the process of constructing and using a DW. A DW is constructed by
integrating data from multiple heterogeneous sources that support analytical
reporting, structured and/or ad hoc queries, and decision making. DWg involves data
cleaning, data integration, and data consolidations.
Benefits of Data Warehousing
The benefits can be classified into two:
a) Tangible benefits (quantified / measureable): It includes,
- Improvement in product inventory
- Decrement in production cost
- Improvement in selection of target markets
- Enhancement in asset and liability management
b) 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
Why a DW, usually separated from the original system. Why?

► It is designed for query and analysis rather than for transaction processing. It
usually contains historical data derived from transaction data, but it can include data
from other sources.
► It separates analysis workload from transaction workload and enables an
organization to consolidate data from several sources.

► A process of transforming data into information and making it available to users in

a timely enough manner to make a difference.
DW includes

★ Retrieving data
★ Analyzing data
★ Extracting data
★ Loading data
★ Transforming data
★ Managing data

Benefits

★ Maintains a copy of information from the source transaction systems

★ Congregate data from multiple sources into a single DB
★ Maintain data history, even if the source transaction systems do not.
★ Improve data quality, by providing consistent codes and descriptions, flagging or
even fixing bad data.
★ Present the organization's information consistently
★ Provide a single common data model for all data of interest regardless of the data's
source
★ Restructure the data so that it makes sense to the business users
★ Saves Time
★ Generates a High ROI

CHARACTERISTICS OF DW

► The concept of a DW was introduced by Bill Inmon, the father of DW. Here are
the characteristics :
★ Subject Orientation ★ Time variance ★ Non-Volatile ★ Integrated
Subject-Oriented: A DW can be used to analyze a particular subject area. For
example, "sales" can be a particular subject.
Integrated: A DW integrates data from multiple data sources. For example, source A
and source B may have different ways of identifying a product, but in a DW, there
will be only a single way of identifying a product.
Time-Variant: Historical data is kept in a DW. For example, one can retrieve data
from 3 months, 6 months, 12 months, or even older data from a DW. This contrasts
with a transactions system, where often only the most recent data is kept. For
example, a transaction system may hold the most recent address of a customer, where
a DW can hold all addresses associated with a customer.
Non-volatile: Once data is in the DW, it will not change. So, historical data in a DW
should never be altered.
In general, fast query performance with high data throughput is the key to a
successful DW.

DESCRIBE THE MAJOR COMPONENTS OF A DW

Key components
1. Data sourcing, cleanup, transformation, and migration tools
2. Metadata repository
3. Warehouse/database technology
4. Data marts
It is inexpensive tool and alternative to the DW. It is based on the subject area
. Data mart is used in the following situation:
- Extremely urgent user requirement
- The absence of a budget for a full scale data warehouse strategy
- The decentralization of business needs
5. Data query, reporting, analysis, and mining tools
Query and reporting tools: used to generate query and report. There are two types of
reporting tools. They are:
- Production reporting tool used to generate regular operational reports
- Desktop report writer are inexpensive desktop tools designed for end users.
Managed Query tools: used to generate SQL query.
Application development tools: This is a graphical data access environment which
integrates OLAP tools with DW and can be used to access all db systems.
OLAP Tools: Are used to analyze the data in multi dimensional and complex views.
Data mining tools: are used to discover knowledge from the DWg data

6. Data warehouse administration and management

The management of data warehouse includes,
- Security and priority management
- Monitoring updates from multiple sources
- Data quality checks
- Managing and updating meta data
- Auditing and reporting DW usage and status
- Purging data
- Replicating, sub setting and distributing data
- Backup and recovery
- DW storage management which includes capacity planning, hierarchical
storage management and purging of aged data etc.,

7. Information delivery system

(It is used to enable the process of subscribing for data warehouse info.
• Delivery to one or more destinations according to specified scheduling algorithm)
What is an ODS?
Operational Data Store is the DB from which a business operates on an on-going
basis.
Differentiate among a data mart, an ODS, and an EDW.
An ODS is the DB from which a business operates on an ongoing basis.
Both an EDW and a data mart are DW. An EDW (Enterprise DW) is an all-
encompassing DW that covers all subject areas of interest to the entire organization.
A data mart is a smaller DW designed around one problem, organizational function,
topic, or other suitable focus area.

BUILDING A DATA WAREHOUSE

DW Design Process:
A DW can be built using a top-down approach, a bottom-up approach, or a
combination of both.
The top-down approach starts with the overall design and planning. It is useful in
cases where the technology is mature and well known, and where the business
problems that must be solved are clear and well understood.
The bottom-up approach starts with experiments and prototypes. This is useful in the
early stage of business modeling and technology development. It allows an
organization to move forward at considerably less expense and to evaluate the
benefits of the technology before making significant commitments.
In the combined approach, an organization can exploit the planned and strategic
nature of the top-down approach while retaining the rapid implementation and
opportunistic application of the bottom-up approach.
To design an effective and efficient DW, we need to understand and analyze the
business needs and construct a business analysis framework. Each person has
different views regarding the design of a DW. These views are as follows −
 The top-down view − This view allows the selection of relevant information
needed for a DW.
 The data source view − This view presents the information being captured,
stored, and managed by the operational system.
 The DW view − This view includes the fact tables and dimension tables. It
represents the information stored inside the DW.
 The business query view − It is the view of the data from the viewpoint of the
end-user.

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

1. Choosing the subject matter

2. Deciding what a fact table represents

3. Identifying and conforming the dimensions

4. Choosing the facts

5. Storing pre calculations in the fact table

6. Rounding out the dimension table

7. Choosing the duration of the db

8. The need to track slowly changing dimensions

9. Deciding the query priorities and query models

PROCESS ARCHITECTURE

(a) Data Warehouse Architecture: Basic

A simple architecture for a data warehouse. End users directly access data derived from several source systems
through the data warehouse.

Architecture of a Data Warehouse

(b) Data Warehouse Architecture: with a Staging Area

Clean and process operational data before putting it into the warehouse. A staging area simplifies building
summaries and general warehouse management.

Architecture of a Data Warehouse with a Staging Area

(c) Data Warehouse Architecture: with a Staging Area and Data Marts
Although the architecture is quite common, we can customize our warehouse's
architecture for different groups within our organization by adding data marts, which
are systems designed for a particular line of business.

THREE TIER DW ARCHITECTURE:

1) Bottom Tier – It is the DW DB server. It is the relational DB system. We use the
back end tools and utilities to feed data into the bottom tier. These back end tools and
utilities perform the Extract, Clean, Load, and refresh functions.
2) Middle Tier − In the middle tier, we have the OLAP Server that can be
implemented in either of the following ways.
 By Relational OLAP (ROLAP), which is an extended relational DB
management system. The ROLAP maps the operations on multidimensional
data to standard relational operations.
 By Multidimensional OLAP (MOLAP) model, which directly implements the
multidimensional data and operations.
3) Top-Tier − This tier is the front-end client layer. This layer holds the query tools
and reporting tools, analysis tools and data mining tools. e.g., trend analysis,
prediction, and so on).

DESIGN CONSIDERATIONS
To be a successful DW designer must adopt a holistic approach that is considering
all DW components as parts of a single complex system, and take into account all
possible data sources and all known usage requirements.
Most successful DWs that meet these requirements have these common
characteristics:
Are based on a dimensional model
Contain historical and current data
Include both detailed and summarized data
Data warehouse is difficult to build due to the following reason:
Heterogeneity of data sources
Use of historical data
Growing nature of data base
** Data warehouse design approach muse be business driven, continuous and
iterative engineering approach.

DW MODELS
From the perspective of DW architecture, we have the following DW models −

 Virtual Warehouse
 Data mart
 Enterprise Warehouse
(a) Virtual Warehouse
The view over an operational DW is known as a virtual WH. It is easy to build a
virtual WH. Building a virtual WH requires excess capacity on operational DB
servers.
(b) Data mart contains a subset of organization-wide data. This subset of data is
valuable to specific groups of an organization.
For example, the marketing data mart may contain data related to items, customers,
and sales. Data marts are confined to subjects.
(c) Enterprise Warehouse
 Collects all the information and the subjects spanning an entire organization

 Data is integrated from operational systems & external information providers.

 This information can vary from a few GB to hundreds of GBs, TBs or beyond.

DATA MART
A data mart is a repository of data that is designed to serve a particular community of
knowledge workers.

Difference between a DW and a data mart

The difference between a DW and a data mart can be confusing because the two
terms are sometimes used incorrectly as synonyms. A DW is a central repository for
all an organization's data. The goal of a data mart, however, is to meet the particular
demands of a specific group of users within the organization, such as human resource
management (HRM). Generally, an organization's data marts are subsets of the
organization's DW.

Points to remember about data marts −

 Window-based or Unix/Linux-based servers are used to implement data marts.
They are implemented on low-cost servers.
  The implementation data mart cycles is measured in short periods of time, i.e.,
in weeks rather than months or years.
 Data marts are small in size.
 Data marts are customized by department.
 The source of a data mart is departmentally structured DW.
 Data mart are flexible.

Note − Do not data mart for any other reason since the operation cost of data
marting could be very high. Before data marting, make sure that data marting
strategy is appropriate for your particular solution.
Data marts are of two types – Dependent and Independent.
Dependent Data Mart – This data mart depends on the enterprise DW and works in
top-down manner.
Independent Data Mart – This data mart does not depend on the enterprise DW and
works in bottom-up manner.

Benefits of Data Marts

 Allows the data to be accessed in lesser time
 Cost-efficient alternative to the bulky DW
 Easy to use as designed according to the needs of specific user group
 Fastens the business processes.

DATA MARTING
The practice of collecting relevant data into a single DB for a particular function,
department, etc., of a commercial organization, so that it can be easily accessed by
users.

MAPPING DW TO A MULTIPROCESSOR ARCHITECTURE

The functions of DW are based on the relational DB technology. The RDB
technology is implemented in parallel manner. There are two advantages of having
parallel RDB for DW:
- Linear Speed up: ability to increase the number of processor to reduce
response time
- Linear Scale up: ability to provide same performance on the same requests as
the DB size increases
• The goals of linear performance and scalability can be satisfied by parallel H/W
architectures, parallel OSs, and parallel DBMSs.
• Parallel hardware architectures are based on Multi-processor systems designed as a
Shared-memory model, Shared-disk model or distributed-memory model.
• Parallelism can be achieved in three different ways:
– Horizontal Parallelism: Which means that the data base is partitioned across
multiple disks and the parallel processing occurs in the specific tasks, that is
performed concurrently on different processors against different sets of data
– Vertical Parallelism: Which occurs among different tasks all components query
operations are executed in parallel in a pipelined fashion. In other words an output
from one task becomes an input into another task as soon as records become
available.
 - Data Partitioning: It spreads data from DB tables across multiple disks so that
I/O operations such as read and write can be performed in parallel.
Database Architecture for parallel Processing
1. Shared-Memory Architecture
Also called as shared-everything style .Traditional approach to implement an
RDBMS on SMP hardware. Simple to implement. The key point of this approach is
that a single RDBMS server can potentially utilize all processors, access all memory,
and access the entire DB, thus providing the user with a consistent single system
image

2. Shared-disk Architecture
It implements the concept of shared ownership of the entire DB between RDBMS
servers, each of which is running on a node of distributed memory system. Each
RDBMS server can read, write, update and delete records from the same shared DB,
which would require the system to implement a form of distributed lock manager
(DLM).

Pining: In worst case scenario, if all nodes are reading and updating same data, the
RDBMS and its DLM will have to spend a lot of resources synchronizing multiple
buffer pool. This problem is called as pining
Data skew: Uneven distribution of data Shared-disk architectures can reduce
performance bottle-necks resulting from data skew
3. Shared-Nothing Architecture
The data is partitioned across many disks, and DBMS is “partitioned” across multiple
conservers, each of which resides on individual nodes of the parallel system and has
an ownership of its own disk and thus, its own data base partition.
It offers non-linear scalability. These requirements includes
Support for function shipping
Parallel join strategies
Support for data repartitioning
Query compilation
Support for data base transactions
Support for the single system image of the data base environment.
4. Combined Architecture
Interserver parallelism of the distributed memory architecture means that each query
is parallelized across multiple servers. While intraserver parallelism of the shared
memory architecture means that a query is parallelized with in the server.

Parallel RDBMS Feature

Scope and techniques of parallel DBMS operations
Optimized implementation
Application transparency
The parallel environment
DBMS Management tools
Price/Performance
Alternative Technologies
In addition to parallel DB, a number of vendors are working on other solutions
improving performance in DWg environments. These includes
Advanced database indexing products
Specialized RDBMS designed especially for the DWg
Multidimensional data bases
Parallel DBMS Vendors
1. Oracle
2. Informix
3. IBM
4. Sybase
5. Microsoft

MULTIDIMENSIONAL DATA MODEL

 The MDDM is an integral part of OLAP. Because OLAP is on-line, it must provide answers
quickly; analysts pose iterative queries during interactive sessions, not in batch jobs that run
overnight. And because OLAP is also analytic, the queries are complex. The MDDM is
designed to solve complex queries in real time.

A DW is based on a MDDM model which views data in the form of a data cube

 A data cube, such as sales, allows data to be modeled and viewed in multiple dimensions
  Dimension tables, such as item (item name, brand, type), or time(day, week, month,
quarter, year) . As number of dimensions increases number of cubes cell increase
exponentially. Dimensions are hierarchical in nature i.e. time dimension may contain
hierarchies for years, quarters, months, weak and day.

 Fact table contains measures (such as dollars sold) and keys to each of the related
dimension tables

 In data warehousing literature, an n-D base cube is called a base cuboid. The top most 0-D
cuboid, which holds the highest-level of summarization, is called the apex cuboid. The
lattice of cuboids forms a data cube.

 Modeling data warehouses: dimensions & measures

 Star schema: It is a relational schema organized around a central table joined to few
smaller tables (dimension tables)using foreign key references

It contains:

A large central table (fact table)

A set of smaller attendant tables (dimension table), one for each dimension

Problem: Since the fact table must carry all key components as part of its primary
key, addition or deletion of levels in the physical modification of the affected table.
 Snowflake schema: A refinement of star schema where some dimensional hierarchy
is normalized into a set of smaller dimension tables, forming a shape similar to
snowflake.

The snowflake schema contains separate fact tables for each level of aggregation. So
it is impossible to make a mistake of selecting product detail.

However, the snowflake structure can reduce the effectiveness of browsing, since
more joins will be needed

 Fact constellations: Multiple fact tables share dimension tables, viewed as a

collection of stars, therefore called galaxy schema or fact constellation
 ** The main shortcoming of the fact constellation schema is a more complicated design
because many variants for particular kinds of aggregation must be considered and selected.
Moreover, dimension tables are still large.

 OLAP Operations:

 Roll up (drill-up): summarize data

 by climbing up hierarchy or by dimension reduction

 Drill down (roll down): reverse of roll-up

 from higher level summary to lower level summary or detailed data, or introducing
new dimensions

 Slice and dice: project and select

 Pivot (rotate):

 reorient the cube, visualization, 3D to series of 2D planes

 Other operations

 drill across: involving (across) more than one fact table

CONCEPT HIERARCHY in DW
Hierarchy represents the relationship between data elements in a such a way that they can relate to each other as
one above another, one below another. Basically it represents the how each attribute each related to each other in
terms of level.

Classic example of hierarchy which you would see in almost every data warehouse design in Date hierarchy
where which forms a relationship as Year->Month->Day-Week etc.

Generally end users like to browse the data by hierarchy as it helps him to explore
data from of less detailed to more detailed way.
e.g. one might start exploring a Revenue by Year and after seeing a revenue drop in
particular year he. She may click on a year and go further drill down to see which
quarter is worst quarter in that year in terms of revenue drop.
Points to note about Hierarchies.
1. You can have multiple hierarchies in a table
e.g. Calendar data hierarchy

2. Hierarchies are useful to define the drill down/drill up navigation path

in BI tools for reporting purpose
3. There are three main types of hierarchies in a general dimensional data
warehouse design.
(a) Balanced Hierarchy. (b) Unbalanced Hierarchy (c) Ragged Hierarchy
(Is a user-defined hierarchy that has an uneven number of levels. Common
example include geographic hierarchies composed of Country-Region-City,
where some cities lack a parent State or Province, such as Washington D.C.,
Vatican City, or New Delhi)

Hierarchies impose a family structure on dimension values. For a particular level value,
a value at the next higher level is its parent, and values at the next lower level are its
children. These familial relationships enable analysts to access data quickly.