Fred Ho's Blog

SQL OLAP Functions: A Primer

Informix is adding a set of SQL OLAP functions in an upcoming release. The following write-up is to introduce the concept of such functions which may be new to Informix users. It is not meant to be a reference manual that lists the complete syntax and optional parameters. These analytic functions are valuable to users because they simplify the expression of complex but commonly used business questions and are efficient without resorting to subqueries or other calculations.

OLAP is OnLine Analytical Processing (as opposed to OnLine Transaction Processing – OLTP). OLAP is generally described as part of Business Intelligence that allows users to perform business reporting for sales, marketing, inventory control, business processes, etc. It usually works with multi-dimensional databases requiring frequent roll-up, roll-down and slicing and dicing.

While OLAP is the overall encompassing term for the type of processing to be done, there are specific functions known as SQL OLAP functions that are supported by different database systems and/or BI tools such as Cognos, Business Object, etc.

SQL OLAP functions are not new. In fact, they adhere to and extend the ANSI SQL-99 standards, known as the “Elementary OLAP” package. Many other databases and BI tools have provided these functions for some number of years, but SQL OLAP functions have not been supported by the Informix Dynamic Server (IDS) until now.

 The OLAP functions to be supported by Informix are as follows:

·           Ranking         RANK(), DENSE_RANK(), DENSERANK(), CUME_DIST(),

       PERCENT_RANK(), NTILE()

·           Numbering    ROW_NUMBER(), ROWNUMBER()

·           Aggregate      RATIO_TO_REPORT, RATIOTOREPORT

·           First/Last       FIRST, LAST

Note that if the underlying database does not support these OLAP functions, as in the case with Informix currently, then the user must rely on the BI tool to perform these calculations. If the underlying database system supports such OLAP functions, users can choose to submit SQL directly to the database or to submit it through the BI tool.  In order to take advantage of the underlying data base support of OLAP functions, the tool must recognize this fact and generates the appropriate SQL to take advantage of it. The difference in performance can be enormous, as in orders of magnitude differences.  A new version of Cognos Enterprise BI will generate SQL to take advantage of the OLAP functions implemented in IDS in an upcoming release.

SQL OLAP Functions: Concepts

A window partition is a subset of rows returned by a query, as defined by one or more columns in a special OVER() clause. SQL OLAP functions are applied to these partitioned result sets within the scope of a single query expression.

    Olap_function () over (partition by col1, col2…)

Non-analytic functions compute their calculations with respect to every row in the result set. However, an OLAP function in a query is applied with respect to the contents of its window partitions. An example with the RANK function is as follows:

Select date, store_name, sum(dollars) as sales_dols,

   rank () over (partition by date order by sales_dols desc) as date_rank

      from period, store, sales

      where period.perkey = sales.perkey

          and store.storekey = sales.storekey

          and state = “CA”

       group by date, store_name

       order by date;

DATE             STORE_NAME                                 SALES_DOL   DATE_RANK

2012-01-02      Beaches Brew                                                 785.55                        1

2012-01-02      Roasters, Los Gatos                                       762.25                        2

2012-01-02      Los Gatos Roasting Company                       636.25                         3

2012-01-02      Cupertino Coffee Supply                               634.00                         4

2012-01-02      Instant Coffee                                                  457.75                        5

2012-01-03      Instant Coffee                                                  713.75                        1

2012-01-03      Los Gatos Roasting Company                         633.25                        2

…                    …

2012-01-04      Instant Coffee                                               1031.50                         1

2012-01-04      Los Gatos Roasting Company                          613.95                       2

…                    …

The query returns ordinal rankings for sales figures. The stores are ranked according to their sales totals for each value; as the date changes, the ranking values are reset. Multiple sets of ranked values (1 to n) are computed within a single query expression.

In practice, OLAP functions allow application developers to compose analytic business queries more easily and more efficiently. For example, moving averages and moving sums can be calculated over various intervals, aggregations and ranks can be reset as selected column values change; and complex ratios can be expressed in simple terms. Within the scope of a single query expression, several different OLAP functions can be defined, each with its own partitioning rules.

Types of OLAP Functions

OLAP functions supported by Informix fall into four categories:

• Ranking
  • RANK
  • DENSE_RANK and DENSERANK (synonyms)
  • NTILE
• Numbering
  • ROW_NUMBER and ROWNUMBER (synonyms)
• Aggregation
  • AVG
  • COUNT
  • MIN
  • MAX
  • SUM
• Ration-to-report
  • RATIO_TO_REPORT AND RATIOTOREPORT (synonyms)

 The OVER Clause

 The OLAP OVER clause differentiates OLAP functions from other analytic or reporting functions. The OVER clause has three distinct capabilities:

• Defining window partitions (PARTITION BY clause)
• Ordering rows within partitions (ORDER BY clause)
• Defining window frames (ROWS/RANGE specification)

 Depending on the type of OLAP function in question (ranking, aggregation, etc.), the OVER clause is subject to different usage rules. The following discussion explains the concepts of window partitions, ordering partitioned rows, and window frames in general terms.

 OLAP Window Partitions

 A window partition is a set of rows that are grouped together for the purpose of applying an OLAP function. The OLAP function is applied to every row, but is calculated with respect to the rows in the partition. If no partition is specified, the OLAP function is computed over the complete intermediate result set.

 Ordering Rows within Partitions

The OLAP ORDER BY clause is separate and distinct from the ORDER BY clause that can be appended to any query expression. Typically, the ORDER BY clause for an OLAP function defines the expressions for sorting rows within window partitions; however, the ORDER BY clause can be used without a preceding PARTITION BY clause, in which case the sort specification ensures that the OLAP function is applied to a meaningful (and intended) ordering of the intermediate result set.

This specification is a prerequisite for the ranking family of OLAP functions; it is the ORDER BY clause, not an argument to the function itself, that identifies the measures(s) for the ranking values. In the case of OLAP aggregations, the ORDER BY clause is not required in general, but it is a prerequisite to defining a window frame. The partitioned rows must be sorted before the appropriate aggregate values can be computed for each frame.

 In the following example, the window partition is defined by the “Dimension” column. The five rows that contain the value “A” comprise a window, as do the five rows for “B” and “C”. The Meaure column is the input data for an OLAP RANK function; the rows within each partition are ordered by the Measure values. When the RANK function is applied, it is calculated over each partition. The Measure values are ranked 1 through 5 within each partition.

 The OLAP ROW_NUMBER function is also calculated in this example, without a PARRITION BY clause, to produce consecutive row numbers for the entire result set.

The SQL for this query would look like this:

            Select row_number() over () as row,

               dimension,

               measure,

               rank () over (partition by dimension order by measure)

                        as Olap_rank

             from …;

You can define window partitions according to the values in a single dimension or you can specify multiple dimensions. For example, you could partition the rows based on city and state values or month, quarter, and year values.

Window Frames

For non-ranking OLAP functions, such as aggregations, you can define a window frame. A window frame defines a set of rows within a window partition. When a window frame is defined, the OLAP function is computed with respect to the contents of this moving frame rather than the fixed contents of the whole partition.

The definition of a window frame can be row-based (ROWS specification) or value-based (RANGE specification).

Row-Based Window Frames  

The reference point for all window frames in the current row. The SQL OLAP syntax provides mechanisms for defining a row-based window frame as any number of rows preceding and/or following the current row.

In the following example, rows 1 through 5 represent a partition; each row becomes the current row as the OLAP window frame slides forward. The frame in this case is defined as between current row and 2 following, so each frame includes a maximum of three rows and a minimum of one row. When the frame reaches the end of the partition, only the current row is included. The shaded areas indicate which rows are excluded from the frame at each step.

In other words, the window frame in this case imposes the following rules:

• When row 1 is the current row, rows 4 and 5 are excluded
• When row 2 is the current row, rows 5 and 1 are excluded
• When row 3 is the current row, rows 1 and 2 are excluded
• When row 4 is the current row, rows 1 to 3 are excluded
• When row 5 is the current row, rows 1 to 4 are excluded

The following example applies these rules to a specific set of values, showing the OLAP AVG function that would be calculated for each row. The sliding calculations produce a moving average with an interval of three rows or fewer, depending on which row is the current one.