Microsoft Analysis Services

From Wikipedia, the free encyclopedia

Jump to: navigation, search
Microsoft Analysis Services
Developed by Microsoft
Latest release Analysis Services 2008 / 2008-08-06; 269 days ago
Operating system Microsoft Windows
Type OLAP, Data Mining
License Microsoft EULA

Microsoft Analysis Services is a group of OLAP and Data Mining services provided in Microsoft SQL Server.


[edit] History

Microsoft's foray into the OLAP Server business began with their 1996 acquisition of the OLAP software technology from Israeli-based Panorama Software. Two years later, Microsoft released OLAP Services as part of SQL Server 7. OLAP Services supported MOLAP, ROLAP and HOLAP architectures, and used OLEDB for OLAP as the client access API and MDX as a query language. It could work in client-server mode or offline mode with local cube files. In 2000, Microsoft released 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 as 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 through support of parent child dimensions, changing dimensions, virtual dimensions etc. Another major area of improvements was a 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. It maintained backward compatibility on the API level, i.e. applications written with OLE DB 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.

[edit] Timeline

  • 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
  • August 6, 2008 - Analysis Services 2008 (codename Katmai) ships

[edit] Storage Modes

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 the same model.

[edit] Partition Storage Modes

  • MOLAP - Multidimensional OLAP - Both fact data and aggregations are processed, stored, and indexed using a special format optimized for multidimensional data.
  • ROLAP - Relational OLAP - Both fact data and aggregations remain in the relational data source, eliminating the need for special processing.
  • HOLAP - Hybrid OLAP - This mode uses the relational data source to store the fact data, but pre-processes aggregations and indexes, storing these in a special format, optimized for multidimensional data.

[edit] Dimension Storage Modes

  • 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.

[edit] APIs and Object Models

Microsoft Analysis Services supports different sets of APIs and object models for different operations and in different programming environments

[edit] Querying

[edit] Administration and Management

[edit] Query Languages

Microsoft Analysis Services supports the following query languages

[edit] Data Definition Language (DDL)

DDL in Analysis Services is XML based and supports commands such as <Create>, <Alter>, <Delete>, <Process> etc. For Data Mining models import and export, it also supports PMML.

[edit] Data Manipulation Language (DML)

  • 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

[edit] Further reading

  • Sivakumar Harinath, Stephen Quinn: Professional SQL Server Analysis Services 2005 with MDX. ISBN 0-7645-7918-5
  • Teo Lachev: Applied Microsoft Analysis Services 2005 : And Microsoft Business Intelligence Platform. ISBN 0-9766353-0-5
  • Reed Jacobson: Microsoft(r) SQL Server(tm) 2000 Analysis Services Step by Step. ISBN 0-7356-0904-7
  • Claude Seidman: Data Mining with Microsoft SQL Server 2000 Technical Reference.'' ISBN 0-7356-1271-4
  • George Spofford: MDX-Solutions. Wiley, 2001, ISBN 0-471-40046-7
  • Mosha Pasumansky, Mark Whitehorn, Rob Zare: Fast Track to MDX. ISBN 1-84628-174-1
  • ZhaoHui Tang, Jamie MacLennan: Data Mining with SQL Server 2005. ISBN 0-471-46261-6
  • Edward Melomed, Irina Gorbach, Alexander Berger, Py Bateman: Microsoft SQL Server 2005 Analysis Services. ISBN 0-672-32782-1

[edit] External links

Personal tools