Microsoft Analysis Services

Online analytical processing (OLAP) and data mining tool in Microsoft SQL Server

Microsoft SQL Server Analysis Services (SSAS) is an online analytical processing (OLAP) and data mining tool in Microsoft SQL Server. SSAS is used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Microsoft has included a number of services in SQL Server related to business intelligence and data warehousing. These services include Integration Services, Reporting Services and Analysis Services. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors multidimensional and tabular, where the difference between the two is how the data is presented. In a tabular model, the information is arranged in two dimensional tables which can thus be more readable for a human. A multidimensional model can contain information with many degrees of freedom, and must be unfolded to increase readability by a human.

In 1996, Microsoft began its foray into the OLAP Server business by acquiring the OLAP software technology from Canada based Panorama Software. Just over two years later, in 1998, Microsoft released OLAP Services as part of SQL Server 7. OLAP Services supported MOLAP, ROLAP, and HOLAP architectures, and it used OLE DB 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. It was renamed from "OLAP Services" due to the inclusion of data mining services. Analysis Services 2000 was considered an evolutionary release, since it was built on the same architecture as OLAP Services and was therefore backward compatible with it. Major improvements included more flexibility in dimension design through support of parent child dimensions, changing dimensions, and virtual dimensions. Another feature was a greatly enhanced calculation engine with support for unary operators, custom rollups, and cell calculations. Other features were dimension security, distinct count, connectivity over HTTP, session cubes, grouping levels, and many others. 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: although applications written with OLE DB for OLAP and MDX continued to work, the architecture of the product was completely different. The major change came to the model in the form of UDM Unified Dimensional Model.

The key events in the history of Microsoft Analysis Services cover a period starting in 1996. +Microsoft Analysis Services Events Date Event 1996 07 01 Microsoft opens new team to build an OLAP product, codenamed Plato (permutation of letters from OLAP) 1996 07 15 Panorama Software delegation meets with Microsoft 1996 10 27 Microsoft announces acquisition of Panorama Software development team 1998 11 OLAP Services 7.0 (codename Sphinx) ships 2000 08 Analysis Services 2000 (codename Shiloh) ships 2001 11 XML for Analysis Software Development Kit 1.0 ships 2003 04 ADOMD. NET and XML for Analysis SDK 1.1 ship 2005 10 28 Analysis Services 2005 (codename Yukon) ships 2008 08 06 Analysis Services 2008 (codename Katmai) ships 2012 03 06 Analysis Services 2012 2014 04 01 Analysis Services 2014 2016 06 01 Analysis Services 2016

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.

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.

In Memory Tabular storage mode compresses data and stores the model in memory for faster execution of queries.

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

XML for Analysis The lowest level API. It can be used from any platform and in any language that supports HTTP and XML
OLE DB for OLAP Extension of OLEDB. COM based and suitable for C/C++ programs on Windows platform. ADOMD Extension of ADO. COM Automation based and suitable for VB programs on Windows platform. ADOMD. NET Extension of ADO. NET. NET based and suitable for managed code programs on CLR platforms. ADO. NET Entity Framework Entity Framework and LINQ can be used on top of ADOMD. NET (SSAS Entity Framework Provider is required)

DSO For AS 2000. COM Automation based and suitable for VB programs on Windows platform. AMO For AS 2005 and later versions. NET based and suitable for managed code programs on CLR platforms.

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