Querying, OLAP and Self-Service BI

Introduction

Business intelligence (BI) depends on the ability to transform operational data into information that supports decisions. Querying provides the technical means to retrieve and combine data. Multidimensional modelling provides a structure for analysing data by business perspectives such as time, product, customer and geography. Online analytical processing (OLAP) provides operations for examining measures at different levels of detail. Self-service BI extends these capabilities to business users, while governance ensures that speed and accessibility do not undermine accuracy, security or accountability.

A business analyst may begin with a question such as: “Which product categories caused the decline in regional profit during the last quarter?” Answering it reliably requires more than a chart. The analyst must identify the correct data sources, understand the grain of the data, select appropriate measures, join tables correctly, apply filters, interpret hierarchies and communicate limitations. This chapter develops those capabilities.

Learning Objectives

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

1. Explain the role of SQL in business intelligence querying.
2. Write and interpret SQL statements using filtering, grouping, joins, subqueries and common table expressions.
3. Distinguish relational, dimensional and multidimensional representations of data.
4. Explain cubes, measures, dimensions, hierarchies and aggregation.
5. Apply the OLAP operations drill-down, roll-up, slice, dice and pivot.
6. Evaluate self-service BI within a governed operating model.
7. Explain the roles of data catalogs, metadata and data literacy.
8. Analyse the benefits, risks and professional implications of self-service analytics.

1. Querying as an Analytical Activity

A query is a structured request for data. In BI, querying is not merely a technical procedure; it is an act of defining a business question precisely. Before writing SQL, an analyst should clarify:

• the population or business process being analysed;
• the time period;
• the unit of observation, known as the grain;
• the measures required;
• the dimensions by which results will be compared;
• the filters and exclusions;
• the required level of accuracy and freshness.

For example, “sales” may mean order value, invoiced value, shipped value or recognised revenue. “Customer” may mean the ordering account, the end consumer or the billing entity. Ambiguous definitions create technically valid but misleading results.

Relational Foundations

In a relational database, data is stored in tables consisting of rows and columns. A primary key uniquely identifies a row. A foreign key refers to a key in another table and supports relationships. Normalisation reduces redundancy in operational systems, whereas analytical models often deliberately introduce dimensional structures that are easier to query.

Consider the following simplified tables:

• Sales(OrderID, CustomerKey, ProductKey, DateKey, Quantity, NetSales, Cost)
• Customer(CustomerKey, CustomerName, Segment, Region)
• Product(ProductKey, ProductName, Category, Brand)
• Date(DateKey, CalendarDate, Month, Quarter, Year)

The grain of Sales must be stated explicitly. If each row represents one order line, then Quantity, NetSales and Cost are line-level measures. If each row represents an order, the same columns have a different meaning.

Core SQL Operations

A basic query selects columns from a table:

SELECT ProductKey, NetSales
FROM Sales;

Filtering is performed with WHERE:

SELECT OrderID, NetSales
FROM Sales
WHERE NetSales > 1000;

Conditions can be combined with AND, OR and NOT. Text values are normally enclosed in single quotation marks. Dates should be filtered using a consistent, unambiguous date representation supported by the database system.

Sorting is performed with ORDER BY:

SELECT OrderID, NetSales
FROM Sales
ORDER BY NetSales DESC;

Aggregation summarises rows. Common aggregate functions include SUM, AVG, MIN, MAX and COUNT:

SELECT SUM(NetSales) AS TotalSales,
       AVG(NetSales) AS AverageLineSales,
       COUNT(*) AS NumberOfLines
FROM Sales;

When a result contains both aggregated and non-aggregated columns, the non-aggregated columns generally belong in a GROUP BY clause:

SELECT ProductKey,
       SUM(NetSales) AS TotalSales
FROM Sales
GROUP BY ProductKey;

WHERE filters rows before aggregation. HAVING filters groups after aggregation:

SELECT ProductKey,
       SUM(NetSales) AS TotalSales
FROM Sales
WHERE DateKey >= 20240101
GROUP BY ProductKey
HAVING SUM(NetSales) >= 100000;

Joins and Their Risks

A join combines related tables. An inner join returns matching rows from both tables:

SELECT p.Category,
       SUM(s.NetSales) AS TotalSales
FROM Sales AS s
JOIN Product AS p
  ON s.ProductKey = p.ProductKey
GROUP BY p.Category;

A left join returns every row from the left table, even when a match does not exist on the right:

SELECT c.Segment,
       SUM(s.NetSales) AS TotalSales
FROM Customer AS c
LEFT JOIN Sales AS s
  ON c.CustomerKey = s.CustomerKey
GROUP BY c.Segment;

Joins can produce incorrect totals when the relationship is not one-to-one. If one sales row joins to three matching rows in another table, its value may be counted three times. Analysts should therefore inspect key uniqueness, relationship cardinality and row counts before and after a join.

A useful validation procedure is:

1. Count rows in the fact table before joining.
2. Check whether the joining key is unique in the dimension table.
3. Compare total sales before and after the join.
4. Investigate unexpected increases or losses.
5. Test records with missing or duplicate keys.

Subqueries and Common Table Expressions

A subquery is a query nested inside another query. A common table expression (CTE) gives a temporary named result and often improves readability:

WITH regional_sales AS (
    SELECT c.Region,
           SUM(s.NetSales) AS TotalSales
    FROM Sales AS s
    JOIN Customer AS c
      ON s.CustomerKey = c.CustomerKey
    GROUP BY c.Region
)
SELECT Region, TotalSales
FROM regional_sales
WHERE TotalSales > 500000;

CTEs are valuable for staged analysis, but they do not automatically improve performance. Performance depends on the database engine, indexes, partitioning, data volume and execution plan.

Nulls, Duplicates and Measures

A null is not the same as zero, an empty string or “not applicable.” For example, a null cost may mean that cost has not been recorded; replacing it with zero could falsely inflate profit. SQL aggregate functions generally ignore null values, although COUNT(*) counts rows and COUNT(column) counts non-null values.

A basic profit measure can be represented as:

Here, Profit, NetSales and Cost must use the same currency and accounting basis. A margin percentage is:

This percentage is meaningful only when NetSales ≠ 0 and when sales and cost are comparable in scope and timing. Aggregating percentages directly can be misleading. A total margin should normally be calculated from total profit divided by total sales, rather than as an unweighted average of product-level margins.

2. Dimensional Modelling

Dimensional modelling organises analytical data around business processes. The central structure is a fact table surrounded by dimension tables, commonly called a star schema.

A fact table contains measurements and foreign keys. Dimension tables contain descriptive attributes used for filtering, grouping and labelling. In a sales model, the fact table may contain quantity, sales, discount and cost, while dimensions may represent date, product, customer, store and promotion.

Grain and Additivity

The grain states what one fact row represents. Examples include one sales order line, one daily inventory position per warehouse and product, or one monthly budget per department. A fact table should have one clearly declared grain.

Measures differ in their additive behaviour:

Inventory is often semi-additive because summing daily closing balances across time creates a meaningless figure. Analysts may use the ending balance, average balance or another explicitly defined time aggregation.

Star and Snowflake Schemas

A star schema has denormalised dimensions connected directly to a fact table. It is generally easy for analysts to understand and often performs well for BI workloads. A snowflake schema normalises some dimensions into additional related tables. Snowflaking can reduce redundancy but may increase query complexity.

Dimensions may contain hierarchies. A geographic hierarchy may be Country → Region → City → Store. A time hierarchy may be Year → Quarter → Month → Day. Hierarchies enable movement between levels of detail.

Dimension keys are often surrogate keys generated by the warehouse rather than business identifiers. This supports historical tracking and avoids dependence on changing operational codes. Slowly changing dimensions manage changes in descriptive attributes. For example, a customer’s region may be overwritten for current reporting or preserved historically through versioned dimension records. The chosen method must match the reporting requirement.

3. Multidimensional Models and OLAP

An OLAP cube is a conceptual model in which measures are analysed across dimensions. A cube need not be physically stored as a geometric cube. It may be implemented through relational tables, columnar storage, an OLAP engine or a semantic layer.

