The SQL of OLAP > > Intelligent Enterprise: Better Insight for Business Decisions

Welcome Guest. | Log In| Register | Membership Benefits

Intelligent Enterprise

Better Insight for Business Decisions

Intelligent Enterprise - Better Insight for Business Decisions
search Intelligent Enterprise
RSS
Webcasts
Digital Library
Subscribe
Home


  • EMAIL
  • PRINT
  • REPRINTS
  • Follow Us on Twitter
  • FOLLOW US
  • Share

The SQL of OLAP


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

Don't overlook the core strength of your OLAP technology solution: SQL.


By Michael Gonzales
September 18, 2004

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.

1 | 2 | 3 | 4 NEXT PAGE

  • EMAIL
  • PRINT
  • REPRINTS
  • Follow Us on Twitter
  • FOLLOW US
  • Share


 





New on the BLOG
SAP Gets Microsoft Nod on Performance Management
11.19.2009
blog author
Cindi Howson
SAP announced yesterday that Microsoft supports SAP BusinessObjects Planning and Consolidation (formerly known as OutlookSoft) as a "preferred solution"... It makes sense that Microsoft would prefer SAP BusinessObjects' product over others in that it runs on the Microsoft platform...

Read more from Cindi Howson >>

Doug Henschen
Text Mining: The Intersection of Content & BI
Companies used to employ armies of people to read through documents such as customer satisfaction surveys, but it took longer, cost a lot more money and yielded far less detailed, reliable and consistent information than you can now quickly uncover using text mining applications.

11.18.2009
Read more from Doug Henschen >>

Is the SharePoint Bubble Going to Burst?
11.18.2009
blog author
Alan Pelz-Sharpe
There is plenty to be excited about in SP2010, especially if you belong to the SharePoint channel of resellers, consultants, developers, and system integrators... but I am starting to get the distinct impression that the SharePoint bubble is about to burst.

Read more from Alan Pelz-Sharpe >>



Intelligent Enterprise Newsletters
Subscribe Here:
*Email:
 First Name:
 Last Name:
  Intelligent Enterprise Blogosphere Newsletter:
  Intelligent Enterprise Newsletter:

Email Type: