Operational Datastore Usage

View previous topic View next topic Go down

Operational Datastore Usage

Post  dwcurious on Thu Jun 23, 2011 2:54 am

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?

dwcurious

Posts : 20
Join date : 2011-04-14

View user profile

Back to top Go down

Re: Operational Datastore Usage

Post  John Simon on Thu Jun 23, 2011 6:14 am

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

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Operational Datastore Usage

Post  hang on Thu Jun 23, 2011 8:16 am

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Operational Datastore Usage

Post  John Simon on Thu Jun 23, 2011 7:22 pm

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Operational Datastore Usage

Post  dwcurious on Fri Jun 24, 2011 6:50 am

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?

dwcurious

Posts : 20
Join date : 2011-04-14

View user profile

Back to top Go down

Re: Operational Datastore Usage

Post  BoxesAndLines on Fri Jun 24, 2011 10:55 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Operational Datastore Usage

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum