Integration of OLTP, ODS, and ADW
Can you hear it rumbling? There’s a freight train coming, hard and fast – and it won’t stop when it gets here. That’s the sound of technology’s wheels spinning. A million plus pounds of
steel, engine, and raw power – all headed this way.
what they need at the source. This mind- set does nothing to address “knowledge” or ensure that information is used efficiently. There is no incentive to really know where all the information is
and what it means.” John Ladley, “Beyond the Data Warehouse: Convergence”, DMReview, June 2003.
In complete agreement with John Ladley on this one, there is a huge level of convergence coming straight at us, it’s as if we are standing in the middle of the tracks with our back to the
approaching freight train. Convergence is barreling down on us – just look at the RFID – pushing businesses to track goods from inception to sale and maybe beyond – not only do
they require reporting of the current information, but they also (from the same system) will demand consistent, historical views of where the RFID traveled and when (historical
What’s happening in the industry?
There’s a convergence afoot… The ODS (operational data store), OLTP (on-line transaction processing), and DW (data warehouse) are converging on one-another. They are on a head-on collision
course with each other.
In order for business to survive, be more competitive and move forward quicker, they must get a feel for the entire business all in one solid picture. We’ve been asked in the past for Corporate
Data Warehouses to hold history; it’s the same story, only now we’re also asked to balance the transactional information against the history to predict future trends (both good and bad). This, my
friends, is the ADW (active data warehouse).
What does this look like from a time-frame perspective? If we examine the history leading up to the trends today, we may see a diagram as follows:
OLTP for reporting is being replaced by ODS, OLAP and ODS systems are being replaced by active or near real time data warehouses (ADW). In the future, all these systems including OLTP may be melded
into a single version of the truth: ATDW (active transaction data warehouse). Eventually it may culminate in ATASW (active transaction, active structure warehouse).
What is being discussed is the questionable need to have all the old systems around. New systems like SAP, Siebel, and PeopleSoft were built to sunset “old” legacy technology, in the
future (ATDW) these applications will either merge together with their warehouse component, or face becoming sunset technology by new applications written that are Active Transaction Data
Warehouses. Hardware and Software is growing faster and smarter by the day. With the introduction of Nano Technology this picture may happen faster than we think.
The conclusion is a natural one, based on sound business principles: Why separate any of these systems in the future if the technology can have both (OLTP and ADW) living happily together? Sorry,
that was the technologists’ principles. The business principles are: Why can’t I get one corporate answer across the enterprise? Why can’t my enterprise use one corporate
application that’s fully web-enabled? Why can’t my data reside in a single location so we can mine the data that is built now along with the information that stores history? Why
It only makes perfect sense. The lines between OLTP queries, Operational Reports, and Data Warehouse questions are blurring; or rather – morphing. The business now poses questions like this:
What is the impact of the current transaction based on what we already know (history) about the customer/product/situation? These questions are asking the systems to apply learning logic (neural
nets and data mining algorithms) to learn what the history means, then apply it to the transactions as they arrive.
Convergence is not new, it’s happening in other industries as well.
as cell phones, personal digital assistants (PDAs), wireless pagers and laptops that have turned our workdays into buzzing, beeping exercises in sensory overload will start to slow; and a new
generation of multifunction mobile devices will appear.”, David A.J. Axson, DMReview, January 2001, http://www.dmreview.com/article_sub.cfm?articleId=2915
Lest we forget – there’s convergence between technology, biology, molecular science, mathematics, physics, and chemistry: Nano Technology.
But wait! There’s More!
The ODS… hmmm last year even with the drop in IT spending, we saw the implementation of ODS’s around organizations, a fully-integrated view of operational data in a single system to provide
corporate wide reports of the company from top-to-bottom.
We also saw the institution of federal mandates, and international trade and securities laws (like Sarbanes-Oxley and others). The ODS represented a separate storage area for “current data only”,
and wasn’t supposed to store history (except transactional history). It was supposed to take the burden off of the Data Warehouse, but it only added pressure to integrate the ODS with the Data
Warehouse – turning it into an Active Data Warehouse.
This is all well and good, but as Bill Inmon pointed out: “the purpose for an ODS and DW to exist is about the same as steel is to sand” So why converge? Do we really have to? Is it a
good idea? Claudia Imhoff and I were discussing this very point, to which we did not reach a conclusion (yet). My feeling is: the only reason for separation today is when there’s not enough
horsepower to combine both the ADW and the ODS.
Wait, are you saying what I think you’re saying?
Yes! Most definitely! I am saying that the ODS, and the ADW, and OLAP are combining today in powerful environments, producing a single version of the truth for both transactions across the
enterprise and the historical views of the enterprise.
What does this mean for the OLTP Systems?
For some, OLTP may eventually be integrated in as well – why have replicated data all over the organization? Besides, can we be in-compliance with replicated data? (I’ll leave this one to
your imagination). Business rules, unstructured, structured, and metadata information systems are centralizing – there’s no doubt about it. Of course – not all OLTP systems will be integrated, but
over the years, there will be quite a few merging together (right along with the ODS and DW).
How does this work?
The key to the success is the data model. The way we represent the information in this store is critical. Those who already are familiar with the Data Vault can attest to this. The data model makes
it possible to combine CURRENT or NOW information (ODS) with historical (DW) information, and use them both at the same time without making copies in different physical environments.
Will the Data Vault (data modeling) work in an OLTP solution?
I can’t say for sure, the jury is still out on this one – but one thing is certain, the Data Vault is currently being used for ODS modeling by several companies today. Their statements are: why
have copies of the data? Why not have the ODS and the DW “look” the same structurally? Isn’t the goal of the ODS to integrate transactional data, and the DW to integrate historical? So if
that’s true, we should be able to use the same modeling techniques for both.
Now wait a minute – why OLTP? How does that fit into this solution?
OLTP typically has many business rules surrounding its implementation, in some cases the legacy systems are 5, 10, or 15+ years old. When we’ve implemented data warehouses, we have found (in many
cases) that the business rules in these legacy systems are: 1) hard to find, 2) hard to change, 3) old and brittle, 4) don’t reflect today’s business rule desires. Why else would users be
entering data into fields it isn’t built for? Like an address field with *123 means “check the address” to the business. Aren’t these band-aids because the business lacks the means to truly fix
or adjust the business rules in these systems?
OLTP must change going forward. There isn’t a better example around than the packages like SAP, PeopleSoft, and Oracle Applications etc… These arose from the need of replacing and recoding
business – bringing it together, consolidating actions across the corporation. New nimble businesses understand this; even some of the giants who’ve implemented these systems are reaping the
benefits. OLTP must change again – it must now be integrated directly into our ADW and ODS systems. Why? So we can change the business rules, study the impact, and affect the current data only
(across the entire corporation) and do it in ONE PLACE!
The company that grasps this convergence concept today, and implements a SINGLE system for OLTP, ODS, and DW will have the jump on tomorrow’s competition – integrating business rules, unstructured
data, structured data, and metadata in a single system will lead to one of the most competitive systems in history.
intelligence technology. A TAS is implemented in real time, much like the original ODS.” Timothy K. Hawes, DMReview, June 2000, “The ODS – She aint what she used to be”.
Technology wise we’ve moved way beyond June 2000, but this quote is more relevant now than it ever has been. Why else do we see database vendors implementing Cubes, Data Mining, OLAP queries
directly into their engines (side-by-side with 3rd normal form and continued application support like transaction capturing, and messaging)?
We see it everywhere – there’s no escaping it. As business competition heats up, and our businesses must make decisions, better, faster, cheaper…. we must provide them with a consistent, single
view of the information (regardless of transaction or historical material). This leads directly to the implementation of the ultimate ADW where ODS, OLTP and DW live happily together.
If you do not agree, ask yourself these questions:
- Would I still be in business if I didn’t seek complete and total integration of my information across my enterprise?
- Can I be in compliance with federal and international trade regulations if I can’t audit the trail of information and all its changes throughout the enterprise?
- Can I save money by consolidating systems?
- Can I save time in build-out of solutions to meet the business needs through consolidation?
- Can I keep my customers and keep them happy by known exactly whom they are, and not storing their information in triplicate across the enterprise?
Remember the old joke? When you went to fill out a form for corporation X, it had to be filled out in triplicate, with each page copied to a different department of the organization… Hmmm
– that process reeked of decentralization of the information. We’re now down to collecting the information on a single or multi-page web-form, which is collected into one storage system
(hopefully also replicated to the warehouse). There-in lies the problem. In the future we don’t have the time or the money to replicate the data and learn from it.
Small businesses are nimble, and sometimes can out-maneuver the big-boys. Why? Their systems are fully integrated; they can trace data quickly, they can change their business processes in one
system, and can predict future competitive moves before the competition. What if the power of the dollar backed a large enterprise to consolidate all these systems? Would the large enterprise
re-gain the nimbleness of a small organization? This is surely something to think about.
© Dan Linstedt, 2004
Core Integration Partners, Inc
455 Sherman St, Suite 120
Denver, CO 80203