Data warehousing 101

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Archive: Integration Services How to convert DTS packages to SSIS and overwrite data How to migrate DTS to SSIS and deploy SSIS packages Query to search text in old DTS packages in SQL Server? Error importing Excel data to SSIS in SQL Server 2005 Delete .bak files automatically with CLR DTS package designed to overwrite extract.xls Displaying a SQL Server database report in Excel Stored procedure practices in SQL Server Call triggers in a stored procedure SQL Server permission to create jobs and schedules in SSIS SQL Server Business Intelligence (BI) and Data Warehousing Programming report generation with SQL Server Reporting Services 2008 Using the Pivot transformation in SQL Server Integration Services DBA career paths could lead to business intelligence Are data warehouses made for the cloud? Q&A: Business intelligence gets a facelift in SQL Server 2008 R2 Project Gemini gets a new name, Madison earns buzz Speed up reports in SQL Server Reporting Services with caching Data Transformation Services vs. SSIS: The key differences Using package configurations in SQL Server Integration Services (SSIS) How SQL Server 2008 components impact SharePoint implementations

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary data aggregation  (SearchSQLServer.com) data preprocessing  (SearchSQLServer.com) data warehouse  (SearchSQLServer.com) FileMaker  (SearchSQLServer.com) GIS  (SearchSQLServer.com) MOLAP  (SearchSQLServer.com) pivot table  (SearchSQLServer.com) Quiz: SQL Server 2000  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

Transactional databases help people carry out activities while a data warehouse helps them make decisions. A transactional database might show seats available on an airline flight so that a travel agent can book a new reservation. A data warehouse might show the historical pattern of empty seats by flight so an airline manager can decide whether to adjust flight schedules in the future. Databases that hold this respective data are as different as apples and oranges.

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.