ODS Redux, Part 1

Data Integration Advisor

Information Management Magazine, June 2005

Rick Sherman

What do you call that thing that's holding your data? Don't know? Then it must be an operational data store (ODS).

No term has been muddied and misused more in the data management field than ODS. We have consensus definitions for data warehouses and data marts; however, ODS seems to mean anything that isn't a data warehouse or data mart. Whereas people understand how to architect, design and build data warehouses and data marts, they are stumped when it comes to an ODS.

In fact, building an ODS is like the lawless Wild West of data management because there is no standard architecture and design. Ask someone what an ODS is and you're likely to hear, "We will know it when we see it." Regardless of whether there is a clear definition of an ODS, IT departments have been very busy building them. According to a survey conducted in 2004 by TDWI, the average organization has four and a half ODS systems.

Typically, from a technical perspective, I would start this column by defining what something is and then examine its uses. There has been excellent published work that defines what an ODS is, but to date, it has fallen short because it deals mainly with explaining what has been built rather than why it was built. As a result, people tend to define an ODS by its symptoms rather than the business reasons for building it. Rather than follow that approach, I am going to use the methodology of developing a solution for a client, which is: gather the business requirements and then define and design the architecture based on the needs. In this manner, we will first identify the causes and business reasons for an ODS and create the appropriate design. In the second part of this column, I will compare the current conventional wisdom regarding ODS definitions and architectures associated with the actual business uses for an ODS.

The Business Reasons for Building an ODS

The business purposes for building an ODS include:

• Provide integrated reporting across a set of business processes, multiple applications or enterprise resource planning (ERP) modules.
• Establish a system of record (SOR) for dimensions.
• Establish a data hub.
• Achieve more frequent updates than those provided by the data warehouse.

Integrated Reporting

ODS systems got their start by supporting reporting requirements across multiple legacy applications. They helped fill a gap that existed in fulfilling data integration across these multiple applications because it was not within the scope of the application vendors or the data warehouse groups. The vendors were too busy expanding or acquiring new functionality and modules and not focusing on integration and reporting across them. Data warehouse groups tended to draw a line between management reporting (what they did) and operational reporting (what they did not do). Data warehouse teams were occupied by building and maintaining their existing projects without taking on the burden of operational reporting. In addition, updating the data more often than once a day, or in real time, was more than many of them could handle. Thus, the group maintaining the applications managed integrated reporting activities. As a result, the worlds of ODS and data warehousing/business intelligence (DW/BI) diverged into different, overlapping and counterproductive approaches.

Integrated reporting is currently being offered by both ERP and business intelligence vendors. ERP vendors have built ODSs, data warehouses and data marts into their products to meet their customers' ever-growing demands for reporting. A pre-built ODS is a way for these vendors to integrate across their own modules, as well as their competitors' products. These ODS systems are architected as a part of an overall data integration and reporting solution offered by the ERP vendor. The solutions work well and can be very cost-effective, helping avoid the need to build custom systems.

BI vendors have developed corporate performance management (CPM) solutions that support both operational and management processes. These solutions are often sold at the executive levels to enable scorecards and other management metrics reporting, but many times provide invaluable support for many operational processes and associated reporting. With an underlying ODS enabling near real-time updates, the CPM solutions again provide an alternative to custom-built solutions on top of enterprise applications.

SOR for Dimensions

When used as a system of record for categories of data, an ODS collects, integrates and distributes current information and provides an enterprise view of it. Common examples of these categories of data are customers and products. Business groups, such as divisions or subsidiaries, may be responsible for managing a subset of the data subjects, but no single group is responsible for managing the overall customer or product lists.

Data categories such as customers or products are often referred to by different names based on your application background. Data modelers and people involved in data warehousing will refer to this data as dimensions. Application-oriented people, especially those that have been involved in legacy applications, call this reference data. Finally, ERP vendors have adopted the term master data.

Regardless of what you call it, this data is generally physically scattered across multiple applications or source systems. For accurate reporting and analysis, an enterprise needs to make sure this data is consistent. For those in the data warehousing arena, making this data consistent is referred to as conforming dimensions. Historically, the DW group received de facto responsibility for making this data consistent. Many people, including those in DW groups, felt that the DW was not the appropriate application to provide this functionality. This involved creating and maintaining the consistent data with update capabilities enabled. This is a great use of an ODS - where the ODS becomes the SOR for this consistent data or conformed dimensions. ERP vendors have recognized this need and are starting to offer master data management across their application modules through their own pre-built ODS solutions. When an enterprise's needs are greater than one ERP vendor's applications, a custom ODS will be required.

Establish a Data Hub

Page 1 of 2.