I want to learn about how to create data warehouse (OLAP database) from OLTP database. I wonder whether the AdventureWorksDW2008 is really built from AdventureWorks2008. If it is, where can I find the sql script used to build AdventureWorksDW2008 from AdventureWorks2008?
- Edited by PCSQL66 Wednesday, July 08, 2009 7:10 PM Wrong title
No, the AdventureWorksDW2008 data warehouse was not created directly from the OLTP database. They have a lot a data in common, but in order to get the right sample data and other features worked out correctly we ended up not being able to do that.
You might start with a really good data warehousing book like The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition) .
Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights
There is a sample created for Integration Services that demonstrates some of the process.
AWDataWarehouseRefresh Package Sample
Demonstrates how to update the AdventureWorksDW data warehouse from the AdventureWorks OLTP database.
Here is where you can find the samples on CodePlex.
As Gail says, the Kimball book is your best overall reference. But you might find the package flow and database diagrams useful when considering your options for populating a data warehouse from an OLTP database.