Avoid Cross Database Views in Data Warehouse

We have a relational database (rd) and a data warehouse (dw). This dw has a table (tw) with all key fields (dimension keys) and metric related (measures) fields. This table is populated with monthly data each month. The tw is joined to various look up views present in the dw to obtain name fields from rd. The DBA wants me to remove the look up views. I now have following 2 options that I can think of –

1) Further de-normalize the tw and store the name fields as well. However, there are two issues with this option –

a. The size of tw will grow tremendously.
b. We are storing monthly data and the values in the name fields may change after some time. Then we will have to put in additional views/objects to obtain the latest name.

2) Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.

As of now these are the possible options I can come up with. Which one would you suggest and why? If you have another option, please let me know.

Thank you all in advance,

sajmeraWe have a relational database (rd) and a data warehouse (dw).
No you don't. You have a datamart. If you had a real data warehouse schema you would not have these issues.

The DBA wants me to remove the look up views.
Why?

2) Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.
A data warehouse (or datamart) should be self-contained, and not rely upon pulling dynamic data from other systems. Maintenance and support are necessary requirements of a data stores.

Availability during ETL processing

Hi,

I am in the midst of designing a new Data Warehouse system. As we get further into the design of the system, the more we are realising how complex our ETL is going to be and that the amount of time it will take to run could be significant i.e. a few days! My question is obviously I don't want to have a down time in my relational system for this long and prevent my users from accessing the data for days at a time each month. So what functionality should I be looking at to allow me to maintain a working copy of the data that users can query whilst I perform database updates and then perform a quick promotion of the updated data to users for querying?

If you can point me in the direction of the right functionality in SQL Server 2005 and possible some relevant white papers that cover this sort of scenario I would be grateful.

regards

Colin

In your ETL process, are you building a completely new DW database, or are you updating the existing one?

This seems like a very good application for some form of snapshots.

If you're updating an existing database, then database snapshots should fit the bill. They will present a static view of the data as it existed at the point in time when the snapshot was created. This allows the source database to be updated without disturbing the users.

If you create and populate a new database in each ETL cycle, then you may want to look into something like volume snapshots using the VSS framework. In this case, you are making a snapshot of the entire volume(s) on which the database resides. This can then be mounted as a read-only database for your users to query. The advantage in this scenario is that it doesn't need to be tied to the previous database.