Bits and Bobs and the Hotsos Symposium
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.