OLAP Functions in SQL iSeries for Advanced Data Analysis: A Focus on Grouping Operations

Introduction

In the realm of data analysis and reporting within IBM iSeries environments, SQL offers a powerful suite of OLAP (Online Analytical Processing) functions designed to enhance data aggregation, analysis, and reporting capabilities.

Among these, the grouping operations – GROUPING SETS, ROLLUP, and CUBE – stand out for their ability to facilitate complex data analysis and reporting tasks with efficiency and flexibility. These functions are instrumental in addressing the multifaceted needs of businesses by enabling comprehensive data aggregation across multiple dimensions, thereby supporting sophisticated reporting requirements.

• GROUPING SETS: This function is a game-changer for data analysts and report developers, as it allows for the specification of multiple levels of aggregation within a single SQL query. By enabling the creation of reports that include both subtotals and grand totals without the need to craft multiple queries, GROUPING SETS significantly streamline the reporting process. This feature is particularly useful for generating complex reports that require a detailed breakdown of data across various dimensions.

• ROLLUP: The ROLLUP function is designed to produce a result set that not only includes the basic aggregation of data but also provides a hierarchical aggregation structure. This means that users can obtain subtotals at multiple levels, culminating in a grand total, all within a single result set. ROLLUP is a great help for financial reporting, inventory analysis, and any scenario where understanding the hierarchical aggregation of data is crucial.

• CUBE: For those seeking the ultimate in data aggregation flexibility, the CUBE function offers the ability to generate all possible combinations of aggregations for a selected group of columns. This function is particularly powerful for multi-dimensional analysis, enabling users to explore data from every conceivable angle. Whether it’s sales data, financial metrics, or any other dataset requiring comprehensive analysis, CUBE provides the means to uncover insights that might otherwise remain hidden.

Leveraging these OLAP functions within iSeries SQL not only enhances the analytical capabilities of organizations but also empowers data professionals to deliver more insightful, accurate, and timely reports. By harnessing the power of GROUPING SETS, ROLLUP, and CUBE, businesses can navigate the complexities of their data landscapes with greater ease and confidence, driving informed decision-making and strategic planning.

Notes:

• These examples use the WITH clause to define a CTE named SalesData for demonstration purposes. The VALUES clause within the CTE provides a simple way to create test data directly within the query.

• The ORDER BY clause in each example ensures that the results are presented in a logical order, making it easier to interpret the aggregated data.

• When running these examples on an iSeries system, make sure that your SQL environment (e.g., IBM i Navigator for Windows, ACS Run SQL Scripts, or third-party tools) is correctly configured to execute SQL statements.

• The actual output and utility of these queries can vary based on the specific requirements of your reporting and analysis tasks. These examples are designed to illustrate the syntax and potential use cases for GROUPING SETS, ROLLUP, and CUBE in the context of iSeries SQL.

Example 1: OLAP Function GROUPING SETS

Introduction to the Query’s Purpose and Techniques

This SQL query is designed to aggregate sales data across different geographical hierarchies, providing a versatile analysis of sales performance by macro area, region, and overall totals. Utilizing the WITH clause to create a Common Table Expression (CTE) named SalesData, the query efficiently organizes raw sales figures into a structured format.

The VALUES clause within the CTE populates this temporary table with predefined sales data, spanning various macro areas (e.g., America, Asia, Europe), regions within these areas, and corresponding sales amounts for different nations.

The core analytical power of the query comes from its use of GROUPING SETS.

This advanced SQL feature allows for multiple levels of aggregation within a single query, enabling the calculation of total sales not just for each macro area and region, but also for the entire dataset.

By specifying grouping sets for macro areas alone, macro areas with regions, and an empty set (to calculate the grand total), the query provides a comprehensive overview of sales distribution and performance across different geographical levels. The ORDER BY clause ensures that the results are presented in a logical and organized manner, first by macro area and then by region, facilitating easy interpretation of the aggregated sales data.

WITH SalesData (MacroArea, Region, Nation, Amount) AS (
VALUES ('America', 'North America'   , 'Nation_NA1', 180000)
     , ('America', 'North America'   , 'Nation_NA2',  80000)
     , ('America', 'North America'   , 'Nation_NA3',  60000)
     , ('America', 'Central America' , 'Nation_CA1',  30000)
     , ('America', 'Central America' , 'Nation_CA2',  40000)
     , ('America', 'South America'   , 'Nation_SA1',  65000)
     , ('America', 'South America'   , 'Nation_SA2',  73000)
     , ('Asia'   , 'Western Asia'    , 'Nation_WA1',  90000)
     , ('Asia'   , 'Western Asia'    , 'Nation_WA2',  67000)
     , ('Asia'   , 'Western Asia'    , 'Nation_WA3',  55000)
     , ('Asia'   , 'Eastern Asia'    , 'Nation_EA1', 130000)
     , ('Asia'   , 'Eastern Asia'    , 'Nation_EA2', 101000)
     , ('Asia'   , 'Eastern Asia'    , 'Nation_EA3',  69000)
     , ('Asia'   , 'Southtern Asia'  , 'Nation_SA1',  89000)
     , ('Asia'   , 'Southtern Asia'  , 'Nation_SA2',  45000)
     , ('Europe' , 'Northern Europe' , 'Nation_NE1',  44000)
     , ('Europe' , 'Northern Europe' , 'Nation_NE2',  58000)
     , ('Europe' , 'Northern Europe' , 'Nation_NE3',  43000)
     , ('Europe' , 'Western Europe'  , 'Nation_WE1',  27000)
     , ('Europe' , 'Western Europe'  , 'Nation_WE2',  32000)
     , ('Europe' , 'Western Europe'  , 'Nation_WE3',  63000)
     , ('Europe' , 'Western Europe'  , 'Nation_WE4',  19000)
     , ('Europe' , 'Eastern Europe'  , 'Nation_EE1',  15000)
     , ('Europe' , 'Eastern Europe'  , 'Nation_EE2',  48000)
     , ('Europe' , 'Eastern Europe'  , 'Nation_EE3',  56000)
     ) --  select * from SalesData;
SELECT
  MacroArea,
  Region,
  SUM(Amount) AS TotalSales
FROM
  SalesData
GROUP BY
  GROUPING SETS ((MacroArea), (MacroArea, Region), ())
ORDER BY
  1, 2;

Result:

Detailed Query Description:

• CTE Initialization (WITH SalesData): The query begins by defining a Common Table Expression named SalesData that serves as a temporary table. This table is structured with columns for macro area, region, nation, and the sales amount associated with each nation. The VALUES clause is then used to manually populate this table with sales data, illustrating a diverse set of geographical locations and corresponding sales figures.

• Data Aggregation (SELECT Statement): Following the CTE definition, the query proceeds to aggregate this sales data. It selects the MacroArea and Region columns for grouping purposes and calculates the total sales (SUM(Amount)) for each grouping set defined.

• Grouping Sets (GROUP BY Clause): The GROUP BY clause is where the query specifies its unique aggregation levels using GROUPING SETS. It defines three sets:

• • The first set groups by MacroArea alone, providing total sales figures for each macro area.
  • The second set groups by both MacroArea and Region, offering a more detailed view of sales within each macro area.
  • The third set, indicated by an empty set (), aggregates the total sales across the entire dataset, yielding a grand total.

• Sorting Results (ORDER BY Clause): Finally, the ORDER BY clause sorts the aggregated results first by MacroArea and then by Region. This ensures that the output is systematically organized, making it easier for users to navigate through the sales data. The sorting facilitates a hierarchical view of the data, from the broadest level of aggregation (macro area) down to more specific regions within those areas, and concludes with the grand total for all sales.

Example another variant:

WITH SalesData (MacroArea, Region, Nation, Amount) AS (
. . . . as above (come sopra)
. . . .
SELECT
  MacroArea,
  SUM(Amount) AS TotalSales
FROM
  SalesData
GROUP BY
  GROUPING SETS ((MacroArea), ())
ORDER BY
  1, 2; 

Result:

And still another Grouping Sets:

WITH SalesData (MacroArea, Region, Nation, Amount) AS (
. . . . as above (come sopra)
. . . .
SELECT
  Region,
  SUM(Amount) AS TotalSales
FROM
  SalesData
GROUP BY
  GROUPING SETS ((Region), ())
ORDER BY
  1, 2; 

Result:

This query, with the variations shown, exemplifies how SQL can be leveraged to perform complex data aggregation tasks, offering insights into sales performance across various geographical dimensions. The use of GROUPING SETS in particular showcases an advanced technique for multi-level data analysis within a single, efficient query.

Example 2: OLAP Function ROLLUP

Introduction to the Query’s Purpose and Techniques

This SQL query is crafted to perform a hierarchical aggregation of sales data across different geographical levels, from macro areas down to specific regions, culminating in a comprehensive summary that includes subtotals for each macro area and a grand total for all sales.

The query employs a Common Table Expression (CTE) named SalesData to simulate a dataset within the query itself, using the VALUES clause to populate this temporary table with sales figures across various geographical segments.

The essence of this query lies in its use of the ROLLUP function, a powerful OLAP (Online Analytical Processing) operation in SQL that facilitates the generation of aggregate summaries at multiple levels of a hierarchy in a single query execution.

This function not only aggregates sales data for each region within a macro area but also automatically computes subtotals for each macro area and a grand total across all macro areas and regions.

By structuring the query in this manner, it leverages SQL’s advanced aggregation capabilities to provide a multi-level breakdown of sales data, which is crucial for in-depth analysis and reporting in business contexts. The ORDER BY clause ensures that the output is neatly organized, first by macro area and then by region, making the aggregated data easily interpretable.

WITH SalesData (MacroArea, Region, Nation, Amount) AS (
VALUES ('America', 'North America'   , 'Nation_NA1', 180000)
     , ('America', 'North America'   , 'Nation_NA2',  80000)
     , ('America', 'North America'   , 'Nation_NA3',  60000)
     , ('America', 'Central America' , 'Nation_CA1',  30000)
     , ('America', 'Central America' , 'Nation_CA2',  40000)
     , ('America', 'South America'   , 'Nation_SA1',  65000)
     , ('America', 'South America'   , 'Nation_SA2',  73000)
     , ('Asia'   , 'Western Asia'    , 'Nation_WA1',  90000)
     , ('Asia'   , 'Western Asia'    , 'Nation_WA2',  67000)
     , ('Asia'   , 'Western Asia'    , 'Nation_WA3',  55000)
     , ('Asia'   , 'Eastern Asia'    , 'Nation_EA1', 130000)
     , ('Asia'   , 'Eastern Asia'    , 'Nation_EA2', 101000)
     , ('Asia'   , 'Eastern Asia'    , 'Nation_EA3',  69000)
     , ('Asia'   , 'Southtern Asia'  , 'Nation_SA1',  89000)
     , ('Asia'   , 'Southtern Asia'  , 'Nation_SA2',  45000)
     , ('Europe' , 'Northern Europe' , 'Nation_NE1',  44000)
     , ('Europe' , 'Northern Europe' , 'Nation_NE2',  58000)
     , ('Europe' , 'Northern Europe' , 'Nation_NE3',  43000)
     , ('Europe' , 'Western Europe'  , 'Nation_WE1',  27000)
     , ('Europe' , 'Western Europe'  , 'Nation_WE2',  32000)
     , ('Europe' , 'Western Europe'  , 'Nation_WE3',  63000)
     , ('Europe' , 'Western Europe'  , 'Nation_WE4',  19000)
     , ('Europe' , 'Eastern Europe'  , 'Nation_EE1',  15000)
     , ('Europe' , 'Eastern Europe'  , 'Nation_EE2',  48000)
     , ('Europe' , 'Eastern Europe'  , 'Nation_EE3',  56000)
     )  -- select * from SalesData;
SELECT MacroArea, Region,
  SUM(Amount) AS TotalSales
FROM
  SalesData
GROUP BY
  ROLLUP (MacroArea, Region)
ORDER BY
  1, 2;

Result:

Detailed Query Description

• CTE Definition (WITH SalesData): The query initiates with the definition of a Common Table Expression named SalesData, which acts as a virtual table for the scope of this query. This CTE is structured to include columns for macro area, region, nation, and the sales amount associated with each nation. The VALUES clause following the CTE declaration meticulously outlines the sales data, categorizing it by geographical hierarchies and corresponding sales figures, thus setting the stage for the subsequent aggregation.

• Aggregation and Rollup (SELECT Statement): The core of the query is the SELECT statement, which aims to aggregate the sales data. It selects the MacroArea and Region for grouping purposes and calculates the total sales (SUM(Amount)) for each group. The use of ROLLUP in the GROUP BY clause is pivotal here, as it specifies the hierarchical levels (macro area and region) for which the query should compute subtotals and a grand total.

• Hierarchical Aggregation with ROLLUP: The ROLLUP function generates a result set that includes not only the total sales for each region within a macro area but also adds subtotals for each macro area and a grand total at the end. This hierarchical aggregation is essential for analyzing sales performance across different geographical levels, providing insights into both regional and overall sales trends.

• Sorting the Results (ORDER BY Clause): To ensure the aggregated data is presented in an orderly and logical manner, the ORDER BY clause sorts the results first by MacroArea and then by Region. This sorting is crucial for readability and usability of the data, as it aligns with the hierarchical structure of the aggregation, from macro areas down to specific regions, and finally to the overall summary.

Example another variant:

Using another ROLLUP values results in new groupings:

WITH SalesData (MacroArea, Region, Nation, Amount) AS (
. . . . as above (come sopra)
. . . .
SELECT MacroArea, Region, Nation,
  SUM(Amount) AS TotalSales
FROM
  SalesData
GROUP BY
  ROLLUP (MacroArea, Region, Nation)
ORDER BY
  1, 2, 3;

Result:

In summary these queries exemplify the strategic use of OLAP SQL’s ROLLUP function to conduct nuanced analysis of sales data across various geographic dimensions. They show how to efficiently aggregate and summarize data at multiple levels of detail into a single query, providing valuable insights for decision making and strategic reporting.

Example 3: OLAP Function CUBE

Introduction to the Query’s Purpose and Techniques

This SQL query is designed to perform an advanced level of data aggregation and analysis on sales figures across different geographical segments, utilizing a Common Table Expression (CTE) and the CUBE function for multi-dimensional analysis.

The primary aim is to provide a comprehensive overview of sales performance by macro area and region, while also offering the flexibility to analyze the data from various aggregation levels, including total sales by macro area, by region, and overall totals across all areas.

The use of a CTE named SalesData allows for the creation of a temporary dataset within the query, populated with sales data across different geographical dimensions (MacroArea, Region, Nation) and their corresponding sales amounts. This setup facilitates the manipulation and analysis of the data without affecting the original data source.

The CUBE function is employed in the GROUP BY clause to generate subtotals and grand totals across multiple dimensions (MacroArea and Region) in a single pass. This OLAP (Online Analytical Processing) function expands the query’s capability to provide a multi-faceted view of the sales data, enabling stakeholders to derive insights from various perspectives of the data hierarchy.

WITH SalesData (MacroArea, Region, Nation, Amount) AS (
VALUES ('America', 'North America'   , 'Nation_NA1', 180000)
     , ('America', 'North America'   , 'Nation_NA2',  80000)
     , ('America', 'North America'   , 'Nation_NA3',  60000)
     , ('America', 'Central America' , 'Nation_CA1',  30000)
     , ('America', 'Central America' , 'Nation_CA2',  40000)
     , ('America', 'South America'   , 'Nation_SA1',  65000)
     , ('America', 'South America'   , 'Nation_SA2',  73000)
     , ('Asia'   , 'Western Asia'    , 'Nation_WA1',  90000)
     , ('Asia'   , 'Western Asia'    , 'Nation_WA2',  67000)
     , ('Asia'   , 'Western Asia'    , 'Nation_WA3',  55000)
     , ('Asia'   , 'Eastern Asia'    , 'Nation_EA1', 130000)
     , ('Asia'   , 'Eastern Asia'    , 'Nation_EA2', 101000)
     , ('Asia'   , 'Eastern Asia'    , 'Nation_EA3',  69000)
     , ('Asia'   , 'Southtern Asia'  , 'Nation_SA1',  89000)
     , ('Asia'   , 'Southtern Asia'  , 'Nation_SA2',  45000)
     , ('Europe' , 'Northern Europe' , 'Nation_NE1',  44000)
     , ('Europe' , 'Northern Europe' , 'Nation_NE2',  58000)
     , ('Europe' , 'Northern Europe' , 'Nation_NE3',  43000)
     , ('Europe' , 'Western Europe'  , 'Nation_WE1',  27000)
     , ('Europe' , 'Western Europe'  , 'Nation_WE2',  32000)
     , ('Europe' , 'Western Europe'  , 'Nation_WE3',  63000)
     , ('Europe' , 'Western Europe'  , 'Nation_WE4',  19000)
     , ('Europe' , 'Eastern Europe'  , 'Nation_EE1',  15000)
     , ('Europe' , 'Eastern Europe'  , 'Nation_EE2',  48000)
     , ('Europe' , 'Eastern Europe'  , 'Nation_EE3',  56000)
     )  -- select * from SalesData;
SELECT
  MacroArea, Region,
  SUM(Amount) AS TotalSales
FROM
  SalesData
GROUP BY
  CUBE (MacroArea, Region)
ORDER BY
  1, 2;

Result:

Detailed Query Description

• CTE Initialization (WITH SalesData): The query begins by defining a Common Table Expression SalesData that acts as a virtual table for the purpose of this query. This CTE is structured with columns for MacroArea, Region, Nation, and Amount, and is populated with predefined values representing sales data across different geographical areas. This approach allows for the simulation of a real-world dataset within the query, facilitating the demonstration and testing of the CUBE function’s capabilities.

• Data Aggregation (SELECT Statement): Following the CTE definition, the query proceeds to aggregate this sales data. It selects the MacroArea and Region for grouping purposes and calculates the total sales (SUM(Amount)) for each possible combination of these dimensions. This step is crucial for analyzing sales performance across different levels of geographical granularity.

• Multi-Dimensional Aggregation with CUBE: The use of the CUBE function in the GROUP BY clause is the centerpiece of this query. It allows for the aggregation of sales data across all combinations of the specified dimensions (MacroArea and Region), including each dimension independently and all dimensions together. This results in a result set that includes total sales for each region within a macro area, each macro area independently, and a grand total for all macro areas and regions combined.

• Sorting the Results (ORDER BY Clause): The ORDER BY clause ensures that the aggregated results are presented in a structured manner, first by MacroArea and then by Region. This sorting is essential for the logical presentation of the data, facilitating easy interpretation and analysis. It aligns the output in a hierarchical order that mirrors the multi-dimensional aggregation performed by the CUBE function, making it straightforward to navigate through the various levels of sales data aggregation.

This query showcases the powerful analytical capabilities of SQL’s CUBE function, enabling a deep dive into sales data across multiple geographical dimensions. By providing a structured yet flexible approach to data aggregation, it allows businesses to gain a comprehensive understanding of their sales performance, identifying trends and opportunities at both macro and micro levels of their operations.

Which, when and why – let’s try to give a comparison

The SQL OLAP (Online Analytical Processing) functions, specifically GROUPING SETS, ROLLUP, and CUBE, are powerful tools for data analysis and reporting, allowing for complex aggregations and summaries.

While these functions can sometimes produce similar results, they each have unique characteristics and are suited to different scenarios.

Here’s a technical description of each, highlighting their peculiarities and suggesting contexts where one might be more advantageous than the others.

GROUPING SETS

• Description: GROUPING SETS is a feature that allows specifying multiple groupings in a single query. It is useful for creating reports that require different levels of aggregation, such as subtotals and grand totals, without having to combine multiple queries.

• Peculiarities: It provides explicit control over the combinations of columns you want to aggregate. Unlike ROLLUP and CUBE, which automatically generate hierarchical or combinatorial groupings, GROUPING SETS require you to specify each grouping set explicitly.

• Use Cases: Ideal for scenarios where you need specific aggregation combinations and not all possible hierarchies or combinations. For example, if you need to see total sales by region, by product, and then by both region and product, but not interested in other combinations.

ROLLUP

• Description: ROLLUP is used to generate a result set that shows aggregates for a hierarchy of values, including subtotals at each level and a grand total. It creates a grouping for each level of hierarchy specified in the query.

• Peculiarities: It automatically generates a hierarchical series of groupings based on the order of columns specified. The first column is the most detailed level, and each subsequent column adds a level to the hierarchy.

• Use Cases: Best suited for reports where you need hierarchical aggregation such as financial reports that require subtotals for each category and a grand total at the end. It simplifies the generation of reports with multiple levels of totals across a single or multiple dimensions.

CUBE

• Description: CUBE generates all possible combinations of aggregations for a set of selected columns. It’s like ROLLUP but more comprehensive, as it includes not only hierarchical aggregations but also cross-tabulated combinations.

• Peculiarities: It can produce a significantly larger result set than ROLLUP because it calculates all possible combinations of the grouping columns, including the overall total.

• Use Cases: Particularly useful for multi-dimensional analysis, such as in data warehousing scenarios where you might want to analyze data across multiple dimensions (e.g., time, geography, product). It’s ideal for creating cross-tab reports or when you need to explore data without a predefined hierarchy.

Choosing the Right Function

• Specific vs. Comprehensive Aggregations: Use GROUPING SETS when you need specific combinations of aggregations. Choose ROLLUP for hierarchical data summaries and CUBE for the most comprehensive aggregation, including all possible combinations.

• Performance Considerations: CUBE can generate a large number of groupings, which might not be performance-efficient for large datasets. ROLLUP and GROUPING SETS can be more performance-friendly depending on the complexity and size of the data.

• Analysis Depth: For deep, multi-dimensional analysis, CUBE is preferable. For simpler, hierarchical analyses, ROLLUP is suitable. GROUPING SETS offer a middle ground, allowing for customized aggregation levels without the full combinatorial explosion of CUBE.

In summary, while GROUPING SETS, ROLLUP, and CUBE can sometimes achieve similar results, their differences make them suitable for various scenarios. The choice among them should be guided by the specific reporting and analysis needs, considering the desired level of detail, performance implications, and the complexity of the data relationships.

Conclusions

In conclusion, the strategic application of iSeries SQL OLAP functions – GROUPING SETS, ROLLUP, and CUBE – can significantly enhance data analysis and reporting capabilities within an organization. Each function serves a unique purpose, catering to different analytical needs and complexities.

GROUPING SETS offer precise control over aggregation levels, making them ideal for targeted analysis. ROLLUP simplifies hierarchical data summarization, providing a streamlined approach to generating reports with multiple aggregation levels. CUBE, on the other hand, delivers comprehensive multi-dimensional analysis, allowing for an exhaustive exploration of data relationships.

Understanding the nuances and appropriate use cases of these functions is crucial for database professionals aiming to leverage SQL for advanced data analysis and reporting. By judiciously applying these OLAP operations, organizations can unlock deeper insights, improve decision-making processes, and enhance overall business intelligence.

As data continues to grow in volume and complexity, mastering these powerful SQL tools will be indispensable for navigating the landscape of modern data analysis.

Useful references

• Data Science: opportunities and face challenges

• OLAP Functions in Data Science: A Modern Approach to Business Intelligence

• Harnessing the Power of Data Science and SQL with OLAP Functions on IBM iSeries

• OLAP Functions in iSeries SQL for Advanced Data Analysis: A Focus on Window Functions

• OLAP Functions in SQL iSeries for Advanced Data Analysis: A Focus on Ranking Functions