12SQLForDW

SQL for Data Warehousing

Outline

SQL/OLAP Operations
Window partitioning
Window ordering
Window framing

The SQL query provided computes a data cube for the SALES fact table using UNION. This approach explicitly generates all the desired aggregation levels by combining separate SELECT statements for each level of granularity.

How it Works

The query breaks down the aggregation task into four distinct parts, each corresponding to a different level of roll-up, and then combines them using UNION:

Full Detail: SELECT ProductKey, CustomerKey, SalesAmount FROM Sales

This first SELECT statement provides the base data, which is the lowest level of granularity. It's essentially the raw SALES table and doesn't perform any aggregation. It represents the full detail of sales for each unique product and customer combination.

Aggregation by Product: SELECT ProductKey, NULL, SUM(SalesAmount) FROM Sales GROUP BY ProductKey

This statement calculates the total sales for each product, regardless of the customer. The GROUP BY ProductKey clause performs the aggregation.

NULL is used for CustomerKey to signify that the data is not specific to any one customer; it's an aggregation across all customers for a given product. This is a common technique in data warehousing to represent roll-up levels in a single result set.

Aggregation by Customer: SELECT NULL, CustomerKey, SUM(SalesAmount) FROM Sales GROUP BY CustomerKey

Similar to the previous statement, this one calculates the total sales for each customer, regardless of the product. The GROUP BY CustomerKey performs this aggregation.

NULL is used for ProductKey to indicate that the data is not specific to any one product.

Grand Total: SELECT NULL, NULL, SUM(SalesAmount) FROM Sales

This final part calculates the grand total of all sales. Since no GROUP BY clause is used, it aggregates the entire SalesAmount column into a single value.

NULL is used for both ProductKey and CustomerKey to signify that this row represents the total for the entire dataset, rolled up across all products and customers.

Alternatives and Modern SQL

While the UNION approach is a valid way to compute a data cube, it can be inefficient because it requires multiple scans of the SALES table. Modern SQL provides more efficient and elegant alternatives for this purpose. The most common are the GROUPING SETS, CUBE, and ROLLUP extensions to the GROUP BY clause

SQL/OLAP Operations

Consider the SALES fact table
To compute all possible aggregations along the dimensions Product and Customer we must scan the whole relational table SALES several times

It can be implemented in SQL using NULL and UNION in the following way:

	      
SELECT ProductKey, CustomerKey, SalesAmount
FROM Sales
  UNION
SELECT ProductKey, NULL, SUM(SalesAmount)
FROM Sales
GROUP BY ProductKey
  UNION
SELECT NULL, CustomerKey, SUM(SalesAmount)
FROM Sales
GROUP BY CustomerKey
  UNION
SELECT NULL, NULL, SUM(SalesAmount)
FROM Sales;

SQL/OLAP Operations

A data cube created through UNION of individual SELECT statements each one creating one combination of dimensions looks in the following way

Relational Model

Computing a data cube using individual SELECT statements with a GROUP BY clause is indeed a cumbersome process.

It requires a separate SELECT statement for each possible combination of dimensions, from the individual dimensions all the way up to the grand total.

For n dimensions, there are 2n possible combinations (subsets) of those dimensions. Therefore, you would need to write and UNION ALL 2n different SELECT

statements to create a full cube.

Extending GROUP BY

SQL/OLAP simplifies this process with powerful extensions to the GROUP BY clause: ROLLUP, CUBE, and GROUPING SETS.

ROLLUP: This operator computes group subtotals in a hierarchical manner based on the order of the attributes provided.

It generates a summary for each level of the hierarchy, from the most detailed grouping up to the grand total.

For example, ROLLUP(A, B, C) would produce subtotals for (A, B, C), (A, B), (A), and the grand total ().

It's ideal for hierarchical data like time or geographical locations.

CUBE: This is a more comprehensive operator. It computes all possible subtotals for a given set of dimensions, regardless of their order.

It's essentially a shorthand for generating all possible grouping sets. CUBE(A, B) computes subtotals for (A, B), (A), (B), and the grand total ().

This is equivalent to 2n grouping sets for n dimensions.

GROUPING SETS: This is the most flexible and powerful of the three.

It allows you to specify the exact combinations of dimensions for which you want to compute aggregations. ROLLUP and CUBE are simply shorthand notations for specific

GROUPING SETS definitions. This operator provides precise control over which subtotals are generated.

Equivalent Queries

As the prompt illustrates, ROLLUP and CUBE can be rewritten using GROUPING SETS.

ROLLUP Example:

The query SELECT ProductKey, CustomerKey, SUM(SalesAmount) FROM Sales GROUP BY ROLLUP(ProductKey, CustomerKey);

is a shorthand for the following GROUPING SETS statement:

SELECT ProductKey, CustomerKey, SUM(SalesAmount)

FROM Sales

GROUP BY GROUPING SETS (

(ProductKey, CustomerKey), -- Most detailed level

(ProductKey), -- Subtotal by ProductKey

()-- Grand total

);

CUBE Example: Similarly, a CUBE query like GROUP BY CUBE(ProductKey, CustomerKey); is equivalent to the following GROUPING SETS statement:

SELECTProductKey,CustomerKey,SUM(SalesAmount)

FROM Sales

GROUP BY GROUPING SETS (

(ProductKey, CustomerKey), -- Detailed combination

(ProductKey), -- Subtotal by ProductKey

(CustomerKey),-- Subtotal by CustomerKey

()-- Grand total

);

By using these operators, you avoid the tedious and error-prone process of writing and combining numerous individual SELECT statements.

SQL/OLAP Operations

Computing a cube with n dimensions requires (2*2*2*... *2)(n times) SELECT statements with GROUP BY clause
SQL/OLAP extends the GROUP BY clause with the ROLLUP and CUBE operators
ROLLUP computes group subtotals in the order given by a list of attributes
CUBE computes all totals of such a list
Shorthands for a more powerful operator, GROUPING SETS

Equivalent queries

	      
SELECT ProductKey, CustomerKey, SUM(SalesAmount)
FROM Sales
GROUP BY ROLLUP(ProductKey, CustomerKey);

	      
SELECT ProductKey, CustomerKey, SUM(SalesAmount)
FROM Sales
GROUP BY GROUPING SETS((ProductKey,CustomerKey),(ProductKey),());

The provided queries are equivalent because the CUBE operator is a shorthand for a specific set of GROUPING SETS.

The GROUP BY CUBE(ProductKey, CustomerKey) query is a simple, concise way to tell the database to generate all possible combinations of subtotals for the listed dimensions.

The GROUP BY GROUPING SETS((ProductKey, CustomerKey),(ProductKey),(CustomerKey),()); query explicitly lists every single combination of dimensions for which a subtotal is required.

In this case, CUBE with two dimensions (n=2) generates 22=4 grouping sets, which are:

(ProductKey, CustomerKey): The most detailed level, grouping by both dimensions.

(ProductKey): A subtotal for each unique ProductKey, summarizing across all CustomerKeys.

(CustomerKey): A subtotal for each unique CustomerKey, summarizing across all ProductKeys.

(): An empty grouping set that represents the grand total for all data.

Since the GROUPING SETS query explicitly lists these same four combinations, both queries produce the exact same result set, with the same rows and SUM(SalesAmount) values. The CUBE operator is just a more efficient way to write the query for a full cube, saving you from manually listing out all 2n combinations.

SQL/OLAP Operations

Equivalent queries


SELECT ProductKey, CustomerKey, SUM(SalesAmount)
FROM Sales
GROUP BY CUBE(ProductKey, CustomerKey);


SELECT ProductKey, CustomerKey, SUM(SalesAmount)
FROM Sales
GROUP BY GROUPING SETS((ProductKey, CustomerKey),(ProductKey),(CustomerKey),());

SQL/OLAP Operations

Operations

SQL for Data Warehousing

Window partitioning is a feature in SQL that lets you perform calculations across a set of table rows that are related to the current row.

It's a way to perform aggregate functions without collapsing the rows into a single summary row, which is what a regular GROUP BY clause does.

This allows you to compare detailed data with aggregate values on the same result set.

SELECT ProductKey,CustomerKey,SalesAmount,MAX(SalesAmount) OVER (PARTITION BY ProductKey) AS MaxAmount

FROM SALES;

Here's how it works:

SELECT ProductKey, CustomerKey, SalesAmount: The query retrieves the raw, detailed data for each sale.

OVER (PARTITION BY ProductKey): This clause defines the "window" or partition. The database groups all rows that have the same ProductKey together. Think of this as creating a temporary, conceptual mini-table for each distinct product.

MAX(SalesAmount): The MAX() function is then applied to each of these partitions.

AS MaxAmount: The result of this calculation, the maximum sales amount for that specific product, is then returned in a new column called MaxAmount on every single row within that product's partition.

This gives you a result set that includes the individual SalesAmount for each customer's purchase, alongside the maximum SalesAmount for that specific product. This is incredibly useful for finding things like the relevance of each customer's purchase with respect to the total sales of a product, as mentioned in the prompt.

You can see how each individual sale compares to the highest sale for that same product, all in one query

Window partitioning

Allows to compare detailed data with aggregate values

For example, find a relevance of each customer with respect to the sales of the product

		    
SELECT ProductKey, CustomerKey, SalesAmount,
       MAX(SalesAmount) OVER (PARTITION BY ProductKey) AS MaxAmount
FROM SALES;

First three columns are obtained from the Sales table
The fourth column is created in the following way
- Create a window called partition that contains all tuples of the same product
- SalesAmount is aggregated over this window using MAX function

The results from

SELECT ProductKey, CustomerKey, SalesAmount,

MAX(SalesAmount) OVER (PARTITION BY ProductKey) AS MaxAmount

FROM SALES;

The OVER (PARTITION BY ProductKey) clause is the key. It instructs the database to divide the result set into "windows" or "partitions" based on the value in the ProductKey column. All rows with the same ProductKey are placed in the same partition.

The Aggregate Function

The MAX(SalesAmount) function is an aggregate function that is now applied within each of these partitions. Instead of calculating the maximum sales for the entire table, it finds the maximum sales amount for each product individually.

The result of this function is then returned as a new column, MaxAmount, for every single row in the original result set. This allows you to see the individual SalesAmount for each customer alongside the overall maximum sales for the product they purchased.

For example, if a table has sales for three different products, the query will first group all sales for 'Product A' together, then all sales for 'Product B', and so on. It then calculates the maximum sales amount within each of those product groups. T

Window partitioning

	      
SELECT ProductKey, CustomerKey, SalesAmount,
       MAX(SalesAmount) OVER (PARTITION BY ProductKey) AS MaxAmount
FROM SALES;

Window partitioning

SQL for Data Warehousing

Outline

SQL/OLAP Operations
Window partitioning
Window ordering
Window framing

The ORDER BY clause within a window function, as shown in your example, determines the specific order of rows within each defined partition. It's crucial for functions like RANK() because it tells the function how to evaluate the data to assign a rank.

How ORDER BY Works with PARTITION BY

Let's break down your example query:

SELECT ProductKey, CustomerKey, SalesAmount,

RANK() OVER (PARTITION BY CustomerKey ORDER BY SalesAmount DESC) AS RowNo

FROM Sales;

PARTITION BY CustomerKey: First, the query divides the entire Sales table into separate, smaller groups (or "partitions").

Each of these partitions contains all the sales records for a single, unique customer.

For instance, all records for CustomerKey = 101 are in one partition, all records for CustomerKey = 102 are in another, and so on.

ORDER BY SalesAmount DESC: Within each of those individual customer partitions, the rows are then sorted based on the SalesAmount column in descending order.

This means the sale with the highest amount for that specific customer will be at the top of their partition.

RANK() OVER (...): The RANK() function then assigns a rank to each row within its sorted partition.

Because of the ORDER BY SalesAmount DESC, the highest SalesAmount receives a rank of 1, the next highest receives a rank of 2, and so on. If two sales amounts are identical, they receive the same rank, and the next rank is skipped (e.g., if two rows are rank 2, the next rank is 4).

Result of the Example Query

The final output will show the ProductKey, CustomerKey, and SalesAmount for every sale. But for each row, a new column named RowNo will be added. This RowNo tells you the rank of that specific product sale relative to all other products purchased by that same customer.

For example, a result row with RowNo = 1 means that the SalesAmount for that product was the highest amount spent by that particular customer. A row with RowNo = 2 means it was the second-highest amount, and so on. The ranking is reset for each new customer, starting back at 1.

Window ordering

ORDER BY clause allows the rows within a partition to be ordered
It is useful to compute rankings, with a function RANK()

For example, how does each product rank in the sales of each customer

	      
SELECT ProductKey, CustomerKey, SalesAmount,
       RANK() OVER (PARTITION BY CustomerKey ORDER BY SalesAmount DESC) AS RowNo
FROM Sales;

Window Framing

SQL for Data Warehousing

Outline

SQL/OLAP Operations
Window partitioning
Window ordering
Window framing

Window framing is a powerful feature of SQL window functions that allows you to define a specific subset of rows within each partition, rather than operating on the entire partition.

This subset is called the "window frame."

The most common use of window framing is for calculating statistical measures over a sliding or moving range of data, like a moving average, sum, or count.

SELECT ProductKey, Year, Month, SalesAmount,

AVG(SalesAmount) OVER (PARTITION BY ProductKey

ORDER BY Year, Month

ROWS 2 PRECEDING) AS MovAvg

FROM SALES;

PARTITION BY ProductKey: The data is first divided into separate groups, with each group containing all the sales records for a single product. This ensures the moving average is calculated independently for each product.

ORDER BY Year, Month: Within each product's partition, the sales records are sorted chronologically. This is a crucial step because a moving average is a time-series calculation; it depends on the order of events.

ROWS 2 PRECEDING: This is the window frame clause. It defines the specific set of rows that the AVG() function will operate on for each row.

For the first row in the partition, the frame consists of just that row (since there are no preceding rows).

For the second row, the frame includes the current row and the one preceding it.

For the third row (and all subsequent rows), the frame includes the current row and the two preceding rows.

The query calculates the average sales amount for each row, but instead of using the entire product's sales history, it only uses the sales data from the current month and the two months immediately before it. This gives you a three-month moving average, smoothing out short-term fluctuations to reveal the long-term trend in sales for each product. The MovAvg value will be different for each row as the window frame "slides" down the dataset.

Window framing

It is possible to define a size of a partition
It can be used to compute statistical functions over time series, like moving average

For example, three-month moving average of sales by product

	      
SELECT ProductKey, Year, Month, SalesAmount,
       AVG(SalesAmount) OVER (PARTITION BY ProductKey
                              ORDER BY Year, Month
                              ROWS 2 PRECEDING) AS MovAvg
FROM SALES;

Processing of a query opens a window with the rows pertaining to the current product
Then, it orders the window by year and month and computes the average over the current row and the previous two ones if they exist

Let's break down the provided SQL query:

SELECT ProductKey, Year, Month, SalesAmount, AVG(SalesAmount) OVER (PARTITION BY ProductKey

ORDER BY Year, Month

ROWS 2 PRECEDING) AS MovAvg

FROM SALES;

SELECT ProductKey, Year, Month, SalesAmount, ...: This part selects the columns you want to display from the SALES table.

AVG(SalesAmount) OVER (...): This is the window function. It calculates the average of SalesAmount over a defined window of rows.

PARTITION BY ProductKey: This divides the data into separate, independent partitions for each unique ProductKey. The window function is applied separately within each partition. Think of it as grouping the data by product.

ORDER BY Year, Month: This specifies the order in which the rows within each partition are processed. This is crucial for defining the "preceding" and "following" rows for the window frame.

ROWS 2 PRECEDING: This is the window frame clause. It defines a moving window that includes the current row and the two rows immediately preceding it in the ordered partition.

In simple terms, for each row, the query calculates the average of the SalesAmount for that product, the row before it, and the row two rows before it. This creates a 3-row moving average of sales for each product, ordered by year and month.

Window framing

	      
SELECT ProductKey, Year, Month, SalesAmount,
       AVG(SalesAmount) OVER (PARTITION BY ProductKey
                              ORDER BY Year, Month
                              ROWS 2 PRECEDING) AS MovAvg
FROM SALES;

Window framing

Let's break down the provided SQL query:

SELECT ProductKey, Year, Month, SalesAmount,

SUM(SalesAmount) OVER (PARTITION BY ProductKey, Year

ORDER BY Month

ROWS UNBOUNDED PRECEDING) AS YTD

FROM SALES;

SELECT ProductKey, Year, Month, SalesAmount, ...: This part selects the columns you want to display from the SALES table.

SUM(SalesAmount) OVER (...): This is the window function. It calculates the sum of SalesAmount over a defined window of rows.

PARTITION BY ProductKey, Year: This divides the data into separate, independent partitions for each unique combination of ProductKey and Year. The window function is applied separately within each partition. Think of it as grouping the data by product and year.

ORDER BY Month: This specifies the order in which the rows within each partition are processed. This is crucial for defining the "preceding" and "following" rows for the window frame.

ROWS UNBOUNDED PRECEDING: This is the window frame clause. It defines a moving window that starts at the very first row of the partition and ends at the current row. The function then calculates the sum of all SalesAmount values from the beginning of the partition up to the current row.

Processing of a query, opens a window with the tuples of the current product and year ordered by month

AVG() is applied to all rows before the current row (ROWS UNBOUNDED PRECEDING )

In simple terms, for each row, the query calculates the cumulative sum of SalesAmount for that product, starting from the beginning of the year. This effectively gives you a year-to-date (YTD) sales total for each product.

Window framing

Another example, a year-to-date sum of sales by product

	      
SELECT ProductKey, Year, Month, SalesAmount,
       SUM(SalesAmount) OVER (PARTITION BY ProductKey, Year
                              ORDER BY Month
                              ROWS UNBOUNDED PRECEDING) AS YTD
FROM SALES;

Processing of a query, opens a window with the tuples of the current product and year ordered by month
AVG() is applied to all rows before the current row (ROWS UNBOUNDED PRECEDING )

SELECT ProductKey, Year, Month, SalesAmount,

SUM(SalesAmount) OVER (PARTITION BY ProductKey, Year

ORDER BY Month

ROWS UNBOUNDED PRECEDING) AS YTD

FROM SALES;

SUM(SalesAmount) OVER (...): This is the window function. It calculates the sum of SalesAmount over a defined window.

PARTITION BY ProductKey, Year: This clause divides the data into separate, independent groups (partitions) for each combination of a product and a year. The sum calculation will restart for each new product and each new year.

ORDER BY Month: This is crucial. It sorts the rows within each partition by Month. Without an ORDER BY clause, the frame would be undefined, and the sum would be the total for the entire partition.

ROWS UNBOUNDED PRECEDING: This is the window frame clause. It instructs the database to define a window that starts at the very first row of the current partition (UNBOUNDED PRECEDING) and ends at the current row.

Imagine the database is processing the data row by row. For the first month of a product's sales, the sum is just that month's sales. For the second month, the sum includes the sales from the first and second months. For the third month, it includes the sum of the first, second, and third months, and so on. The ROWS UNBOUNDED PRECEDING frame expands with each new row, effectively creating a cumulative sum or running total for each product within a given year.

Window framing

	      
SELECT ProductKey, Year, Month, SalesAmount,
       SUM(SalesAmount) OVER (PARTITION BY ProductKey, Year
                              ORDER BY Month
                              ROWS UNBOUNDED PRECEDING) AS YTD
FROM SALES;

Window framing

References

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