Data Warehousing and

Mining
Course Code: CSC504

[Link]
 Module-1
Data Warehousing and
Fundamentals
Why Data-Warehousing?
What is a Data Warehouse?
 Data-Warehousing Definition
• Data Warehouse stores huge amount of data, which is typically collected from
multiple heterogeneous sources like files, DBMS, etc.

• The goal is to produce statistical results that may help in decision making.

• The term Data Warehouse was defined by Bill Inmon in 1990, in the following
way:
A Data Warehouse is a subject-oriented, integrated, non-volatile, and time
variant collection of data in support of management’s decision making process.
 Data-Warehousing contd..
• A Data Warehouse is a group of data specific to the entire organization, not only
to a particular group of users.

• It is not used for daily operations and transaction processing but used for making
decisions.
 Data-Warehousing Contd..
Subject-Oriented Data:
Data that gives information about a particular subject instead of about a
company’s operations.
 Data-Warehousing Contd..
Integrated Data:
A data warehouse integrates various heterogeneous data sources like
RDBMS, flat files, and online transaction records.
• It requires performing data cleaning and integration during data warehousing to
ensure consistency in naming conventions, attributes types, etc., among different
data sources.
 Data-Warehousing contd..
Time Variant Data:
• Historical data is kept in a data warehouse. For example, one can retrieve data
from 3 months, 6 months, 12 months, or even older data from a data warehouse.
• This contrasts with a transactions system, where often only the most recent data is
kept.

Non-volatile:
• Once data is in the data warehouse, it will not change.
• So, historical data in a data warehouse should never be altered.
 Dimensional Modeling
• Dimensional modeling represents data with a cube operation that allows data to be
modeled and viewed in multiple dimensions.
• Dimensional Modeling (DM) is a data structure technique optimized for data
storage in a Data warehouse.
• The purpose of dimensional modeling is to optimize the database for faster
retrieval of data.
• The concept of Dimensional Modeling was developed by Ralph Kimball and
consists of “fact” and “dimension” tables.
• A dimensional model in data warehouse is designed to read, summarize, analyze
numeric information like values, balances, counts, weights, etc.
• In contrast, relation models are optimized for addition, updating and deletion of
data in a real-time Online Transaction System.
• In dimensional modeling, the transaction record is divided into either "facts," which
are frequently numerical transaction data, or "dimensions," which are the reference
information that gives context to the facts.

❖ Dimensions:
• Dimensions are the perspectives or entities with respect to which an organization
wants to keep records.
• For example, All Electronics may create a sales data warehouse in order to keep
records of the store’s sales with respect to the dimensions time, item, branch, and
location.
• These dimensions allow the store to keep track of things like monthly sales of items
and the branches and locations at which the items were sold.
• Each dimension may have a table associated with it, called a dimension table, which
further describes the dimension. For example, a dimension table for item may
contain the attributes item name, brand, and type.
❖Facts:
• Facts are numeric measures.
• Facts are quantities by which we can analyze relationships between dimensions.
• Examples of facts for a sales data warehouse include dollars sold (sales amount in
dollars), units sold (number of units sold), and amount budgeted.
• The fact table contains the names of the facts, or measures, as well as keys to each
of the related dimension tables.
Fig: Formation of the automaker sales fact table
Fig: Formation of the automaker dimension tables
Fig: Supermarket Example
 Dimensional Model Vs ER Model
Sr. E-R Modeling Dimensional Modeling
No.
1. Suitable for Online Transaction Processing Suggested for Data Warehousing applications.
(OLTP) application.
2. It consists of Entities and Relationships It consists of Facts and Dimensions.

3. It removes data redundancy. It permits data redundancy.

4. Data Storage: Volatile Data Storage : Non-volatile

5. It is data oriented. It is business-oriented.

 Benefits of Data Warehousing
1. Data Warehouse enable end-users to access wide variety of data.
2. Business analyst and decision makers can analyze the current trends in the
market to predict future trends.
3. Data Warehouse provides consistent data.
4. It helps to increase the productivity and decrease computing costs.
5. Data Warehouse contain data that has been integrated from a number of
different sources.
6. The results obtained can be presented in a variety of formats in the form of
reports, graphs, etc.
7. Data Warehouse users can obtain trend reports.
8. Data Warehouses enhance the value of operational business applications,
notably customer relationship management (CRM) systems.
 Features of Data Warehouse
