OLAP, Queries and Analytical Exploration

Introduction

Business intelligence is valuable only when decision-makers can move from stored data to meaningful evidence. A sales database may contain millions of transactions, but managers usually ask questions such as: Which regions are underperforming? Did revenue decline because of fewer orders or lower prices? Which products grew during the holiday period? These questions require more than simple record retrieval. They require analytical exploration across dimensions such as time, geography, product, customer and channel.

This chapter introduces two complementary approaches. Relational queries use SQL to select, join, filter and aggregate records. Online analytical processing (OLAP) organizes data for rapid multidimensional analysis. Together, these approaches support descriptive analysis, diagnostic analysis and ad hoc investigation.

OLAP operations such as slicing, dicing, drill-down, roll-up and pivoting help users examine data at different levels of detail. The analyst must also understand the structure of the data, the meaning of measures, the limitations of aggregation and the governance risks associated with interpreting business information.

Learning Objectives

By the end of this chapter, you should be able to:

1. Explain the purpose and structure of OLAP systems.
2. Distinguish facts, dimensions, measures, attributes and hierarchies.
3. Use relational SQL queries for filtering, joining, grouping and aggregation.
4. Apply slicing, dicing, drill-down, roll-up and pivoting to analytical questions.
5. Distinguish descriptive, diagnostic and ad hoc analysis.
6. Interpret analytical results responsibly and identify limitations.
7. Design a practical analytical exploration process for business decisions.

1. The Analytical Perspective: From Transactions to Insights

Operational systems are designed to record events accurately and efficiently. Examples include point-of-sale systems, banking systems, order-entry systems and human-resource systems. Their tables are often normalized to reduce duplication and support frequent updates.

Analytical systems are designed for reading, comparison and aggregation. They combine historical records from multiple sources and present them in forms suitable for reporting and exploration. A sales analysis may combine orders, products, customers, stores, calendars and sales representatives.

A useful distinction is between events and summaries. An event is a recorded occurrence, such as one invoice line. A summary is a calculated view, such as monthly revenue by region. OLAP enables the user to move between these levels without manually inspecting every transaction.

1.1 Business questions and analytical questions

A business question should be converted into an analytical specification. For example:

The distinction between a measure and a dimension is fundamental. Revenue, quantity and cost are measures. Region, product category and month are dimensions used to organize or filter measures.

2. OLAP Data Structures

2.1 Facts and dimensions

An OLAP model commonly contains a fact table and several dimension tables. The fact table records measurable business events. Dimension tables describe the context of those events.

For example, a sales fact table may include:

• DateKey
• ProductKey
• StoreKey
• CustomerKey
• QuantitySold
• SalesAmount
• CostAmount
• DiscountAmount

Related dimensions may include Date, Product, Store and Customer. The keys connect each fact to descriptive information.

The grain of a fact table states what one row represents. For example, the grain might be “one product on one customer order.” Grain must be defined before measures are interpreted. If the grain is unclear, analysts may double-count revenue or quantity.

2.2 Measures

A measure is a numerical value that can be analyzed. Measures may be:

• Additive: safely summed across all relevant dimensions, such as sales revenue.
• Semi-additive: summed across some dimensions but not others, such as account balance, which may be summed across customers but not across time.
• Non-additive: should not be summed, such as a percentage, ratio or average price.

For example, total revenue is generally calculated as:

Here, Revenue_i is the revenue associated with transaction or fact row i, and the summation applies to a defined population of rows. The calculation is valid when each row contributes once to the selected business event and when duplicate joins have been avoided.

Profit can be expressed as:

The units of Revenue and Cost must be the same currency and reporting period. Profit margin is commonly calculated as:

Revenue must be non-zero, and the numerator and denominator must refer to the same scope, currency and period. A margin percentage should not normally be summed across products; it should be recalculated from aggregated profit and revenue.

2.3 Dimensions and hierarchies

A dimension contains descriptive attributes. The Date dimension may contain day, week, month, quarter and year. These attributes form a hierarchy:

Day → Month → Quarter → Year

A geographic hierarchy might be:

Store → City → Region → Country

Hierarchies enable drill-down and roll-up. They must reflect the organization’s actual reporting structure. A calendar year and a financial year are not interchangeable, and a product category hierarchy may differ between marketing and finance.

2.4 The multidimensional cube

An OLAP cube is a conceptual structure in which measures are examined across multiple dimensions. A three-dimensional example might contain sales by Product, Region and Month. The cube need not literally be stored as a geometric cube; it may be implemented using relational tables, columnar storage or a specialized analytical engine.

The cube metaphor emphasizes that a user can select a subset, change the level of detail and reorganize the presentation without changing the underlying business events.

3. Relational Queries for Analytical Exploration

SQL provides the foundation for many analytical systems. A typical analytical query follows this logical sequence:

1. Identify the source tables.
2. Join related tables using valid keys.
3. Filter rows with WHERE.
4. Group rows using GROUP BY.
5. Calculate measures with aggregate functions.
6. Filter groups with HAVING.
7. Sort or present the result.

Consider the following query:

SELECT
    d.Year,
    s.Region,
    SUM(f.SalesAmount) AS Revenue,
    SUM(f.CostAmount) AS Cost,
    SUM(f.SalesAmount - f.CostAmount) AS Profit
FROM FactSales AS f
JOIN DimDate AS d ON f.DateKey = d.DateKey
JOIN DimStore AS s ON f.StoreKey = s.StoreKey
WHERE d.Year BETWEEN 2023 AND 2024
GROUP BY d.Year, s.Region
ORDER BY d.Year, Revenue DESC;

The query groups fact rows by year and region. It calculates revenue, cost and profit for each group. The join conditions are essential: an incorrect or incomplete join can multiply fact rows and produce overstated results.

3.1 Filtering and aggregation

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. For example:

SELECT
    p.Category,
    SUM(f.SalesAmount) AS Revenue
FROM FactSales AS f
JOIN DimProduct AS p ON f.ProductKey = p.ProductKey
GROUP BY p.Category
HAVING SUM(f.SalesAmount) > 100000;

This returns categories whose aggregated revenue exceeds 100,000 currency units. The threshold is meaningful only if the currency, period and inclusion rules are known.

3.2 Ratios and safe aggregation

A common error is averaging percentages when the business question requires a weighted result. Suppose Product A has profit of 20 on revenue of 100, while Product B has profit of 90 on revenue of 900. The individual margins are 20% and 10%. The combined margin is:

The simple average, 15%, is not the correct overall margin because the products have different revenue bases. In SQL, the preferred approach is generally:

SUM(Profit) / NULLIF(SUM(Revenue), 0)

NULLIF prevents division by zero in systems that return an error for a zero denominator. The exact syntax may vary by database platform.

3.3 NULL values and missing data

A NULL value is not the same as zero. NULL may mean unknown, not recorded or not applicable. Replacing all NULL values with zero can create misleading totals. Analysts should establish whether missing values should be excluded, treated as zero or investigated as a data-quality problem.

4. Core OLAP Operations

4.1 Slicing

A slice fixes one dimension at a particular value and examines the remaining dimensions. For example, selecting only the year 2024 creates a slice of the sales cube. In SQL, slicing is often implemented with a WHERE condition:

WHERE d.Year = 2024

Slicing is useful when a manager wants to focus on one period, region, channel or product category.

4.2 Dicing

Dicing selects a sub-cube using multiple filters. For example, an analyst may select 2024, the regions North and West, and the categories Electronics and Furniture. Dicing is implemented through several predicates:

WHERE d.Year = 2024
  AND s.Region IN ('North', 'West')
  AND p.Category IN ('Electronics', 'Furniture')

Dicing supports focused comparison, but excessive filtering can hide important context. A result should always identify its selected population.

4.3 Drill-down

Drill-down moves from a summary to greater detail. A user may move from annual revenue to quarterly, monthly, daily or transaction-level revenue. Drill-down can also move from country to region to store.

For example, a query grouped by year can be changed to:

GROUP BY d.Year, d.Quarter, d.Month

Drill-down is especially useful in diagnostic analysis. If annual revenue is below target, monthly detail may reveal whether the problem was concentrated in one period.

4.4 Roll-up

Roll-up moves from detailed data to a higher-level summary. Daily store sales can be rolled up to monthly regional sales. Roll-up improves interpretability and performance, but it can conceal variation. A region may appear healthy even though one store is performing poorly.

A roll-up must respect the measure’s aggregation behavior. Revenue can usually be summed, while inventory balance may require an ending-period or average-period rule rather than a sum across dates.

4.5 Pivoting

Pivoting changes the orientation of a result so that dimensions appear as rows, columns or filters. For example, a manager may view regions as rows and quarters as columns. Pivoting changes presentation, not the underlying facts.

Some SQL systems support a PIVOT operator. In portable SQL, conditional aggregation can produce a similar result:

SELECT
    s.Region,
    SUM(CASE WHEN d.Quarter = 'Q1' THEN f.SalesAmount ELSE 0 END) AS Q1_Revenue,
    SUM(CASE WHEN d.Quarter = 'Q2' THEN f.SalesAmount ELSE 0 END) AS Q2_Revenue
FROM FactSales AS f
JOIN DimDate AS d ON f.DateKey = d.DateKey
JOIN DimStore AS s ON f.StoreKey = s.StoreKey
GROUP BY s.Region;

Pivoted displays are convenient for comparison, but they may be less suitable for automated processing. A normalized, row-based result is often easier to reuse in downstream analysis.

5. Descriptive, Diagnostic and Ad Hoc Analysis

5.1 Descriptive analysis

Descriptive analysis answers “What happened?” It summarizes historical data using totals, counts, averages, trends and distributions. Examples include monthly revenue, number of customers and average order value.

Descriptive results should specify the period, population, measure definition and aggregation method. “Sales increased” is incomplete unless the analyst states by how much, compared with what baseline and under which inclusion rules.

5.2 Diagnostic analysis

Diagnostic analysis asks “Why did it happen?” It compares segments, investigates exceptions and drills into contributing factors. For example, a fall in profit may be examined by product, region, discount, return rate and cost category.

Diagnostic analysis does not automatically establish causation. If sales fell after a price change, the timing suggests a relationship but does not prove that the price change was the only cause. Other factors may include competitor actions, stock-outs or seasonal demand.

5.3 Ad hoc analysis

Ad hoc analysis is an unscheduled investigation prompted by a new question. It differs from a routine dashboard because the question, filters or comparison were not fully specified in advance. Ad hoc exploration is valuable for discovery, but it requires discipline.

A responsible ad hoc workflow is:

1. State the question and decision context.
2. Define the population, period and measures.
3. Check data quality and grain.
4. Produce a baseline summary.
5. Slice, dice, drill down or roll up systematically.
6. Test alternative explanations.
7. Record assumptions and communicate uncertainty.

6. Worked Example: Investigating a Profit Decline

Assume a retailer reports the following quarterly figures:

The revenue change is 20,000, or 4%. Profit decreased by 20,000, or approximately 13.3% relative to Q1:

The margin changed from 30% in Q1 to 25% in Q2:

The descriptive conclusion is that revenue rose but profit and margin fell. A diagnostic investigation should drill down by product category and region. It may discover that discounts increased in one category, input costs rose in another, or sales shifted toward lower-margin products.

The correct conclusion is not simply “discounts caused the decline” unless the data and a suitable analytical design support that claim. OLAP reveals patterns and supports investigation; managerial judgment and further analysis are required to establish explanations.

7. Illustrative Case: Northstar Retail

Background

Northstar Retail operates 80 stores and an online channel. Senior management observes that annual revenue is above target, but operating profit is below target. The business intelligence team is asked to investigate.

Problem

A single annual total cannot show whether the problem is caused by geography, product mix, discounting, returns or operating costs. The team must explore the data without losing the meaning of each measure.

Analysis

The team first confirms the fact-table grain: one row represents one product line on one order. It validates that cancelled orders are excluded and returned items are represented according to the finance policy.

The team creates a descriptive summary by year, channel and region. It then slices the data to the current year, dices it to the online channel and two regions, and drills down from year to quarter and product category. A pivoted table compares quarterly margins across regions.

The analysis shows that revenue growth is concentrated in online sales of low-margin products. At the same time, promotional discounts are higher in Q3 and returns are elevated for one product category. A roll-up to company level had hidden these differences.

Approach and solution

Management introduces a category-level margin review, separates gross sales from net sales after returns, and creates a governed dashboard containing definitions for revenue, cost, discount and margin. Analysts retain the ability to perform ad hoc exploration, but published metrics are based on documented semantic rules.

