is a group of
OLAP and
Data Mining services provided in
Microsoft SQL Server .
Microsoft's foray into OLAP Server business began in 1996 with acquisition of OLAP technology from Israeli company Panorama. In 1998 Microsoft released the first version, named OLAP Services, part of SQL Server 7. OLAP Services supported
MOLAP ,
ROLAP ,
HOLAP and Hybrid OLAP architectures, used OLEDB for OLAP as the client access API and
MDX as a query language. It could work in the client server mode or in offline mode with local cube files. In 2000 Microsoft released the next version, named Analysis Services 2000. The rename from OLAP Services happened because of the inclusion of Data Mining services, so the product wasn't just about OLAP anymore. Analysis Services 2000 was an evolutionary release, it was built on the same architecture as OLAP Services and was backward compatible with it. The major improvements included more flexibility in dimension design, by supporting parent child dimensions, changing dimensions, virtual dimensions etc. Another major area of improvements was much enhanced calculation engine with support for unary operators, custom rollups and cell calculations. Other new features were dimension security, distinct count, connectivity over HTTP, session cubes, grouping levels etc. In 2005 Microsoft released the next generation of OLAP and Data Mining technology as Analysis Services 2005. Unlike previous releases, Analysis Services 2005 was a revolutionary release. It maintained backward compatibility on the API level, i.e. applications written with OLEDB for OLAP and
MDX continue to work, but the architecture of the product was completely different. The major change came to the model in the form of UDM -
Unified Dimensional Model .
- July 1 , 1996 - Microsoft opens new team to build an OLAP product, codenamed Plato (permutation of letters from OLAP)
- July 15 , 1996 - Panorama Software delegation meets with Microsoft
- October 27 , 1996 - Microsoft announces acquisition of Panorama Software development team
- November 1998 - OLAP Services 7.0 (codename Sphinx) ships
- August 2000 - Analysis Services 2000 (codename Shiloh) ships
- November 2001 - XML for Analysis SDK 1.0 ships
- April 2003 - ADOMD.NET and XML for Analysis SDK 1.1 ship
- October 28 , 2005 - Analysis Services 2005 (codename Yukon) ships
Microsoft Analysis Services takes a neutral position in the
MOLAP vs.
ROLAP arguments among OLAP products.
It allows all the flavors of
MOLAP ,
ROLAP and
HOLAP to be used within same model.
- MOLAP - both fact data and aggregations for the partition have to be processed and stored and indexed using special optimized format
- ROLAP - both fact data and aggregations remain in the relational data source and therefore don't need to be processed. Often, ROLAP aggregations point to the materialized views
- HOLAP - fact data remains in the relational data source, but aggregations are processed and stored and indexed using special optimized format
- Hybrid OLAP - some partitions can have MOLAP storage mode, others can be ROLAP, yet others can be HOLAP.
- MOLAP - dimension attributes and hierarchies are processed and stored in the special format
- ROLAP - dimension attributes are not processed and remain in the relational data source. Partitions dimensioned by ROLAP dimensions must be in the ROLAP mode as well.
Microsoft Analysis Services supports different sets of APIs and object models for different operations and in different programming environments
Microsoft Analysis Services supports the following query languages
DDL in Analysis Services is XML based and supports commands such as
, , , etc.
For Data Mining models import and export, it also support PMML .
- MDX - for querying OLAP cubes
- SQL - small subset of SQL for querying OLAP cubes and dimensions as if they were tables
- DMX - for querying Data Mining models
- Teo Lachev: ''Applied Microsoft Analysis Services 2005 : And Microsoft Business Intelligence Platform''. ISBN 0976635305
- Reed Jacobson: ''Microsoft(r) SQL Server(tm) 2000 Analysis Services Step by Step''. ISBN 0735609047
- George Spofford: ''MDX-Solutions''. Wiley, 2001, ISBN 0471400467
- Mosha Pasumansky, Mark Whitehorn, Rob Zare: ''Fast Track to MDX''. ISBN 1846281741
- ZhaoHui Tang, Jamie MacLennan: ''Data Mining with SQL Server 2005''. ISBN 0471462616