1. Subject Oriented
2. Integrated
3. Non-Volatile
4. Time-Variant
 Data Warehouse and Data Marts
❖Data Mart:
• A data mart contains a subset of corporate-wide data that is of value to a specific
group of users.
• The scope is confined to specific selected subjects.
• For example, a marketing data mart may confine its subjects to customer, item,
and sales.
• The data contained in data marts tend to be summarized.
• Data marts are usually implemented on low-cost departmental servers that are
Unix/Linux or Windows based.
• The implementation cycle of a data mart is more likely to be measured in weeks
rather than months or years.
• However, it may involve complex integration in the long run if its design and
planning were not enterprise-wide.
Data Mart Contd..
Depending on the source of data, data marts can be categorized as
independent or dependent.
• Independent data marts are sourced from data captured from one or more
operational systems or external information providers, or from data generated
locally within a particular department or geographic area.
• Dependent data marts are sourced directly from enterprise data warehouses.
 Data Warehousing Design strategies
• A data warehouse can be built using a top-down approach, a bottom-up approach, or a
combination of both.
❖ Top-down approach:
• The top-down approach starts with 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.
 Data Warehousing Design strategies
❖Bottom-up approach:
• 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 technological benefits before making significant commitments.
 Data Warehousing Design strategies contd..
❖ Combined Approach:
• 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.

From the software engineering point of view, the design and construction of a
data warehouse may consist of the following steps:
• planning,
• requirements study,
• problem analysis,
• warehouse design,
• data integration and testing, and
• finally deployment of the data warehouse.
 Data Warehousing Design strategies contd..
In general, the warehouse design process consists of the following steps:
1. Choose a business process to model (e.g., orders, invoices, shipments, inventory,
account administration, sales, or the general ledger). If the business process is
organizational and involves multiple complex object collections, a data warehouse
model should be followed. However, if the process is departmental and focuses on
the analysis of one kind of business process, a data mart model should be chosen.
2. Choose the business process grain, which is the fundamental, atomic level of data to
be represented in the fact table for this process (e.g., individual transactions,
individual daily snapshots, and so on).
3. Choose the dimensions that will apply to each fact table record. Typical dimensions
are time, item, customer, supplier, warehouse, transaction type, and status.
4. Choose the measures that will populate each fact table record. Typical measures are
numeric additive quantities like dollars sold and units sold.
 The Star Schema
• The most popular data model for a data warehouse is a multidimensional model,
which can exist in the form of a star schema, a snowflake schema, or a fact
constellation schema.

❖Star schema:
• The most common modeling paradigm is the star schema, in which the data
warehouse contains:
i. a large central table (fact table) containing the bulk of the data, with no
redundancy, and
ii. a set of smaller attendant tables (dimension tables), one for each dimension.
• The schema graph resembles a starburst, with the dimension tables displayed in a
radial pattern around the central fact table
 Characteristics of Star Schema:

• Every dimension in a star schema is represented with the only one-

dimension table.
• The dimension table should contain the set of attributes.
• The dimension table is joined to the fact table using a foreign key
• The dimension table are not joined to each other
• Fact table would contain key and measure
• The Star schema is easy to understand and provides optimal disk
usage.
• The schema is widely supported by BI Tools
 The Star Schema contd..

Example 1.1: Star schema.

AllElectronics company have sales department. Sales are considered along
four dimensions: time, item, branch, and location. The schema contains a central
fact table for sales that contains keys to each of the four dimensions, along with two
measures: dollars sold and units sold. Design a star schema for the same.
 The Star Schema contd..
Soln:

Fig: Star schema of sales data warehouse

 The Star Schema contd..
Example 1.2: Star schema.
Mumbai University wants you to help design a star schema to record grades for
course completed by students. There are four dimension tables namely course_ section,
professor, student, period with attributes as follows:
1. Course_Section attributes: course_id, section_number, Course_name, Units,
Room_id, Room capacity.
2. Professor attributes:prof_id,Prof_name, Title, Department_id, Department_name
3. Student attributes: student_id, student_name, Major.
4. Period Semester: semester_id, year.
The only fact that is to be recorded in the fact table is course grade.
a) Design a star schema for this problem.
 The Star Schema contd..
