Monday, April 21, 2008

Don't Mix Transactions and Reporting


"You don't need to keep all historical data online in the same database. In particular, reporting and ad hoc analysis should never be done in the production database....Data mining, reporting, or any other kind of analysis should be done in a true warehouse anyway. The OLTP (Online Transaction Processing) schema is no good for data warehousing." -Michael Nygard, Release It!

I am working on a weird incremental system replacement project at the moment. It's odd in that they are replacing the reporting, search, and view parts of the system, without replacing the forms. A lot of the design and architecture is optimized for those operations. We're all really quite worried about what happens when we have to edit some of this data- turn into an OLTP. It's hard to update de-normalized data without all sorts of locking issues.

My current silly idea is to try to tell people that the schema and system that we are working on now is really just the reporting and search system. The "real" system replacement schema will be coming in the next increment. It sort of pains me, because the "genius" solution we came up with for incremental system replacement on my last project would have worked even better here- one screen at a time, one table at a time. Plenty of complexity, to be sure, but you can get new stuff out there tomorrow.

As it is, we are running a ridiculously high risk of creating shelfware at the moment. Sigh.

2 comments:

Anonymous said...

I couldn't agree more that your last project was "genius" in action. Especially that part where that one guy (Drew was his name?) re-wrote the meat of the legacy application in only 3 weeks after his colleague failed to product so much as a screenshot in 3+ months. Methodology over skill, that's what I always say.

Matt McKnight said...

Indeed. The biggest problem on the last project was fitting our enormous intellects into a single room. This was obviously exacerbated by the times when our purported managers entered the room with either their gargantuan hands or unfathomable odor.