SQL Server 2000 Tools for Data Warehouses
Microsoft® SQL Server™ 2000 provides many tools that support database applications. Some of these tools are used more often than others in data warehouse applications, and some are specifically designed to address special needs of data warehouses. The tools listed here are commonly used in data warehouse applications, although most are also applicable to other database applications. Many other tools not mentioned here can often be used to solve specific problems in data warehouse applications.
General descriptions of the tools commonly used in data warehouse applications are provided here with links to more detailed information about the tools themselves. The uses of these tools in data warehouse applications are specifically discussed in other topics in this section.
Relational Database
Data warehouses use relational database technology as the foundation for their design, construction, and maintenance. The core component of SQL Server 2000 is a powerful and full-featured relational database engine. SQL Server 2000 provides many tools for design and manipulation of relational databases, regardless of the applications for which the databases are used. Information about the many relational database management tools is provided throughout the SQL Server 2000 documentation. For more information, see SQL Server 2000 Features.
Data Transformation Services
Data warehouse applications require the transformation of data from many sources into a cohesive, consistent set of data configured appropriately for use in data warehouse operations. SQL Server 2000 provides a powerful tool for such tasks, Data Transformation Services (DTS). DTS can access data from a wide variety of sources and transform it using built-in and custom transformation specifications. For more information, see DTS Overview.
Replication
Database replication is a powerful tool with many uses. Often used to distribute data and coordinate updates of distributed data in online transaction processing systems (OLTP), replication can also be used in data warehouses. Some potential data warehouse applications of replication are the distribution of data from a central data warehouse to data marts, and the updating of data warehouse data from the data preparation area. For more information, see Replication Overview.
Analysis Services
Data warehouses collect and organize enterprise data to support organizational decision-making through analysis. SQL Server 2000 Analysis Services provides online analytical processing (OLAP) technology to organize massive amounts of data warehouse data for rapid analysis by client tools, and sophisticated data mining technology to analyze and discover information within the data warehouse data. For more information, see Analysis Services Overview.
English Query
English Query provides access to data warehouse data using English language queries such as "Show me the sales for stores in California for 1996 through 1998." English Query is a development tool for creating client applications that transform English language into the syntax of SQL to query relational databases or the syntax of Multidimensional Expressions (MDX) to query OLAP cubes. You can develop English Query models specific to your data warehouse to reduce sophisticated and complex SQL or MDX queries to simple English questions. For more information, see English Query Overview.
Meta Data Services
Many of the various tools in SQL Server 2000 store meta data in a centralized repository in the msdb system database. SQL Server 2000 Meta Data Services provides a browser for viewing this meta data and application interfaces for use in developing custom meta data applications. For more information, see Meta Data Services Overview.