Soln:

Fig: University Star Schema

 The Snowflake Schema
• The snowflake schema is a variant of the star schema model, where some
dimension tables are normalized, thereby further splitting the data into additional
tables.
• The resulting schema graph forms a shape similar to a snowflake.
• The major difference between the snowflake and star schema models is that the
dimension tables of the snowflake model may be kept in normalized form to
reduce redundancies.
• Such a table is easy to maintain and saves storage space.
• However, this space savings is negligible in comparison to the typical magnitude
of the fact table.
• Furthermore, the snowflake structure can reduce the effectiveness of browsing,
since more joins will be needed to execute a query.
 Characteristics of Snowflake Schema:

• The main benefit of the snowflake schema it uses smaller disk space.
• Easier to implement a dimension is added to the Schema
• Due to multiple tables query performance is reduced
• The primary challenge that you will face while using the snowflake
Schema is that you need to perform more maintenance efforts because
of the more lookup tables.
 The Snowflake Schema contd..
• Consequently, the system performance may be adversely impacted. Hence, although
the snowflake schema reduces redundancy, it is not as popular as the star schema in
data warehouse design.

• Example 1.4: Snowflake schema for AllElectronics sales .

• Here, the sales fact table is identical to that of the star schema (Example 1.1).
• The main difference between the two schemas is in the definition of dimension tables.
• The single dimension table for item in the star schema is normalized in the snowflake
schema, resulting in new item and supplier tables.
• For example, the item dimension table now contains the attributes item key, item
name, brand, type, and supplier key, where supplier key is linked to the supplier
dimension table, containing supplier key and supplier type information.
 The Snowflake Schema contd..
❖Snowflake schema of a sales data warehouse.
 The Snowflake Schema contd..
• Similarly, the single dimension table for location in the star schema can be
normalized into two new tables: location and city.
• The city key in the new location table links to the city dimension.
• Notice that, when desirable, further normalization can be performed on province or
state and country in the snowflake schema
 The Snowflake Schema contd..
Example 1.5: Snowflake schema.
Mumbai University wants you to help design a snowflake schema to record
grades for course completed by students. There are four dimension tables namely
course_ section, professor, student, period with attributes as follows:
1. Course_Section attributes: course_id, section_number, Course_name, Units,
Room_id, Room capacity.
2. Professor attributes:prof_id,Prof_name, Title, Department_id, Department_name
3. Student attributes: student_id, student_name, Major.
4. Period Semester: semester_id, year. The database will contain data for 30
months period.
The only fact that is to be recorded in the fact table is course grade.
a) Design a Snowflake schema for this problem.
 The Snowflake Schema contd..
Soln:
• Courses are conducted in different rooms, so course dimension can be further
normalized to rooms dimension.
• Professor belongs to a department, and department dimension is not added in the
star schema, so professor dimension can be further normalized to department
dimension.
• Similarly students can have different major subjects, so it can also be normalized
to major dimension.
The Snowflake Schema contd..

Fig: University Snowflake Schema

 Star Schema Snowflake Schema
Hierarchies for the dimensions are stored in the
Hierarchies are divided into separate tables.
dimensional table.

One fact table surrounded by dimension table which are in

It contains a fact table surrounded by dimension tables.
turn surrounded by dimension table
In a star schema, only single join creates the
relationship between the fact table and any dimension A snowflake schema requires many joins to fetch the data.
tables.
Simple DB Design. Very Complex DB Design.
Denormalized Data structure and query also run faster. Normalized Data Structure.
High level of Data redundancy Very low-level data redundancy
Single Dimension table contains aggregated data. Data Split into different Dimension Tables.

Cube processing might be slow because of the complex

Cube processing is faster.
join.

Offers higher performing queries using Star Join Query

The Snowflake schema is represented by centralized fact
Optimization.
table which unlikely connected with multiple dimensions.
Tables may be connected with multiple dimensions.
 Fact Constellation Schema
