Fig 3-2. SQL Server 2000 Data Warehousing
Microsoft SQL Server 2000 provides several components (as shown in Fig 3-2 on the previous page) that allow the transformation of OLTP data into OLAP data, and make the OLAP information available to decision makers.
3.3.1 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.
3.3.2 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 between 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.
3.3.3 Analysis Services and Data Mining
Analysis Services is an easy-to-use, integrated, and scalable set of components that enables users 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.
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. 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.
3.3.4 Features of Analysis Services
. Ease of use - Many wizards, editors, and help materials are available to help the user at every step of the way. The Analysis Manager user interface gives access to metadata views and cube data and also provides wizards to create and edit cubes, dimensions, and levels.
. Flexibility - Several storage modes are available through the Microsoft OLAP Server. These include storing cube data into multidimensional cube files (MOLAP), relational databases (ROLAP), or a hybrid of the two (HOLAP). Cubes can also be partitioned, with each partition stored in a different storage mode (this is available only in Microsoft SQL Server 2000 Enterprise Edition).
. Scalability - Microsoft Analysis services support both the Intel-based servers and existing DEC Alpha servers (future DEC Alpha servers will not support Windows NT). The OLAP client can run on Windows 9x, Windows NT, and Windows 2000. Analysis Services has also been designed to address a variety of data warehousing scenarios, such as customized aggregation options, usage based optimization, data compression, and distributed calculations.
. Integration - Analysis Services are integrated with the Microsoft Management Console (MMC). MMC is a graphical tool that allows you to access several Microsoft (and non-Microsoft) services in the Windows NT environment. Analysis Manager can be added as a snap-in to the MMC. Security in Analysis Services is integrated with SQL Server and Windows NT security. Finally, many third-party vendors are developing integration adaptors between their products and Microsoft Analysis Services.
. Widely supported APIs and functions - The OLAP Server and Microsoft PivotTable service support OLE DB, ADO, user-defined function, and decision support objects (DSO).
3.3.5 Metadata and the Microsoft Repository
Meta data is, by definition, data about data. In other words, the information about the way storage is structured in the data warehouse, OLAP, data mining, and DTS services are all kept as meta data, which is stored in the Microsoft Repository. The Repository is built to maintain such technical information about the data sources involved with the services mentioned above. Access to the repository is possible through the interfaces exposed by the Analysis Manager\'s graphical user interface (GUI).
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 mode.
3.3.6 Decision Support Systems (DSS)
Microsoft Decision Support Systems (DSS) consist of the DSS Analysis server, also referred to as the OLAP server, and the PivotTable Service.
The Analysis server is the heart of Microsoft Analysis Services, extracting data from heterogeneous sources across the organization for analysis and querying, as well as storing aggregated and processed data in the multi-dimensional database for quick access when needed. It can also utilize data stored in summary tables in the OLTP database as well. It is also responsible for creating and using the mining models to extract trends and predictions from OLAP data and to integrate decision support objects.
PivotTable Service is the main interface for applications that use Analysis Services. It is an OLE DB provider for data-mining operations and multi-dimensional data access. PivotTable Service allows client applications to browse, analyze, and update data from cubes managed by an Analysis Server, and even save cubes on the client machines for offline analysis. PivotTable Service can be used from a number of development platforms, including Microsoft Visual Basic or Visual C++, using either the ActiveX Data Objects Multidimensional (ADO MD) object library or the OLE DB for OLAP Component Object Model (COM) interface. PivotTable Service allows the user to create cross-tab tables on the fly by specifying the columns and rows on which aggregations should be made. Analysis Server can work with several tools on the OLAP client side, such as English Query, and Microsoft Office (especially, MS Excel and MS Access) allowing these tools to access its cube data through the pivot table service. This gives the user a great advantage, because they are most likely familiar with these tools, and can use them efficiently for the data analysis.
3.3.7 Analysis Manager
Analysis Manager is a graphical user interface (GUI) that allows the user to build an OLAP solution based on existing data sources. Analysis Manager is opened as a snap-in into the Microsoft Management Console (MMC).
Analysis Manager allows the user to easily manage OLAP databases. It provides wizards that can be used to add new data cubes, and to change the structure of existing cubes by adding or changing dimensions and their levels of complexity. It also can be used to manage the security of the cubes in order to guarantee only authorized access to their data.
Finally, Analysis Manager allows access to the data mining features and to the meta data of the cubes and their components.
3.3.8 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, which analyses 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.
