I am just starting on Data Warehousing and do not understand what the best
practice is to get the data warehouse database (fact,star tables) refreshed
from the OLTP database.

As I understand it, the (best practice) sequence is this

1. Create new DW database on a new server from the OLTP server

2. Populate a Fact table and star tables from possibly complex queries to
put the data into DW best format.

3. Create cubes off the Data Warehouse database

4. Refresh the cubes daily, which will pick up the data from the Data
Warehouse.

My question is this:

What is the best practice to refresh the data warehouse database itself from
the OLTP database. I know I should use DTS, but it doesn't seem right to
make a complete refersh of the DW, especially if the OLTP is many GB in size
and not much has changed, so how are only new records and changes identified
and uploaded, other than by using transaction logs which DTS cannot see.
There must a simple approach to this, as this must be the normal scenario?

Any help/guidance on this greatly appreciated.

thanks

Neil.

There are many books on just this subject , it depends on the magnitude of
the problem you are trying to solve.

Generally you should only bring across changed records from the tables on
the source system , how you identify these changes is unqiue to each souce
system.

Try looking at some data warehousing books , Kimball and Inman good places
to start, do not over engineer though, remember these guys are looking at
enterprise solutions, but do look at their best practice stuff & the major
no no's and go from there (never use the primary key from the source as they
key in your whouse etc).

Good luck !!!

Mark Hill
------------------------------------------------
Blog: http://markiehill.blogspot.com/
------------------------------------------------
"Neil" <Neil (AT) discussions (DOT) microsoft.com> wrote