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

In the realm of database analysis and management on IBM iSeries systems, Online Analytical Processing (OLAP) functions stand out as powerful tools for data interrogation and transformation.

Among these, ranking functions provide indispensable capabilities for performing comparative analysis within datasets, enabling businesses to derive meaningful insights from their data.

This post delves into the core ranking functions available in iSeries SQL, illustrating their significance and practical applications in real-world scenarios.

1. ROW_NUMBER(): This function is pivotal for assigning a unique, sequential integer to rows within a partition of a result set, beginning with 1. It is instrumental in scenarios where a clear, unambiguous ordering of rows is required, ensuring that each row can be distinctly identified and accessed.
    
2. RANK(): The RANK() function goes a step further by allocating a rank to each row within a partition of a result set. Unlike ROW_NUMBER(), RANK() allows for ties; when two or more rows qualify for the same rank, they are assigned the same rank number, creating gaps in the sequence for subsequent ranks. This function is particularly valuable in competitive analyses, where it’s crucial to understand positioning without artificially inflating or deflating the importance of tied entities.
    
3. DENSE_RANK(): Operating on a similar premise to RANK(), DENSE_RANK() eliminates the gaps in the ranking sequence, ensuring a dense, continuous rank allocation even in the presence of ties. This function is essential when the continuity of rank values is critical for analysis, allowing for a more compact and intuitive ranking system.
    
4. NTILE(n): For more granular analysis, the NTILE(n) function divides an ordered partition into a specified number of groups, n, making it an invaluable tool for segmenting datasets into quantiles or other specified groupings. Whether for dividing customers into sales quartiles or categorizing performance metrics, NTILE(n) offers a straightforward approach to data stratification.

These ranking functions are integral to leveraging the full analytical power of iSeries SQL, providing the foundation for sophisticated data analysis and decision-making processes.

By mastering these functions, professionals can unlock deeper insights into their data, facilitating enhanced operational efficiency and strategic foresight.

This introduction sets the stage for a detailed exploration of each function, including syntax examples, use cases, and best practices for integrating these OLAP functions into your data analysis and reporting workflows on the iSeries platform.

Note on the examples: excluding the first one, the other examples all deliberately use the same temporary table in memory created with CTE to give a vision of the different approaches and possibilities that can be implemented with the Ranking Functions examined in this article.

Example 1: OLAP Function ROW_NUMBER() to Detecting Double Numbers

Introduction to the Query

This SQL query is designed to demonstrate the advanced capabilities of iSeries SQL for data analysis, particularly focusing on the use of window functions and Common Table Expressions (CTEs) to process and analyze data sets.

The query shows how to partition data, apply ranking within partitions, and filter and aggregate results based on specific criteria.

By utilizing a sample data set created on-the-fly within the query, it illustrates a practical example of identifying and aggregating rows based on their calculated ranks within partitions of data, emphasizing the power of SQL in extracting meaningful insights from structured data.

WITH
  Sample(ID, Value) AS (
      VALUES (1,10),  (2, 20), (3, 30), (4, 30)
           , (5, 30), (6, 40), (7,20), (8,50)
           , (9,60), (10,70), (11,80), (12,90)
) -- SELECT * FROM Sample;
, Sample2 (ID, Value, ROW_NUM) AS (
    SELECT ID, Value,
           ROW_NUMBER()
              OVER (PARTITION BY Value
              ORDER BY ID)
           AS row_number
      FROM Sample
) -- SELECT * FROM Sample2;
SELECT Value, MAX(ROW_NUM)
  from Sample2
 WHERE ROW_NUM > 1
group by Value 
ORDER BY 1,2;

Result:

Detailed Description of the Query

The query begins with the declaration of a CTE called Sample, which constructs a temporary in-memory table composed of ID and value pairs.

After the initial CTE, a second CTE called Sample2 is defined. This CTE is based on Sample data using the ROW_NUMBER() window function.

The function is applied to dataset partitions created based on the Value column, sorting the rows within each partition by ID.

This step assigns a unique sequential integer (row number) to each row within its partition, effectively classifying rows by their ID in the context of their shared Value.

The core of the query lies in its final SELECT statement, which operates on the data prepared in Sample2. It specifically targets rows where the calculated row number (ROW_NUM) is greater than 1, implying a focus on rows within each Value partition that have a non-unique value.

The query then proceeds to aggregate these targeted rows by their value, selecting the maximum ROW_NUM found within each group.

