Data Warehousing

1. What Is a Data Warehouse?

A Data Warehouse (DW) is a centralized, structured, and integrated repository of data

collected from various sources within an organization. It is designed specifically to support
decision-making and analytical processes by enabling users to perform complex queries
and data analysis without affecting operational systems.

Key Characteristics:

 Subject-Oriented: Organized around key subjects like sales, finance, inventory, etc.
 Integrated: Consolidates data from heterogeneous sources (databases, ERP systems,
CRM, etc.), standardizing formats and structures.
 Time-Variant: Stores historical data, making it possible to analyze trends over time.
 Non-Volatile: Once data is entered, it is not changed or deleted—only new data is
added, ensuring historical accuracy.

Benefits:

 Provides a single version of truth for reporting and analysis.

 Improves the accuracy, consistency, and efficiency of business intelligence.
 Supports business performance tracking, forecasting, and strategy development.

Data Marts and Analytical Data

1. What is a Data Mart?

A Data Mart is a focused, subject-specific subset of a data warehouse that is tailored for the
needs of a particular business unit, department, or function.

Key Characteristics:

 Scope: Narrow and focused (e.g., sales, marketing, HR).

 Users: Typically used by a specific group of business users.
 Data Volume: Smaller in scale compared to a data warehouse.
 Implementation Time: Faster and less costly to implement.
 Data Source: Can be created from:
o A central data warehouse (dependent data mart).
o Directly from operational systems (independent data mart).
o External data sources (hybrid data mart).

Purpose of a Data Mart:

 To provide fast and simplified access to relevant data for a specific team.
 To reduce query complexity for business users.
 To enable department-specific analytics, dashboards, and reports.
Example Use Cases:

 A Sales Data Mart may include sales transactions, customer contacts, and territory
information.
 A Finance Data Mart might contain data about budgets, expenditures, revenues, and
forecasts.
 A Marketing Data Mart could hold campaign performance, lead tracking, and
conversion data.

Characteristics of Analytical Data:

 Aggregated and Summarized: Often rolled up to higher-level metrics (e.g., monthly

sales totals).
 Time-Series Based: Includes timestamps for trend analysis.
 Structured: Cleaned and formatted to fit into dimensional models.
 High-Quality: Deduplicated, validated, and consistent across sources.

Analytical Techniques:

 OLAP (Online Analytical Processing):

o Enables users to analyze data from multiple dimensions (e.g., time, geography,
product).
o Typical operations include:
 Slice: Filter by one dimension (e.g., sales in 2024).
 Dice: Filter by multiple dimensions (e.g., sales in Q1 2024 in Europe).
 Drill-down/up: Navigate between levels of detail.
 Pivot: Rotate the data view for comparison.
 Data Mining:
o Discovering patterns or relationships in data (e.g., customer churn predictors).
 Descriptive & Predictive Analytics:
o Descriptive: Understand what happened.
o Predictive: Forecast what might happen.

3. Benefits of Using Data Marts and Analytical Data

Benefits Data Marts Analytical Data

Faster Query Smaller, focused datasets Pre-aggregated and indexed for
Performance improve speed quick access
Business Focus Tailored to departmental needs Structured for meaningful insights
Clean and intuitive format for end-
Ease of Use Less technical skill needed
users
Improved Decision- Directly supports operational Enables forecasting, trend
Making decisions analysis, etc.
Can grow with data and analytical
Scalability Can be scaled per department
needs
 o Dashboards and Reports: Visual representations of key metrics (KPIs,
trends).

Benefits:

 Enables faster access to relevant data.

 Improves user autonomy in analysis without relying heavily on IT.
 Allows more agile decision-making within departments.

4. When to Use Data Marts

 When a department needs autonomous access to specific data.

 When implementing a phased data warehousing approach (bottom-up model).
 When needing quick wins for BI in smaller organizations or pilots.
 When optimizing self-service BI by empowering business units.

5. Architecture Overview

A simplified flow looks like this:

Operational Systems → ETL → Data Warehouse → Data Marts → BI Tools →

Business Insights
↓
Analytical Data Store

 ETL (Extract, Transform, Load): Prepares the data.

 Data Warehouse: Central storage.
 Data Mart: Department-specific subset.
 BI Tools: Analyze and visualize

