It’s a bit of a blog frenzy today!
On the 3rd November last year, immediately after the UKOUG conference, there was a seminar in Edinburgh to discuss temporal databases. (Mogens blogged about it here and I’d mentioned some of Rob Squire’s work here.) I had planned to go initially but was unable to get the time off work. However, Peter Robson attended as one of the instigators. Peter’s a friend who I’ve been encouraging to blog. Here’s his response to that idea
“I am someone wary of ‘the blog’, as a contributory activity to data overload, and therefore a medium, if I were to write one, that I would exercise so infrequently that everyone would forget I even had one! So I am grateful to you for inviting a contribution from myself!”
I asked Peter if he would contribute a guest blog giving his perspective on the seminar because I have a few regular readers now who might be interested. So, it’s over to Peter …. and as I’m aware how contentious a subject this could be, I’ll be ducking down immediately after posting 😉
“I recently attended a couple of lectures covering aspects of data warehouse, which prompted the very real question � is there any future for the data warehouse approach?
The first lecture was an all-day seminar from Chris Date on temporal database. This seminar was closely based on the book – Temporal Data & the Relational Model – by himself, Hugh Darwen and Nikos Lorentzos. It is the temporal component of database which becomes particularly significant in the data warehouse context. Chris took the relational model as his starting point, and basically introduced extensions to it which would in no way compromise the existing model. Consideration of intervals, pack and unpack functions, and the necessity for a final normal form (Sixth) were all aspects of his presentation. The point to note (without understanding the details of his presentation) was that the subject matter was complex, and required very careful attention on the part of the audience to follow his line of argument. Chris provided twelve archetypal queries that any competent temporal database must be able to solve to be worthy of the name. (It will not have escaped the attentive reader that the original tests for relational completeness presented by Ted Codd numbered also twelve …)
The final part of the day was given over to a presentation by Rob Squire of Oracle, who presented some of the research work that he has been doing on temporal database. He addressed exactly the same problems which Chris had explored, but with significant differences. His demonstration used the existing relational model (as implemented by Oracle) without ANY extensions. His demonstrations were all in standard SQL*Plus. Although he was somewhat reticent concerning the exact technology (there are IPR issues involved here), he used a view approach to provide what he described as a ‘temporal normal form’ over the existing data model. His demonstration showed how his approach enabled him to solve several of the twelve key queries that Chris Date had presented. He is confident of solving all of them. (Please note this work does NOT comprise an Oracle product.)
A couple of weeks later I attended a data warehouse meeting organised by DAMA (The Data Management Association), which gave great opportunity to discuss matters relating to data warehouse issues. What was significant here was the answer provided by one of the data warehouse gurus when the following question was put to him:
“How much longer can the data warehouse continue to exist as a separate database concept in the face of
a) increasing hardware performance
b) cheaper storage
c) the appearance of powerful functions within SQL*Plus?”
(Note? … Tom Kyte once said at a UKOUG conference presentation that he didn’t think there was anything he could not do in SQL*Plus which previously required a 3GL, given the appearance of the functions now present in SQL).
The answer from the data warehouse guru was basically … “Probably not much longer.”
Couple that with the progress made by Rob Squire without the use of functions or extension, and I would suggest that the days of the data warehouse may indeed be limited. The unanswered question of course is whether the solution proposed by Chris Date is required to handle temporal data, or whether that proposed by Rob Squire is sufficient? It is my intention to get Rob to demonstrate the solution of the twelve queries using a test database, but loaded with very large quantities of data. We need to understand just how Rob’s solution conforms to, and varies from that proposed by Chris Date. I am also working on a plan to get Chris to present his solution using the TransRelational Model – but once again there are IPR issues here.
Watch this space!”
My thanks to Peter for the contribution. If nothing else, you’ve certainly raised the standard of written English 😉
Data warehouses do indeed have a short future. Probably the only thing keeping them going now is the unsuitability of the current source systems for historical data reporting. In a couple of years time I see a few niche players dominating the massively parallel data warehouse appliance space for very fast, complex analysis (such as real-time fraud detection) and the rest of the market using the same commodity databases as their operational systems.
A few years ago, a DW typically ran weeks (or months) behind the operational systems that fed it; this gap is ever reducing as more an more organisations want to see up-to-the-second reporting of performance mixed with a historical view. This requirement to mix operational and historic reporting is reducing the utility of conventional data warehouses.
All sounds extremely interesting to me and I was gutted that I only found out from Peter about that temporal seminar whilst I was at the UKOUG and in the middle of a warehouse deployment so couldn’t attend.
I’ll start to look into it more in Feb once I’ve finished my BIRT SIG presentation off. Too many things to do…
Very interesting; Mark Twain’s quotation “The reports of my death have been greatly exaggerated” springs to mind!
The separate data warehouse still has a some pragmatic justifications for many companies:
1) performance isolation When your SLA dictates specific response times for contact centre, batch, website or web service applications, you often can’t afford to jeopardise that by letting a number of unpredictable monster queries into the database (resource plans or not)
2) change isolation Whilst BI systems are becoming far more critical, they are almost never as critical as the front-line systems. Take for example high-street banks; if their front-office systems are unavailable they have been known to close their branches. Now do you really want to be installing less critical BI software updates into the same system? (I would suggest that most IT directors currently don’t)
3) data simplification Warehouses are popular because they make lots of complicated operational tables into fewer, easier to understand, ones. Where businesses are trying to empower employees by giving more people access to BI, the easier it has to be to understand. Yes, you can put these structures into your OLTP system, but once you’ve done that there’s not a big difference to having them in a warehouse (same tools, same data, different hardware).
Note: academically neither of the first two arguments holds much water, but in the real world, much of IT is about risk and change management which makes them more relevant.
Don’t forget that warehouse systems will get the benefit of cheap hardware too. Yes I realise that you have to shuffle data about, but even with a single database you’d still have that – I don’t accept that you can run all powerful queries that would have been on separate star schemas by using current analytic SQL on normalised tables. Yes, it might be possible in theory, but what happens when you get a 15 table query with many repeating full table scans?
Whilst I don’t dispute that there will come a day where the majority of routine/daily/”easy ad-hoc” management reporting will be done from OLTP databases we’ll still need powerful analytic tools. Whether they are inside the core OLTP database, outside in systems like SAS, or in a separate data warehouse will depend on the future mix between reliability, managability and costs (e.g. licence/support fees). I personally think the data warehouse still has a healthy lifespan ahead of it yet.
And for the reasons that SimonH cited they are not dead (quite) yet. Another reason is that data can come from many sources: ERP, e-commerce, CRM, whatever and consolidated views of data currently need a consolidated source
(ah, bikixs, my favourite snack)
I agree with Simonh. The issue is really about normalized/denormalized data. Operational systems tend to be developed with normalized schemas; but warehouses need denormalized schemas for performance.
Cheap storage may ultimately doom the normalized schema. I’ve never seen a real justification for why OLTP systems need to be normalized. The standard ERD approach arose to reduce storage demands, but with the price of storage what it is now, is there any reason to normalize data across so many tables, even in an operational system? Perhaps object-oriented development would be harder with a denormalized schema, but how much harder?
I also agree with Pete_s’s comment about integrated systems. How many single source warehouses are being built these days?
My earlier comment might have sounded a bit negative – it wasn’t meant to. I’ve thought before now a single database for everything would be great; imagine… just one thing to monitor, backup/protect & tune. I’ll have RAC too – switch a couple of nodes over from BI when operations are busy, an extra few there for month-end batch (I saw the Oracle advert so I’ve got my 64 node ‘mainframe’ made out of 1U servers of course)… and then I wake up!
Stewart: denormalised data on OLTP systems by design… trigger city or perhaps some new kind of DDL so that you don’t need worry about updates? Now there’s a new topic in itself!
I hadn’t planned on entering this debate because I’m no expert, but then there were a few points that I don’t feel I can let go.
Stewart Bryson said …
“Operational systems tend to be developed with normalized schemas; but warehouses need denormalized schemas for performance”
Well they might at the moment, but the point is that hardware capacities and implementation strategies are changing all the time. Basing a data model on the limitations of current technologies may be the pragmatic solution, but doesn’t make it the best solution.
“. I’ve never seen a real justification for why OLTP systems need to be normalized. The standard ERD approach arose to reduce storage demands, but with the price of storage what it is now, is there any reason to normalize data across so many tables, even in an operational system?”
I’ll admit that my jaw hit the floor when I read that comment. The only reason for the relational model is to save space?!?! With an open, helpful heart, could I recommend this? You might learn some surprising new things ….
http://tinyurl.com/cfhwv