Operational Datastore Usage

Hi

I have requirement for real time reporting. Reporting against OLTP wouldn't be advised. Is using ODS better option? What are the pros and cons? Is ODS table structure exactly the same as OLTP or are there transformations involved?

Depends who you ask.
If you ask Inmon the ODS is a blend of both 3NF and Dimensional - in other words, whatever you need to get the job done.

Obviously you would want to do some denormalisation for improved query performance.

You could make the ODS entirely dimensional, but it may end up being more work than the above option for ETL.

John Simon wrote:If you ask Inmon the ODS is a blend of both 3NF and Dimensional

Really, I thought Inmon advocates 3NF DW which has nothing to do with dimensional modeling at all. Now if someone creates another logical dimensional layer on top of 3NF physical model, I don't think you should call it half dimensional and half relational. Just like you can't say you have a relational database because your application programs relate data in memory but loaded from flat files.

John Simon wrote:You could make the ODS entirely dimensional

In my understanding an ODS should be a normalised relational data store. If a data store is dimensional, it should be called dimensional data store or DDS.

dwcurious wrote:Is ODS table structure exactly the same as OLTP or are there transformations involved?

From modeling perspective, they are both normalised relational systems. However ODS may contain data from multiple OLTP systems, so obviously quite bit of transformation and integration are involved.

Is ODS better for real time reporting? I would say yes, if the task is only limited to operational reporting, and you have got one or plan to have one. Unlike most OLTP systems that contain mixture of history and current data in mixed formats (normalised and denormalised) for both data maintenance and reporting purposes, a pure ODS should only contain current operational data as part of data warehousing strategy aiming at storing history data in the dimensional data store for OLAP purposes. So an ODS should be far less voluminous (vertically) than DDS or OLTP system, and involve less ETL work caused by SCD, and hence deliver better latency and performance for real time reporting.

I attended an Inmon course last November and asked him quite a few questions specifically about the ODS.

I think it is important to be clear on terms here - the DW is not the ODS. According to Inmon, the ODS is on a separate database to the DW. Inmon believes the DW should be 3NF, and data marts dimensional. As I said above, he believes the ODS should be for intra-day reporting and modelled in whichever format (3NF or Dimensional) works best for the environment.

hang wrote:
Unlike most OLTP systems that contain mixture of history and current data in mixed formats (normalised and denormalised) for both data maintenance and reporting purposes, a pure ODS should only contain current operational data as part of data warehousing strategy aiming at storing history data in the dimensional data store for OLAP purposes.

I thought OLTP only had current data and all history is purged or put into DWH. Ideally wouldnt tht be the case?
Typically, how back is the data stored in an ODS and OLTP? Day, Days, weeks, quarters?

Every company is different. If there were requirements to track history for an OLTP application then the modeleres would build in history/audit tables to meet this requirement. Trying to define what an ODS has or doesn't have is pointless since there is no universally accepted definition. Reporting real time data out of an ODS isn't really real time. At best, it is near real time.