This article originally appeared on the BeyeNETWORK.
There are data warehouses, enterprise data warehouses, data marts and exploration warehouses. Each of these architectural structures has its own advocates. The end user and OLAP technology support data marts. The data warehouse architect and management support the data warehouse and the enterprise data warehouse. But pity the operational data store – the ODS. Almost no one pays much attention to the ODS. A few users and a few database administrators pay attention, but that’s about it.
Perhaps the sparsity of attention paid to the ODS stems from the fact that the architectural structure of the ODS is a lot more free-form than other parts of the information infrastructure. The truth is that the ODS is a lot like a chameleon. It blends in with the environment and can look quite different from one organization to the next. What the organization is doing at the time determines how the operational data store is configured.
As a rule, the ODS is the place where you can do updates. You can get transaction response time there. You can operate on integrated data. You can make detailed decisions at the ODS. And, as a rule, the ODS does not contain much historical data. And, the transaction environment can be used to feed the ODS or transactions can be executed directly in the ODS environment (or both).
Data can pass into the data warehouse from the transaction environment without passing through the ODS. Or, data can pass from the transaction environment into the ODS, and then pass from the ODS into the data warehouse. Either path is acceptable, depending on the requirements that shape the ODS.
There can be many ODSs in an environment or there may be no ODS at all. The number of ODSs depends entirely on different requirements for transaction processing against integrated data.
With all of these variations and permutations, it is easy to see why the ODS is so confusing. It can look like almost anything.
However it is configured, the ODS is in support of the clerical community. The ODS is a tactical support structure. It is a mistake to think of or use the ODS as a strategic structure. Stated differently, the ODS is properly used to make up-to-the-second, detailed decisions. The ODS should not be used to make long-term analytical decisions. As such, the ODS serves the clerical community, not the managerial community.
The ODS can serve as a sort of staging area for data on its way to the data warehouse.
The ODS can have business intelligence (BI) technology placed on top of it. You can place Business Objects on top of an ODS if you like. However, when business intelligence is placed on top of an ODS, the ODS and the BI technology exhibit different properties than if a BI package is placed on top of a data mart.
When BI technology is placed on top of an ODS, any report from the BI environment is recognized to be accurate only as of the moment of the creation of the report. This is because the data in the ODS has the potential to be changed at any moment. With an ODS, you may run a transaction at 10:00 a.m. and get one answer and rerun the same transaction at 11:15 a.m. and get an entirely different answer. This discrepancy is acceptable because the data in the ODS may have been updated between 10:00 am and 11:15 am.
When business intelligence is placed on a data mart, the variability of the data is not a possibility. If you run a report on a data mart at 9:00 a.m. and you rerun the exact same report at 5:30 p.m., you’d better get the same answer – down to the penny.
Thus, analytical processing can be done in the ODS, but with different architectural considerations.
The ODS exists in the DW2.0 environment as the interactive sector. There are a few slight differences between a classical ODS and the interactive sector in the DW2.0 environment. In DW2.0, the architecture calls for a close coordination of metadata found in the interactive sector with other metadata found in DW2.0. This is especially true for enterprise-wide metadata. In the classical ODS environment, there is no call for the coordination of metadata between the ODS and the data warehouse.
There is a temptation to place data in the ODS that belongs inside the data warehouse. Over time, the data that does not belong in the ODS grows. It can be said that the data “creeps.” At some point, the data needs to be moved to the data warehouse environment.
On occasion, the question is asked, “Isn’t a data mart the same thing as an ODS?” The answer is no. There are distinct differences between the two architectural structures. Some of those differences are:
- Update of data can occur inside the ODS, whereas update of data is not done inside of a data mart.
- The ODS contains detailed data, whereas the data mart contains summarized and aggregated data.
- There is consistent 2 to 3 second response time in the ODS, whereas there are somewhat more relaxed time constraints in the data mart.
- The ODS is used for tactical decisions, whereas the data mart is used for strategic decisions.
- The ODS contains a very limited amount of history, while the data mart may contain a large amount of history.
- The ODS is used to stage data as it goes into the data warehouse, whereas the data mart takes data as it moves out of the data warehouse.
Bill is universally recognized as the father of the data warehouse. He has more than 36 years of database technology management experience and data warehouse design expertise. He has published more than 40 books and 1,000 articles on data warehousing and data management, and his books have been translated into nine languages. He is known globally for his data warehouse development seminars and has been a keynote speaker for many major computing associations.