Intelligent Enterprise

When considering online analytic processing (OLAP), architects often focus on issues such as which dimensions to include, what facts are relevant, how often to refresh the data contents, and so on. Among these issues, the OLAP language is often overlooked. And of all the OLAP-centric languages, the most often ignored is SQL itself. To overlook the language of your OLAP technology solution is to ignore its real strength, or weakness, because this language dictates your applications' flexibility and complexity.

Many aspects of OLAP are already integrated with the relational database engine. This blending of technology blurs the distinction between an RDBMS and OLAP data management technology, effectively challenging the passive role often relegated to relational databases with regard to dimensional data. The more your RDBMS can address the needs of both normalized (traditional relational data and related techniques and technology) and denormalized (dimensional/OLAP-centric techniques and technology above and beyond star schemas) data, then the more you can realize the genuine value-add for OLAP-only technology: providing an environment to perform end-user interrogation of a data set instead of the heavy lifting associated with OLAP data management. Leveraging your investment in RDBMS technology, skills, and resources to fill dual roles not only makes for more efficient administration, but also minimizes the needed investment in OLAP-only technology.

OLAP Functionality

OLAP is a critical BI technology that enables analysts and executives alike to gain informational insight through fast, reliable, and interactive access to a wide variety of views. The target information is transformed from raw data to reflect the real dimensionality of the enterprise as understood by the user and defined by the business. While OLAP systems have the ability to answer "who" and "what" questions, it's their ability to answer "what if" that sets them apart from other BI tools.

Leading RDBMS products, such as DB2 and Oracle, currently offer core, OLAP-centric SQL functions, including categories such as ranking, numbering, and grouping.

Ranking is performed with two functions: RANK and DENSE_RANK.

• RANK assigns a sequential rank of a row within a window. The RANK of a row is defined as one plus the number of rows that strictly precede the row. Rows that aren't distinct within the ordering of the window are assigned equal ranks. If two or more rows aren't distinct with respect to the ordering, then the sequential rank numbering will have one or more gaps. That is, the results of RANK may have gaps in the numbers resulting from duplicate values.

• DENSE_RANK also assigns a sequential rank to a row in a window. However, a row's DENSE_RANK is one plus the number of rows preceding it that are distinct with respect to the ordering. Therefore, the sequential rank numbering will have no gaps, with ties being assigned the same rank.

Numbering uniquely identifies rows in a resultant set with ROW_NUMBER. This function computes the sequential row number of the row within the window defined by an ordering clause (if one is specified), starting with 1 for the first row and continuing sequentially to the last row in the window. If an ordering clause, ORDER BY, isn't specified in the window, the row numbers are assigned to the rows in arbitrary order as returned by the subselect.