Study material of Unit-7

Advance Database Concept : Data Mining and Data Warehousing Design Architecture,
Basic Principles, OLAP, OLTP Vs OLAP.

1
Data Warehousing
Introduction
Data warehousing is the process of collecting, integrating, storing, and managing data
from multiple sources in a central repository. It enables organizations to organize large
volumes of historical data for efficient querying, analysis, and reporting.
The main goal of data warehousing is to support decision-making by providing clean,
consistent, and timely access to data. It ensures fast data retrieval even when working with
massive datasets.
A Data Warehouse is built to support management functions whereas data mining is used
to extract useful information and patterns from data. Data warehousing is the process of
compiling information into a data warehouse. The main purpose of data warehousing is to
consolidate and store large datasets from various sources for efficient retrieval and
analysis, supporting reporting and decision-making. Data mining focuses on analyzing
data to discover patterns, trends, and insights, while data warehousing focuses on storing
and managing data in a centralized location.
It is a technology that aggregates structured data from one or more sources so that it can
be compared and analyzed rather than transaction processing. A data warehouse is
designed to support the management decision-making process by providing a platform for
data cleaning, data integration, and data consolidation. A data warehouse contains subject-
oriented, integrated, time-variant, and non-volatile data. The Data warehouse consolidates
data from many sources while ensuring data quality, consistency, and accuracy. Data
warehouse improves system performance by separating analytics processing from
transactional databases. Data flows into a data warehouse from the various databases. A
data warehouse works by organizing data into a schema that describes the layout and type
of data. Query tools analyze the data tables using schema.

2
 Figure: Data Warehousing process

Need for Data Warehousing

• Handling Large Data Volumes: Traditional databases store limited data (MBs to
GBs), while data warehouses are built to handle huge datasets (up to TBs), making
it easier to store and analyze long-term historical data.
• Enhanced Analytics: Databases handle transactions; data warehouses are
optimized for complex analysis and historical insights.
• Centralized Data Storage: A data warehouse combines data from multiple
sources, giving a single, unified view for better decision-making.
• Trend Analysis: By storing historical data, a data warehouse allows businesses to
analyze trends over time, enabling them to make strategic decisions based on past
performance and predict future outcomes.
• Business Intelligence Support: Data warehouses work with BI tools to give quick
access to insights, helping in data-driven decisions and improving efficiency.

Components of Data Warehouse

The main components of a data warehouse include:
• Data Sources: These are the various operational systems, databases, and external
data feeds that provide raw data to be stored in the warehouse.

3
 • Extract, Transform, Load (ETL) Process: The ETL process is responsible for
extracting data from different sources, transforming it into a suitable format, and
loading it into the data warehouse.
• Data Warehouse Database: This is the central repository where cleaned and
transformed data is stored. It is typically organized in a multidimensional format
for efficient querying and reporting.
• Metadata: Metadata describes the structure, source, and usage of data within the
warehouse, making it easier for users and systems to understand and work with the
data.
• Data Marts: These are smaller, more focused data repositories derived from the
data warehouse, designed to meet the needs of specific business departments or
functions.
• OLAP (Online Analytical Processing) Tools: OLAP tools allow users to analyze
data in multiple dimensions, providing deeper insights and supporting complex
analytical queries.
• End-User Access Tools: These are reporting and analysis tools, such as dashboards
or Business Intelligence (BI) tools, that enable business users to query the data
warehouse and generate reports.

Characteristics of Data Warehousing

Data warehousing plays a key role in modern data management by helping organizations
store, integrate, and analyze data effectively. Its main features include:
• Centralized Data Storage: Combines data from various sources into one place for
a complete view.
• Query & Analysis: Supports fast and flexible data analysis for better decision-
making.
• Data Transformation: Cleans and formats data for consistency and quality.
• Data Mining: Finds hidden patterns to discover insights and predict trends.
• Data Security: Protects data with encryption, access control, and backups.

4
Advantage and Disadvantage of Data Warehousing
Advantage Disadvantage
Better Decisions: Centralized data High Cost: Setup requires major
supports faster, smarter decisions. investment.
Business Intelligence: Enables Complexity: Needs skilled professionals
strong operational insights. to manage.
High Data Quality: Ensures Time-Consuming: Long setup and
consistency and reliability. integration time.
Scalable: Handles large and growing Integration Issues: Combining data from
datasets. sources can be challenging.

