Microsoft now call OLAP – Analysis services. Another term for this feature is data warehousing. Recent research show that interest in OLAP or Analysis Service is the fastest growing area of SQL 2000. To see this feature you need to re-run Install and select options, Install Analysis Services.
The ‘regular’ part of SQL is OLTP, the Online Transaction Processing arm of SQL 2000 server. OLTP deals with transactions like adding sales or entering new products into your database.
Planning
In addition to creating database tables, views and indexing, analysis services benefits from DTS packages to extract data into the analysis databases. With DTS you can filter or clean the data to make it easier to separate key indicators from ‘noise’. It is important to separate the analysis database form the production database so no live data is compromised by a rogue query.
Compatibility
From time to time I comment on Microsoft’s weaknesses, however, backwards compatibility is one of their strengths; and SQL 2000’s compatibility with SQL 7 is no exception. From my spreadsheet days I have always been fond of Pivot table to analyse data, and once again there is complete match with SQL 7 so that clients will not notice any difference when you upgrade.
New Features of Analysis Services
DSO – Decision Support Objects. Useful for third party tools or Visual Basic scripts to mine your information and package it for the users.
Cube enhancements include real time updates
Clustering to unearth non intuitive relationships using ‘near neighbour’ algorithms.
MDX – Multidimensional extension to select and analyse a subset of data, example:
SELECT
{ [Measures].[Unit Sales], [Measures].[Store Sales] } ON COLUMNS,
{ [Time].[2002], [Time].[2003] } ON ROWS
FROM Sales
WHERE ( [Store].[USA].[NY] )