• Fact Constellation is a schema for representing multidimensional
model. It is a collection of multiple fact tables having some common
dimension tables. It can be viewed as a collection of several star
schemas and hence, also known as Galaxy schema. It is one of the
widely used schema for Data warehouse designing and it is much
more complex than star and snowflake schema. For complex systems,
we require fact constellations.
Example
 Fact Tables and Dimension Tables
A dimensional model consists of fact tables and dimension tables.
❖ Fact Table:
• Each dimensional model has a primary table which is a fact table that is meant to
contain the business measurements.
• Numeric and additive are the most useful facts.
• A fact table has many to many relationships; it contains a set of two or more
foreign keys that join to a dimension table.
• A fact in the fact table depends on many facts. For e.g. In sales schema,
sales_amount fact depends on product, location and time. These factors are called
as dimensions.
- In sales fact table we may also include other facts like sales_unit and cost.
 Fact Tables and Dimension Tables contd..
❖Dimension Table:
• Dimensions are factors on which a given fact depends.
• The sales_amount fact can also be thought of as a function of three variables.
sales_amount = (product, location, time)
• Dimension tables are companion tables to a fact table in star schema.
• Each dimension table is defined by its primary key that serves as the basis for
referential integrity with any given fact table to which it is joined.
• Most dimensional tables contain textual information.
 Factless Fact Tables
• A factless fact table is a fact table that does not have any measures.
• A fact table is said to be empty if it has no measures. It is designed to only record
the occurrences of events.
• A fact table contains numeric values or facts stored in them but some fact tables
do not have any facts at all and they may consist of nothing but keys. These are
called factless fact tables.
• Such fact tables are used to record events.
• Thus, many event-tracking tables in the data warehouse are rather factless fact
tables.
Factless Fact Tables contd..

Fig. Factless Fact Table

 Updates to Dimension Tables
• Over time, the fact table will keep growing with every new transaction
that takes place.
• For example, every day with the occurrence of every individual sales
transaction, new rows will get added to the fact table.
• Very rarely are the rows in a fact table updated with changes.
 Primary Keys, Surrogate Keys and Foreign
Keys
❖ Primary Keys:
• The primary key of the dimension table is one of the attribute value which
identifies each row in a dimension table uniquely.
• Foe example, in the customer dimension table, the primary key (customer_id)
identifies each individual customer uniquely.
• In the product dimension table, the product_key identifies every product uniquely.
Primary Keys, Surrogate Keys and Foreign
Keys

Fig. Primary key of operation table

 Primary Keys, Surrogate Keys and Foreign
Keys
❖ Surrogate Keys:
• Surrogate keys are system generated sequence numbers.
• They do not have any built in meanings.
• All data Warehouse keys must be meaningless surrogate keys.
• One must not use the original production keys.
• A four byte integer makes a good surrogate key.
• A surrogate key is the primary key for a dimension table.
• A surrogate key is independent of operational system keys.
• Every join between dimension tables and fact tables should be made using
surrogate keys.
 Primary Keys, Surrogate Keys and Foreign
Keys
❖Foreign Keys:
• Every dimension table has one-to-many relationship with the fact table.
• So the primary key of each dimension table is a foreign key in the fact table.
OLAP (On-line Analytical Processing)
• Online Analytical Processing Server (OLAP) is based on the
multidimensional data model.

• OLAP is a category of software technology that enables analysts,

managers, and executives to gain insight into the data through fast,
consistent, interactive access in a wide variety of possible views of
information that has been transformed from raw data to reflect the
real dimensionality of the enterprise as understood by the clients.

• The main aim of OLAP is to provide multidimensional analysis to the

underlying data.
 OLAP contd..
• Consider the data of a shop for items sold per quarter in the city of Delhi. The
data is shown in the table.
• In this 2D representation, the sales for Delhi are shown for the time dimension
(organized in quarters) and the item dimension (classified according to the types
of an item sold).
• The fact or measure displayed in rupee_sold (in thousands).
 OLAP contd..
• Now, if we want to view the sales data with a third dimension, For example,
suppose the data according to time and item, as well as the location is considered
for the cities Chennai, Kolkata, Mumbai, and Delhi.
• These 3D data are shown in the table. The 3D data of the table are represented as a
series of 2D tables.
 OLAP contd..
• Conceptually, it may also be represented by the same data in the form of a 3D data
cube, as shown in fig:
 OLAP Operations
