ODS vs DW: How do they co-exist?

[bdukes]

A little background first:

I worked for an Healthcare organization that uses a large off the shelf Practice Managment (Scheduling, Patient Registration, Billing) and Electronic Medical Records applications through an Application Service Provider. As part of the product suite, we have a local Operational Data Store (ODS) that is updated weekly by the ASP (we do not have direct access to OLTP database). The ODS conains 1000+ tables for a rather complex and poorly documented data model.

We're being pushed by parts of management to move toward Data Warehousing using Dimensional Modeling, but (for now) only including data from this single ODS. Part of the belief is that we can build a Dimensionaly modeled DW, cram all of the ODS data into it, and life will be good.

Is it true that even with a well thought out and properly constructed DW that there will still be a need for the ODS? Where is the line drawn on what purpose the ODS serves for reporting versus the DW? Can you point me to any resources (articles, whitepapers, books) that help describe this co-existence?

Your feedback, suggestions, professional opion would be greatly appreciated.

Thanks in advance,
Brent

 

[Hans63]

I can only talk about the situations I have encountered. I don't have any articles or whitepapers I cna point out to you.

I have seen use of both ODS and DW in coexistence. As the ODS contains timestamped operational data, you can find any minute detail of the data as it existed at some point in time. In the DW you often (but not always) do some aggregation and you apply business logic, to get to your dimensional miodelled data.
So when you come across some anomalies in a report, you can do the in-depth analysis on the ODS.

Another function we use the ODS for is to find out the Delta (what has changed since the last run).

Of course this is dependend on the time you keep your data in your ODS, which is probably related to the amount of data you get to process each day.

When your grain in the DW is equal to the grain you have in your ODS, I doubt there is a need to keep your ODS for longer than a few ETL runs.

 

[johnherman]

The general opinion is that the ODS contains Operational data of a tactical duration. This data is used for short term planning. The DW contains a long history of data, useful for trend and strategic planning. Some ODS have up to three years data; ours has two.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright