09DWConcepts

09DWConcepts
ISIT312 Big Data Management

Data Warehouse Concepts

Outline

OLAP versus OLTP

The Multidimensional Model

OLAP Operations

Data Warehouse Architecture

TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

Characteristics and purpose of Online Transaction Processing (OLTP)

(1) Designed and tuned for day-to-day operations: Their primary goal is to support the ongoing business activities, such as processing sales, managing inventory, handling customer inquiries, and so on. They are optimized for the speed and efficiency of these routine operations.

(2)Ensure fast, concurrent access to data: Many users or applications need to access and modify data simultaneously. OLTP systems are built to handle a high volume of concurrent read and write operations without significant slowdowns.

(3) Transaction processing and concurrency control: A "transaction" in this context is a single, logical unit of work (e.g., placing an order, updating a customer's address). OLTP systems employ sophisticated mechanisms for transaction processing (ensuring that transactions are either fully completed or completely rolled back) and concurrency control (managing how multiple transactions interact to prevent data corruption).

(4) Focus on online update data consistency: Data integrity is paramount. OLTP systems ensure that data is always consistent and accurate, even when many updates are happening simultaneously. This is achieved through mechanisms like ACID properties (Atomicity, Consistency, Isolation, Durability).

OLTP Database Characteristics:

Specific Characteristics of OLTP databases:

(1) Detailed data: They store the most granular, individual pieces of information necessary for daily operations. For example, every single sales transaction, every individual product in stock, every customer's contact detail.

(2) Do not include historical data: While they capture current data, OLTP systems typically don't store extensive historical records. Keeping vast amounts of old data can slow down current operations. For example, once an order is shipped, the detailed line items of that order from five years ago might not be readily available for quick operational queries.

(3) Highly normalized: Normalization is a database design technique that organizes tables to minimize data redundancy and improve data integrity. In a highly normalized schema, data is broken down into many small, interconnected tables. This helps ensure consistency when data is updated. For example, customer information might be in one table, order details in another, and product information in a third, with relationships between them.

(4) Poor performance on complex queries including joins and aggregation: Because of their highly normalized structure (data spread across many tables) and their optimization for rapid, individual transactions, OLTP systems are not efficient for complex analytical queries. Queries that involve joining many tables together or aggregating large sets of data (like calculating total sales across all products for a year) can be very slow because they require reading and processing a vast amount of data from multiple distributed locations.

Typical OLTP query: pending orders for a customer: This is a classic operational query. It's specific, focused on current data, and typically involves retrieving a small, precise set of records.

Typical OLAP query: total sales amount by a product and by a customer: This is an analytical query. It requires summarizing and aggregating large volumes of historical data, often across different dimensions (product, customer, time, region, etc.). Such a query would perform poorly on a typical OLTP system.

Tthe need for deep data analysis led to the development of a new paradigm: Online Analytical Processing (OLAP). OLAP systems are designed specifically to handle these complex, aggregate queries efficiently, often by storing data in a denormalized or multidimensional format optimized for reporting and analysis. They typically use historical data, are less concerned with individual transaction updates, and prioritize fast query performance for analytical insights.

OLAP versus OLTP

Traditional database systems designed and tuned to support the day-to-day operation:

Ensure fast, concurrent access to data

Transaction processing and concurrency control

Focus on online update data consistency

Known as operational databases or online transaction processing (OLTP)

OLTP database characteristics:

Detailed data

Do not include historical data

Highly normalized

Poor performance on complex queries including joins an aggregation

Data analysis requires a new paradigm: online analytical processing (OLAP)

Typical OLTP query: pending orders for a customer

Typical OLAP query: total sales amount by a product and by a customer

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

OLTP paradigm focused on transactions, OLAP focused on analytical queries: This is the core distinction.

OLTP is about processing individual, real-time transactions quickly and reliably (e.g., placing an order, updating a record). It's "row-oriented" in that it typically retrieves or updates a few specific rows at a time.

OLAP is about analyzing large volumes of historical data to uncover trends, patterns, and insights (e.g., total sales by region over the last five years). It's often "column-oriented" or "summary-oriented" in that it aggregates data across many rows and columns.

Normalization not good for analytical queries, reconstructing data requires a high number of joins: As previously discussed, OLTP databases are highly normalized to ensure data integrity during frequent updates. While excellent for transactions, this normalization is detrimental to analytical queries.

To answer an OLAP query like "total sales by product category and customer region," you would need to join numerous normalized tables (e.g., sales, products, categories, customers, regions).

Each join operation is computationally expensive, especially when dealing with millions or billions of records. Reconstructing the data for analysis from a highly normalized structure leads to incredibly slow query performance.

OLAP databases support a heavy query load: While OLTP handles a high volume of concurrent transactions, OLAP systems are designed to handle a heavy load of complex analytical queries. These queries can be resource-intensive, but OLAP systems are optimized to execute them efficiently, often in parallel, over vast datasets. The "load" here refers to the complexity and data volume of individual queries, rather than just the sheer number of simple, concurrent operations.

OLTP indexing techniques not efficient in OLAP: oriented to access few records; OLAP queries typically include aggregation:

OLTP indexes (like B-tree indexes) are optimized for quickly finding and accessing a small number of specific records (e.g., find a customer by their ID). They work well for transactional lookups.

OLAP queries, however, are primarily focused on aggregation (SUM, AVG, COUNT, MIN, MAX) over large datasets. Traditional OLTP indexes are not effective for these operations because they don't efficiently support scanning large ranges of data or summarizing entire columns. OLAP often employs different indexing strategies (e.g., bitmap indexes, join indexes) and data structures (like columnar storage) that are specifically designed for efficient aggregation and filtering across large datasets.

Data warehouse: (usually) large repositories that consolidate data from different sources (internal and external to the organization), are updated offline, follow the multidimensional data model, designed and optimized to efficiently support OLAP queries

OLAP versus OLTP

OLAP characteristics

OLTP paradigm focused on transactions, OLAP focused on analytical queries

Normalization not good for analytical queries, reconstructing data requires a high number of joins

OLAP databases support a heavy query load

OLTP indexing techniques not efficient in OLAP: oriented to access few records; OLAP queries typically include aggregation

The need for a different database model to support OLAP was clear: led to data warehouses

Data warehouse: (usually) large repositories that consolidate data from different sources (internal and external to the organization), are updated offline, follow the multidimensional data model, designed and optimized to efficiently support OLAP queries

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

Data Warehouse Concepts

Outline

OLAP versus OLTP

The Multidimensional Model

OLAP Operations

Data Warehouse Architecture

TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

The multidimensional model is a core concept in data warehousing and Online Analytical Processing (OLAP). It provides a way to view and analyze data from multiple perspectives, making it easier for users to understand complex business information.

At its heart, the multidimensional model conceptualizes data as residing in an "n-dimensional space," often visualized as a data cube. Think of it as a logical structure that organizes data for quick and efficient analysis, rather than a physical cube in the traditional sense.

Data Cube: Dimensions and Facts

A data cube is fundamentally composed of two main elements:

Dimensions: These are the categories or perspectives along which you want to analyze your data. They represent the different axes of your "cube." Dimensions are typically qualitative attributes that provide context to the data.

Example: "Product," "Time," and "Customer" are excellent dimensions. You might want to see sales by a specific product, over a certain period, or to a particular customer segment. Other common dimensions could include "Region," "Salesperson," "Promotion," etc.

Facts (or Measures): These are the quantitative values that you are analyzing. They are the actual numerical data points that reside within the cells of the data cube. Facts are typically numeric and additive, meaning they can be summed up across different dimensions.

Example: "Quantity" in your sales data is a fact. Other common facts in a sales cube might be "Sales Amount," "Profit," "Cost," etc.

How Dimensions and Facts Work Together

Imagine your three-dimensional sales cube with Product, Time, and Customer as dimensions. Each cell within this cube would represent a specific combination of a product, a time period, and a customer. The value within that cell would be the "Quantity" sold for that particular combination.

For instance, if you pick:

Product: "Laptop X"

Time: "January 2026"

Customer: "Corporate Client A"

The cell at the intersection of these three dimensions would contain the "Quantity" of "Laptop X" sold to "Corporate Client A" in "January 2026."

Attributes Describe Dimensions

While dimensions provide the main perspectives for analysis, they often have associated descriptive information called attributes. Attributes provide more detailed characteristics about the dimension members. They help to further qualify or categorize the data within a dimension.

Example: "Product" dimension:

ProductNumber: This attribute uniquely identifies each product.

UnitPrice: This attribute describes the price of a particular product.

Other possible attributes for "Product" could be "Category," "Brand," "Color," etc.

These attributes aren't typically used as primary axes of the cube themselves but are crucial for filtering, drilling down, and providing more detailed context when analyzing data along a specific dimension. You might want to see the total quantity sold for all products with a "UnitPrice" greater than $1000, for instance.

The Multidimensional Model

A view of data in n-dimensional space: a data cube

A data cube is composed of dimensions and facts

Dimensions: Perspectives used to analyze the data

Example: A three-dimensional cube for sales data with dimensions Product, Time, and Customer, and a measure Quantity

Attributes describe dimensions

Product dimension may have attributes ProductNumber and UnitPrice (not shown in the figure)

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

The Cells or Facts of a Data Cube Have Associated Numeric Values Called Measures

As we discussed, the intersection of dimensions forms the "cells" of the data cube. Each of these cells holds a specific measure (or fact).

Measures are the core numerical values that you are interested in analyzing. They are the "what" of your analysis. Measures are typically quantitative and can be aggregated (summed, averaged, counted, etc.) across different dimensions.

Example: "Quantity of units sold" is the measure. This means that for any given combination of a product category, a quarter, and a customer's city, the cell at that intersection will contain the total number of units sold.

If you select:

Dimension 1 (Category): "Electronics"

Dimension 2 (Quarter): "Q3 2026"

Dimension 3 (Customer's City): "Sydney"

The measure within that specific cell would be the total "Quantity of units sold" for "Electronics" in "Q3 2026" to customers in "Sydney."

Other common measures in a business context include:

Sales Amount

Profit

Cost of Goods Sold (COGS)

Number of Customers

Average Order Value

Clicks (for web analytics)

Data Granularity: Level of Detail

Data granularity refers to the level of detail at which the measures are represented for each dimension of the cube. It defines how "fine-grained" or "coarse-grained" your data is.

Think of it as the smallest unit of measurement or aggregation in your data cube. The chosen granularity directly impacts what kind of questions you can answer and how deeply you can drill into your data

Example: sales figures aggregated to granularities Category, Quarter, and City." This means that the lowest level of detail available for analysis in this particular cube is at the intersection of a specific product category, a specific quarter, and a specific customer city.

Let's break down the granularity

Category: You won't see sales for individual products (e.g., "iPhone 15" or "Samsung Galaxy S24"). Instead, the data is rolled up to the "Electronics," "Apparel," "Books," etc., level.

Quarter: You won't see daily or even monthly sales figures. The data is aggregated to "Q1 2026," "Q2 2026," etc.

City: You won't see sales for individual streets or postcodes within a city. The data is aggregated to the "Sydney," "Melbourne," "Brisbane," etc., level.

The Multidimensional Model

The cells or facts of a data cube have associated numeric values called measures

Each cell of the data cube represents Quantity of units sold by category, quarter, and customer's city

Data granularity: level of detail at which measures are represented for each dimension of the cube

Example: sales figures aggregated to granularities Category, Quarter, and City

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

Instances of a Dimension are Called Members

Within a dimension, the individual distinct values are referred to as members. Members are the actual data points that make up a dimension at a specific level of granularity.

Example: In "Product" dimension, if "Category" is a granularity level, then "Seafood" and "Beverages" are specific members of the "Product" dimension at that "Category" level.

If we had a "Time" dimension with "Quarter" as a granularity, then "Q1 2026," "Q2 2026," "Q3 2026," and "Q4 2026" would be members.

For a "Customer's City" dimension, "Sydney," "Melbourne," and "Brisbane" would be members.

A Data Cube Contains Several Measures

While our previous examples often focused on a single measure (like "Quantity"), a data cube can, and often does, contain multiple measures. This allows for a more comprehensive analysis of various aspects of the business.

Example: Besides "Quantity," you might also have "Amount" (total sales amount), "Profit," "Cost," or "Discount" as additional measures within the same sales data cube.

This means that for the same combination of Category, Quarter, and City, a cell might contain not only the "Quantity" sold but also the "Amount" of sales, the "Profit" generated, and so on. Users can then switch between viewing these different measures based on their analytical needs.

A Data Cube May Be Sparse (Typical Case) or Dense

The concept of sparsity relates to how many cells in a data cube actually contain a value (a measure) versus how many are empty or contain a null value.

Dense Cube: A dense cube is one where most or all possible combinations of dimension members have an associated measure value. This is less common in real-world business scenarios.

Sparse Cube: A sparse cube is one where a significant number of cells are empty or contain null values because certain combinations of dimension members simply don't have corresponding data. This is the typical case in most data warehousing environments.

Example: "Not all customers may have ordered products of all categories during all quarters." This perfectly illustrates sparsity.

In such cases, the cells corresponding to these non-existent combinations would be empty or null. Data warehousing systems are designed to handle sparsity efficiently, as it's a common characteristic of real-world business data. Storing every possible combination, including empty ones, would be highly inefficient in terms of storage and processing.

Hierarchies: Allow Viewing Data at Several Granularities

Hierarchies are a fundamental and powerful aspect of dimensional modeling. They define natural relationships within a dimension, allowing users to navigate and analyze data at different levels of detail (granularities). They define a sequence of mappings relating lower-level, detailed concepts to higher-level, more generalized ones.

How they work: A hierarchy establishes a parent-child relationship between different levels of aggregation within a single dimension.

The lower level is called the child.

The higher level is called the parent.

A child rolls up to its parent, meaning the data from multiple children is aggregated to form the parent's value.

Example: Consider a "Time" dimension hierarchy:

Day (child) rolls up to Month (parent)

Month (child) rolls up to Quarter (parent)

Quarter (child) rolls up to Year (parent)

The Multidimensional Model

Instances of a dimension are called members

Example: Seafood and Beverages are members of the Product at the granularity Category

A data cube contains several measures, e.g. Amount, indicating the total sales amount (not shown)

A data cube may be sparse (typical case) or dense

Example: not all customers may have ordered products of all categories during all quarters

Hierarchies: allow viewing data at several granularities

Define a sequence of mappings relating lower-level, detailed concepts to higher-level ones

The lower level is called the child and the higher level is called the parent

The hierarchical structure of a dimension is called the dimension schema

A dimension instance comprises all members at all levels in a dimension

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

Hierarchies of the Product, Time, and Customer Dimensions

Hierarchies define the structured relationships within each dimension, allowing for these drill-down and roll-up operations. They map lower-level (child) members to higher-level (parent) members.

Let's illustrate typical hierarchies for \\l example dimensions:

Product Dimension Hierarchy:

All Products (Highest Level / Grandparent)

Department (Parent, e.g., "Electronics," "Apparel")

Category (Child of Department, but Parent of Sub-Category; e.g., "Laptops," "Smartphones" under "Electronics")

Sub-Category (Child of Category, but Parent of Product Name; e.g., "Gaming Laptops," "Business Laptops" under "Laptops")

Product Name (Lowest Level / Child, e.g., "Dell XPS 15," "MacBook Air M3")

Optional: SKU (Stock Keeping Unit) - even finer detail

Time Dimension Hierarchy:

Year (Highest Level / Grandparent, e.g., "2026," "2026")

Half-Year / Semester (Parent, e.g., "H1 2026," "H2 2026")

Quarter (Child of Half-Year, but Parent of Month; e.g., "Q1 2026," "Q2 2026")

Month (Child of Quarter, but Parent of Week; e.g., "January," "February")

Week (Child of Month, but Parent of Day; e.g., "Week 1," "Week 2")

Day (Lowest Level / Child, e.g., "2026-07-30")

In your initial example, your cube's granularity for Time is at the Quarter level. This allows you to roll up to "Half-Year" or "Year," or drill down to "Month," "Week," and "Day."

Customer Dimension Hierarchy (Location-based):

All Customers / All Locations (Highest Level / Grandparent)

Country (Parent, e.g., "Australia," "New Zealand")

State / Province (Child of Country, but Parent of City; e.g., "New South Wales," "Victoria")

City (Child of State, but Parent of Suburb; e.g., "Sydney," "Melbourne")

Suburb / Postcode (Child of City, but Parent of Customer Address; e.g., "Pyrmont 2009")

Customer Address / Individual Customer (Lowest Level / Child, e.g., "123 Main St, Pyrmont")

Cube's granularity for Customer is at the City level. This means you can roll up to "State" or "Country," or drill down to "Suburb" and individual "Customer Addresses."

These hierarchies are critical because they define the paths for aggregation and disaggregation within your data cube, enabling powerful OLAP analysis.

The Multidimensional Model

In the previous figure, granularity of each dimension indicated between parentheses: Category for the Product dimension, Quarter for Time, and City for Customer

We may want sales figures at a finer granularity (Month), or at a coarser granularity (Country)

Hierarchies of the Product, Time, and Customer dimensions

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

The essence of hierarchies in a multidimensional model:

They provide structure organizing dimension members from general to specific.

They enable navigation allowing users to easily move between different levels of detail (drill-down, roll-up).

They facilitate analysis by defining how measures are aggregated and disaggregated across various granularities.

Without well-defined hierarchies, a data cube would largely be a static, fixed-granularity table,

The Multidimensional Model

Members of a hierarchy Product - Category

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

Types of Measures and Their Impact on Aggregation

The type of measure dictates how it can be meaningfully aggregated and, therefore, how valid the changes in abstraction level are.

1. Additive Measures

Definition: These measures can be meaningfully summed (added) across all dimensions (e.g., Time, Product, Geography, Customer).

Examples: Sales Revenue, Quantity Sold, Profit, Number of Customers.

Impact on Abstraction: Additive measures are the most flexible for aggregation. You can sum sales revenue by day, by month, by product category, by region, or any combination. Each aggregation provides a valid, meaningful summary at a different abstraction level.

Example: Summing daily sales for a month gives you monthly sales. Summing monthly sales for a year gives you annual sales. All these sums are valid and represent the total value for that period.

2. Semiadditive Measures

Definition: These measures can be meaningfully summed along some dimensions but not others. The most common restriction is along the Time dimension.

Examples: Inventory Quantity, Account Balance, Headcount.

Impact on Abstraction: Across non-restricted dimensions: You can aggregate them meaningfully. For example, you can sum inventory quantity across different warehouses to get total inventory for a product across all warehouses.

Across restricted dimensions (especially Time): You cannot simply sum them. Summing inventory quantity over time (e.g., adding up daily inventory levels for a month) would produce a meaningless result. Inventory is a "snapshot" at a point in time. To see inventory over time, you'd typically take an average, a last-period value, or a beginning/ending balance.

Example: If you have daily inventory levels, summing them for a month doesn't tell you the average monthly inventory or the inventory at the end of the month. It's just a cumulative sum that doesn't represent actual inventory. However, you can sum inventory for different products in a single day to get the total inventory value for that day.

Changing Abstraction: For semiadditive measures, changing the abstraction level along the restricted dimensions requires different aggregation functions (e.g., average, last value) to maintain meaning, rather than simple summation.

3. Nonadditive Measures

Definition: These measures cannot be meaningfully summed (added) across any dimension.

Examples: Item Price, Cost per Unit, Exchange Rate, Ratios (e.g., Profit Margin).

Impact on Abstraction:

No direct summation: Summing prices or exchange rates is almost always meaningless. What does "total price" across different items or "total exchange rate" across different days tell you?

The Multidimensional Model: Measures

Aggregation of measures changes the abstraction level at which data in a cube are visualized

Measures can be:

Additive: can be meaningfully summarized along all the dimensions, using addition; The most common type of measures

Semiadditive: can be meaningfully summarized using addition along some dimensions; Example: inventory quantities, which cannot be added along the Time dimension

Nonadditive measures cannot be meaningfully summarized using addition across any dimension; Example: item price, cost per unit, and exchange rate

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

1. Distributive Measures

Definition: A measure is distributive if its aggregate function can be computed by partitioning the data into smaller subsets, computing the aggregate for each subset, and then applying a "combining" function to the results from the subsets to get the overall aggregate. The key is that the combining function gives the same result as if the function was applied to the entire dataset at once.

Characteristics:

Distributed Computation: They are ideal for parallel processing and distributed systems because you can break down the calculation across multiple machines or nodes.

Simple Combination: The results from the partitions can be combined in a straightforward manner.

Examples:

SUM(): If you want the total sales for a region, you can sum sales for each store in that region and then sum those individual store totals to get the regional total. SUM(S)=SUM(SUM(S1),SUM(S2),…)

COUNT(): MIN():MAX(): Similar to MIN, you can find the maximum in each partition and then the maximum of those maximums. MAX(S)=MAX(MAX(S1),MAX(S2),…)

Why DISTINCT COUNT is NOT Distributive:

Original Set: S={3,3,4,5,8,4,7,3,8}

Distinct Count on Original Set: The unique values are {3,4,5,7,8}. So, DISTINCT COUNT(S)=5.

Partitioned Subsets:

S1={3,3,4} -> DISTINCT COUNT(S1)=2 (unique values: {3,4})

S2={5,8,4} -> DISTINCT COUNT(S2)=3 (unique values: {4,5,8})

S3={7,3,8} -> DISTINCT COUNT(S3)=3 (unique values: {3,7,8})

Combining Results: If you sum the distinct counts from the partitions (2+3+3=8), it does not equal the distinct count of the original set (5). This is because values like 3, 4, and 8 appear in multiple partitions, and simply summing the distinct counts from each partition leads to overcounting.

2. Algebraic Measures

Definition: An algebraic measure is defined by an aggregate function that can be expressed as a scalar function of a bounded number of distributive aggregate functions. In simpler terms, you can calculate it by first computing a few distributive aggregates over the data, and then performing a final calculation (an "algebraic" operation) on those aggregate results.

Characteristics:

Two-Step Process: They involve an initial distributed computation of distributive measures, followed by a final, non-distributed calculation.

butive aggregates required for the final calculation is fixed and doesn't depend on the size of the data.

Examples:

AVERAGE(): This is the quintessential algebraic measure.

You can compute AVERAGE(S) by first computing SUM(S) (a distributive measure) and COUNT(S) (another distributive measure) over the entire dataset.

The Multidimensional Model: Measures

Another classification of measures:

Distributive: defined by an aggregation function that can be computed in a distributed way; Functions count, sum, minimum, and maximum are distributive, distinct count is not; Example: S = {3, 3, 4, 5, 8, 4, 7, 3, 8} partitioned in subsets {3, 3, 4}, {5, 8, 4}, {7, 3, 8} gives a result of 8, while the answer over the original set is 5

Algebraic measures are defined by an aggregation function that can be expressed as a scalar function of distributive ones; example: average, computed by dividing the sum by the count

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

Data Warehouse Concepts

Outline

OLAP versus OLTP

The Multidimensional Model

OLAP Operations

Data Warehouse Architecture

TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

Drill-Down

Concept: Drill-down is the process of navigating from a summary view to a more detailed, lower-level view of the data. It's like peeling back layers to see what makes up the aggregate.

Action: You move down the hierarchy within a dimension.

Purpose: To uncover the specific components or individual records that contribute to a higher-level aggregate. It answers questions like "What are the individual sales that make up this quarterly total?" or "Which cities contributed to this state's sales?"

Example: You are viewing total Sales Amount for Australia (Country level).

You drill-down on the Geography dimension by clicking on "Australia."

The view changes to show Sales Amount for each State within Australia (e.g., New South Wales, Victoria, Queensland).

You could then drill-down further on "New South Wales" to see sales by City within New South Wales (e.g., Sydney, Newcastle).

You can also drill down on multiple dimensions simultaneously, for instance, seeing monthly sales for a specific product category.

Concept: Roll-up is the opposite of drill-down. It's the process of aggregating data from a detailed level to a higher, more summarized level. It's like consolidating or summarizing information.

Action: You move up the hierarchy within a dimension, or you reduce the number of dimensions being analyzed.

Purpose: To gain a broader perspective, identify overall trends, or reduce the granularity of the data for high-level analysis. It answers questions like "What are the total sales for this continent, given the sales of its countries?" or "How did all products in a category perform?"

Example: You are viewing Sales Amount by City within Australia.

You roll-up on the Geography dimension.

The view changes to show Sales Amount for each State within Australia.

You could then roll-up again to see total Sales Amount for Australia as a whole.

Another form of roll-up is to reduce the number of dimensions. For example, if you're looking at Sales Amount by Product and Time, rolling up might involve removing the Product dimension to just see total sales over time, regardless of product.

3. Sort

Concept: Sort involves arranging the data based on the values of one or more measures or dimension members, either in ascending or descending order.

Action: Reordering the rows or columns of a report or table based on a chosen criterion.

Purpose: To easily identify top or bottom performers, find anomalies, or structure the data for easier comprehension. It helps answer questions like "Which are our top 5 best-selling products?" or "Which region had the lowest profit?"

Example: You have a table showing Sales Amount by Product Category.

OLAP Operations

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

Starting cube: quarterly sales (in thousands) by product category and customer cities for 2012

We first compute the sales quantities by country: a roll-up operation to the Country level along the Customer dimension

Sales of category Seafood in France significantly higher in the first quarter

To find out if this occurred during a particular month, we take cube back to City aggregation level, and drill-down along Time to the Month level

To explore alternative visualizations, we sort products by name

To see the cube with the Time dimension on the x axis, we rotate the axes of the original cube, without changing granularities → pivoting (see next 2 slides)

OLAP Operations

Starting cube: quarterly sales (in thousands) by product category and customer cities for 2012

We first compute the sales quantities by country: a roll-up operation to the Country level along the Customer dimension

Sales of category Seafood in France significantly higher in the first quarter

To find out if this occurred during a particular month, we take cube back to City aggregation level, and drill-down along Time to the Month level

To explore alternative visualizations, we sort products by name

To see the cube with the Time dimension on the x axis, we rotate the axes of the original cube, without changing granularities → pivoting (see next 2 slides)

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

To visualize the data only for Paris → slice operation, results in a 2-dimensional sub-cube, basically a collection of time series (see next slide)

To obtain a 3-dimensional sub-cube containing only sales for the first two quarters and for the cities Lyon and Paris, we go back to the original cube and apply a dice operation

OLAP Operations

To visualize the data only for Paris → slice operation, results in a 2-dimensional sub-cube, basically a collection of time series (see next slide)

To obtain a 3-dimensional sub-cube containing only sales for the first two quarters and for the cities Lyon and Paris, we go back to the original cube and apply a dice operation

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

1. Slice

Concept: A slice operation selects a single dimension from the cube and provides a new "sub-cube" or 2D table representing a specific "slice" of the data along that chosen dimension. It's like cutting through the cube at a particular point.

Action: You fix one or more members of a dimension, effectively reducing the dimensionality of the view.

Purpose: To focus on a specific subset of the data by filtering along one dimension. It helps answer questions like "What were the sales for a specific product category across all regions and over time?" or "Show me all data for Q2 2026."

Visual Analogy: Imagine a 3D cube. A slice takes a 2D cross-section of that cube.

Example: You have a cube showing Sales Amount by Product Category, Geography (Country), and Time (Month).

You perform a slice operation on the Time dimension, specifically selecting "July 2026".

2. Dice

Concept: A dice operation selects a specific range of values across multiple dimensions, creating a smaller "sub-cube" from the larger cube. It's like cutting out a smaller block from within the cube.

Action: You define criteria or filter conditions for two or more dimensions simultaneously.

Purpose: To focus analysis on a very specific segment of the data that meets multiple criteria. It answers questions like "What were the sales for 'Electronics' products in 'North America' during 'Q1 2026'?"

Visual Analogy: If a slice is a single cross-section, a dice is like taking a smaller, defined block out of the cube.

Example:Starting with the full sales cube.

You perform a dice operation with the following conditions:

Time Dimension: "Q1 2026" (January, February, March)

Product Dimension: "Electronics" and "Apparel" Categories

Geography Dimension: "United States" and "Canada"

The resulting view will be a sub-cube containing Sales Amount for "Electronics" and "Apparel" products, only for "United States" and "Canada", and only for the months of January, February, and March 2026. All other data outside these specified ranges is temporarily excluded from the current view.

3. Pivot (Rotate)

Concept: A pivot (also known as "rotate") operation changes the orientation of the data cube. It re-arranges the dimensions on the axes of a report, allowing you to view the same data from a different perspective. It's like rotating the cube to see a different face.

Action: You swap the positions of dimensions on the rows, columns, or pages of a report.

Purpose: To gain new insights by changing which dimensions serve as the primary comparison points. It helps answer questions like "How do sales vary by product across different regions?" versus "How do sales vary by region for each product?"

OLAP Operations

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

The operations in the previous slides can be defined using the following algebraic operators.

Roll-up: aggregates measures along a dimension hierarchy (using an aggregate function) to obtain measures at a coarser granularity

ROLLUP(CubeName, (Dimension → Level)*, AggFunction(Measure)*)

ROLLUP(Sales, Customer → Country, SUM(Quantity))

Extended roll-up: similar to rollup, but drops all dimensions not involved in the operation

ROLLUP*(CubeName, [(Dimension → Level)*], AggFunction(Measure)*)

ROLLUP*(Sales, Time → Quarter, SUM(Quantity))

ROLLUP*(Sales, Time → Quarter, COUNT(Product) AS ProdCount)

Recursive roll-up: aggregates over a recursive hierarchy (a level rolls-up to itself)

RECROLLUP(CubeName, Dimension → Level, AggFunction(Measure)*)

OLAP Operations

The operations in the previous slides can be defined using the following algebraic operators.

Roll-up: aggregates measures along a dimension hierarchy (using an aggregate function) to obtain measures at a coarser granularity

ROLLUP(CubeName, (Dimension → Level)*, AggFunction(Measure)*) ROLLUP(Sales, Customer → Country, SUM(Quantity))

Extended roll-up: similar to rollup, but drops all dimensions not involved in the operation

ROLLUP*(CubeName, [(Dimension → Level)*], AggFunction(Measure)*) ROLLUP*(Sales, Time → Quarter, SUM(Quantity)) ROLLUP*(Sales, Time → Quarter, COUNT(Product) AS ProdCount)

Recursive roll-up: aggregates over a recursive hierarchy (a level rolls-up to itself)

RECROLLUP(CubeName, Dimension → Level, AggFunction(Measure)*)

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

Drill-down moves from a more general level to a more detailed level in a hierarchy

DRILLDOWN(CubeName, (Dimension → Level)*)

DRILLDOWN(Sales, Time → Month)

Sort returns a cube where the members of a dimension have been sorted according to the value of Expression

SORT(CubeName, Dimension, Expression [ASC | DESC])

SORT(Sales, Product, NAME)

NAME is a predefined keyword in the algebra representing the name of a member

OLAP Operations

Drill-down moves from a more general level to a more detailed level in a hierarchy

DRILLDOWN(CubeName, (Dimension → Level)*) DRILLDOWN(Sales, Time → Month)

Sort returns a cube where the members of a dimension have been sorted according to the value of Expression

SORT(CubeName, Dimension, Expression [ASC | DESC])

SORT(Sales, Product, NAME)

NAME is a predefined keyword in the algebra representing the name of a member

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

Pivot

PIVOT(CubeName, (Dimension → Axis)*)

where the axes are specified as {X, Y, Z, X1, Y1, Z1, ... }.

PIVOT(Sales, Time → X, Customer → Y, Product → Z)

Slice:

SLICE(CubeName, Dimension, Level = Value)

Dimension will be dropped by fixing a single Value in the Level, other dimensions unchanged

SLICE(Sales, Customer, City = 'Paris')

Slice supposes that the granularity of the cube is at the specified level of the dimension

OLAP Operations

Pivot

PIVOT(CubeName, (Dimension → Axis)*)

where the axes are specified as {X, Y, Z, X₁, Y₁, Z₁, ... }.

PIVOT(Sales, Time → X, Customer → Y, Product → Z)

Slice:

SLICE(CubeName, Dimension, Level = Value)

Dimension will be dropped by fixing a single Value in the Level, other dimensions unchanged

SLICE(Sales, Customer, City = 'Paris')

Slice supposes that the granularity of the cube is at the specified level of the dimension

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

Dice:

DICE(CubeName, ? )

where ? is a Boolean condition over dimension levels, attributes, and measures.

DICE(Sales, (Customer.City = 'Paris' OR Customer.City = 'Lyon') AND

(Time.Quarter = 'Q1' OR Time.Quarter = 'Q2') )

OLAP Operations

Dice:

DICE(CubeName, ? )

where ? is a Boolean condition over dimension levels, attributes, and measures.

DICE(Sales, (Customer.City = 'Paris' OR Customer.City = 'Lyon') AND (Time.Quarter = 'Q1' OR Time.Quarter = 'Q2') )

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

Data Warehouse Concepts

Outline

OLAP versus OLTP

The Multidimensional Model

OLAP Operations

Data Warehouse Architecture

TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

Let's break down the general data warehouse architecture, focusing on its common multi-tiered structure. This architecture is designed to support analytical processing and business intelligence, separating it from day-to-day operational systems.

General Data Warehouse Architecture: Several Tiers

A typical data warehouse architecture is often conceptualized as having several tiers, each with distinct responsibilities. This tiered approach promotes modularity, scalability, and maintainability.

The tiers include:

Back-end tier composed of composed of: the extraction, transformation, and loading (ETL) tools and data staging area.

Data warehouse tier composed of an enterprise data warehouse and/or several data marts and a metadata repository.

OLAP tier composed of ier composed of an OLAP server.

Front-end tier used for data analysis and visualization

Typical Data Warehouse Architecture

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

1. Back-End Tier (or Data Source/Integration Tier)

This tier is responsible for collecting, preparing, and consolidating data from various sources before it enters the data warehouse. It's the "preparation" phase.

The Extraction, Transformation, and Loading (ETL) Tools:

Extraction: The process of reading data from one or more source systems. These sources can be diverse:

- Operational Databases (OLTP Systems): The databases that run an organization's daily business operations (e.g., sales, inventory, CRM, HR systems). These are designed for high transaction throughput.

- Internal Data Sources: Flat files, spreadsheets, legacy systems, departmental databases, etc.

- External Data Sources: Data from third-party vendors, market research firms, social media, weather data, etc.

Transformation: The most complex and critical part of ETL. Once extracted, data needs to be transformed to fit the data warehouse's schema and quality standards. This includes:

- Cleaning: Handling missing values, correcting errors, standardizing formats.

- Filtering: Selecting only relevant data.

- Joining: Combining data from different sources.

- Aggregating: Summarizing data (e.g., calculating daily totals from individual transactions).

- Deriving: Creating new calculated fields.

- Standardizing: Ensuring consistent units, codes, and naming conventions.

- Loading: The process of writing the transformed data into the target data warehouse or data mart. This can be a full load (replacing all data) or an incremental load (adding only new or changed data).

The Data Staging Area:

Concept: This is an intermediate, temporary storage area (often a database or file system) where the data extracted from source systems resides before it is loaded into the main data warehouse.

Purpose: Isolation: It isolates the complex and resource-intensive ETL processes from the live operational systems and the final data warehouse.

Transformation Sandbox: It provides a space where all the data integration, cleaning, and transformation processes can run without impacting the performance or integrity of the source systems or the data warehouse itself.

Error Handling: It's easier to detect and fix data quality issues in the staging area before they contaminate the data warehouse.

Recovery: If a load fails, the staging area can be used to re-run transformations or loads without re-extracting from sources.

Nature: Data in the staging area is often highly granular and may resemble the source system's structure more closely, before being fully conformed to the data warehouse's dimensional model. It's typically volatile and purged after successful loads.

Data Warehouse Tier

Concept: A centralized repository of integrated data from across an entire organization. It aims for a single, consistent, and comprehensive view of the business.

Scope: Broad in scope, covering all major subject areas (e.g., sales, marketing, finance, HR).

Granularity: Often stores data at a very detailed, atomic level, along with some summarized data.

Users: Serves a wide range of users, from executives to analysts.

Design: Typically follows a Kimball-style dimensional model (star/snowflake schemas) or an Inmon-style normalized enterprise data model.

3. OLAP Tier (or Presentation/Access Tier)

This tier is where end-users interact with the data for analysis and reporting.

Data Warehouse Architecture

General data warehouse architecture: several tiers

Back-end tier composed of:

The extraction, transformation, and loading (ETL) tools: Feed data into the data warehouse from operational databases and internal and external data sources

The data staging area: An intermediate database where all the data integration and transformation processes are run prior to the loading of the data into the data warehouse

Data warehouse tier composed of:

An enterprise data warehouse and/or several data marts

A metadata repository storing information about the data warehouse and its contents

OLAP tier composed of:

An OLAP server which provides a multidimensional view of the data, regardless the actual way in which data are stored

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

4. Front-End Tier: Data Analysis and Visualization

The primary purpose of the front-end tier is to provide an intuitive and powerful interface for business users, analysts, data scientists, and executives to extract insights from the data warehouse. It translates the complex, underlying data structures into meaningful reports, dashboards, and visualizations.

This tier contains various client tools, each designed for specific analytical purposes:

1. OLAP Tools

Purpose: These tools are specifically designed to interact with an OLAP server (from the OLAP tier) or directly with dimensional models in the data warehouse. They enable users to perform interactive, multidimensional analysis.

2. Reporting Tools

Purpose: These tools are used to create structured, static, or interactive reports that present data in a predefined format. They are essential for routine information delivery and performance monitoring.

3. Statistical Tools

Purpose: These tools are used by statisticians, analysts, and researchers to perform advanced statistical analysis on data warehouse data. They go beyond simple aggregations to uncover deeper relationships, test hypotheses, and build predictive models.

4. Data-Mining Tools

Purpose: These tools are used to discover hidden patterns, relationships, anomalies, and insights from large datasets. They employ sophisticated algorithms from machine learning and artificial intelligence.

Data Warehouse Architecture

Front-end tier is used for data analysis and visualization

Contains client tools such as OLAP tools, reporting tools, statistical tools, and data-mining tools

In HTML view press 'p' to see the lecture notes
TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026

References

A. VAISMAN, E. ZIMANYI, Data Warehouse Systems: Design and Implementation, Chapter 3 Data Warehouse Concepts, Springer Verlag, 2014

TOP Created by Janusz R. Getta, ISIT312/ISIT912 Big Data Management, Spring, 2026