SQL Server 2000 Data Warehouse and OLAP Components

SQL Server Architecture

SQL Server Architecture

SQL Server 2000 Data Warehouse and OLAP Components

Microsoft® SQL Server™ 2000 provides several components (as shown in the illustration) that allow you to transform OLTP data into OLAP data, and make the OLAP information available to decision makers.

Extensible Markup Language and OLE DB

Extensible Markup Language (XML) is a standard that defines a formatting and data representation language independent of specific data stores or applications. It is becoming an increasing important standard in the transmission of data between applications and across the Web. SQL Server 2000 is enabled to return the result sets of queries as XML documents, and also to extract the data from XML documents and store them in the relevant tables in a database.

OLE DB is a common data access specification defined by Microsoft. Many data storage products, such as spreadsheets, databases, or other server applications, supply OLE DB providers that can be used by an OLE DB application to access the data. Applications using the OLE DB API can access any data for which there is an OLE DB provider. OLE DB can present its data as XML documents. OLE DB 2.5 also includes multidimensional extensions that let OLE DB providers expose information from multidimensional cubes.

XML and OLE DB are important mechanisms for communicating data between the various SQL Server 2000 data warehousing components. The definitions of some of the conceptual models used by some components are based on XML.

Microsoft ActiveX® Data Objects (ADO) is an object API that maps over OLE DB, but is more concise and easier to code. Like OLE DB, ADO can return its data as XML documents and also supports multi-dimensional extensions. Many applications use ADO as their API for accessing OLTP data.

SQL Server 2000 Relational Database Engine

The SQL Server 2000 database engine is used primarily in the OLTP systems, and also to store the intermediate data stores used when transforming OLTP data for storage in the data warehouse or data mart, and to store and manage the data in a data warehouse or data mart.

Data Transformation Services

Data Transformation Services (DTS) is a component built to take data from one OLE DB data source, perform operations, such as aggregating the data (SUM, MIN, MAX, AVG), and storing it in a destination OLE DB data source. DTS consists of packages, which define a particular set of work that forms a logical work item. Packages contain multiple connections to data sources, tasks to be performed, and workflows connecting connections and tasks. Examples of tasks include copying data from source to destination connections, transforming data from a source connection and placing the transformed data in the destination connection, executing a set of Microsoft ActiveX scripts or Transact-SQL statements against a connection.

DTS transforms OLTP data stored in relational tables into a different organization that can be used as the foundation for multidimensional cubes. Although the data in OLTP databases is stored in entity and relationship tables, data in an OLAP data warehouse is stored in fact and dimension tables. Fact tables store the measures exposed in multidimensional cubes, and dimension tables stores information about dimension members.

DTS is a powerful tool for any system that must repeatedly access data in one format and transform it into another format. The use of DTS is not limited to building data warehouses, but the power and capabilities of the component are excellently suited to the work of transforming OLTP data into OLAP data warehouse data. For more information, see DTS Overview.

Analysis Services and Data Mining

Analysis Services is an easy-to-use, integrated, and scalable set of components that enables you to build multidimensional cubes and provide the application programs with access to the cubes. Analysis Services is very flexible in the types of storage mechanisms it supports for the cubes. The cubes can be stored in relational databases (ROLAP), as separate, high-performance multidimensional data structures (MOLAP), or hybrid combinations of both (HOLAP). Analysis Services support wizards that ease tasks such as defining dimensions and cubes. For more information, see Analysis Services Architecture.

Analysis Services exposes the data in the multidimensional cubes to applications through an OLE DB provider. The Analysis Services provider supports multi-dimensional extensions defined as part of OLE DB 2.5, and the ActiveX Data Objects (Multidimensional) (ADO MD) API. For more information, see Programming Analysis Services Applications.

Analysis Services also supports industry-standard data mining algorithms. Data mining supports new and sophisticated tools for discovering trends in data and predicting future results. For more information, see Data Mining Models.

English Query

English Query allows end users to pose English language questions about information stored in SQL Server 2000 databases, or data warehouses, and OLAP cubes. An English Query administrator defines the logical and semantic relationships between the various tables and columns in a database or cubes, dimensions, and measures in a data warehouse. An application can be coded to ask the end user to type in an English query into a character field on a form. The character string is then passed to the English Query engine. The engine analysis the question against the logical definitions of the data provided by the administrator. When querying OLAP cubes, the English Query engine returns to the application an SQL statement that extracts the requested information from the database. When querying a data warehouse or data mart, the English Query engine returns an MDX query. The application executes the SQL statement or MDX query and returns the results to the end user. For more information, see English Query Overview.

Meta Data Services

SQL Server 2000 Meta Data Services stores a model that maps the organization of data in SQL Server 2000 databases and data warehouses. This information is primarily used by third-party rapid-development tools that can either prototype applications or provide application templates based on the information in the Meta Data Services model. For more information, see Meta Data Services Overview.

See Also

Analysis Services Overview

Creating and Maintaining Databases Overview

Creating and Using Data Warehouses Overview

DTS Overview

English Query Overview

Meta Data Services Overview