Define ODS

[Jeff Smith]

I've done some research and the consensus seems to be that there is no consisent definition of an ODS. Two people talking about an ODS can have 2 entirely different concepts in mind. One place says it's a place to replicate the source systems another says that it should not be a dumping ground for data. One place says it should be 3NF another says it should have little ETL and still another says it should be used for Real Time Reporting.

In the Kimball world where we are using a Bus Architecture to create Star Schemas using data from multiple source systems, what is an ODS? We have a need for real time reporting. We have several transactions systems where the structures are exactly the same (used for different business units). We have Oracle Financial that is doing it's own thing. And then we have a few customer service focused systems for the call center.

Please suggest what the ODS should look like?

[BoxesAndLines]

I gave up trying to define it. It's just another one of those terms that lack clarity, like "enterprise data warehouse". From an architecture perspective, you can always replace ODS with other generic abstract terms such as integration layer or staging layer.

[ngalemmo]

I'll have a go at it…

The term ODS stands for Operational Data Store. The term 'operational' refers to the level of business decision making. There are three tiers: operational, tactical, and strategic. Operational decisions are very time sensitive, they require up to the moment information and rapid resolution. Tactical decision making is less time sensitive and usually involve timeframes ranging from a few weeks to months. Strategic decision making involves setting business direction that spans months and years.

A data warehouse is intended to support tactical and strategic decision making, while an operational data store is intended to support operational decision making. It's purpose is not to stage data (although it can be leveraged as such) and it is not a replication of the operational data base (although that is often an implementation option). It is important not to confuse purpose with implementation specifics.

Kimball doesn't talk about it much because it doesn't fit the dimensional paradigm. Dimensional modeling is geared toward tactical and strategic support. An ODS is typically a normalized data model with a lot of process attributes that don't necessarily make it to the DW.

So, if someone replicates the operational database but does not use it to support operational decision making, it is not an ODS. Its just a replicant database.

[Jeff Smith]

Thanks ngalemmo. Is the ODS typically 3NF because the source system is 3NF? I assume very little transformation is done. Does it store some of the dimensional information so that data can be rolled up similar to how it might roll up in the DW?

Let's say that the source systems are replicated. If operational reports go against the replicated database, then essentially the replicated database has an ODS component?

And the DW can pull data from the ODS, but I'm guessing the ODS shouldn't have data that isn't needed for operational reporting. It shouldn't be used as a pathway for all transactional data into the DW - that would seem to bog the ODS down.

[ngalemmo]

The reason its usually 3NF is it is used to address operational type queries, such as 'what are the outstanding orders for customer #123?'.  They tend to be very focused rather than mass queries against large amounts of data.  But that doesn't mean you could not piggyback on existing dimensional structures.  For example, I built a DW and ODS for a manufacturer.  In that instance, the transactional ODS data (orders, shipments, invoicing) was normalized but it referenced some dimensions (such as product and customer) in the DW.  It also served as a CDC mechanism for feeding the DW.  As transactions were updated in the ODS, it spooled a change history that was used to load the DW.

As far as replication goes, it is one method some use to create an ODS.  As long as it is used to support operational reporting, then you can call it an ODS.

The data in the ODS should fit was is required for the ODS. For example, you would not want to run HR data through the ODS if the ODS is being used for customer support.

[JR19]

An operational data store (or "ODS") is a databasedesigned to integrate data from multiple sources for additional operations on the data. The data is then passed back to operational systems for further operations and to the data warehouse for reporting.Because the data originates from multiple sources, the integration often involves cleaning, resolving redundancy and checking against business rules for integrity. An ODS is usually designed to contain low-level or atomic (indivisible) data (such as transactions and prices) with limited history that is captured "real time" or "near real time" as opposed to the much greater volumes of data stored in the data warehouse generally on a less-frequent basis.

[Jeff Smith]

My concern about the ODS as JR explains it is that how do you have real time reporting in an environment that is doing "stuff".

In my mind the ODS is used primarily for Real Time/Operational reporting. It can be used as a source for the DW if there is data moving through it that is needed in the DW. I might use it to capture daily production numbers that are then sent to the DW. But if the data isn't needed for Real Time reporting, I'm not sure I would put in the ODS.

I'm thinking there needs to be a data dump somewhere that holds data from the transaction systems. But I'm not sure.

[ngalemmo]

To me, JR19 appears to be describing a Master Data Management system more than anything else. Something that receives data, manipulates it, then passes it back to the operational system is a component of that operational system, not an ODS in the traditional sense, or at least how Inmon described it. It's intent is create a platform to support reporting of operational data outside the operational system. It's basic premise is to offload the query work as well as simplify access to the data. Its real need grew out of the complexity of contemporary ERP systems and concern about compromising the performance of those systems.

[Jeff Smith]

I get that there might be a need to take into a replica of the transaction system to load the DW. But my feeling is that the replica is too big to be used for real time reporting. I would think that databases for real time reporting need to be focused on just those things that need to be included in operational reporting. Of course, a line of business is never going to say no to the ability to roll up data from the past year up to the moment, but that's not operational reporting. Plus, if they needed a report that such information, it should be possible for the report to point to the ODS and the DW and combine the data into a single report.

So am I correct in thinking that the architecture for a system that has real time as well as historical data involves 4 logical points: Transaction System feeds ODS and Data Repository/Staging. Data Repository/Staging feeds DW. ODS may or may not feed DW, but primary source of DW us Data Repository. And the DW may supply heirarchies to the ODS. (Say the source data comes in at the client level. Clients are assigned to districts and regions which are stored in a dimension table in the DW. To report real time client information, the ODS would pull the Client to District to Region from the DW.

[ngalemmo]

I agree. Generally an ODS is created to support specific functions, and as such, is limited in scope. For example, it would be silly for a manufacturer to use an ODS to produce pick lists and invoices, beside the accounting implications, these are functions well covered by an ERP system. There is also no reason why an ODS cannot leverage other information contained within the DW.

[Sponsored content]