Rittman Mead - Delivered Intelligence

After the flurry of articles around Open World, it’s been pretty quiet around
here for the past two weeks. Usually a lack of postings on the blog equates to
very busy at work, and now I’m back I’ve been working away from home and trying
to do some prep for the UKOUG Conference
during the evenings. If you’re a vendor or a consultancy who’s exhibited at a
conference you’ll know the drill – lots of demos to create, collateral to
produce and if you’re like me and a speaker at the event, presentations to
produce and papers to write. This year I’m doing one on OLAP and Discoverer
(draft paper
here
if you’re interested, quite high level but some good positioning stuff) and
another on using the BI10g tools to build a data warehouse, the first one is
more or less done but the second one hasn’t even been started and therefore this
is taking priority over keeping the blog up to date.

The list of attendees for the
UKOUG bloggers’ dinner
looks good, with the list so far being myself, Niall Litchfield, Pete Finnigan,
Doug Burns, Peter Scott, Tom Kyte, Tim Hall, Jeff Moss, Nick Goodman, Richard
Byrom, Connor McDonald and Lisa Dobson and the general consensus being that
we’ll go for a chinese. As I said the invite’s open to anyone with an Oracle
blog and if you want to come along, just let me know.

I got an email through the other day from
Hotsos with a call for papers for the
Hotsos Symposium next
March in Dallas. I expect most readers of this site will be aware of the Hotsos
event, or at least Cary Millsap and the Optimising Oracle Performance book, and
to get accepted to speak at the Hotsos Symposium must be a pretty big event. Now
most of the papers that get accepted for this event are around "core" database
performance tuning, but I thought I’d take a flyer and put something forward on
how the OLAP Option can boost the aggregation and ad-hoc query performance of an
Oracle 10g data warehouse. When I put forward papers for events I always try and
think of something that would interest me, and something I’ve meant to do a bit
of research on recently was on how we could use analytic workspaces as
substitutes for materialized views, as from what I’ve seen so far they aggregate
quicker, retrieve data faster and handle sparse data better than traditional MVs.
Now that they can be integrated in with regular SQL – using

OLAP_TABLE or through

DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE – I thought the Hotsos
audience might be interested in a few test cases and worked through examples to
see whether the benefit was really there. Anyway, here’s what I’m going to
submit:

Proposed Title :
"Using
the OLAP Option to Boost Data Warehouse Performance"

Brief Abstract
: "The OLAP Option to Oracle Database 10g
provides an alternate means to store and query dimensional data in a data
warehouse. Using the OLAP Option, you can create an alternative to
materialized views called "analytic workspaces" which store aggregated data
and are queryable using extensions of SQL or through the
DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE built-in procedure.

This presentation takes the audience through the creation of an analytic
workspace as an alternative to a traditional star schema, and uses sql*plus,
tkprof and a number of test cases to show how analytic workspaces can be
faster to create than materialized views, take up less disk space and
provide a better performing aggregate layer for ad-hoc queries. It also
examines the use of DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE  to
provide a hybrid OLAP solution, and again uses test cases and sql*plus to
determine whether this is a viable alternative to a wholly ROLAP or MOLAP
setup."

Describe
the form of materials you will submit : "A
sample dataset in the form of an Oracle export file, scripts to create
materialized views and analytic workspaces, and scripts to perform the
various aggregation and query routines used during the presentation. The
only tools and products that will be required will be Oracle Database 10g
10.2, sql*plus and tkprof."

Being honest I don’t
think it’ll get accepted, because I’ve never specifically presented on Oracle
performance tuning before and because of the quality of the other submissions,
but it’s something that interests me and I think the central question – "Can we
use the OLAP Option to improve the aggregate handling of Oracle data
warehouses?" – is one that needs a proper "scientific" look at it.

Finally, I noticed
that
Howard is trying out the Opera blogging service and is talking about making
some changes to his site. I’ve been thinking the same myself recently, as this
site is looking a bit tired now and could do with a bit of an overhaul. It’s
about as non-standards compliant as you could get, cobbled together out
of bits from HTML DB, standard Moveable Type templates, bits of Radio Userland
and bits of Frontpage, and I’m keen to look at a proper content management
system such as Plone to manage some of the articles and papers that I’ve
uploaded here. I’m also keen for a bit of a new look, as much as for a change as
for anything else, and hopefully I can get something put in place for the end of
the year. Any suggestions, or thoughts on what Moveable Type can now do (I’m
running MT3.1 but haven’t really used any new features), I’d be keen to hear.

Posted in Uncategorized | 3 Comments »