Update directly in the data warehouse ?

[w860098]

We run a data warehouse system supplied by our parent company (based upon the Oracle and Cognos products).
Due to local business needs, it is necessary to extend the content of the database, i.e. add a number of additional tables containing 'local' business information.
Because the information will require input/update/maintenance processing, I am of the opinion that we ought to create an external database where all this information is manipulated (i.e. minimise the risk of 'corrupting' the data warehouse content) and only transfer specific detail into the data warehouse at the appropriate time.
It is true to say that certain detail will have to be downloaded from the data warehouse into the external database to provide data integrity; however the main feeds to the data warehouse only occur once a week and therefore transfer between the two environments can easily be catered for.
Since there is a difference of opinion amongst some of the other IT staff, I would be interested to hear how other sites have tackled this sort of problem.

 

[avjoshi]

General rule of thumb is that the warehouse applications are read only applications. DW system is a reporting system by nature. That is, any data manipulation should occur out side of DW system. This enables the system to maintain single version of truth. If you perform any data manipulation within the warehouse application then two subsequent runs of report may yield different results.
If you are required to include the local business information then you should, just as you said, create an external application for that purpose. This application can take feeds from the warehouse. You can load the warehouse from this application just like any other source system.

 

[johnherman]

I agree. Take whatever info you need out of the warehouse for integrity (natural key to surrogate key mapping, for instance) and do you transactional processing elsewhere. Databases are tuned for OLAP or OLTP, but usually do not tune well for both at one time. You can then copy or replicate the local tables to the warehouse for analysis.

A good example of a situation which occurs frequently involves forecasts. Comparisons of forecasts to actuals is a good DW application, but the forecast data should be maintained elsewhere and copied into the DW.

Sometimes the grass is greener on the other side because there is more manure there - original.