Re: Need Help on Operational Data Store

Dennis,

I've always characterized the ODS as having a data model that is only "lightly transformed" from it's source operational systems, while a data warehouse and the downstream data marts have a query-optimized star schema data model. By saying "lightly transformed", I'm referring to the fact that data "transformation" doesn't refer only to summarization, but also to standardization/consolidation and cleansing. Performing the standardization and cleansing parts of "transformation" on the way into the ODS enables some terrific functionality, which might fall into the following categories:   a.. mid-way "staging" area for data flowing from operational systems into the data warehouse and on to the data marts     This is probably the way that most people think of the ODS. Depending on the complexity of transformation, it may or may not be a valid purpose. Some simpler transformations can (and should!) be performed "on the fly" straight into the star-schema data model of the data warehouse, but for complex multi-step transformations and integration/merge of data from many sources, where synchronization, failures, and restarts are the norm, a staging area for this complex processing is crucial.   a.. offloaded "tactical" reporting from operational systems     This is another valid purpose for an ODS -- whether as a simple "reporting database copy" or something more sophisticated. Sometimes, if this is the only requirement, it can be as simple as a standby database that has been opened READ ONLY and used for offloading reporting or even a periodic EXP/IMP or the like. However, such simple replication processes usually preclude the other usage categories described in this email.

    This is very useful when the operational system is "legacy" and additional development is not possible or cost-effective, yet new reporting requirements still exist and continue to proliferate. Instead of re-hiring old RPG programmers, use the ODS to enable new reports using Crystal, Oracle Reports, or even good ol' SQL*Plus...   a.. consolidated-view "tactical" reporting (if there are many disparate source operational systems)     This is a slight variation on the purpose mentioned above. I experienced it for the first time at a company which was growing rapidly via acquisition (way back in the hazy, crazy 90s). We spent a great deal of time working on the data modeling to ensure that we accurately defined and mapped data entities from the disparate source operational systems and merged them into a consolidate enterprise data model. The result was a consolidated database of operational data that could be used for consolidated-view reporting, relieving much of management's pressure to consolidate operational systems. An additional side-benefit was that all the mapping work between data models were later used on conversion projects to truly consolidate the disparate source operational systems...   a.. "data-archival" point for operational systems     I worked one project where the source operational systems were all factory-floor controllers (micro-vaxen) and there was a legal requirement to save that information for a very long time (20 years). There was also a desire to analyze and mine that data. The solution to both requirements was an ODS where the data was archived to optical storage mounted on an HSM file-system. Rather than archive from hundreds of factory-floor machines, we killed two requirements with one stone...

    Mixing this solution with the previous one ("consolidated-view tactical reporting") is something I've not experienced, but it seems like another interesting purpose. Never mind "legacy" applications, there aren't many "modern" ERP or CRM systems that adequately handle data archival/retrieval; only "purge" at best. An ODS is an elegant solution for these requirements... There might be more uses for ODS, but I've found it useful to maintain these possible solutions in mind. I would at least consider whether business requirements fall into any of these categories, because an ODS (not a DW, not another "operational system", not a simple "reporting database copy") is a better solution for all of them...

Hope this helps...

-Tim

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, November 08, 2002 12:43 PM

>
> After reading literature from Kimball, Inmon and some other experts, it
> seems to me that we should have a 3-tier architecture -
> Tier 1 - operational data
> Tier 2 - ODS which contains integrated, cleansed, transformed operational
> detail data, or staging area in relational schema
> Tier3 - star schema Data marts
>
> The question/contention here is tier 2 - should we provide reporting
> capability for this? According to Kimball, staging area should not be
> accessible to end users for direct reporting. But Inmon seems to disagree
> with him on this one.
> If ODS is an integrated, authoritative data source of the enterprise, I
> don't see why we can't create ad-hoc reports against it. Of course since it
> is relational, we might need reporting tools and heavy IS involvement. So
> IMHO it depends on what the user needs are to decide what tier2 should look
> like and be used for.
> Anybody cares to commend on this?
>
> Thanks
>
> Dennis Meng
> Database Administrator
> Focal Communications Corp.
>
>
>
> Jared Still
> <jkstill_at_cybcon. To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> com> cc:
> Sent by: Subject: Re: Need Help on Operational Data Store
> root_at_fatcity.com
>
>
> 11/08/2002 11:19
> AM
> Please respond
> to ORACLE-L
>
>
>
>
>
>
>
> Dennis,
>
> I think you got it wrong right off when you stated that there's a
> "lot of confusion in ODS vs. DW ".
>
> It isn't that issue at all. No two people can agree on what an ODS
> is at all, much less compare it to a DW.
>
> To me for instance, an ODS is a place to stage data for the final
> stages of some other process, be it a DW, or anything else.
>
> An ODS is a rather generic term, and therefor whatever you
> want it to be.
>
> Jared
>
> On Friday 08 November 2002 07:59, dmeng_at_focal.com wrote:
> > Greetings -
> > I need some help with building an Operational Data Store. I know there
> are
> > a lot of confusion in ODS vs. DW but I belong to the camp of 'ODS should
> be
> > used only for operational reporting, not decision support'. So while
> > Kimball talks a lot about building a DW in his books, he does not cover
> ODS
> > much. Are there any books/websites/third parties that deal with building
> an
> > ODS?
> >
> > TIA
> >
> >
> > Dennis Meng
> > Database Administrator
> > Focal Communications Corp.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jared Still
> INET: jkstill_at_cybcon.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: dmeng_at_focal.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).