Outcome

The organization identifies a product-mix problem rather than a simple sales-volume problem. It tests revised promotions, improves product descriptions to reduce returns and monitors margin by channel and category.

Learning points

1. The grain of the fact table must be known before aggregation.
2. Revenue growth does not guarantee profit growth.
3. Drill-down and dicing expose patterns hidden by company totals.
4. Ratios should be recalculated from their components.
5. Ad hoc analysis should lead to governed, repeatable metrics when a question becomes routine.

8. Quality, Governance and Ethical Considerations

Analytical exploration depends on trustworthy data. Important controls include:

• Accuracy: values should represent the real business events.
• Completeness: missing stores, products or periods should be identified.
• Consistency: definitions and currencies should be aligned.
• Timeliness: users should know the data-refresh time.
• Uniqueness: duplicate facts should not be counted twice.
• Validity: values should conform to permitted formats and business rules.

Governance should define who owns a measure, how it is calculated, which data sources are authoritative and how changes are approved. A metric catalogue or semantic layer can reduce conflicting versions of “sales” or “customer.”

Ethical issues are also relevant. Customer-level analysis should follow privacy and data-protection requirements. Access should be restricted according to legitimate business need. Analysts should avoid presenting a selected slice as if it represented the whole population. Dashboards should make filters and exclusions visible, especially when decisions affect employees, customers or suppliers.

AI-assisted query generation can help users formulate SQL or discover patterns, but generated queries must be reviewed. An AI tool may misunderstand table grain, join keys, privacy rules or metric definitions. Human oversight, testing and auditability remain essential.

9. Best Practices and Common Errors

Best practices

• Define the business question before exploring.
• Document fact-table grain and measure definitions.
• Use explicit joins and validate row counts after joins.
• Compare totals with an authoritative source.
• Show both absolute and percentage changes where appropriate.
• Recalculate ratios from aggregated numerators and denominators.
• Make filters, dates and exclusions visible.
• Drill down only as far as the decision requires.
• Preserve reproducible queries for important findings.
• Separate evidence from interpretation and causal claims.

Common errors

Double counting: Joining a fact table to a one-to-many table without controlling the relationship can multiply measures.

Summing percentages: Percentages and ratios usually require recalculation from base values.

Ignoring grain: Combining order-level and line-level data can inflate counts or revenue.

Confusing zero and NULL: A missing value is not automatically a zero.

Overlooking time definitions: Calendar and financial periods may differ.

Overinterpreting correlations: A visible pattern is not proof of causation.

Hiding filters: A chart without its population and time period can mislead users.

Contemporary Developments

Modern analytical platforms increasingly combine cloud data warehouses, semantic layers, columnar storage, in-memory processing and natural-language interfaces. These technologies can make multidimensional exploration faster and more accessible. However, speed does not correct poor definitions or poor data.

Self-service BI allows business users to create their own reports and analyses. This improves agility but increases the risk of metric duplication, uncontrolled access and inconsistent interpretations. A balanced operating model provides governed core datasets and approved measures while allowing controlled exploration.

Chapter Summary

OLAP and relational queries provide complementary ways to explore business data. Relational SQL selects, joins, filters, groups and aggregates records. OLAP organizes measures across dimensions and hierarchies, enabling users to slice, dice, drill down, roll up and pivot.

A reliable analysis begins with the grain of the fact table and clear definitions of measures. Additive measures can generally be summed, whereas semi-additive and non-additive measures require special treatment. Ratios should usually be recalculated from aggregated components rather than averaged or summed.

Descriptive analysis explains what happened, diagnostic analysis investigates why it happened, and ad hoc analysis responds to unscheduled questions. None of these automatically proves causation. Effective analytical exploration combines technical accuracy, business context, data governance, privacy protection and clear communication.

Key Terms

Ad hoc analysis: An unscheduled analysis designed to answer a new or specific question.

Additive measure: A measure that can be summed across the relevant dimensions.

Dimension: A descriptive category used to organize, filter or compare measures.

Dicing: Selecting a sub-cube using filters on multiple dimensions.

Drill-down: Moving from summarized data to a more detailed level.

Fact table: A table containing business events and measurable values.

Grain: The precise meaning represented by one row in a fact table.

