Menu
Home
People
Places
Arts
History
Plants & Animals
Science
Life & Culture
Technology
Reference.org
Microsoft Analysis Services
open-in-new
<h2 id="history">History</h2> <p>In 1996, Microsoft began its foray into the OLAP Server business by acquiring the OLAP software technology from Canada-based <a href="/facts/Panorama_Software_(BI)/tmak7Iwd">Panorama Software</a>.<a class="footnote-ref" id="fnref:2" href="#fn:2"><sup>2</sup></a> Just over two years later, in 1998, Microsoft released OLAP Services as part of SQL Server 7. OLAP Services supported <a href="/facts/MOLAP/VNNbIlD0">MOLAP</a>, <a href="/facts/ROLAP/VNNbIlD0">ROLAP</a>, and <a href="/facts/HOLAP/VNNbIlD0">HOLAP</a> architectures, and it used <a href="/facts/OLE_DB_for_OLAP/vA38TPeu">OLE DB for OLAP</a> as the client access API and <a href="/facts/Multidimensional_Expressions/7ppno3el">MDX</a> as a query language. It could work in client-server mode or offline mode with local cube files.<a class="footnote-ref" id="fnref:3" href="#fn:3"><sup>3</sup></a> </p><p>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 <a href="/facts/Backward_compatibility/FbHFBu5y">backward compatible</a> 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 <a href="/facts/Unary_operation/kb6CkgsX">unary</a> operators, custom rollups, and cell calculations. Other features were dimension security, distinct count, connectivity over HTTP, session cubes, grouping levels, and many others.<a class="footnote-ref" id="fnref:4" href="#fn:4"><sup>4</sup></a> </p><p>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 <a href="/facts/OLE_DB_for_OLAP/vA38TPeu">OLE DB for OLAP</a> and <a href="/facts/Multidimensional_Expressions/7ppno3el">MDX</a> 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.<a class="footnote-ref" id="fnref:5" href="#fn:5"><sup>5</sup></a> </p> <h3>Timeline</h3> <p>The key events in the history of Microsoft Analysis Services cover a period starting in 1996. </p> Microsoft Analysis Services Events<table><tbody><tr><th>Date</th><th>Event</th></tr><tr><td>1996-07-01</td><td>Microsoft opens new team to build an OLAP product, codenamed Plato (<a href="/facts/Permutation/JSw9ukmv">permutation</a> of letters from OLAP)</td></tr><tr><td>1996-07-15</td><td>Panorama Software delegation meets with Microsoft</td></tr><tr><td>1996-10-27</td><td>Microsoft announces acquisition of Panorama Software development team</td></tr><tr><td>1998-11</td><td>OLAP Services 7.0 (codename Sphinx) ships</td></tr><tr><td>2000-08</td><td>Analysis Services 2000 (codename Shiloh) ships</td></tr><tr><td>2001-11</td><td>XML for Analysis Software Development Kit 1.0 ships</td></tr><tr><td>2003-04</td><td>ADOMD.NET and XML for Analysis SDK 1.1 ship</td></tr><tr><td>2005-10-28</td><td>Analysis Services 2005 (codename Yukon) ships</td></tr><tr><td>2008-08-06</td><td>Analysis Services 2008 (codename Katmai) ships</td></tr><tr><td>2012-03-06</td><td>Analysis Services 2012</td></tr><tr><td>2014-04-01</td><td>Analysis Services 2014</td></tr><tr><td>2016-06-01</td><td>Analysis Services 2016</td></tr><tr><td>2017-10-02</td><td>Analysis Services 2017 RTM</td></tr><tr><td>2019-08-21</td><td>Analysis Services 2019 RTM</td></tr><tr><td>2022-11-16</td><td>Analysis Services 2022 RTM</td></tr></tbody></table> <h2 id="multidimensional-storage-modes">Multidimensional Storage modes</h2> <p>Microsoft Analysis Services takes a neutral position in the <a href="/facts/Online_analytical_processing/VNNbIlD0">MOLAP</a> vs. <a href="/facts/ROLAP/VNNbIlD0">ROLAP</a> arguments among OLAP products. It allows all the flavors of <a href="/facts/MOLAP/VNNbIlD0">MOLAP</a>, <a href="/facts/ROLAP/VNNbIlD0">ROLAP</a> and <a href="/facts/HOLAP/VNNbIlD0">HOLAP</a> to be used within the same model. </p> <h3>Partition storage modes</h3> <ul><li>MOLAP - Multidimensional OLAP - Both fact data and aggregations are processed, stored, and indexed using a special format optimized for multidimensional data.</li> <li>ROLAP - Relational OLAP - Both fact data and aggregations remain in the relational data source, eliminating the need for special processing.</li> <li>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.</li></ul> <h3>Dimension storage modes</h3> <ul><li>MOLAP - dimension attributes and hierarchies are processed and stored in the special format</li> <li>ROLAP - dimension attributes are not processed and remain in the relational data source.</li></ul> <h3>Tabular storage mode</h3> <ul><li>In-Memory - Tabular storage mode compresses data and stores the model in memory for faster execution of queries.</li></ul> <h2 id="apis-and-object-models">APIs and object models</h2> <p>Microsoft Analysis Services supports different sets of APIs and object models for different operations and in different programming environments. </p> <h3>Querying</h3> <ul><li><a href="/facts/XML_for_Analysis/mL2WR0u1">XML for Analysis</a> - The lowest level API. It can be used from any platform and in any language that supports <a href="/facts/HTTP/TWtvf7JD">HTTP</a> and <a href="/facts/XML/clNgUfWk">XML</a></li> <li><a href="/facts/OLE_DB_for_OLAP/vA38TPeu">OLE DB for OLAP</a> - Extension of <a href="/facts/OLEDB/YjelpY3o">OLEDB</a>. <a href="/facts/Component_Object_Model/2DtIAufW">COM</a> based and suitable for <a href="/facts/C_(programming_language)/Ky2No763">C</a>/<a href="/facts/C%252B%252B/Et0F2qn9">C++</a> programs on <a href="/facts/Microsoft_Windows/bwhAhuSL">Windows</a> platform.</li> <li>ADOMD - Extension of <a href="/facts/ActiveX_Data_Objects/dU8V95sx">ADO</a>. <a href="/facts/Component_Object_Model/2DtIAufW">COM</a> Automation based and suitable for <a href="/facts/Visual_Basic/7QMXM6fP">VB</a> programs on <a href="/facts/Microsoft_Windows/bwhAhuSL">Windows</a> platform.</li> <li>ADOMD.NET - Extension of <a href="/facts/ADO.NET/j8ynkv5q">ADO.NET</a>. <a href="/facts/Microsoft_.NET/Hh5tWDIw">.NET based</a> and suitable for <a href="/facts/Managed_code/3jJfe94N">managed code</a> programs on <a href="/facts/Common_Language_Runtime/4Y0cUdkd">CLR</a> platforms.</li> <li><a href="/facts/ADO.NET_Entity_Framework/20FoR2zr">ADO.NET Entity Framework</a> - Entity Framework and <a href="/facts/LINQ/qz61xeUf">LINQ</a> can be used on top of ADOMD.NET (SSAS Entity Framework Provider<a class="footnote-ref" id="fnref:6" href="#fn:6"><sup>6</sup></a> is required)</li></ul> <h3>Administration and management</h3> <ul><li>DSO - For AS 2000. <a href="/facts/Component_Object_Model/2DtIAufW">COM</a> Automation based and suitable for <a href="/facts/Visual_Basic/7QMXM6fP">VB</a> programs on <a href="/facts/Microsoft_Windows/bwhAhuSL">Windows</a> platform.</li> <li>AMO - For AS 2005 and later versions. <a href="/facts/Microsoft_.NET/Hh5tWDIw">.NET based</a> and suitable for <a href="/facts/Managed_code/3jJfe94N">managed code</a> programs on <a href="/facts/Common_Language_Runtime/4Y0cUdkd">CLR</a> platforms.</li></ul> <h2 id="query-languages">Query languages</h2> <p>Microsoft Analysis Services supports the following <a href="/facts/Query_languages/Sa6K4xXM">query languages</a> </p> <h3><a href="/facts/Data_definition_language/Hx7aNozV">Data definition language</a> (DDL)</h3> <p>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 <a href="/facts/Predictive_Model_Markup_Language/zFD2g0WI">PMML</a>. </p> <h3><a href="/facts/Data_manipulation_language/eeJRDUEl">Data manipulation language</a> (DML)</h3> <ul><li><a href="/facts/Multidimensional_Expressions/7ppno3el">MDX</a> - for querying OLAP cubes</li> <li><a href="/facts/LINQ/qz61xeUf">LINQ</a> - for querying OLAP cubes from .NET using ADO.NET Entity Framework and Language INtegrated Query (SSAS Entity Framework Provider<a class="footnote-ref" id="fnref:7" href="#fn:7"><sup>7</sup></a> is required)</li> <li><a href="/facts/SQL/UjQLJcYm">SQL</a> - 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</li> <li><a href="/facts/Data_Mining_Extensions/U6vNR2Kk">DMX</a> - for querying Data Mining models</li> <li><a href="/facts/Data_analysis_expressions/9K5BjR9f">DAX</a> - for querying Tabular models</li></ul> <h2 id="see-also">See also</h2> <ul><li><a href="/facts/Comparison_of_OLAP_servers/lbSpe9vz">Comparison of OLAP servers</a></li></ul> <h2 id="bibliography">Bibliography</h2> <ul><li>Sivakumar Harinath, Stephen Quinn: <i>Professional SQL Server Analysis Services 2005 with MDX</i>. <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 0-7645-7918-5</li> <li>Teo Lachev: <i>Applied Microsoft Analysis Services 2005 : And Microsoft Business Intelligence Platform</i>. <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 0-9766353-0-5</li> <li>Reed Jacobson: <i>Microsoft(r) SQL Server(tm) 2000 Analysis Services Step by Step</i>. <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 0-7356-0904-7</li> <li>Claude Seidman: Data Mining with Microsoft SQL Server 2000 Technical Reference<i>.''</i> <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 0-7356-1271-4</li> <li>George Spofford: <i>MDX-Solutions</i>. Wiley, 2001, <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 0-471-40046-7</li> <li>Mosha Pasumansky, Mark Whitehorn, Rob Zare: <i>Fast Track to MDX</i>. <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 1-84628-174-1</li> <li>ZhaoHui Tang, Jamie MacLennan: <i>Data Mining with SQL Server 2005</i>. <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 0-471-46261-6</li> <li>Edward Melomed, Irina Gorbach, Alexander Berger, Py Bateman: <i>Microsoft SQL Server 2005 Analysis Services</i>. <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 0-672-32782-1</li> <li>Chris Webb, Marco Russo, Alberto Ferrari: <i>Expert Cube Development with Microsoft SQL Server 2008 Analysis Services</i>. <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 1-84719-722-1</li></ul> <h2 id="external-links">External links</h2> <ul><li><a href="https://www.microsoft.com/en-gb/sql-server/sql-business-intelligence">Microsoft Analysis Services</a></li> <li><a href="https://web.archive.org/web/20051217184219/http://www.mosha.com/msolap/">Microsoft OLAP Information</a>[<i>usurped</i>]</li> <li><a href="http://www.sqlserverdatamining.com">Microsoft Data Mining Information</a></li> <li><a href="https://web.archive.org/web/20060823065845/http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1">Analysis Services public forum archive</a>[<i>usurped</i>]</li> <li><a href="http://ssas-info.com">Microsoft Analysis Services information hub - FAQs, tools, blogs, forums</a></li></ul> <h2 id="references">References</h2> <ol> <li id="fn:1"><p>Tableau frequently refers to SSAS Cubes as MSAS Cubes Archived 2016-12-29 at the Wayback Machine <a href="/wiki/Tableau_Software" target="_blank">/wiki/Tableau_Software</a> <a href="#fnref:1" class="footnote-back-ref">↩</a></p></li> <li id="fn:2"><p>"Microsoft Announces Acquisition Of Panorama Online Analytical Processing (OLAP) Technology". Microsoft. <a href="http://www.microsoft.com/presspass/press/1996/oct96/pan2pr.mspx" target="_blank">http://www.microsoft.com/presspass/press/1996/oct96/pan2pr.mspx</a> <a href="#fnref:2" class="footnote-back-ref">↩</a></p></li> <li id="fn:3"><p>"MS SQL Server 7.0 OLAP Services". 28 January 2010. <a href="https://technet.microsoft.com/en-us/library/cc966398.aspx" target="_blank">https://technet.microsoft.com/en-us/library/cc966398.aspx</a> <a href="#fnref:3" class="footnote-back-ref">↩</a></p></li> <li id="fn:4"><p>"SQL Server 2000 – Analysis Services". <a href="http://msdn.microsoft.com/en-us/sqlserver/bb671229.aspx" target="_blank">http://msdn.microsoft.com/en-us/sqlserver/bb671229.aspx</a> <a href="#fnref:4" class="footnote-back-ref">↩</a></p></li> <li id="fn:5"><p>"SQL Server 2005 Analysis Services". Microsoft. <a href="http://www.microsoft.com/sqlserver/2005/en/us/Analysis-Services.aspx" target="_blank">http://www.microsoft.com/sqlserver/2005/en/us/Analysis-Services.aspx</a> <a href="#fnref:5" class="footnote-back-ref">↩</a></p></li> <li id="fn:6"><p>"SSAS Entity Framework Provider for LINQ to SSAS OLAP". Archived from the original on September 29, 2011. <a href="https://web.archive.org/web/20110929151255/http://www.agiledesignllc.com/Products" target="_blank">https://web.archive.org/web/20110929151255/http://www.agiledesignllc.com/Products</a> <a href="#fnref:6" class="footnote-back-ref">↩</a></p></li> <li id="fn:7"><p>"SSAS Entity Framework Provider for LINQ to SSAS OLAP". Archived from the original on September 29, 2011. <a href="https://web.archive.org/web/20110929151255/http://www.agiledesignllc.com/Products" target="_blank">https://web.archive.org/web/20110929151255/http://www.agiledesignllc.com/Products</a> <a href="#fnref:7" class="footnote-back-ref">↩</a></p></li> </ol>