Microsoft Analysis Services

Microsoft SQL Server Analysis Services (SSAS)

Developer(s)	Microsoft
Stable release	Analysis Services 2016 / December 21, 2010 (2010-12-21)
Operating system	Microsoft Windows
Type	OLAP, Data Mining
License	Microsoft EULA
Website	http://technet.microsoft.com/en-us/sqlserver/cc510300.aspx

Microsoft SQL Server Analysis Services, SSAS (and sometimes MSAS),^[1] 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.

History

In 1996, Microsoft began its foray into the OLAP Server business by acquiring the OLAP software technology from Canada-based Panorama Software.^[2] 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.^[3]

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.^[4]

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.^[5]

Timeline

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

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

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.

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.

APIs and object models

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

Querying

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^[6] is required)

Administration and management

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.

Query languages

Microsoft Analysis Services supports the following query languages

Data definition language (DDL)

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.

Data manipulation language (DML)

MDX - for querying OLAP cubes
LINQ - for querying OLAP cubes from .NET using ADO.NET Entity Framework and Language INtegrated Query (SSAS Entity Framework Provider^[7] is required)
SQL - small subset of SQL (in form of management views also called as DMV's) for querying OLAP cubes and dimensions as if they were tables
DMX - for querying Data Mining models

References

Bibliography

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
Chris Webb, Marco Russo, Alberto Ferrary: Expert Cube Development with Microsoft SQL Server 2008 Analysis Services. ISBN 1-84719-722-1

External links

Microsoft development tools

Development
environments

Visual Studio	Code Express Lab Management Team System Profiler Test Professional Tools for Applications Tools for Office

Others	.NET Compiler Platform App Studio Blend Expression Web GW-BASIC MSBuild Robotics Developer Studio SharePoint Designer FrontPage PowerShell ISE QuickBASIC QBasic Small Basic WebMatrix Windows SDK CLR Profiler ILAsm Native Image Generator XAMLPad

Languages

Major APIs and
frameworks
(See all)

Windows API Silverlight XNA DirectX Managed DirectX Xbox Development Kit Windows Installer

.NET Framework	ASP.NET ASP.NET AJAX ASP.NET Dynamic Data ASP.NET MVC ASP.NET Razor ADO.NET Entity Framework CardSpace Communication Foundation Identity Foundation LINQ Presentation Foundation Workflow Foundation

Windows Driver Frameworks	Windows Driver Model KMDF UMDF Windows Driver Kit Windows Hardware Certification Kit

Database

SQL Server	Express Compact Management Studio Business Intelligence Development Studio MSDE

SQL services	Analysis Reporting Integration Notification

Other	Visual FoxPro Microsoft Access Jet Engine Extensible Storage Engine Azure DocumentDB

Source control

Delivery

This article is issued from Wikipedia - version of the Friday, January 08, 2016. The text is available under the Creative Commons Attribution/Share Alike but additional terms may apply for the media files.