This aggregation step highlights the highest rank achieved by the rows within each distinct value group that meets the filter criterion and ultimately indicates how many “doubles” are present for that value.

Finally, the results are sorted by Value and maximum number of rows, providing a clear and tidy summary of the data based on the analytical logic you specify.

In essence, this query not only exemplifies the syntactic structure and use of CTEs and window functions in iSeries SQL, but also offers a glimpse into the practical applications of these features for complex data analysis tasks.

It serves as an example of how to dissect and reassemble data to reveal patterns or insights that are not immediately apparent from the raw data set.

Example 2: ROW_NUMBER() OLAP Function to Ranking Sales

Introduction to the Query

In this example, we delve into the practical application of OLAP functions within an iSeries SQL environment, focusing on a scenario that analyzes sales data across different regions and products.

Specifically, we’ll use the ROW_NUMBER() function to perform a detailed ranking of sales figures within each region.

This approach not only highlights the versatility of window functions in SQL for analytical tasks but also showcases how they can be leveraged to gain insights into sales performance disparities and successes across various geographic locations.

By partitioning the data by region and ordering it by sales in a descending manner, we’ll be able to assign a unique rank to each product based on its sales performance, providing a clear and actionable overview of regional sales dynamics.

WITH SalesData(Product, Region, Sales) AS (
  VALUES
    ('Product A', 'East', 100),
    ('Product B', 'East', 150),
    ('Product A', 'West', 200),
    ('Product B', 'West', 200),
    ('Product C', 'East', 50),
    ('Product C', 'West', 100),
    ('Product D', 'East', 75),
    ('Product D', 'West', 75)
) 
SELECT Product, Region, Sales,
       ROW_NUMBER()
           OVER (PARTITION BY Region
                 ORDER BY Sales DESC)
       AS RowNum
FROM SalesData;

Result:

Detailed Description of the Query

The query begins with the creation of a Common Table Expression (CTE) named SalesData, which serves as a temporary in-memory table for our analysis.

This CTE is populated with sample sales data, including columns for Product, Region, and Sales, thereby simulating a real-world dataset that might be encountered in a business analytics scenario.

The data includes sales figures for four different products across two regions (East and West), with varying sales amounts.

Following the setup of the SalesData CTE, the query proceeds to select all columns from this dataset and applies the ROW_NUMBER() OLAP function.

This function is used to generate a sequential integer ranking for each row within a partition of the dataset.

The partitioning is done based on the Region column, ensuring that the ranking is specific to each region, and the ordering criterion is the Sales column in descending order.

This means that within each region, products will be ranked according to their sales figures, with the highest-selling product receiving a RowNum of 1, and so on.

The final result of this query will be a list of all products, alongside their respective regions and sales figures, each accompanied by a row number indicating their sales rank within the region.

This ranking provides immediate visibility into which products are performing best in each region, offering valuable insights for strategic planning, inventory management, and marketing efforts.

By using the ROW_NUMBER() function in this way, businesses can precisely identify top-performing products and regions, facilitating targeted actions to enhance sales performance and address underperforming areas.

Example 3: OLAP Function RANK() to Ranking Sales (same rank for ties, with gaps)

Introduction to the Query

In this exploration, we will delve into the sophisticated utilization of OLAP functions within SQL on an iSeries system, specifically focusing on the RANK() function to analyze sales data.

This query exemplifies how to effectively partition data by region and then rank products within those partitions based on their sales figures. By employing these techniques, we aim to uncover insights into product performance across different regions, enabling targeted strategic decisions.

This approach not only demonstrates the power of OLAP functions for data analysis but also illustrates their practical application in identifying key sales trends and performance metrics across diverse geographical areas.

