1

Should you use the staging database within a data warehouse as a source of operational data?

ie, is it good practice for other operational (non BI) systems to source their data from this database? Or is the data warehouse there for information reporting/analysis and definitely not for information processing/feeding to other systems?

In general, is it good practice to source operational data from any part of a data warehouse system? Or should a data warehouse be a consumer of data only?

2 Answers 2

1

There is a forum discussion which discusses this briefly, but quite well IMO: Tek Tips - Using a data warehouse as a source system

Some points for consideration:

Duplication of effort / Single source of the truth

Will the operational system need to apply the same logic to the source data that the data warehouse is already performing?

Read-only source

Does the operational system expect to write changes/updates back to the warehouse?

Timely information

Is the operational system happy with the latency of the warehouse? (Generally T-1)

Service-level agreement

What is the impact to the operational system if there is an outage to the warehouse? In my experience warehouses, inherently, have a lower priority than transactional systems and may have, for example, up to 24 hours to become available (for querying) and up to 4 days for the ETL to be restored, running, and for the warehouse to be up to date.
If the operational system is internal and non-critical, this may be acceptable. If it is customer-facing, and retrieving FX rates from the warehouse for pricing, probably not.

I think the quote from that forum post that sums it up nicely is:

The business drives the technical solution with their requirements.

Your role would be to provide the facts to the business. If the business is willing to accept the risks and proceed with using the data warehouse as a source for an operational system, then I suggest you get that in writing and triplicate.

A "best-of-both-worlds" solution would be for the warehouse to publish the data once processed for the operational system to consume. The data could be extracted to a file or replicated to another/the-operational-system's database. This assumes that your warehouse is not "real-time".

I must admit that I get the heeby-jeebies whenever someone suggests connecting an operational system to our warehouse. Within our environment, we made the architectural decision that we would not control how users could consume the data, provided it does not unfairly impact our ETL processes or other users. An operational system becomes another "user query", and as such we provide the same level of service wrt to availability and accuracy as we do to Joe Bloggs, the junior analyst in Finance.
If a user requires a higher level of service, then we provide the data (via FTP'd files) rather than the user pulling the data (via queries/direct access). This assists in impact analysis for future changes because the extracts are visible within our ETL tool/suite.

3
  • In terms of your comments regarding duplication, timeliness etc, it is actually proposed that we use the staging area of our data warehouse as a data source. This means that it would be non-transformed data. Would this be OK?
    – muzzamo
    Mar 20, 2014 at 6:33
  • Without knowing the specifics of your situation, I suggest you ask the business if the benefit is really worth it. There is a big difference between a system hitting your server once a day to pull a reference table that hasn't changed in over a year vs hitting your server every time a customer clicks checkout on your site to calculate the cart price in the customer's local currency and shipping costs. Mar 20, 2014 at 10:31
  • If they are after the untransformed source data, why can't they get it from the source? Mar 20, 2014 at 10:33
1

Nothing should ever access the Staging Database, because that is where partially-cleansed data is stored and no systems should ever access partially cleansed data (except, of course, those processes that finish the cleansing and load the data into the Data Warehouse Database).

Now, once the data has been fully cleansed then allowing Operational Source databases to access it can be considered, based on requirements like timeliness of data, speed of access, write-back, and so on. Personally, I think the Kimball one-way flow ( Operational Source → Data Warehouse Star Schema → Data Warehouse Cube → Presentation) is a good idea so it would take a very compelling argument for me to implement Data Warehouse → Operational Source.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.