We have database which stores our enterprise data and we need to keep track of the history of the information relevant to time. How do we map all the OLTP schema to OLAP to answer any possible query about historical information in the data warehouse?


Chuck Kelley's Answer: That is what an enterprise data warehouse is - the historical view of the data. So you're concept is correct. Can you do it? Sure. Many have done it before you. Will it be easy? Maybe, maybe not. However, a bit of caution: make sure that you want to use the data as a reporting environment, not an audit trail or archive. Audit trail and archives are quite different from the enterprise data warehouse (even though there are some who want to do it!). Maybe you should contract with someone to teach or help you with the modeling process.

Joe Oates' Answer: Using the DW to store historical information is, indeed, the right concept. You probably don't want to bring everything in the transaction system into the data warehouse. What you should do is to determine what things are important to management for analysis.

Chapter 2 in The Data Warehouse Toolkit 2nd Edition, by Ralph Kimball, et al, goes into considerable detail about setting up a data mart for POS transactions. Of course a book that explains the concepts of several industries cannot go into all of the detail that you must handle in a production data mart. For example, in working with DW projects for retail organizations, management also wanted to capture returns, the reason for the return and, if the return was for a defect, the vendor of the product returned. I have seen retail organizations switch suppliers if the defect rate for a particular product was too high.

Also, you will probably need to handle "bundles" where product X and product Y are sold together as a single item for less than purchasing each separately would be.

There will be several other areas that may not be covered by the reference above, but I think that it should get you started on the right track.

Les Barbusinski's Answer: No. You need to first build a historical data store (HDS) within your DW to store information in a normalized structure. You can then support any number of data marts - for any business purpose or organizational perspective - from this HDS. In other words, map your OLTP source systems to the HDS, then map your HDS to your data marts as needed. Please read the detailed dissertation I did on this topic at the following link: http://www.dmreview.com/article_sub.cfm?articleID=1006693

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access