Software Developer Consultant, San Diego, California

Data Mart / Data Warehouse

Data Mart / Data Warehouse databases are designed for efficient reporting. Can your users locate the data they need to furnish management with good decision support? Can users generally create reports on their own, or do they always need to get a programmer or other IT support personnel involved? When users try to run reports on your data, do the reports take a long time to run? A database that is well-designed for data entry purposes (OLTP - Online Transaction Processing) may not be well suited for reporting purposes (OLAP - Online Analytical Processing). You may benefit by having a reporting database (a Data Mart or Data Warehouse) designed for your specific reporting requirements. Such a database can be automatically refreshed weekly, nightly, hourly, etc., according to the particular need.

Microsoft SQL Server comes with SQL Server Integration Services (SSIS), a tool that facilitates the gathering of data from various sources into a common database for reporting purposes. (Prior versions of SQL Server had a similar tool, DTS - Data Transformation Services). SSIS can handle any needed transformation of the data. SQL Server also manages the scheduling of these transformations to automatically refresh data at regular predetermined intervals.

SQL Server also contains tools for pre-aggregating data into "cubes" through Analysis Services. These cubes can be accessed through SQL Server Reporting Services through the Report Designer, as well as through the Ad Hoc reporting tool.