MSAS : The Data warehousing framework of SQL Server 2000 - Part 2

The Online Analytical Processing (OLAP) tools offered by Microsoft are impressive, considering the fact that Microsoft entered the OLAP market only in 1998. A review of the growth of Microsoft tools in this area, clearly indicates that they have grown from being a mere entrant to being a market leader in the field. What started as OLAP services in SQL Server 7.0 has been enhanced and renamed as Analysis Services in SQL Server 2000. We will be learning more about Analysis services a little later in this tutorial. For the present we will examine the broad features of the OLAP services offered by Microsoft.

The OLAP functionality provided by Microsoft with SQL Server 2000, helps build and manage powerful, multidimensional models of data and applications for use in large enterprise systems. It provides processing capabilities against heterogeneous OLE DB data sources. The efficient algorithms for defining and building multidimensional cubes can be referenced by OLE DB OLAP extensions. These multidimensional structures and cubes can be configured and implemented through the use of a variety of storage options called Multidimensional OLAP, Relational OLAP and Hybrid OLAP. It includes predefined data access functionalities and application interfaces for these functionalities. Quantitative analysis functions provide strength to the Analysis services. These functions make statistical processing capabilities and data mining capabilities a reality. It supports user defined functions and amply provides documentation to assist the user in building such capabilities. Custom rollups and actions are two features that distinguish the Microsoft OLAP tools. Actions like triggers extend analyses to incorporate custom functions and they are also useful in closing the loop between analytic applications and operational systems. Custom rollups enable the calculation of values from individual child dimensions for populating the values in the parent dimension. These custom rollups also enable the implementation of domain specific analysis for businesses.

Analysis services provide four OLAP and data mining application interfaces. The MDX ( Multidimensional Expressions) is an interface to Analysis services multi dimensional data. This is similar to the SQL interface with relational data. MDX provides data definition syntax and data manipulation syntax and over hundred MDX functions with which to work. MDX data manipulation functions can be used within the Decision support objects(DSO), PivotTable Service and XML/A programming models. Decision Support Objects (DSO) defines the COM based object model and provides an interface to the internal structure Analysis services OLAP and data mining functionality and the data structures, models etc. PivotTable Service is a client based OLE DB provider that applications can access, manipulate and retrieve relational and multidimensional data, create local multidimensional cubes on the client, perform OLAP functions on those cubes and display the results of the processing. XML/A is a Simple Object Access Protocol(SOAP) based XML API designed for accessing SQL Server Analysis services data and Web Client applications. Key, standard web service protocols are used to create OLAP interfaces that are language independent and require no pre-installed components on the client machine.

A data mining model is a virtual structure that represents the grouping and predictive analysis of relational or multidimensional data. Though the structure of the model resembles the structure of a database table, the record set in the data mining model represents the interpretation of records as rules and patterns, composed in statistical patterns called cases. The case set defines the data mining model and the data stored therein represents the rules and patterns learned from processing the case set. A case set is a way of viewing the physical data and different case sets can be constructed from the same physical data. Since the information is innately hierarchical, the case set is stored as a collection of data mining columns. Each column will then contain a group of data mining columns instead of a single data item and are stored in the Decision Support objects Library.

The most important task of data mining is to determine the impact of the attributes of the items on classification and prediction of trends and patterns. The relative importance of each of these attributes is determined by a process of mining known as model training. Data is supplied to the model for analysis and the algorithms used examine the ‘data set’ in a multitude of ways and draws conclusions about classification and prediction of data. The algorithms used in the mining model are stored in the Mining model object in the Decision support Objects Library