The pros of a data warehouse include the fact that the database is tuned specifically for the retrieval of historical data, slicing and dicing based on date ranges, part numbers or geographical regions, etc. It's tuned specifically for these queries so they better run fast! It's possible that your transactional database doesn't even have the historical data required to generate the reports you require.
The cons may be the potential size. These databases can be extremely large, which may require a significant hardware investment. Also, if your current expertise is more geared towards transactional systems, you'll either have to invest in data warehouse training or bring in external resources.
I'd recommend that you look into books by Ralph Kimball. He is the modern day guru and has some great offerings such as The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition), The Data Warehouse Lifecycle Toolkit : Expert Methods for Designing, Developing and Deploying Data Warehouses, and The Microsoft Data Warehouse Toolkit : With SQL Server 2005 and the Microsoft Business Intelligence Toolset.
Dig deeper on SQL Server Data Warehousing
Migrate DTS packages to SQL Server Integration Services with Migration Wizard and deploy SSIS packages in SQL 2005. Learn how to access DTS Designer ...continue reading
Convert SQL Server 2000 DTS packages to SQL Server 2005 with this advice and also see how to overwrite data.continue reading
Is there a query to search text in a SQL Server DTS package? SQL Server expert Joe Toscano explains which database objects can be viewed.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.