Our Articles

A Monthly Article from our Speakers

Current Article of the month

Articles of 2020

Articles of 2019

Articles of 2018

Articles of 2017

Articles of 2016

Articles of 2015

Articles of 2014

Articles of 2013

Articles of 2012

Articles of 2011

Articles of 2010

Articles of 2009

Articles of 2008

Articles of 2007

Articles of 2006

Articles of 2005

Articles of 2004

Articles of 2003

Articles of 2002

Claudia ImhoffThe Operational Data Store: Hammering Away

by Claudia Imhoff

December 2004


Perhaps you’ve seen or used a child’s toy that includes a hammer of some sort. Kids just love hammer toys. They seem to have some pre-wired talent for hammering. Watching them learn the art of hammering can be entertaining, too -- at least at first.

Then comes the moment when they realize that it might be fun to hammer other things in their environment. Like the cat, the dog, the coffee table, and their siblings. Bonk bonk.

So, we teach them that hammers are good in some situations and not so good in others.

In this regard, the Operational Data Store (ODS) has some remarkable similarities to the hammer. The ODS has been around for a while. It’s gaining acceptance and credibility as a tool. But unfortunately, the ODS is being used where another tool would probably work better.

To get the most out of any tool, the user should understand its features. Experience using the tool is obviously a plus, as well. To help you optimize the performance of your ODS, I’ve included ODS primary features below along with highlights of my experiences “hammering”.

ODS Features

Every tool is defined by its features, and the ODS is no exception. Furthermore, similar tools are distinguished by their unique features. For example, mallets, hatchets, ball peens, and rig axes are all hammers, but each has specific features and applications – especially to the experienced user.

When the tools being discussed are rather intangible, such as the ODS, differences may seem to be a matter of semantics, but they are differences none-the-less – again especially to the experienced user. (See sidebar for other terminology differences in the Corporate Information Factory.)

So, here are the primary features of an ODS:

Subject Oriented ODS’s are designed and organized around the major data subjects of a corporation – like CUSTOMER or PRODUCT. They are not organized around specific applications or functions – like Order Entry or Accounts Receivable.

Integrated ODS’s represent a collectively integrated image of subject oriented data that is pulled in from potentially any operational system. If the CUSTOMER subject is included, then all of the CUSTOMER information in the enterprise is fair game for inclusion in the ODS.

Current Valued An ODS reflects the “current” content of its legacy source systems. “Current” may be defined in different ways for different ODS’s depending on the requirements of the implementation (See Fryman and Montanari article, this issue). An ODS should not contain multiple snapshots of whatever “current” is defined to be. That is, if “current” means one accounting period, then the ODS does not include more that one accounting period’s data. The history is either archived or brought into the data warehouse for analysis.

Volatile Since an ODS is current valued, it’s subject to change on a frequency that supports the definition of “current”. That is, it’s updated to reflect the changes occurring in the systems that feed it in the true OLTP sense. Therefore, identical queries, made at different times, will likely yield different results because the data has been updated with changes.

Detailed The definition of “detailed” also depends on the business problem that’s being solved by the ODS. The granularity of data in the ODS may or may not be the same as that of its source operational systems.

Experience in “hammering” with the ODS

I view an enterprise’s inventory of information systems as those contained within the Corporate Information Factory (Figure 1).


The legacy environment is called Business Operations. These include transaction oriented, day-to-day application systems, grouped by business function or application, each addressing tactical business problems. The next environment is called Business Management and consists of the Operational Data Store. The ODS integrates data from multiple Business Operations sources to address operational problems that span business functions. The final environment consists of the strategic or Business Intelligence systems. These contain the Data Warehouse and Data Marts, permitting the analysis of trends, patterns, exceptions, etc., over time .

Understanding this is the key to cutting through the confusion about when and how to use an ODS.

Here are few examples of when to use, and when not to use, an ODS.

Good Application Of An ODS #1: Subject area reporting. “It would be great if I didn’t have to look at a credit report from Accounting, a demographic report from Marketing, a sales report from Finance, and an open order report from Order Entry just to get a complete picture for this customer.” This is a very common requirement.  Getting back to the features, it’s going to require subject oriented, integrated, current, and detailed information, that will require a transaction-based update mechanism in order to retain integrity and currency.

Good Application Of An ODS #2: Functional Integration. “We could really increase sub-component inventory turnover at the Dallas final assembly plant if we just could synchronize the Charlotte production line with the one in Atlanta.” Again, the solution will require subject oriented, integrated, current, and detailed information, that will require a transaction-based update mechanism in order to retain integrity and currency.

Bad Application Of An ODS #1: Single Source For All Data Warehouse Data. First, the semantic issues. Is the data subject oriented? Yes.  Is it integrated? Yes. Is it detailed? Maybe. Is it current? No. Is it volatile? No. In addition, the business problem is not operational. So, the construct is not an ODS. That doesn’t make it wrong, it just isn’t an ODS. Next, the practical issues. Staging data warehouse data is not a bad idea, and some of the requisite data may exist only in an ODS, but it’s very unlikely that you’ll be able to efficiently place all of the DW source data in one ODS for staging into the DW.

Bad Applications of An ODS #2: The Operational Data Store is used as a department-specific application

There is a lot of confusion surrounding how the ODS is different from the legacy or operational systems environment. The best way to explain the difference here is to look upon the ODS as a corporate-wide system whereas a legacy or Business Operations system is quite specific to a particular department or function.

For example, if you build a customer management ODS, focusing upon the creation of an integrated view of all customer information, there will be many departments or functions requesting access to that database. Marketing, sales, billing and perhaps financial people have a need to see this integrated customer record.

Compare that to something like an order entry system that has just enough information to support the creation and processing of an order. This is a very specific set of data for a very specific function for a very specific set of users. You would never think of trying to capture unnecessary data in this system, nor would you allow just anyone access to this data.

Once you know that you need an ODS…

Make sure you have a good enterprise data model before you start. By definition, you will be integrating data from multiple functional business areas and applications. A high quality, enterprise data model will make the job of understanding and mapping the source data from different areas a whole lot easier.

Don’t include every attribute of a subject area in your ODS simply because the data warehouse may need that data. Use only the attributes that are relevant to solving the operational business problem at hand. Remember that every field takes “x” amount of time to extract, integrate, cleanse, verify, etc. If your update mechanism calls for immediate or near-immediate updates, then your success will dictate that you extract fewer fields each having simpler data acquisition processing. Don’t slow the process by including fields that you don’t need.

Use an iterative approach. Identify one or more related operational business problems. Prioritize them. Start with one that you can “get your arms around” and that has a strong return on your investment. Then, tackle the rest of the list in phases, in a way that makes the most sense given your situation.


We are still working through many of the implementation issues concerning this new architecture and I am sure that the characteristics, technologies, definitions, and so forth, will continue to evolve as we learn more from building these important and critical systems. I hope this article has helped you to understand more about the operational data store, when to build it, when not to, and how to determine if you have an ODS in your Corporate Information Factory future.