Hi Robert, thank you for your comments.

The ODS is not a data warehouse because ODS is in the third normal form, not in dimensional model. But yes you are right, ODS contains all the data in the enterprise, and data warehouse database also contains all the data in the enterprise. Because of the amount of data the ODS contains is the same as data warehouse, yes in this sense ODS is redundant. But the benefits I mentioned in the articles outweigh the cost of this redundancy.

About what operational functions (updates) that you would like to apply in the ODS, and why this updates are not applied back in the source system, please allow me to illustrate this case with an example. I hope it will clarify the situation. Referring to the diagram in the article, let's say for example you have 3 ERP systems: JDE, SAP and a propriatary system (custom developed in your company) called CGL. Say you have a business performance metric called strike rate, which is computed in the ODS, based on the information from all 3 ERP systems. This strike rate is by service centre, by service type and daily. So for example, for Birmingham, for Fiscal Year 2006 week 24, the strike rate for ServiceType1 are 93%, 92%, 96%, 95%, 91%, 94% (Monday to Saturday). And so on, we calculate the strike rate for every service centre, for every service type, for every day in the year. To calculate these percentages you need information not only from 1 ERP system, but from all 3.

Now, for this strike rate metric, your company also have strike rate target, which is by service centre, by service type. For example, for Birmingham, for ServiceType1, the strike rate is 95% whilst for Manchester, for ServiceType2, the strike rate is 92%. This information are not anywhere in the source system and we need to get it to the ODS. We could update the ODS directly, for example by building a web/windows .NET application. Or (this one is probably more preferable) by designing the new application to save the strike rate targets on a separate database, and we then ETL / import this information into staging, then into ODS.

About your last point, if the data marts are being fed from the ODS then what is the data warehouse used for. Yes we could feed the data marts from the data warehouse database (as the data warehouse database contains all the information on the ODS), but it is more efficient to feed them straight from ODS, especially if the data marts are fed daily.

Thank you for all your comments Roberts, they have been an eye opener to me too.

Kind regards,

Vincent