A cube can be described using:

• Measures: numerical values such as sales, units, cost and profit.
• Dimensions: analytical perspectives such as time, product and geography.
• Members: individual values within a dimension, such as “2024,” “Electronics” or “North Region.”
• Hierarchies: ordered levels within a dimension.
• Cells: intersections of dimension members containing measures.
• Aggregations: summaries calculated at selected levels.

Suppose sales are analysed by three dimensions: product category, region and month. A cell may represent Electronics × North × March. The measure in that cell might be total sales for that intersection.

The multidimensional perspective helps analysts ask questions such as:

• How did sales change from year to year?
• Which regions contributed to a category decline?
• What is the total sales value for one product category in one quarter?
• How does a store compare with its region and national total?

OLAP Operations

Roll-up moves from detailed data to a higher-level summary. For example, daily sales may be rolled up to month, or city sales to region. Roll-up may use a predefined hierarchy or remove a dimension entirely.

Drill-down moves from summary to greater detail. An analyst may drill down from annual sales to quarterly, monthly and daily sales, or from region to store.

Slice selects one member of a dimension, producing a smaller view. For example, selecting the year 2024 creates a slice of the cube.

Dice selects multiple members or ranges from two or more dimensions. For example, an analyst may select Electronics and Furniture, the North and West regions, and the first two quarters.

Pivot, sometimes called rotate, changes the orientation of dimensions in a report. A table with regions as rows and months as columns may be pivoted so that months are rows and regions are columns. Pivot changes presentation rather than the underlying facts.

These operations are conceptually distinct, although BI tools may combine them through filters, hierarchy controls and visual interactions.

Figure: Conceptual relationship between a cube and major OLAP operations.

4. Querying Dimensional Data

A dimensional query commonly joins a fact table to dimensions, filters a period, groups by descriptive attributes and calculates measures:

SELECT d.Year,
       d.Quarter,
       p.Category,
       c.Region,
       SUM(s.NetSales) AS TotalSales,
       SUM(s.NetSales - s.Cost) AS TotalProfit
FROM Sales AS s
JOIN Date AS d ON s.DateKey = d.DateKey
JOIN Product AS p ON s.ProductKey = p.ProductKey
JOIN Customer AS c ON s.CustomerKey = c.CustomerKey
WHERE d.Year IN (2023, 2024)
GROUP BY d.Year, d.Quarter, p.Category, c.Region
ORDER BY d.Year, d.Quarter, p.Category, c.Region;

This query illustrates a multidimensional view using relational SQL. The same result might be produced through a semantic model in which the user selects fields and measures without writing SQL.

Some SQL systems support window functions, which calculate values across related rows without collapsing them. For example, a regional total can be compared with the national total using a window expression:

SELECT Region,
       SUM(NetSales) AS RegionalSales,
       SUM(SUM(NetSales)) OVER () AS NationalSales
FROM Sales AS s
JOIN Customer AS c ON s.CustomerKey = c.CustomerKey
GROUP BY Region;

The inner SUM creates one result per region; the window function then sums those grouped results. Syntax and behaviour vary among database platforms, so analysts must consult the relevant documentation.

5. Self-Service BI

Self-service BI enables business users to access data, create analyses and build reports with limited dependence on a central IT team. Typical capabilities include visual query builders, drag-and-drop modelling, dashboards, natural-language questions, collaboration and scheduled refreshes.

Self-service BI can reduce reporting delays and encourage experimentation. Subject-matter experts often understand business context better than a central technical team. However, unrestricted self-service can produce conflicting definitions, duplicated datasets, insecure sharing and “spreadsheet sprawl.” The objective is therefore not uncontrolled independence but governed empowerment.

Governed Self-Service

A governed self-service model combines user autonomy with institutional controls. Important components include:

1. Certified data sources: approved datasets whose ownership, refresh schedule, grain and definitions are documented.
2. Semantic models: shared definitions for measures such as revenue, active customer and gross margin.
3. Role-based access: permissions based on job responsibilities and least-privilege principles.
4. Row-level security: restrictions that ensure users see only authorised records, such as their own region.
5. Workspace standards: rules for naming, publication, retention and development versus production content.
6. Lineage and impact analysis: visibility into where data originated and which reports depend on it.
7. Quality monitoring: checks for completeness, timeliness, validity, uniqueness and consistency.
8. Certification and endorsement: visible signals distinguishing trusted content from personal or experimental work.
9. Change management: communication and testing when schemas, definitions or pipelines change.
10. Training and support: practical instruction in data interpretation, security and responsible use.

A useful governance principle is “centralise what must be consistent; decentralise what benefits from local expertise.” Enterprise definitions, security and critical data quality controls generally require central oversight. Exploratory analysis and local presentation may be delegated within those boundaries.

Data Catalogs and Metadata

A data catalog is an organised inventory of data assets. It may contain tables, dashboards, reports, measures, owners, business definitions, sensitivity classifications, refresh information, quality indicators and lineage.

Metadata is “data about data.” Technical metadata describes data types, columns and relationships. Business metadata explains meaning, ownership and appropriate use. Operational metadata records refresh times, failure history and usage. Governance metadata may record classifications, retention requirements and access restrictions.

A catalog is useful only when it is maintained and integrated into work practices. Users should be able to discover a dataset, understand its meaning, assess its quality and request access without excessive friction. Owners should review assets regularly and retire obsolete reports.

Data Literacy

Data literacy is the ability to read, interpret, create and communicate data appropriately. It includes statistical reasoning, source evaluation, awareness of uncertainty, understanding of measures and recognition of misleading visualisations. Technical SQL skills are valuable but do not by themselves establish data literacy.

A data-literate analyst asks:

• What exactly does each row represent?
• Who owns this dataset and when was it refreshed?
• Is the measure additive at the selected level?
• Could missing values or exclusions change the result?
• Does the chart show correlation, comparison or change over time?
• Are the conclusions appropriate for the evidence?

Organisations can assess data literacy through practical tasks rather than only software training. For example, learners might be asked to identify a duplicated join, interpret a dashboard with missing data or choose an appropriate aggregation for inventory.

6. Applied Case: Regional Retail Performance

The following is an illustrative case.

Background

Northstar Retail operates 180 stores and an online channel. Executives report that total quarterly sales increased, but profit declined. Regional managers use separate spreadsheets, and the organisation has several conflicting definitions of “sales” and “margin.”

Problem

The BI team must create a trusted analysis that identifies the source of the profit decline while allowing regional managers to explore their own data. The solution must protect customer information and prevent users from publishing unverified corporate metrics.

Analysis

The team first defines the fact grain as one order line. Net sales exclude tax and returns are represented as negative line values. Cost is recorded at the same line level. Product, customer, store and date dimensions are linked using warehouse keys.

An initial SQL query aggregates sales and profit by quarter, category and region. Validation identifies that an old promotional table contains multiple rows per promotion code. Joining it directly duplicates some sales lines. The team resolves the issue by creating a unique promotion dimension and testing row counts before approving the model.

The analysis shows that total sales increased by 6%, but high-margin electronics declined in the West region. Discounting increased, while returns in the online channel were recorded later than sales. The apparent timing difference exaggerated the decline in some weekly reports.

Approach and Solution

The BI team creates a certified semantic model with the following measures:

• Net Sales
• Cost of Goods Sold
• Gross Profit
• Gross Margin Percentage
• Units Sold
• Return Rate

Gross margin percentage is defined as total gross profit divided by total net sales, not as an average of row-level percentages. The model includes a date hierarchy, product hierarchy and geography hierarchy. Regional managers receive row-level access to their authorised region. Executives receive an enterprise view.

A catalog entry documents the grain, measure definitions, owner, refresh schedule, known limitations and lineage. A separate workspace permits experimentation, but content must pass review before being promoted to the certified workspace.

Managers use roll-up to examine annual and quarterly performance, drill-down to identify stores, slice the model to 2024, dice it to selected categories and regions, and pivot the report to compare months across regions.

Outcome

The organisation identifies discounting and delayed return recognition as the principal contributors to the reported profit decline. It standardises metric definitions, reduces reconciliation meetings and enables managers to investigate performance without creating uncontrolled copies of the data.

Learning Points

1. Correct grain is a prerequisite for reliable aggregation.
2. Join cardinality can create silent financial errors.
3. Semantic definitions are as important as visual design.
4. Governance can accelerate self-service when it makes trusted data easy to find.
5. A dashboard should communicate limitations as well as results.

7. Risks, Ethics and Professional Practice

Self-service BI introduces several risks. Confidential customer or employee data may be exposed through inappropriate sharing. A user may infer sensitive attributes from apparently harmless combinations of fields. Access controls should therefore be designed around data sensitivity, purpose limitation and least privilege.

Automated recommendations and natural-language interfaces can make BI easier to use, but they may also generate incorrect queries, obscure assumptions or reproduce biased historical patterns. AI-assisted BI should provide traceability, allow users to inspect the underlying data and clearly distinguish generated suggestions from verified results.

Important controls include:

• authentication and multi-factor protection;
• encryption in transit and at rest where appropriate;
• access reviews and audit logs;
• retention and deletion policies;
• privacy-preserving aggregation where necessary;
• documented limitations and confidence information;
• human review for high-impact decisions.

Legal requirements vary by jurisdiction and sector. Privacy, financial reporting, employment and records-management obligations may all affect BI design. Organisations should consult applicable law and internal policy rather than treating governance as merely a software feature.

8. Best Practices and Contemporary Developments

Effective BI practice combines technical discipline with business judgement. Analysts should begin with a clearly stated question, document assumptions, validate totals against a trusted control, and test unusual results. Queries should be readable, use meaningful aliases and avoid unnecessary complexity. Production reports should be version-controlled or otherwise subject to controlled change.

Modern platforms increasingly use cloud warehouses, columnar storage, semantic layers, metrics stores, data mesh arrangements and embedded analytics. These developments can improve scalability and access, but they do not eliminate the need for ownership, definitions or quality controls. A “single source of truth” is often better understood as a shared, governed definition and lineage—not necessarily one physical database.

Performance can be improved through appropriate indexing, partitioning, aggregation, query reduction and efficient modelling. Performance tuning should follow measurement of actual workload rather than assumptions. A fast query that returns the wrong result has negative value.

A mature self-service BI programme therefore measures both adoption and trust. Useful indicators include certified-content usage, unresolved data-quality incidents, report duplication, access-review completion, time to answer approved questions and user capability development. Adoption alone is not evidence of analytical success.

Chapter Summary

SQL enables analysts to retrieve, filter, join and aggregate data, but reliable querying begins with a precise business definition and an explicit statement of data grain. Dimensional models organise facts and descriptive dimensions for analytical use. Measures must be classified according to their additive behaviour, and joins must be checked for cardinality and duplication risks.

OLAP provides a conceptual framework for examining measures across dimensions and hierarchies. Roll-up summarises data, drill-down reveals detail, slice selects one member, dice selects combinations or ranges, and pivot changes presentation. These operations support structured exploration of business performance.

Self-service BI gives business users greater analytical independence. Its benefits are realised when supported by governed semantic models, certified datasets, security controls, catalogs, lineage, quality monitoring and data literacy. The central challenge is to balance autonomy with consistency, privacy and accountability. Effective BI professionals combine SQL and modelling competence with critical interpretation, communication and ethical judgement.

Key Terms

Aggregation: The process of summarising detailed records into totals or other measures.

Business metadata: Information describing the meaning, ownership and appropriate use of data.

Data catalog: An inventory of data assets, definitions, owners, quality indicators and lineage.

Data grain: The precise business meaning of one row in a fact table.

Data literacy: The ability to read, interpret, create and communicate data appropriately.

Dimension: A descriptive analytical perspective, such as product, date or region.

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

Fact table: A table containing business-event measures and links to dimensions.

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

Join: An operation that combines rows from related tables.

Measure: A numerical value analysed in a BI model.

OLAP: Online analytical processing for interactive multidimensional analysis.

Pivot: Changing the orientation of dimensions in a report or analysis.

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

Semantic model: A governed layer containing business entities, relationships and measure definitions.

Slice: Selecting one member of a dimension.

Dice: Selecting multiple members or ranges across dimensions.

Star schema: A dimensional model with a central fact table connected directly to dimensions.

Surrogate key: A warehouse-generated identifier used to identify a dimension record.

Row-level security: A control that restricts which records a user can view.

Review Questions

Short Answer Questions

1. Why must the grain of a fact table be stated before measures are aggregated?
2. What is the difference between a WHERE clause and a HAVING clause?
3. Define roll-up and drill-down in OLAP.
4. Why can a direct average of percentages be misleading?
5. What information might a data catalog contain?

Long Answer Questions

1. Explain how a star schema supports business intelligence querying. Include the roles of fact tables, dimensions, keys and hierarchies.
2. Compare slice, dice and pivot operations, using a retail example for each.
3. Discuss the benefits and risks of self-service BI and propose a governed operating model.
4. Explain how join cardinality, null values and additive behaviour can affect the reliability of BI results.
5. Evaluate the role of data literacy in achieving value from a semantic model and data catalog.

Analytical / Application Questions

1. A sales query returns total revenue that is twice the amount recorded in the source system after a promotion table is joined. Diagnose the likely cause and describe a validation procedure.
2. An inventory dashboard sums daily closing balances across a quarter and labels the result “quarterly inventory.” Assess the problem and recommend a more appropriate measure or aggregation.
3. Design an OLAP analysis for a retailer investigating declining profit. Specify suitable measures, dimensions, hierarchies and at least one use of roll-up, drill-down, slice, dice and pivot.

Multiple Choice Questions with Answers

Question 1

Which SQL clause filters groups after aggregation?

A. WHERE

B. HAVING

C. ORDER BY

D. FROM

Correct Answer: B. HAVING

Question 2

Which measure is normally semi-additive across time?

A. Units sold

B. Net sales

C. Ending inventory balance

D. Order-line quantity

Correct Answer: C. Ending inventory balance

Question 3

An OLAP roll-up operation generally does what?

A. Moves from summary to greater detail

B. Changes only the visual colour of a chart

C. Moves from detail to a higher-level summary

D. Deletes unwanted dimension members

Correct Answer: C. Moves from detail to a higher-level summary

Question 4

What is the primary purpose of a data catalog?

A. To replace all database security controls

B. To inventory data assets and provide metadata

C. To automatically correct every data-quality error

D. To prevent business users from creating reports

Correct Answer: B. To inventory data assets and provide metadata

Question 5

Which SQL operation is most likely to inflate totals when the right-hand table has multiple matching rows for one fact?

A. A many-to-one join

B. A one-to-one join

C. A many-to-many or duplicate-key join

D. An ORDER BY operation

Correct Answer: C. A many-to-many or duplicate-key join

Question 6

Selecting the year 2024 from a multidimensional model is an example of which OLAP operation?

A. Slice

B. Drill-down

C. Pivot

D. Roll-up

Correct Answer: A. Slice

Question 7

Which statement best describes governed self-service BI?

A. Users may access any data without controls

B. Only IT employees can create any analysis

C. Users receive analytical autonomy within standards for security, quality and definitions

D. Every user creates an independent copy of enterprise data

Correct Answer: C. Users receive analytical autonomy within standards for security, quality and definitions

Question 8

What does data grain describe?

A. The visual resolution of a dashboard

B. What one row in a fact table represents

C. The number of users accessing a report

D. The storage capacity of a database

Correct Answer: B. What one row in a fact table represents

Question 9

If total profit is 30,000 and total net sales are 150,000, what is the gross margin percentage?

A. 5%

B. 20%

C. 50%

D. 180%

Correct Answer: B. 20%

Question 10

Which control restricts users to records belonging to their authorised region?

A. Row-level security

B. Pivoting

C. Query sorting

D. Column formatting

Correct Answer: A. Row-level security