Microsoft Analysis Services
Encyclopedia
Microsoft SQL Server Analysis Services is part of Microsoft SQL Server
, a database
management system
. Microsoft
has included a number of services in SQL Server related to business intelligence
and data warehousing. These services include Integration Services and Analysis Services. Analysis Services includes a group of OLAP
and data mining
capabilities.
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.
vs. ROLAP
arguments among OLAP products.
It allows all the flavors of MOLAP
, ROLAP
and HOLAP
to be used within the same model.
, , , and .
For data mining models import and export, it also supports PMML
.
Microsoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
, a database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
management system
Database management system
A database management system is a software package with computer programs that control the creation, maintenance, and use of a database. It allows organizations to conveniently develop databases for various applications by database administrators and other specialists. A database is an integrated...
. Microsoft
Microsoft
Microsoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions...
has included a number of services in SQL Server related to business intelligence
Business intelligence
Business intelligence mainly refers to computer-based techniques used in identifying, extracting, and analyzing business data, such as sales revenue by products and/or departments, or by associated costs and incomes....
and data warehousing. These services include Integration Services and Analysis Services. Analysis Services includes a group of OLAP
OLAP
In computing, online analytical processing, or OLAP , is an approach to swiftly answer multi-dimensional analytical queries. OLAP is part of the broader category of business intelligence, which also encompasses relational reporting and data mining...
and data mining
Data mining
Data mining , a relatively young and interdisciplinary field of computer science is the process of discovering new patterns from large data sets involving methods at the intersection of artificial intelligence, machine learning, statistics and database systems...
capabilities.
History
In 1996, Microsoft began its foray into the OLAP Server business by acquiring the OLAP software technology from Israeli-based Panorama Software.Just over two years later, in 1998, Microsoft released OLAP Services as part of SQL Server 7. OLAP Services supported MOLAP
MOLAP
MOLAP stands for Multidimensional Online Analytical Processing.MOLAP is an alternative to the ROLAP technology...
, ROLAP
ROLAP
ROLAP stands for Relational Online Analytical Processing.ROLAP is an alternative to the MOLAP technology...
, and HOLAP
HOLAP
HOLAP is a combination of ROLAP and MOLAP which are other possible implementations of OLAP. HOLAP allows storing part of the data in a MOLAP store and another part of the data in a ROLAP store, allowing a tradeoff of the advantages of each...
architectures, and it used OLE DB for OLAP as the client access API and MDX
Multidimensional Expressions
Multidimensional Expressions is a query language for OLAP databases, much like SQL is a query language for relational databases. It is also a calculation language, with syntax similar to spreadsheet formulas.-Background:...
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
Backward compatibility
In the context of telecommunications and computing, a device or technology is said to be backward or downward compatible if it can work with input generated by an older device...
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
Unary
* Unary numeral system, the simplest numeral system to represent natural numbers* Unary operation, a kind of mathematical operator that has only one operand* Unary coding, an entropy encoding that represents a number n with n − 1 ones followed by a zero...
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
Multidimensional Expressions
Multidimensional Expressions is a query language for OLAP databases, much like SQL is a query language for relational databases. It is also a calculation language, with syntax similar to spreadsheet formulas.-Background:...
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.
Timeline
The key events in the history of Microsoft Analysis Services cover a period starting in 1996.| Date | Event |
|---|---|
| 1996-07-01 | Microsoft opens new team to build an OLAP product, codenamed Plato (permutation Permutation In mathematics, the notion of permutation is used with several slightly different meanings, all related to the act of permuting objects or values. Informally, a permutation of a set of objects is an arrangement of those objects into a particular order... 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 |
Storage Modes
Microsoft Analysis Services takes a neutral position in the MOLAPMOLAP
MOLAP stands for Multidimensional Online Analytical Processing.MOLAP is an alternative to the ROLAP technology...
vs. ROLAP
ROLAP
ROLAP stands for Relational Online Analytical Processing.ROLAP is an alternative to the MOLAP technology...
arguments among OLAP products.
It allows all the flavors of MOLAP
MOLAP
MOLAP stands for Multidimensional Online Analytical Processing.MOLAP is an alternative to the ROLAP technology...
, ROLAP
ROLAP
ROLAP stands for Relational Online Analytical Processing.ROLAP is an alternative to the MOLAP technology...
and HOLAP
HOLAP
HOLAP is a combination of ROLAP and MOLAP which are other possible implementations of OLAP. HOLAP allows storing part of the data in a MOLAP store and another part of the data in a ROLAP store, allowing a tradeoff of the advantages of each...
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 AnalysisXML for AnalysisXML for Analysis is an industry standard for data access in analytical systems, such as OLAP and data mining. XMLA is based on other industry standards such as XML, SOAP and HTTP...
- The lowest level API. It can be used from any platform and in any language which support HTTP and XMLXMLExtensible Markup Language is a set of rules for encoding documents in machine-readable form. It is defined in the XML 1.0 Specification produced by the W3C, and several other related specifications, all gratis open standards.... - OLE DB for OLAP - Extension of OLEDB. COMComponent Object ModelComponent Object Model is a binary-interface standard for software componentry introduced by Microsoft in 1993. It is used to enable interprocess communication and dynamic object creation in a large range of programming languages...
based and suitable for CC (programming language)C is a general-purpose computer programming language developed between 1969 and 1973 by Dennis Ritchie at the Bell Telephone Laboratories for use with the Unix operating system....
/C++C++C++ is a statically typed, free-form, multi-paradigm, compiled, general-purpose programming language. It is regarded as an intermediate-level language, as it comprises a combination of both high-level and low-level language features. It was developed by Bjarne Stroustrup starting in 1979 at Bell...
programs on WindowsMicrosoft WindowsMicrosoft Windows is a series of operating systems produced by Microsoft.Microsoft introduced an operating environment named Windows on November 20, 1985 as an add-on to MS-DOS in response to the growing interest in graphical user interfaces . Microsoft Windows came to dominate the world's personal...
platform. - ADOMD - Extension of ADOActiveX Data ObjectsMicrosoft's ActiveX Data Objects is a set of Component Object Model objects for accessing data sources. A part of MDAC, it provides a middleware layer between programming languages and OLE DB...
. COMComponent Object ModelComponent Object Model is a binary-interface standard for software componentry introduced by Microsoft in 1993. It is used to enable interprocess communication and dynamic object creation in a large range of programming languages...
Automation based and suitable for VBVisual BasicVisual Basic is the third-generation event-driven programming language and integrated development environment from Microsoft for its COM programming model...
programs on WindowsMicrosoft WindowsMicrosoft Windows is a series of operating systems produced by Microsoft.Microsoft introduced an operating environment named Windows on November 20, 1985 as an add-on to MS-DOS in response to the growing interest in graphical user interfaces . Microsoft Windows came to dominate the world's personal...
platform. - ADOMD.NET - Extension of ADO.NETADO.NETADO.NET is a set of computer software components that programmers can use to access data and data services. It is a part of the base class library that is included with the Microsoft .NET Framework. It is commonly used by programmers to access and modify data stored in relational database systems,...
. .NET based and suitable for managed codeManaged codeManaged code is a term coined by Microsoft to identify computer program code that requires and will only execute under the "management" of a Common Language Runtime virtual machine ....
programs on CLRCommon Language RuntimeThe Common Language Runtime is the virtual machine component of Microsoft's .NET framework and is responsible for managing the execution of .NET programs. In a process known as just-in-time compilation, the CLR compiles the intermediate language code known as CIL into the machine instructions...
platforms. - ADO.NET Entity FrameworkADO.NET Entity FrameworkADO.NET Entity Framework is an object-relational mapping framework for the .NET Framework.-Overview:ADO.NET Entity Framework abstracts the relational schema of the data that is stored in a database and presents its conceptual schema to the application...
- Entity Framework and LINQLINQLinq is a word-based card game from Endless Games, introduced at the American International Toy Fair in 2005.Game play requires at least four players, two of whom are dealt cards with the same word, while the others receive blanks. The goal is to gain points by correctly naming the players with...
can be used on top of ADOMD.NET (SSAS Entity Framework Provider is required)
Administration and Management
- DSO - For AS 2000. COMComponent Object ModelComponent Object Model is a binary-interface standard for software componentry introduced by Microsoft in 1993. It is used to enable interprocess communication and dynamic object creation in a large range of programming languages...
Automation based and suitable for VBVisual BasicVisual Basic is the third-generation event-driven programming language and integrated development environment from Microsoft for its COM programming model...
programs on WindowsMicrosoft WindowsMicrosoft Windows is a series of operating systems produced by Microsoft.Microsoft introduced an operating environment named Windows on November 20, 1985 as an add-on to MS-DOS in response to the growing interest in graphical user interfaces . Microsoft Windows came to dominate the world's personal...
platform. - AMO - For AS 2005. .NET based and suitable for managed codeManaged codeManaged code is a term coined by Microsoft to identify computer program code that requires and will only execute under the "management" of a Common Language Runtime virtual machine ....
programs on CLRCommon Language RuntimeThe Common Language Runtime is the virtual machine component of Microsoft's .NET framework and is responsible for managing the execution of .NET programs. In a process known as just-in-time compilation, the CLR compiles the intermediate language code known as CIL into the machine instructions...
platforms.
Data Definition Language (DDL)
DDL in Analysis Services is XML based and supports commands such asFor data mining models import and export, it also supports PMML
Predictive Model Markup Language
The Predictive Model Markup Language is an XML-based markup language developed by the Data Mining Group to provide a way for applications to define models related to predictive analytics and data mining and to share those models between PMML-compliant applications.PMML provides applications a...
.
Data Manipulation Language (DML)
- MDXMultidimensional ExpressionsMultidimensional Expressions is a query language for OLAP databases, much like SQL is a query language for relational databases. It is also a calculation language, with syntax similar to spreadsheet formulas.-Background:...
- for querying OLAP cubes - LINQLINQLinq is a word-based card game from Endless Games, introduced at the American International Toy Fair in 2005.Game play requires at least four players, two of whom are dealt cards with the same word, while the others receive blanks. The goal is to gain points by correctly naming the players with...
- for querying OLAP cubes from .NET using ADO.NET Entity Framework and Language INtegrated Query (SSAS Entity Framework Provider is required) - SQLSQLSQL is a programming language designed for managing data in relational database management systems ....
- small subset of SQL for querying OLAP cubes and dimensions as if they were tables - DMXData Mining ExtensionsData Mining Extensions is a query language for Data Mining Models supported by Microsoft's SQL Server Analysis Services product.Like SQL, it supports a data definition language, data manipulation language and a data query language, all three with SQL-like syntax.Whereas SQL statements operate on...
- for querying Data Mining models