• OLAP stands for Online Analytical Processing Server. It is a software technology
that allows users to analyze information from multiple database systems at the
same time. It is based on multidimensional data model and allows the user to
query on multi-dimensional data (eg. Delhi -> 2018 -> Sales data). OLAP
databases are divided into one or more cubes and these cubes are known as Hyper-
cubes
There are five basic analytical operations that can be performed on an
OLAP cube:
1. Drill down
2. Roll up
3. Dice
4. Slice
5. Pivot
 Drill down
In drill-down operation, the less detailed data is converted into highly detailed data. It can be done
by: Moving down in the concept hierarchy
• Adding a new dimension
• In the cube given in overview section, the drill down operation is performed by moving down in
the concept hierarchy of Time dimension (Quarter -> Month).
 Roll up
It is just opposite of the drill-down operation. It performs aggregation on the OLAP
cube. It can be done by: Climbing up in the concept hierarchy
• Reducing the dimensions
• In the cube given in the overview section, the roll-up operation is performed by
climbing up in the concept hierarchy of Location dimension (City -> Country).
 Dice
It selects a sub-cube from the OLAP cube by selecting two or more dimensions. In
the cube given in the overview section, a sub-cube is selected by selecting following
dimensions with criteria:Location = “Delhi” or “Kolkata”
• Time = “Q1” or “Q2”
• Item = “Car” or “Bus”
 Pivot
It is also known as rotation operation as it rotates the current view to get
a new view of the representation. In the sub-cube obtained after the slice
operation, performing pivot operation gives a new view of it.
 Slice
It selects a single dimension from the OLAP cube which results in a new sub-cube
creation. In the cube given in the overview section, Slice is performed on the
dimension Time = “Q1”.
Data Warehouse Architecture
 Data Warehouses adopt a three-tier architecture:

❖Bottom Tier:
• It is a warehouse database server, that is generally a RDBMS.
• Using Application Programming interfaces (called as gateways), data is extracted
from operational and external sources.
• Gateways like, ODBC(Open Database Connection), OLE-DB (Open linking and
embedding for database), JDBC (Java Database Connection) is supported by
underlying DBMS.
• The tier also contains metadata repository, which stores information about the data
warehouse and its contents.
• Metadata are data about data.
• Metadata are the data that define warehouse objects.
❖Middle Tier:
• The middle tier is an OLAP server that is typically implemented using either
1) a relational OLAP (ROLAP) model (i.e., an extended relational DBMS that
maps operations on multidimensional data to standard relational operations); or
2) a multi-dimensional OLAP (MOLAP) model (i.e., a special-purpose server that
directly implements multidimensional data and operations).
• OLAP servers present business users with multidimensional data from data
warehouses or data marts, without concerns regarding how or where the data are
stored.

❖Top Tier:
• The top tier is a front-end client layer, which contains query and reporting tools,
analysis tools, and/or data mining tools (e.g., trend analysis, prediction, and so
on).
 Types of OLAP Servers
Four types of OLAP servers −
1. Relational OLAP (ROLAP)
2. Multidimensional OLAP (MOLAP)
3. Hybrid OLAP (HOLAP)
4. Specialized SQL Servers
Relational OLAP
ROLAP servers are placed between relational back-end server and client
front-end tools. To store and manage warehouse data, ROLAP uses
relational or extended-relational DBMS.

ROLAP includes the following −

• Implementation of aggregation navigation logic.
• Optimization for each DBMS back end.
• Additional tools and services.
Multidimensional OLAP
• MOLAP uses array-based multidimensional storage engines for multidimensional
views of data. With multidimensional data stores, the storage utilization may be
low if the data set is sparse. Therefore, many MOLAP server use two levels of
data storage representation to handle dense and sparse data sets.
Hybrid OLAP
• Hybrid OLAP is a combination of both ROLAP and MOLAP. It offers higher
scalability of ROLAP and faster computation of MOLAP. HOLAP servers allows to
store the large data volumes of detailed information. The aggregations are stored
separately in MOLAP store.
Specialized SQL Servers
• Specialized SQL servers provide advanced query language and query processing
support for SQL queries over star and snowflake schemas in a read-only
environment.