Hierarchy: Ordered levels within a dimension, such as day, month, quarter and year.

Measure: A numerical value used for analysis, such as revenue or quantity.

Non-additive measure: A measure that should not be summed, such as a percentage.

OLAP: Online analytical processing for multidimensional business analysis.

Pivoting: Reorienting a result so dimensions appear in different rows or columns.

Roll-up: Moving from detailed data to a higher-level summary.

Semantic layer: A governed business representation of data entities and measures.

Slice: Fixing one dimension value to examine a selected portion of a cube.

SQL: Structured Query Language used to retrieve and analyze relational data.

Star schema: A dimensional model with a central fact table linked to dimension tables.

Diagnostic analysis: Analysis that investigates factors associated with an observed result.

Descriptive analysis: Analysis that summarizes what has happened.

Review Questions

Short Answer Questions

1. What is the difference between a fact table and a dimension table?
2. Define slicing and dicing in OLAP.
3. Why must the grain of a fact table be documented?
4. What is the difference between WHERE and HAVING in SQL?
5. Why should profit margin generally be recalculated after aggregation?

Long Answer Questions

1. Explain how drill-down and roll-up support managerial analysis, using a sales example.
2. Compare descriptive, diagnostic and ad hoc analysis, including an appropriate use case for each.
3. Discuss additive, semi-additive and non-additive measures and explain why their aggregation rules differ.
4. Describe the main data-quality and governance controls required for reliable OLAP reporting.
5. Explain how a star schema supports analytical queries and multidimensional exploration.

Analytical / Application Questions

1. A company’s revenue increases by 8%, but its profit margin declines from 24% to 19%. Design an OLAP exploration using dimensions, slices and drill-down levels to investigate the change.
2. An analyst joins order lines to a table containing several promotions per order and reports that revenue has doubled. Identify the likely technical problem and propose validation steps.
3. A regional manager wants to compare quarterly margins across stores but also needs a company-wide margin. Explain how the result should be calculated and displayed to avoid misleading aggregation.

Multiple Choice Questions with Answers

Question 1

Which statement best describes a measure in an OLAP model?

A. A descriptive category such as region

B. A numerical value such as revenue or quantity

C. A database connection key only

D. A reporting-period label only

Correct Answer: B. A numerical value such as revenue or quantity

Question 2

Which OLAP operation selects data for one specific year?

A. Slice

B. Roll-up

C. Drill-down

D. Pivot

Correct Answer: A. Slice

Question 3

What is the main purpose of a fact-table grain definition?

A. To determine the colour of a dashboard

B. To specify what one row represents

C. To remove all NULL values

D. To replace dimension tables

Correct Answer: B. To specify what one row represents

Question 4

Which SQL clause filters groups after aggregation?

A. WHERE

B. ORDER BY

C. HAVING

D. FROM

Correct Answer: C. HAVING

Question 5

A manager moves from annual revenue to monthly revenue. Which operation is being performed?

A. Dicing

B. Drill-down

C. Roll-up

D. Pivoting

Correct Answer: B. Drill-down

Question 6

Why is averaging product-level profit percentages often misleading?

A. Percentages cannot be displayed in reports

B. Products may have different revenue bases

C. SQL cannot calculate averages

D. Profit is always non-numerical

Correct Answer: B. Products may have different revenue bases

Question 7

Which type of analysis asks, “What happened?”

A. Descriptive analysis

B. Diagnostic analysis

C. Predictive analysis

D. Ad hoc governance

Correct Answer: A. Descriptive analysis

Question 8

Revenue is 200,000 and cost is 150,000. What is the profit margin?

A. 15%

B. 20%

C. 25%

D. 35%

Correct Answer: C. 25%

Question 9

Which is a likely consequence of an incorrect one-to-many join involving a fact table?

A. Measures may be duplicated and overstated

B. All dimensions become additive

C. NULL values become valid totals

D. Drill-down becomes impossible in every system

Correct Answer: A. Measures may be duplicated and overstated

Question 10

Which statement about diagnostic analysis is most accurate?

A. It proves causation whenever two trends coincide

B. It investigates factors associated with an observed result

C. It only reports annual totals

D. It cannot use OLAP operations

Correct Answer: B. It investigates factors associated with an observed result