sql server data warehouse: Best method to transfer data -- sql server data warehouse

Best method to transfer data

dcomcnfg NO[at]SPAM hotmail.com
10/18/2004 9:58:32 AM

sql server data warehouse: Hi Group,

I just started at a company and am trying to come up with a solution
to streamline the datawarehouse.

The problem is, we have two databases. Database1 (548 tables) is
generated from user input and we cannot control the schema. Database2
(40 tables) is a staging DB that optimally will contain some of the
Creates and Updates from the previous day from within Database1.
Database2 is built from a conglomeration of tables in Database1,
therefore we have created 40 views which encapsulates data from
multiple tables in Database1 and are using DTS to call these views and
populate Database2 with a snapshot.

There are 2 problems with the above setup. First is, we do not need
to take an entire snapshot of the views to populate Database2, we only
need the previous days changes (the DB is growing and we cannot afford
it). Second, DTS is a pain because we are using a separate view for
every table and a separate DTS package to copy every view to
Database2. Maintenance is tough.

Currently, we are investigating the use of triggers, but I think this
will end up being a maintenance nightmare also. Is there anyway to
use replication in conjunction with views to copy *only* the previous
days changes to the other Database? Or does anyone have any other
suggestions to the best way to set this up? *Any* insight or advice
on a better setup is welcome.

Thanks much,

Re: Best method to transfer data

dcomcnfg NO[at]SPAM hotmail.com
10/21/2004 9:30:39 AM

Thanks very much Paul. Because of your suggestion, I am investigating
using this method.

I read that indexed views tax the system it runs on, so I'm looking at
using transactional replication to replicate the data to another box
which maintains the indexed views, then publish that data to the box
that needs it.

Thanks again,
Derek

[quoted text, click to view]

RE: Best method to transfer data

Richard S. Hale
10/24/2004 12:07:02 AM

Derek take a look at Trey Johnsons DTS Best Practices for Business
Intelligence white paper in msdn online it should steer you in the right
direction as far as coming up with a standard data capture methodology

[quoted text, click to view]

Re: Best method to transfer data

dcomcnfg NO[at]SPAM hotmail.com
10/26/2004 3:30:03 PM

Wow... About 95% of that article is over my head. I have a lot of
research to do. I was actually beginning to thing that SQL server was
limited in it's DataWarehousing. How wrong was I.

Thanks,
Derek

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server data warehouse group.