3rd party data - Store in Data Warehouse or Primary database?

Question

This is mostly a data warehouse philosophy question.

My project involves an Oracle forms application, and a Teradata Data Warehouse for reporting and ad-hoc purposes. In addition to the primary data created by the users of our application, we also require data from various other sources. Currently, this 3rd party data comes via FTPd flat files directly to our Data Warehouse. To access the data, our users must use a series of custom BusinessObjects reports.

My question is, would it make more sense for this data to be sent to our source Oracle system instead? Is it ever appropriate for a Data Warehouse to be the point of origin for users to access raw data?

In short, is it more important that the operational database contain only the data created by your project, or that the data warehouse remain dedicated solely to reporting and analysis?

Answer 1

We do either depending on the situation.

If the third party data needs to be seen by the regular users and the everyday application, we put the data where it will be avaialable to them. In our case, this data is often data that is stored in custom tables in the database that are selectable rather than editable to prevent users from changing third party data. If you use yoour regular tables, you may need a trigger to prevent accidental change of this data. Also, it often comes in a form that may not easily fit your datas tructure and if they only need to see if for report purposes, you may not want to take the time to clean it up so that your regular application can take it. SO inthis case custom tables may become necessary.

For instance a 3rd party might have a field that is larger than your field for the same thing. You might remove the meaning by truncating their data to fit your structure. Further, your structure might have a set of constraints that the 3rd party data did not, do you want to risk your own data integrity by removing those constraints? Probably not. If my application thinks a field should be required or be a valid date, then I don't want to change to accomodate storing 3rd party reporting data. If the data can and should be available to the users for change (we do a lot of this) then go ahead and clean it up to your database standards and insert it.

Often 3rd party data does not need to be seen by the users who do the regular data entry but only in management reports pulled from the data warehouse. In this case, no I would not try to put the data anywhere except the data warehouse. Why complicate life by making available for accidental change?