Unit II Data

Warehousing
By – Sayali B. Shinde
 Introduction to Data
Warehouse
• A data warehouse is a centralized system used for storing
and managing large volumes of data from various sources.
It is designed to help businesses analyze historical data and
make informed decisions. Data from different operational
systems is collected, cleaned, and stored in a structured
way, enabling efficient querying and reporting.
• Goal is to produce statistical results that may help in
decision-making.
• Ensures fast data retrieval even with the vast datasets.
Data Warehouse Architecture
and its components
• A Data Warehouse is a system that combine data from multiple
sources, organizes it under a single architecture, and helps
organizations make better decisions. It simplifies data handling,
storage, and reporting, making analysis more efficient. Data
Warehouse Architecture uses a structured framework to manage and
store data effectively.
• There are two common approaches to constructing a data
warehouse:
• Top-Down Approach: This method starts with designing the overall
data warehouse architecture first and then creating individual data
marts.
• Bottom-Up Approach: In this method, data marts are built first to
meet specific business needs, and later integrated into a central data
warehouse.
 Components of Data
Warehouse Architecture
• A data warehouse architecture consists of several key components
that work together to store, manage, and analyze data.
• External Sources: External sources are where data originates.
These sources provide a variety of data types, such as structured
data (databases, spreadsheets); semi-structured data (XML, JSON)
and unstructured data (emails, images).
• Staging Area: The staging area is a temporary space where raw data
from external sources is validated and prepared before entering the
data warehouse. This process ensures that the data is consistent and
usable. To handle this preparation effectively, ETL (Extract,
Transform, Load) tools are used.
• Extract (E): Pulls raw data from external sources.
• Transform (T): Converts raw data into a standard, uniform format.
• Load (L): Loads the transformed data into the data warehouse for
further processing.
 Contd…
• Data Warehouse: The data warehouse acts as the central repository
for storing cleansed and organized data. It contains metadata and
raw data. The data warehouse serves as the foundation for advanced
analysis, reporting, and decision-making.
• Data Marts: A data mart is a subset of a data warehouse that stores
data for a specific team or purpose, like sales or marketing. It helps
users quickly access the information they need for their work.
• Data Mining: Data mining is the process of analyzing large datasets
stored in the data warehouse to uncover meaningful patterns, trends,
and insights. The insights gained can support decision-making,
identify hidden opportunities, and improve operational efficiency.
 Data Cube or OLAP
approach in Data Mining
• Online Analytical Processing (OLAP) refers to software tools used for the
analysis of data in business decision-making processes.
• OLAP systems generally allow users to extract and view data from various
perspectives, many times they do this in a multidimensional format which
is necessary for understanding complex interrelations in the data.
• These systems are part of data warehousing and business intelligence,
enabling users to do things like trend analysis, financial forecasting, and
any other form of in-depth data analysis.
OLAP Examples
Any type of Data Warehouse System is an OLAP system. The uses of the
OLAP System are described below.
• Spotify personalizes homepages with custom songs and playlists based on
user preferences.
• Netflix movie recommendation system.
 Contd…
Benefits of OLAP Services
• Helps in keeping consistency and performing calculation on data.
• Can store planning, analysis, and budgeting for business analytics within one
platform.
• Efficiently handle large volumes of data, making them suitable for enterprise-
level business applications.
• Assist in applying security restrictions for data protection.
• Provide a multidimensional view of data, which helps in applying operations
on data in various ways.
Drawbacks of OLAP Services
• Requires professionals to handle the data because of its complex modeling
procedure.
• Expensive to implement and maintain in cases when datasets are large.
• Data analysis occurs only after extraction and transformation, leading to system
delays.
• Not efficient for decision-making, as it is updated on a periodic basis.
 Online Transaction
Processing (OLTP)
• Online Transaction Processing, commonly known as OLTP, is
a data processing approach emphasizing real-time execution of
transactions.
• The majority of OLTP systems are meant to manage numerous
short atomic operations that keep databases in line.
• To maintain transaction integrity and reliability, these systems
support ACID (Atomicity, Consistency, Isolation, Durability)
properties.
• It is through this that numerous unavoidable applications run
their critical courses like online banking, reservation systems
etc.
 Contd…
OLTP Examples
• An example considered for OLTP System is ATM Center a
person who authenticates first will receive the amount first and
the condition is that the amount to be withdrawn must be
present in the ATM. The uses of the OLTP System are
described below.
• ATM center is an OLTP application.
• OLTP handles the ACID properties during data transactions
via the application.
• It's also used for Online banking, Online airline ticket booking,
sending a text message, add a book to the shopping cart.
 Contd…
Benefits of OLTP Services
• Allow users to quickly read, write, and delete data operations.
• Support an increase in users and transactions for real-time data
access.
• Provide better data protection through multiple security features.
• Aid in decision-making with accurate, up-to-date data.
• Ensure data integrity, consistency, and high availability.
Drawbacks of OLTP Services
• Limited analysis capability, not suited for complex analysis or
reporting.
• High maintenance costs due to frequent updates, backups, and
recovery.
• Susceptible to disruption during hardware failures, impacting
online transactions.
• Prone to issues like duplicate or inconsistent data.
 Data mart
• Data mart is such a storage component which is concerned on
a specific department of an organization. It is a subset of the
data stored in the data warehouse.
• Data mart is focused only on particular function of an
organization and it is maintained by single authority only, e.g.
finance, Marketing. Data Marts are small in size and are
flexible.
 Types of Data Mart:
There are three types of data marts:
1. Dependent Data Mart -
 Contd…
• Dependent Data Mart is created by extracting the data from
central repository, Data warehouse.
• First data warehouse is created by extracting data (through
ETL tool) from external sources and then data mart is created
from data warehouse. Dependent data mart is created in top-
down approach of data warehouse architecture.
• This model of data mart is used by big organizations.
 Contd…
2. Independent Data Mart –
• Independent Data Mart is created directly from external
sources instead of data warehouse.
• First data mart is created by extracting data from external
sources and then datawarehouse is created from the data
present in data mart.
• Independent data mart is designed in bottom-up approach of
datawarehouse architecture.
• This model of data mart is used by small organizations and is
cost effective comparatively.
 3. Hybrid Data Mart -
• This type of Data Mart is created by extracting data from
operational source or from data warehouse.
• 1Path reflects accessing data directly from external sources
and 2Path reflects dependent data model of data mart.
 Fact table & Dimension
table
• Fact tables and dimension tables are key components of a
schema in a data warehouse.
• A fact table contains records that combine attributes from
different dimension tables. These records allow users to
analyze different aspects of their business, which can aid in
decision-making and improving the business.
What is a Fact Table?
• In a data warehouse, a fact table is a table that stores the
measurements, metrics, or facts related to a business
operation.
• It is located at the center of a star or snowflake schema and is
surrounded by dimension tables.
 Contd…
What is a Dimension Table?
• Dimension tables contain descriptions of the objects in a fact
table and provide information about dimensions such as
values, characteristics, and keys.
• These tables are usually small, with a number of rows ranging
from a few hundred to a few thousand.
 OLAP Cube
• OLAP Cube is a data structure that allows fast analysis of
data according to the multiple Dimensions that define a
business problem.
• A multidimensional cube for reporting sales might be, for
example, composed of 7 Dimensions: Salesperson, Sales
Amount, Region, Product, Region, Month, Year.
 OLAP Operations
• Since OLAP servers are based on multidimensional view of
data,
• Here is the list of OLAP operations −
• Roll-up
• Drill-down
• Slice and dice
• Pivot (rotate)
 Contd…
Roll-up
• Roll-up performs aggregation on a data cube in any of the
following ways −
• By climbing up a concept hierarchy for a dimension
• By dimension reduction
 Contd…
Drill-down
• Drill-down is the reverse operation of roll-up. It is performed
by either of the following ways −
• By stepping down a concept hierarchy for a dimension
• By introducing a new dimension.
 Contd…
Slice
• The slice operation selects one particular dimension from a
given cube and provides a new sub-cube.
• Consider the following diagram that shows how slice works.
 Contd…
Dice
• Dice selects two or more dimensions from a given cube and
provides a new sub-cube. Consider the following diagram that
shows the dice operation.
 Contd…
Pivot
• The pivot operation is also known as rotation. It rotates the
data axes in view in order to provide an alternative
presentation of data. Consider the following diagram that
shows the pivot operation.
 Schema
• Schema is a logical description of the entire database.
• It includes the name and description of records of all record
types including all associated data-items and aggregates.
• Much like a database, a data warehouse also requires to
maintain a schema.
• A database uses relational model, while a data warehouse uses
Star, Snowflake, and Fact Constellation schema.
 Star Schema
• Each dimension in a star schema is represented with only one-dimension
table.
• This dimension table contains the set of attributes.
• The following diagram shows the sales data of a company with respect to
the four dimensions, namely time, item, branch, and location.
• There is a fact table at the center. It contains the keys to each of four
dimensions.
 Snowflake Schema
• Some dimension tables in the Snowflake schema are
normalized.
• The normalization splits up the data into additional tables.
• Unlike Star schema, the dimensions table in a snowflake
schema are normalized. For example, the item dimension table
in star schema is normalized and split into two dimension
tables, namely item and supplier table.
 Introduction to Machine
Learning
• Machine learning is a branch of Artificial Intelligence that
focuses on developing models and algorithms that let
computers learn from data without being explicitly
programmed for every task.
• In simple words, ML teaches the systems to think and
understand like humans by learning from the data.
• It is used in various applications like image recognition,
speech processing, language translation, recommender
systems, etc.
 Types of Machine
Learning
• Supervised Machine Learning (SVM): It is a type of
machine learning that trains the model using labeled datasets
to predict outcomes.
• Supervised learning algorithms are generally categorized
into two main types:
• Classification - where the goal is to predict discrete labels or
categories
• Regression - where the aim is to predict continuous numerical
values.
 Contd…
There are different supervised learning algorithms that are widely
used −
• Linear Regression
• Logistic Regression
• Decision Trees
• Random Forest
• K-nearest Neighbor
• Support Vector Machine
• Naive Bayes
• Linear Discriminant Analysis
• Neural Networks
 Contd…
• Unsupervised Machine Learning: It is a type of machine
learning that learns patterns and structures within the data
without human supervision.
• Unsupervised learning are again divided into three main
categories based on their purpose:
 Clustering
 Association Rule Mining
 Dimensionality Reduction
 Contd…
Followings are some commonly used unsupervised learning
algorithms −
• K-Means Clustering
• Principal Component Analysis(PCA)
• Hierarchical Clustering
• DBSCAN Clustering
• Agglomerative Clustering
• Apriori Algorithm
• Auto encoder
• Restricted Boltzmann machine (RBM)
 Contd…
• Semi-supervised learning: It is a type of machine learning
that is neither fully supervised nor fully unsupervised. The
semi-supervised learning algorithms basically fall between
supervised and unsupervised learning methods.
 Contd…
• Reinforcement Machine Learning: It is a type of machine
learning model that is similar to supervised learning but does
not use sample data to train the algorithm. This model learns
by trial and error.
• Reinforcement learning interacts with environment and learn
from them based on rewards.
 Applications of Machine
Learning
• Speech Recognition: Machine learning is used to convert spoken language into text using
natural language processing (NLP). It is used in voice assistants like Siri, voice search, and
text accessibility features on mobile devices.
• Customer Service: There are several chatbots that are useful for reducing human interaction
and providing better support on websites and social media, handling FAQs, giving
recommendations, and assisting in e-commerce. For example, virtual agents, Facebook
Messenger bots, and voice assistants.
• Computer Vision: It helps computers in analyzing the images and videos to take action. It is
used in social media for photo tagging, in healthcare for medical imaging, and in self-driving
cars for navigation.
• Recommendation Engines: ML recommendation engines suggest products, movies, or
content based on user behavior. Online retailers use them to improve shopping experiences.
• Robotic Process Automation (RPA): RPA uses AI to automate repetitive tasks and reduce
manual work.
• Automated Stock Trading: AI-driven trading platforms make rapid trades to optimize stock
portfolios without human intervention.
• Fraud Detection: Machine learning identifies suspicious financial transactions, which help
banks to detect fraud and prevent unauthorized activities.
Stages of Machine Learning
 What is Pattern
Matching?
• The process of algorithmically searching for patterns in
sequences of unprocessed data or tokens is called pattern
matching.
Match patterns
• Regular expressions
• String methods
• Conditional statements
• Loop constructs
• Custom functions