3. Organization of the Data Warehouse

Data warehouses are organized in layers or tiers that separate functions and optimize data
flow and access:

A. Staging Layer

 This is the initial area where raw data from various sources is collected.
 ETL processes (Extract, Transform, Load) are performed here:
o Extract: Pulls data from source systems.
o Transform: Cleans, deduplicates, normalizes, and structures the data.
o Load: Moves transformed data into the data warehouse.

B. Data Integration Layer (Data Storage Layer)

 The central repository where transformed, cleaned data is stored.

  Organized using schemas:
o Star Schema: Central fact table surrounded by dimension tables. Simplifies
queries.
o Snowflake Schema: More normalized than star schema; dimension tables are
split into sub-dimensions.

C. Data Presentation Layer

 The layer that makes data accessible to end users and applications.
 Supports business intelligence tools (like Power BI, Tableau, Looker).
 May include:
o OLAP cubes
o Materialized views
o Data visualizations

D. Metadata Layer

 Describes the data’s origin, format, and business meaning.

 Helps users understand what the data means, how it was derived, and how to use it.
 Improves governance and self-service BI.

Architecture Models

1. Top-Down (Inmon): Start with an enterprise data warehouse, then create data marts.
2. Bottom-Up (Kimball): Build data marts first, then integrate into a warehouse.
3. Hybrid: Combines both strategies for flexibility.

Enterprise Resource Planning (ERP)

Enterprise Resource Planning (ERP) is a system that integrates core business processes into
a single unified platform, often in real-time. It helps organizations streamline operations,
share data efficiently, and maintain consistency across departments such as finance, HR,
supply chain, manufacturing, procurement, and more.

1. Distributing the Enterprise

What Does "Distributing the Enterprise" Mean?

 Refers to the decentralized nature of modern businesses, where operations are

spread across multiple locations, departments, and even geographies.
 These distributed environments pose challenges in coordinating data, processes,
and workflows.

Role of ERP:
  ERP acts as a central nervous system that connects all distributed parts of the
enterprise.
 Allows centralized data storage but decentralized access, ensuring:
o Consistent data across departments.
o Real-time visibility into business performance.
o Streamlined operations and reduced duplication.
 Supports multi-location, multi-currency, and multi-lingual operations—critical for
global enterprises.

Example:

 A company with factories in Asia, a sales office in Europe, and a finance team in
North America can manage inventory, orders, invoicing, and reporting in one ERP
system, ensuring all parts of the business are aligned.

2. First ERP, Then Business Intelligence

Why ERP First?

 ERP systems are transactional in nature—they capture and manage data from
everyday operations (e.g., sales orders, inventory levels, payroll).
 Implementing ERP first helps:
o Standardize and clean operational data.
o Automate processes across departments.
o Create a foundation for advanced analytics.

Why BI Comes After:

 Once ERP is in place and data is flowing consistently, Business Intelligence tools
can be applied to that data for:
o Reporting and dashboarding.
o Strategic analysis (e.g., sales trends, financial performance).
o Predictive modeling (e.g., demand forecasting, churn prediction).
 BI extracts value and insights from the vast volumes of ERP data.

Benefits of This Approach:

 BI tools work best with clean, integrated data—ERP provides this.

 Allows cross-functional reporting (e.g., linking HR data with productivity or
finance data with procurement).
 Encourages data-driven decision-making at all levels of the organization.

Example: A retailer might first implement ERP to manage inventory, sales, and
procurement. Once stable, BI tools analyze which products sell best, where inventory is
overstocked, or what supplier patterns impact cost.
3. The Current State of Affairs

Modern ERP Trends:

 Cloud-Based ERP: Systems like SAP S/4HANA Cloud, Oracle ERP Cloud, and
NetSuite offer scalable, flexible ERP delivered as SaaS.
 Modular ERP: Companies can adopt only the components they need (e.g., just
finance or HR), then expand later.
 Mobile ERP: Access to ERP functionality via mobile apps improves accessibility and
field productivity.
 Embedded Analytics: BI capabilities are increasingly built within ERP systems.
o Real-time dashboards
o Embedded KPIs
o AI-driven recommendations
 AI and Automation: ERPs now integrate machine learning to automate tasks (e.g.,
invoice matching, anomaly detection).

ERP + BI = Strategic Advantage

 BI is no longer a separate system but is often tightly integrated with ERP.

 Examples of integration:
o SAP Analytics Cloud integrated with SAP ERP.
o Power BI pulling data from Microsoft Dynamics 365.
o Oracle Analytics paired with Oracle ERP Cloud.
 This enables:
o Real-time decision-making
o Self-service BI for business users
o Predictive insights (e.g., demand trends, risk analysis)

Challenges & Considerations

 Implementation complexity: ERP rollouts can be costly and time-consuming.

 Change management: Requires significant process and cultural change.
 Data governance: With BI layered on ERP, data accuracy and ownership become
critical.
 Customization vs. Standardization: Balancing out-of-the-box ERP functionality
with custom business needs.

ERP Component
Function
Distributing the
Connects decentralized operations through a unified platform
Enterprise
ERP lays the groundwork with clean data; BI extracts insights from
First ERP, then BI
that data
Current State of Affairs Cloud, modular, AI-enabled ERP with embedded BI capabilities
ERP systems are no longer just back-office tools—they are now integral to real-time
analytics and strategic business decisions through their integration with Business Intelligence
platforms.

CRM, ERP, and Business Intelligence: How They Work Together

What Is CRM?

 Customer Relationship Management (CRM) is a system used to manage a

company’s interactions with current and potential customers.
 It helps track customer data, manage sales pipelines, conduct marketing
campaigns, and provide customer service support.

Core Functions of a CRM System:

 Contact Management: Centralized database of leads, customers, and communication

history.
 Sales Automation: Lead tracking, opportunity management, and sales forecasting.
 Marketing Automation: Campaign management, email tracking, segmentation.
 Customer Service: Case tracking, support ticketing, knowledge base.

CRM vs. ERP:

Aspect CRM ERP

Focus Front-office (customer-facing) Back-office (operations, finance)

Main Goal Improve customer acquisition & retention Optimize internal processes

Users Sales, Marketing, Customer Support Finance, HR, Manufacturing, Supply Chain

CRM + ERP Integration:

 Combines customer-facing and internal process data for a holistic view.

 Example: A salesperson using CRM can see inventory availability (ERP) before
promising delivery.
 Allows better coordination between departments like sales, finance, and operations.

CRM + Business Intelligence:

 BI tools applied to CRM data can reveal:

o Which marketing campaigns drive the most revenue.
o Which customers are most profitable over time.
o Where sales reps or regions are underperforming.
 Enables data-driven sales strategies, personalized marketing, and targeted
support improvements.

2. Customer Decisions
What Are Customer Decisions?

These are the choices customers make throughout their journey with a brand—what to buy,
when to buy, how much to spend, and whether to return.

How CRM and BI Help Understand These Decisions:

A. BI Enables Insights Into Customer Behaviour

 Analyze purchase patterns, website behaviour, engagement metrics, etc.

 Segment customers based on demographics, behaviour, or purchase history.
 Predict future behaviour using machine learning (e.g., likelihood to churn, upsell
potential).

B. Types of Customer Decisions Analysed:

Decision Type BI/CRM Insight

What to buy Product recommendation engines, purchase history

When to buy Seasonal trends, campaign response data

How much to spend Customer lifetime value (CLV), average order value

Whether to switch/leave Churn prediction models, customer satisfaction scores (CSAT)

What support channel to use CRM records of preferred communication methods

C. Personalization and Proactivity

 BI helps businesses anticipate customer needs.

 CRM systems can then automate personalized interactions (e.g., sending offers,
follow-ups).
 Example: A CRM integrated with BI might alert a sales rep when a high-value
customer hasn't made a purchase in 30 days and suggest a loyalty discount.

Use Case Example:

 A bank uses CRM to store all customer profiles.

 BI analyses spending data to identify customers eligible for premium accounts.
 A targeted campaign is launched, tracked through CRM, and results analysed in BI
dashboards.

Benefits of Combining CRM, ERP, and BI

 Benefit Description

360° Customer View Combines data from marketing, sales, support, and finance.

Improved Customer Retention BI can identify at-risk customers early.

Optimized Marketing Campaigns Analyze campaign ROI and customer response patterns.

Smarter Sales Strategy Use data to prioritize leads and tailor sales approaches.

Enhanced Customer Experience Personalize interactions using data insights.

Decisions About Customers, Business Intelligence, and

Financial Information

1. Decisions About Customers

These are strategic and operational decisions organizations make based on customer data.
Rather than focusing on what customers decide (as in "Customer Decisions"), this focuses on
how businesses evaluate and act on customer-related opportunities, risks, and value.

Key Types of Decisions Businesses Make About Customers:

Decision Area Description

Customer Segmentation Grouping customers by behavior, value, demographics, geography, etc.

Customer Acquisition Identifying which segments are most profitable or receptive to

Strategy marketing efforts.

Customer Retention &

Prioritizing high-value customers and designing programs to retain them.
Loyalty

Customer Profitability Evaluating which customers or segments are costing more than they
Analysis bring in revenue.

Sales & Service Allocating resources based on customer tier/value (e.g., premium
Prioritization support for top customers).

Pricing & Discounts Offering customized pricing based on customer value, volume, or loyalty.

Churn Mitigation Identifying at-risk customers and planning retention actions.

BI’s Role:

 Uses historical and real-time data from CRM, ERP, and transactional systems.
  Applies analytics (e.g., cohort analysis, customer lifetime value Modeling) to drive
data-informed decisions.
 Powers dashboards and reports that help executives and front-line managers take
timely action.

2. Business Intelligence and Financial Information

Business Intelligence bridges the gap between customer insight and financial
performance by helping businesses measure, track, and forecast how customer behaviour
affects revenue, costs, and profitability.

Key Connections Between BI, Finance, and Customers:

A. Customer Lifetime Value (CLV)

 BI calculates the net profit attributed to a customer over their entire relationship with
the business.
 Helps prioritize marketing, sales, and support efforts based on expected return.

B. Revenue Attribution

 BI tools can attribute revenue to specific:

o Marketing channels
o Customer segments
o Campaigns or sales reps
 Enables better budget allocation and ROI tracking.

C. Cost-to-Serve Analysis

 BI helps determine how much it costs to serve different customers or segments.

 Combines operational data (from ERP) and CRM (support/service history) to assess if
certain customers are unprofitable.

D. Credit and Risk Management

 Integrating BI with financial systems helps assess:

o Customer creditworthiness
o Risk of late payments or default
 Enables dynamic decisions about payment terms, limits, and collections.

E. Forecasting and Planning

 BI uses historical trends and predictive models to forecast:

o Sales revenue by customer segment
o Customer churn and its impact on future revenue
o Customer acquisition cost vs. lifetime value
Financial KPIs Related to Customers Tracked Through BI:

KPI Purpose

Customer Lifetime Value

Long-term revenue potential of a customer.
(CLV)

Customer Acquisition Cost

How much it costs to acquire a customer.
(CAC)

Gross Margin by Segment Profitability of different customer groups.

Churn Rate Percentage of customers lost over time.

Days Sales Outstanding Average number of days to collect payments—used to monitor

(DSO) customer payment behaviour.

Revenue per Customer How much each customer is contributing to top-line revenue.

3. Practical Example: Retail Chain

Imagine a retail company with physical stores and an e-commerce platform.

 CRM tracks each customer’s purchases, preferences, and engagement.

 ERP manages inventory, fulfilment costs, returns, and financial transactions.
 BI analyses:
o Which customers generate the most profit (based on CLV and cost-to-serve).
o Which customers frequently return items (affecting margins).
o Which geographic regions or segments are underperforming.
o Which marketing channels are generating profitable leads.
 Based on this analysis, the company might decide to:
o Shift budget to more profitable marketing channels.
o Re-segment its loyalty program.
o Offer different return policies to high-risk customers.

4. Strategic Benefits of Using BI for Customer and Financial Decisions

 Align customer strategies with financial goals (e.g., reduce churn to improve
predictable revenue).
 Improve ROI on sales and marketing by focusing on high-value segments.
 Enhance cash flow and profitability by targeting financially stable customers and
avoiding loss leaders.
 Support agile decision-making by giving leadership real-time, data-driven insights.