Data Warehouse Architecture

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.
Before diving deep into these approaches, we will first discuss the components of data
warehouse architecture.
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: Where data originates. Includes:
• Structured (databases, spreadsheets)
• Semi-structured (XML, JSON)
• Unstructured (emails, images)

5
Staging Area: A temporary space where raw data is cleaned and validated before moving
to the warehouse. ETL tools manage this process:
• Extract (E): Pulls raw data from sources
• Transform (T): Standardizes and formats the data
• Load (L): Moves the data into the data warehouse
Data Warehouse: A central storage for organized, cleansed data, including both raw data
and metadata. Supports analysis, reporting, and decision-making.
Data Marts: Smaller, focused sections of the data warehouse for specific teams (e.g.,
sales, marketing), enabling quick access to relevant data.
Data Mining: Analyzing large datasets in the warehouse to find patterns, trends, and
insights that support decisions and improve operations.

Top-Down Approach
The Top-Down Approach, introduced by Bill Inmon, is a method for designing data
warehouses that starts by building a centralized, company-wide data warehouse. This
central repository acts as the single source of truth for managing and analyzing data across
the organization. It ensures data consistency and provides a strong foundation for decision-
making.
Working of Top-Down Approach
• Central Data Warehouse: The process begins with creating a comprehensive data
warehouse where data from various sources is collected, integrated, and stored. This
involves the ETL (Extract, Transform, Load) process to clean and transform the
data.
• Specialized Data Marts: Once the central warehouse is established, smaller,
department-specific data marts (e.g., for finance or marketing) are built. These data
marts pull information from the main data warehouse, ensuring consistency across
departments.

6
Advantages of Top-Down Approach
• Consistent View: Data marts built from a central warehouse ensure uniform data
across departments, reducing reporting discrepancies.
• High Data Consistency: Standardizing data through one source minimizes errors
and improves the reliability of insights.
• Simplified Maintenance: Updates in the central warehouse automatically reflect in
all data marts, saving time and effort.
• Scalable Architecture: New data marts can be added easily as business needs grow
or change.
• Stronger Governance: Centralized control improves data security, access
management, and compliance.
• Less Data Duplication: Data is stored once in the warehouse, saving space and
avoiding redundant or conflicting records.
• Better Reporting: A unified data source enables faster, more accurate reporting and
decision-making.
• Improved Integration: Central sourcing makes it easier to combine data from
multiple systems for deeper analysis.

7
Disadvantages of Top-Down Approach
• High Cost & Time: Building a central data warehouse and data marts requires major
investment and long implementation time, making it hard for smaller organizations.
• Complex Setup: Designing and managing a centralized system is technically
complex and requires skilled resources and careful planning.
• Low Flexibility: Predefined structures make it hard to adapt quickly to changing
business needs or reporting requirements.
• Limited User Input: IT-led development can exclude business users, resulting in
solutions that may not meet their actual needs.
• Data Delays: Pulling data from various systems can cause processing delays,
affecting real-time reporting and insights.
• Unclear Data Ownership: Centralization can blur responsibility, making it unclear
who manages or maintains specific data.

Bottom-Up Approach
The Bottom-Up Approach, popularized by Ralph Kimball, takes a more flexible and
incremental path to designing data warehouses. Instead of starting with a central data
warehouse, it begins by building small, department-specific data marts that cater to the
immediate needs of individual teams, such as sales or finance. These data marts are later
integrated to form a larger, unified data warehouse.
Working of Bottom-Up Approach
• Department-Specific Data Marts: The process starts with creating data marts for
individual departments or specific business functions. These data marts are
designed to meet immediate data analysis and reporting needs, allowing
departments to gain quick insights.
• Integration into a Data Warehouse: Over time, these data marts are connected and
consolidated to create a unified data warehouse. The integration ensures
consistency and provides a comprehensive view of the organization’s data.

8
Advantage and Disadvantage of Bottom-Up Approach
Advantage Disadvantage
Faster Reporting – Data marts Inconsistent Views – Different structures
allow quick insights and report can lead to inconsistent reporting.
generation.
Step-by-Step Development – Data Silos – Independent marts may cause
Enables gradual rollout with quick duplication and isolation.
wins.
User-Centric – Involves business Integration Difficulty – Combining varied
users to meet actual needs. marts into one warehouse is hard.
Highly Flexible – Easily Redundant Efforts – Similar marts may be
customized for departments or built by different teams.
evolving needs.
Quick Results – Early setup gives Harder to Manage – Multiple marts
immediate value. increase maintenance overhead.

Types of Data Warehouses

The different types of Data Warehouses are:
1. Enterprise Data Warehouse (EDW): A centralized warehouse that stores data
from across the organization for analysis and reporting.
2. Operational Data Store (ODS): Stores real-time operational data used for day-to-
day operations, not for deep analytics.

9
 3. Data Mart: A subset of a data warehouse, focusing on a specific business area or
department.
4. Cloud Data Warehouse: A data warehouse hosted in the cloud, offering scalability
and flexibility.
5. Big Data Warehouse: Designed to store vast amounts of unstructured and
structured data for big data analysis.
6. Virtual Data Warehouse: Provides access to data from multiple sources without
physically storing it.
7. Hybrid Data Warehouse: Combines on-premises and cloud-based storage to offer
flexibility.
8. Real-time Data Warehouse: Designed to handle real-time data streaming and
analysis for immediate insights.

Data Warehouse vs DBMS

Database Data Warehouse
A common Database is based on A data Warehouse is based on analytical
operational or transactional processing. processing.
Each operation is an indivisible
transaction.
Generally, a Database stores current and A Data Warehouse maintains historical
up-to-date data which is used for daily data over time. Historical data is the data
operations. kept over years and can used for trend
analysis, make future predictions and
decision support.
A database is generally application A Data Warehouse is integrated generally
specific. at the organization level, by combining
Example: A database stores related data, data from different databases.
such as the student details in a school. Example: A data warehouse integrates the
data from one or more databases , so that
analysis can be done to get results , such as
the best performing school in a city.
Constructing a Database is not so Constructing a Data Warehouse can be
expensive. expensive.

10
Issues Occur while Building the Warehouse
1. When and How to Gather Data?
• Source-driven: Data sources push updates to the warehouse periodically or
continuously.
• Destination-driven: The warehouse pulls data on a fixed schedule.
• Perfect sync is costly, so data is slightly outdated — acceptable for analysis.
2. What Schema to Use?
• Sources have varied formats.
• The warehouse stores a cleaned, unified version — not a direct copy, but a
consistent snapshot for analysis.
3. Data Transformation and Cleansing
• Fixes errors like typos or invalid codes using reference data.
• Fuzzy lookup helps match similar but not identical values.
4. How to Propagate Updates?
• If warehouse schema = source schema -> easy sync.
• If not -> it becomes a view maintenance challenge.
5. What Data to Summarize?
• Raw data is large; store summaries (e.g., total sales by category).
• Aggregates support efficient querying without full details.

Real world Example of Data warehousing

Data Warehousing can be applied anywhere where we have a huge amount of data and we
want to see statistical results that help in decision making.
1. E-commerce: Flipkart
• Data Gathering: Orders, returns, payments, user clicks, delivery updates.
• Schema: Combines source data into a structured star schema for analysis.

11
 • Cleansing: Standardizes customer names, locations, and product categories.
• Updates: Near real-time or scheduled loads for fresh insights.
• Summarization: Bestsellers by category, regional demand trends, logistics
performance.
2. Banking: HDFC Bank
• Data Gathering: ATM transactions, online banking, credit card usage, loan
records.
• Schema: Integrates data from core banking, CRM, and fraud detection systems.
• Cleansing: Fixes inconsistencies in account info, transaction logs, and addresses.
• Updates: Transaction data is batched and uploaded nightly.
• Summarization: Daily cash flow reports, high-risk account flags, and customer
profitability analysis.

Data Mining
Data Mining Overview
Data bears the maximum significance in a business setup. Without timely and relevant
data, no one can establish a profitable enterprise. Relevant statistics and facts help
marketers trace their target audience, industry trends, competitors, and everything about a
market.
Data mining, in this regard, assists companies in identifying and evaluating raw data sets
to extract valuable insights for business advancement. Data states that companies around
the globe spent around $150,000 in 2023 for data mining purposes.
Data mining is the method of identifying, gathering, and analyzing relevant data for a
business. It assists in understanding in-depth insights and tracing patterns in the market to
make solid decisions. This data-driven tactic allows marketers to attain high predictability
abilities with few chances of making mistakes.
In the contemporary market setup, data mining is an essential technique marketers often
employ to get a competitive advantage. There are multiple principles of data mining that
contribute to its effectiveness. It is a valuable method for companies of all sizes that aim

12
to progress in the industry. There are various data mining tools and software available in
the market that organizations can leverage either for free or by subscribing to them.
Data mining is the process of extracting insights from large datasets using statistical and
computational techniques. It can involve structured, semi-structured or unstructured data
stored in databases, data warehouses or data lakes. The goal is to uncover hidden patterns
and relationships to support informed decision-making and predictions using methods like
clustering, classification, regression and anomaly detection.
Data mining is widely used in industries such as marketing, finance, healthcare and
telecommunications. For example, it helps identify customer segments in marketing or
detect disease risk factors in healthcare. However, it also raises ethical concerns
particularly regarding privacy and the misuse of personal data, requiring careful
safeguards.
Data Mining is the process of finding patterns and correlations within large data sets to
identify relationships between data. Data mining tools allow a business organization to
predict customer behavior. Data mining tools are used to build risk models and detect
fraud. Data mining is used in market analysis and management, fraud detection, corporate
analysis, and risk management.

Figure: Data Mining process

13
1. Introduction to Data Mining
Data Mining involves extracting useful insights from large datasets using various
techniques like clustering, classification and association rule mining.

2. Extract Transform Load (ETL)

ETL stands for Extract, Transform and Load which are the three fundamental steps in data
processing. This process helps in collecting, cleaning and organizing data for analysis. In
this section we will
2.1. Extract
The extraction process involves gathering raw data from various sources such as
databases, APIs or data lakes. The goal is to retrieve data in its original form which will
later be processed for analysis.
2.2. Transform
Transformation step involves cleaning and structuring the data. This can include removing
inconsistencies, handling missing values and converting the data into a format suitable for
analysis like normalization, aggregation, etc.
2.3. Load
In the loading phase, the transformed data is stored in a target database or data warehouse
making it ready for further analysis and use in decision-making processes.

3. Exploratory Data Analysis (EDA)

EDA is an important step in data analysis that helps you understand the underlying
structure of your data through statistical and graphical techniques.
3.1. Statistics and Graphs
This involves summarizing the key features of the dataset using descriptive statistics
(mean, median, standard deviation) and visualizations such as histograms, bar charts and
box plots.
3.2. Trend Analysis
Trend analysis focuses on identifying patterns over time or sequences in the data. This
helps to understand how data points evolve and predict future behavior or outcomes.

14
4. Data Mining Techniques
We will explore various data mining techniques such as clustering, classification and
regression that are applied to data in order to uncover insights and predict future trends.
4.1 Classification and Prediction
we will cover methods used for classification and prediction in Data Mining. These
methods help in predicting outcomes based on historical data.
4.2. Clustering and Cluster Analysis
In this section we will explore Clustering techniques which are used to group similar data
points into clusters, uncovering patterns in large datasets.
• Clustering
• Partitioning Methods
• Hierarchical Methods
• Cluster Analysis
• Associative Classification

Data Mining Architecture and its part

Data Mining refers to the detection and extraction of new patterns from the already
collected data. Data mining is the amalgamation of the field of statistics and computer
science aiming to discover patterns in incredibly large datasets and then transform them
into a comprehensible structure for later use.
The architecture of Data Mining:

15
Basic Working:
1. It all starts when the user puts up certain data mining requests, these requests are
then sent to data mining engines for pattern evaluation.
2. These applications try to find the solution to the query using the already present
database.
3. The metadata then extracted is sent for proper analysis to the data mining engine
which sometimes interacts with pattern evaluation modules to determine the result.

16
 4. This result is then sent to the front end in an easily understandable manner using a
suitable interface.

A detailed description of parts of data mining architecture is shown:

1. Data Sources: Database, World Wide Web(WWW), and data warehouse are parts
of data sources. The data in these sources may be in the form of plain text,
spreadsheets, or other forms of media like photos or videos. WWW is one of the
biggest sources of data.
2. Database Server: The database server contains the actual data ready to be
processed. It performs the task of handling data retrieval as per the request of the
user.
3. Data Mining Engine: It is one of the core components of the data mining
architecture that performs all kinds of data mining techniques like association,
classification, characterization, clustering, prediction, etc.
4. Pattern Evaluation Modules: They are responsible for finding interesting patterns
in the data and sometimes they also interact with the database servers for producing
the result of the user requests.
5. Graphic User Interface: Since the user cannot fully understand the complexity of
the data mining process so graphical user interface helps the user to communicate
effectively with the data mining system.
6. Knowledge Base: Knowledge Base is an important part of the data mining engine
that is quite beneficial in guiding the search for the result patterns. Data mining
engines may also sometimes get inputs from the knowledge base. This knowledge
base may contain data from user experiences. The objective of the knowledge base
is to make the result more accurate and reliable.

