Microsoft® SQL Server™ version 7.0 introduces several components that improve the ability to build data warehouses that effectively support decision support processing needs.
The Data Warehousing Framework is a set of components and APIs that implement the data warehousing features of SQL Server. It provides a common interface to be used by various components seeking to build and use a data warehouse or data mart.
DTS provides a set of services that aids in building a data warehouse or data mart. Decision support systems analyze data to find trends of interest to the database users. While online transaction processing (OLTP) databases store large numbers of records covering the details of each transaction, online analytical processing (OLAP) systems want to aggregate and summarize the information to speed analysis of the trends exhibited in the data.
DTS offers support for extracting data from heterogeneous OLE DB data sources and the summarizing or aggregating the data to build a data warehouse.
SQL Server 7.0 includes Microsoft Repository, which consists of a set of Microsoft ActiveX® interfaces and information models that define database schema and data transformations as defined by the Microsoft Data Warehousing Framework. A goal of the Microsoft Data Warehousing Framework is to provide meaningful integration of multiple products through shared metadata. It combines business and technical metadata to provide an industry standard method for storing the schema of production data sources and destinations.
Repository is the preferred means of storing DTS packages in a data warehousing scenario because it is the only method of providing data lineage for packages. DTS also uses Repository storage to allow transformations, queries, and ActiveX scripts to be reused by heterogeneous applications.
Microsoft SQL Server OLAP Services provides OLAP processing capabilities against heterogeneous OLE DB data sources. It has efficient algorithms for defining and building multidimensional cubes that can be referenced by applications using the OLE DB 2.0 OLAP extensions or the Microsoft ActiveX Data Objects Multidimensional extensions (ADO MD). OLAP Services is an excellent tool for multidimensional analysis of data in SQL Server databases.
English Query makes a definition of the entities and relationships defined in a SQL Server database. Given this definition, an application can use an OLE Automation API to pass English Query a string containing a natural-language question about the data in the database. English Query returns an SQL statement the application can use to extract the data needed to answer the question.
Data Warehousing and Online Analytical Processing