Note: The ranking result obtained with this approach is similar to the previous one using ROW_NUMBER(), but gives the same rank for ties, with gaps: this will rank the sales records within each region, allowing identical rankings in case of equal sales amounts, with gaps for subsequent rankings (immediately visible if you compare the results of the two versions: with ROW_NUMBER() and with RANK().

WITH SalesData(Product, Region, Sales) AS (
  VALUES
    ('Product A', 'East', 100),
    ('Product B', 'East', 150),
    ('Product A', 'West', 200),
    ('Product B', 'West', 200),
    ('Product C', 'East', 50),
    ('Product C', 'West', 100),
    ('Product D', 'East', 75),
    ('Product D', 'West', 75)
) -- select * from SalesData;
 SELECT Product, Region, Sales,
        RANK()
          OVER (PARTITION BY Region
                ORDER BY Sales DESC)
        AS Rank
FROM  SalesData;

Result:

Detailed Description of the Query

This SQL query start wtth the construction and utilization of a Common Table Expression (CTE) named SalesData. This CTE is designed to simulate a real-world dataset by creating an in-memory table populated with sales records for various products across different regions.

Each record consists of a product name, the region in which the sale was made, and the total sales amount for that product in the respective region.

Following the establishment of the SalesData table, the query leverages the RANK() function, one of SQL’s OLAP capabilities, to perform an advanced analysis of the sales data.

The RANK() function is applied within a window defined by the OVER clause, which partitions the data by Region.

Within each partition, the function orders the products by their Sales in descending order.

The RANK() function then assigns a rank to each product based on this ordering, with the highest-selling product in each region receiving a rank of 1.

In cases where multiple products have the same sales figures, they receive the same rank, and the next rank is incremented accordingly.

This selective process results in a dataset that not only includes the original product, region, and sales information but also enriches it with a ranking of products within each region based on their sales performance.

This ranked list provides critical insights into which products are leading in sales in each region and how closely competitive products are performing relative to each other.

By executing this query, businesses and analysts can gain a clearer understanding of market dynamics and product performance across different geographic areas.

The insights derived from this analysis can inform a range of strategic decisions, from product development and marketing strategies to inventory management and regional sales focus, ultimately enabling a more data-driven approach to business optimization and growth.

Example 4: OLAP Function DENSE_RANK() to Ranking Sales (without gaps)

Introduction to the Query

In this example, we will delve into the advanced application of OLAP functions within SQL on an iSeries system, focusing on the DENSE_RANK() function for an analysis of sales data across various regions.

This query is designed to demonstrate how data can be segmented and analyzed within specific partitions—here, by region—to assign rankings to products based on their sales performance.

Unlike the RANK() function, DENSE_RANK() provides a unique perspective by ensuring a continuous sequence of ranks, even in the presence of ties, thus offering a more granular view of competitive standings within each region.

This approach is particularly beneficial for identifying how closely products compete within the market, facilitating strategic decision-making processes without the skew of rank gaps.

WITH SalesData(Product, Region, Sales) AS (
  VALUES
    ('Product A', 'East', 100),
    ('Product B', 'East', 150),
    ('Product A', 'West', 200),
    ('Product B', 'West', 200),
    ('Product C', 'East', 50),
    ('Product C', 'West', 100),
    ('Product D', 'East', 75),
    ('Product D', 'West', 75)
) -- select * from SalesData;
SELECT Product, Region, Sales,
       DENSE_RANK()
           OVER (PARTITION BY Region
                 ORDER BY Sales DESC)
       AS DenseRank
FROM SalesData;

Result:

Detailed Description of the Query

The query begins with the establishment of a Common Table Expression (CTE), SalesData, which acts as a temporary data source for the analysis.

This CTE is populated with hypothetical sales information, detailing product names, their corresponding regions, and sales figures.

This dataset provides a simplified yet representative model of sales performance data that businesses might analyze to understand product performance across different markets.

Following the CTE setup, the query employs the DENSE_RANK() function to analyze the sales data.

This function is applied within a specified window, defined by the OVER clause, which organizes the data into partitions by Region.

Within each partition, the products are ordered by their Sales in descending order.

The DENSE_RANK() function then assigns ranks to each product based on their sales figures, ensuring that products with identical sales figures receive the same rank.

Crucially, DENSE_RANK() does not leave gaps in the ranking sequence for tied ranks, meaning the rank sequence is continuous and dense, hence the name.

For example, if two products in the East region both have the highest sales figures, they will both receive a dense rank of 1, and the next highest product will receive a dense rank of 2, regardless of how many products shared the top rank.

This ranking method is especially useful for closely analyzing competitive standings, as it allows stakeholders to see precisely how many products perform similarly in terms of sales, without the distortion of rank gaps that might imply a greater disparity in performance than actually exists.

The output of this query provides a clear, ordered list of products within each region, ranked by their sales performance with a dense ranking.

This insight is invaluable for businesses looking to understand their product standings in various regions, identify key competitors, and strategize on how to improve or capitalize on their market positions.

By leveraging the DENSE_RANK() function in this manner, organizations can refine their market analysis and strategic planning efforts, ensuring they are based on a nuanced understanding of their competitive environment.

Example 5: OLAP Function NTILE(n) to divide the sales records in  groups

Introduction to the Query

In this exploration, we will delve into the advanced application of OLAP functions within SQL on an iSeries system, with a focus on utilizing the NTILE() function to perform a nuanced segmentation of sales data.

This analytical approach is geared towards dividing sales records into distinct groups within each region, based on their sales performance.

By implementing the NTILE(2) function, we aim to categorize the data into two groups, essentially creating quartiles (in this simplified context) that allow us to differentiate between higher and lower sales performances within each geographic segment.

This method of data analysis is particularly valuable for organizations looking to gain deeper insights into their sales distribution and to identify potential areas for strategic improvement or investment.

WITH SalesData(Product, Region, Sales) AS (
  VALUES
    ('Product A', 'East', 100),
    ('Product B', 'East', 150),
    ('Product A', 'West', 200),
    ('Product B', 'West', 200),
    ('Product C', 'East', 50),
    ('Product C', 'West', 100),
    ('Product D', 'East', 75),
    ('Product D', 'West', 75)
) -- select * from SalesData;
SELECT Product, Region, Sales,
       NTILE(2)
           OVER (PARTITION BY Region
                 ORDER BY Sales DESC)
       AS SalesQuartile
FROM SalesData; 

Result:

Detailed Description of the Query

The query in question initiates with the construction of a Common Table Expression (CTE), SalesData, which is designed to simulate a typical sales dataset.

This CTE comprises sales records, including the product name, the region of sale, and the total sales amount for that product within the specified region.

This setup provides a foundational dataset that reflects common scenarios faced by businesses in analyzing sales performance across different markets.

Following the creation of the SalesData CTE, the query proceeds to apply the NTILE(2) function within a specified window, delineated by the OVER clause.

This window organizes the data into partitions by Region and then orders the records within each partition based on their Sales in descending order.

The NTILE(2) function then divides these ordered records into two groups, or “quartiles,” within each region.

The division aims to evenly distribute the records into groups based on their relative sales figures, thus facilitating a clear distinction between higher-performing and lower-performing products within each region.

The outcome of this query is a refined view of the sales data, where each record is not only associated with its original attributes (Product, Region, Sales) but also annotated with a SalesQuartile value.

This value indicates whether the product falls into the top half (quartile 1) or the bottom half (quartile 2) of sales performance within its region, based on the NTILE categorization.

Obviously the subdivision in NTILE can vary, depending on the values in the field and the policies followed by the company and, for example, maintaining the data sampling example, if you execute the same query with NTILE(3) you obtain three categories :

This segmentation provides critical insights into the sales landscape of each region, highlighting which products are leading the market and which may require additional support or strategic realignment.

By leveraging the NTILE() function in this manner, businesses can harness a more granular understanding of their sales distribution across different regions.

This insight is invaluable for strategic planning, allowing companies to tailor their marketing, production, and inventory strategies to better align with regional sales performance trends.

Moreover, this analytical approach underscores the versatility and power of OLAP functions in SQL for iSeries for conducting sophisticated data analysis, particularly in scenarios that necessitate detailed ranking and segmentation of data, like assessing sales performance across multiple dimensions.

Conclusions

In conclusion, the exploration of iSeries SQL and its OLAP ranking functions – ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE(n) -reveals the profound capability of these tools in enhancing data analysis and decision-making processes within an organization.

By effectively utilizing these functions, businesses can achieve a nuanced understanding of their data, enabling them to identify trends, segment data into meaningful categories, and rank items within specific contexts.

Whether it’s assigning a unique rank to each row with ROW_NUMBER(), understanding relative standings with RANK() and DENSE_RANK(), or categorizing data into quantiles with NTILE(n), these functions offer a versatile set of tools for dealing with complex datasets.

This technical prowess not only streamlines the analytical process but also provides actionable insights that are critical for strategic planning and competitive analysis.

As we continue to delve into the depths of SQL on iSeries systems, it becomes evident that mastering these OLAP functions is essential for any professional looking to leverage data for informed decision-making, highlighting the indispensable value of SQL in the modern data-driven landscape.

Please note, as always, that actual implementation and functionality may vary depending on the version of the iSeries operating system and DB2 database.

It is always good practice to refer to the official IBM documentation for more precise and detailed information.

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