Types of Data Mining architecture:

1. No Coupling: The no coupling data mining architecture retrieves data from
particular data sources. It does not use the database for retrieving the data which is
otherwise quite an efficient and accurate way to do the same. The no coupling
architecture for data mining is poor and only used for performing very simple data
mining processes.
2. Loose Coupling: In loose coupling architecture data mining system retrieves data
from the database and stores the data in those systems. This mining is for memory-
based data mining architecture.
17
 3. Semi-Tight Coupling: It tends to use various advantageous features of the data
warehouse systems. It includes sorting, indexing, and aggregation. In this
architecture, an intermediate result can be stored in the database for better
performance.
4. Tight coupling: In this architecture, a data warehouse is considered one of its most
important components whose features are employed for performing data mining
tasks. This architecture provides scalability, performance, and integrated
information

Basic Principles of Data Mining:

Data Mining is a strategic approach, and companies need to follow certain principles and
concepts to employ data mining appropriately.
There are several concepts that formulate the principles of data mining. Below are some
of them. The process starts with a challenge or problem that organizations encounter, and
they employ data mining methodologies and collect data from various sources.
Data preparation: The collected data is raw and vague, which data mining cleans and
synthesizes for better comprehension. Big data sets may also contain extra and irrelevant
details that are of no use to any firm. Hence, cleaning and preparing data is an essential
segment of data mining.
Data exploration: One of the concepts and principles of data mining is data exploration.
This stage highlights that marketers must understand the insights to integrate them into
the business model. It is an evaluation stage where companies determine whether the data
they gathered and cleaned is relevant or not by exploring its minimum and maximum
values.
Pattern identification: After exploring the data, this process tracks patterns and
similarities in the data sets. Here, new data is combined with existing data to identify
patterns. These similarities help in recognizing trends and practices for the betterment of
business activities.
Data deployment: The means of data mining is completed with appropriate deployment
or implementation of the assessed data. This is probably the most difficult stage as it
requires thorough knowledge and proper understanding of the existing data and insights
gathered, cleaned, as well as explored.

18
Advantages of Data Mining

• Better audience identification: Many companies fail to last long in their

respective domains due to audience identification challenges. The chief benefit of
data mining is audience clustering capabilities. As per the principles of data mining,
relevant data helps marketers shortlist the correct set of audiences and allows them
to identify appropriate mediums to reach them.
• Risk management: Fruitful business always comes with certain risks. However,
several companies fall short of recognizing the market risks or threats before
entering the market, and this leads to unwanted situations. The in-depth market
insights of data mining not only help identify the probable market risks but also
support managing risks or threats.
• Data-driven decision making: Apt decisions are important for a company’s
progression. The data-driven insights of data mining assist in making better
decisions in terms of strategy implementation, partnerships, threat mitigation, team
management, innovation, and others. This is majorly influenced by the principles
of data mining.
• Enhanced predictability: Businesses that predict the market well are more likely
to evolve in a competitive industry. Data mining backs companies with dependable
insights to understand and predict the market better. Moreover, as per the
predictions, enterprises can invest in advantageous areas and withdraw from risky
domains.
• Data mining aids in a variety of data analysis and sorting procedures. The
identification and detection of any undesired fault in a system is one of the best
implementations here. This method permits any dangers to be eliminated sooner.
• In comparison to other statistical data applications, data mining methods are both
cost-effective and efficient.
• Companies can take advantage of this analytical tool by providing appropriate and
easily accessible knowledge-based data.
• The detection and identification of undesirable faults that occur in the system are
one of the most astonishing data mining techniques.

Disadvantages of Data Mining

• Data mining isn't always 100 percent accurate, and if done incorrectly, it can lead
to data breaches.

19
 • Organizations must devote a significant amount of resources to training and
implementation. Furthermore, the algorithms used in the creation of data mining
tools cause them to work in different ways.

Difference Between Data Mining and Data Warehousing

Basis of Data Warehousing Data Mining
Comparison
Definition A data warehouse is a database Data mining is the process of
system that is designed for analyzing data patterns.
analytical analysis instead of
transactional work.
Process Data is stored periodically. Data is analyzed regularly.
Purpose Data warehousing is the process Data mining is the use of pattern
of extracting and storing data to recognition logic to identify patterns.
allow easier reporting.
Managing Data warehousing is solely Data mining is carried out by business
Authorities carried out by engineers. users with the help of engineers.
Data Handling Data warehousing is the process Data mining is considered as a process
of pooling all relevant data of extracting data from large data sets.
together.
Functionality Subject-oriented, integrated, AI, statistics, databases, and machine
time-varying and non-volatile learning systems are all used in data
constitute data warehouses. mining technologies.
Task Data warehousing is the process Pattern recognition logic is used in
of extracting and storing data in data mining to find patterns.
order to make reporting more
efficient.
Uses It extracts data and stores it in an This procedure employs pattern
orderly format, making reporting recognition tools to aid in the
easier and faster. identification of access patterns.
Examples When a data warehouse is Data mining aids in the creation of
connected with operational suggestive patterns of key parameters.
business systems like CRM Customer purchasing behavior, items,
(Customer Relationship and sales are examples. As a result,
Management) systems, it adds businesses will be able to make the
value. required adjustments to their
operations and production.

20
Conclusion
Data mining and data warehousing both serve different purposes, but they are
complementary in nature. Data warehousing creates a centralized and organized database
for efficient querying and reporting, while data mining digs deep into these data sets to
uncover hidden patterns and valuable insights. Warehousing helps an organization in
making sure the accuracy and access of data, whereas mining turns that data into
actionable intelligence. This marriage of techniques would let businesses make better
strategic decisions, and operations would be optimized more effectively.

Online Analytical Processing (OLAP) and Online Transaction

Processing (OLTP) in Databases
Online Analytical Processing (OLAP) and OLTP (Online Transaction Processing) are
both integral parts of data management, but they have different functionalities.

• OLTP focuses on handling large numbers of transactional operations in real time,

ensuring data consistency and reliability for daily business operations.

• OLAP is designed for complex queries and data analysis, enabling businesses to
derive insights from vast datasets through multidimensional analysis.

Online Analytical Processing (OLAP)

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.

21
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.

22
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.

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.

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.

23
 • 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.

Difference Between OLAP and OLTP

Category OLAP (Online Analytical OLTP (Online Transaction

Processing) Processing)
Definition It is well-known as an online It is well-known as an online
database query management database modifying system.
system.
Data source Consists of historical data from Consists of only operational current
various Databases. data.
Method used It makes use of a data It makes use of a standard database
warehouse. management system (DBMS).
Application It is subject-oriented. Used It is application-oriented. Used for
for Data Mining, Analytics, business tasks.
Decisions making, etc.
Normalized In an OLAP database, tables In an OLTP database, tables
are not normalized. are normalized (3NF).
Usage of data The data is used in planning, The data is used to perform day-to-
problem-solving, and decision- day fundamental operations.
making.

24
Task It provides a multi-dimensional It reveals a snapshot of present
view of different business business tasks.
tasks.
Purpose It serves the purpose to extract It serves the purpose to Insert,
information for analysis and Update, and Delete information
decision-making. from the database.
Volume of A large amount of data is The size of the data is relatively
data stored typically in TB, PB small as the historical data is
archived in MB, and GB.
Queries Relatively slow as the amount Very Fast as the queries operate on
of data involved is large. 5% of the data.
Queries may take hours.
Update The OLAP database is not The data integrity constraint must be
often updated. As a result, data maintained in an OLTP database.
integrity is unaffected.
Backup and It only needs backup from time The backup and recovery process is
Recovery to time as compared to OLTP. maintained rigorously
Processing The processing of complex It is comparatively fast in processing
time queries can take a lengthy time. because of simple and
straightforward queries.
Types of users This data is generally managed This data is managed by clerksForex
by CEO, MD, and GM. and managers.
Operations Only read and rarely write Both read and write operations.
operations.
Updates With lengthy, scheduled batch The user initiates data updates,
operations, data is refreshed on which are brief and quick.
a regular basis.
Nature of The process is focused on the The process is focused on the
audience customer. market.
Database Design with a focus on the Design that is focused on the
Design subject. application.
Productivity Improves the efficiency of Enhances the user's productivity.
business analysts.

25