The SQL Of OLAP - InformationWeek

InformationWeek is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them.Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

Software // Information Management
04:07 PM


Don't overlook the underpinning -- and core strength -- of your OLAP technology application.

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.

We welcome your comments on this topic on our social media channels, or [contact us directly] with questions about the site.
1 of 4
Comment  | 
Print  | 
More Insights
InformationWeek Is Getting an Upgrade!

Find out more about our plans to improve the look, functionality, and performance of the InformationWeek site in the coming months.

Remote Work Tops SF, NYC for Most High-Paying Job Openings
Jessica Davis, Senior Editor, Enterprise Apps,  7/20/2021
Blockchain Gets Real Across Industries
Lisa Morgan, Freelance Writer,  7/22/2021
Seeking a Competitive Edge vs. Chasing Savings in the Cloud
Joao-Pierre S. Ruth, Senior Writer,  7/19/2021
White Papers
Register for InformationWeek Newsletters
Current Issue
Monitoring Critical Cloud Workloads Report
In this report, our experts will discuss how to advance your ability to monitor critical workloads as they move about the various cloud platforms in your company.
Flash Poll