Wednesday, July 9, 2008

Article : Closing the Ad Hoc Query Performance Gap for Good

I found an interesting article today in amongst my Google alerts. It related to a topic of conversation at the recent TDWI conference in May about the issue of adhoc query performance within data warehouse environments. The article, on the whole, was very good (in my opinion) and included comments by Oracle's vice-president of database marketing, Willie Hardie.

The full article in Enterprise Systems is available here.

The article examines the reasons why business users feel their queries are taking too long and what steps companies are taking to try to improve query performance. The basic reasons for poor query performance was given as being down to two issues:
  • success of pervasive BI - so more users are running adhoc queries
  • more data - users want access to more and more data, both in terms of level of detail and time span.
I would add a third reason, which is increasing sophistication. Simply presenting users with reports that show revenue and expenses for the latest month, quarter and year to date are not adequate in today's highly competitive environment. Business users want to know about trends - this period vs last period, this year vs last year, this period vs the same period last year, like-for-like, shares, ranks, forecast, customer segmentation, market basket analysis. The list goes on and on and on.

Here is a direct quote from the article:

......The RDBMS -- or, more specifically, the Oracle RDBMS -- is an unmatched analytic workhorse, Hardie argues. Oracle is one of the biggest data warehousing players in the business, he points out, and the Oracle database powers some of the largest DWs in existence.

"The Oracle database is proven to be the fastest database out there for both transactional systems and data warehousing systems, across all scales, from small to extremely large systems. You ask any Oracle customer out there and they'll all give you the same answer: Oracle is the fastest database out there on the market right now," he claims.

What I would add is the Oracle Database is the only database with embedded multidimensional OLAP, which is fine tuned for adhoc query performance, query scaleability and, most importantly, calculation power. As I stated above it is no longer about simply what is happened in the last trading period, BI analysis is now all about comparisons, trends and KPIs (calculations) at aggregate levels with the ability to drill right through to the lowest level of detail.

As the article quite clearly acknowledges there is a trend to opening up the corporate data warehouse to more and more users, which means query performance and scalability are becoming increasingly important. Only Oracle Database has specific built-in optimisations, such as OLAP and data mining, to meet these growing requirements.

For those of you new to Oracle OLAP Option and Oracle Data Warehousing you can get more information from these links:

Oracle OLAP Option on OTN
Oracle OLAP Option Forum on OTN
Oracle OLAP Option Wiki
Oracle OLAP Option on Oracle.com
Oracle Data Warehousing on OTN
Oracle Data Warehousing on Oracle.com
Optimised Warehouse Initiative