OLTP and OLAP, a tale of two cities.

It was the best of times, it was the worst of times…

I’ve been working in OLAP systems (OnLine Analytical Processing – for Jim) for about 9 years now and have conciously avoided OLTP (OnLine Transaction Processing – for Jim).  The main reason for this being that the draw of big data — dealing with terabytes and terabytes of the stuff — seemed more  facinating to me than trying to squeeze more transactions into a second.  TPC-H appealed to me more that TPC-C  ( I’ll leave these acronyms as a homework exercise for Jim).

As it turns out and as I have predicted for some time now the two worlds do collide and in the future may merge.  But for now colliding is good enough for me.  This collision occured as a matter of happenstance… the EDW is being used as a source of data for transactional applications.  A good example would be a Call Centre look-up of Customer History in the EDW.  The OLTP part comes in when the Customer Service Rep has to wait for the EDW to fetch the information and return it in second or sub-second response time.  Now imagine a Call Centre with 1000 CSRs (Customer Service Reps).

I believe in the past I’ve talked about how the two worlds (OLTP and OLAP) diverged for performance reasons.  Reporting applications conflicted with Transactional systems for scarce resources.  We can expect to see the two worlds colide as the performance of systems increases to the point where they can support the two different types of accesses against the data.  The only other thing stopping this merging from happening is the systems themselves.  Millions (perhaps Billions) of dollars has been spent in the last decade splitting these systems apart.  Now it’s time to put all the pieces back together again.

And that will bring organizations and their systems toward my favourite thing, "one version of the truth." 

I guess I’ll have to get back into thinking about transactions and trying to squeeze more of them into a second.  Or I could just become a data modeler and focus on metadata 🙂  We’re still looking for that silver bullet… but that’s a different post.

  1. Jim Reply

    My homework:
    The TPC Benchmark™H (TPC-H) is a decision support benchmark. It consists of a suite of business oriented ad-hoc queries and concurrent data modifications. The queries and the data populating the database have been chosen to have broad industry-wide relevance. This benchmark illustrates decision support systems that examine large volumes of data, execute queries with a high degree of complexity, and give answers to critical business questions.
    TPC-C is an on-line transaction processing benchmark. The current version of the TPC-C benchmark is Version 5.8. The TPC-C has continued to evolve its benchmarks to remain as representative of current practice as possible. The TPC-C benchmark continues to be a popular yardstick for comparing OLTP performance on various hardware and software configurations.
    So it is easier to write the first time in a entry, on-line transaction processing benchmark (TPC-C) and decision support benchmark (TPC-H) or to force the reader who does not know it to do research. If you are writing for people who know all this stuff then you do not need to write it.

Leave a Reply

captcha *