computer science

Microsoft Analysis Services

Microsoft Analysis Services is a computer science topic covered in the lgStudy science library. This page brings together a partial reference excerpt, illustrations, worked examples, real-world applications and a short study plan, so you can understand Microsoft Analysis Services rather than just read about it. In short: 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.

Key takeaways

Microsoft Analysis Services belongs to computer science; place it in that map before memorising details.
Learn the definition first, then one example that makes the definition concrete.
Connect Microsoft Analysis Services to a quantity you can measure, compute or draw — that is where exam questions come from.
Reproduce the core statement of Microsoft Analysis Services from memory before moving on to harder problems.

Reference excerpt

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.

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

Timeline The key events in the history of Microsoft Analysis Services cover a period starting in 1996.

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.

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

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 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 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 DAX - for querying Tabular models

Worked examples

Example 1 — a first encounter with Microsoft Analysis Services

Start with the simplest possible case. Write down what Microsoft Analysis Services claims or describes in one sentence, then invent the smallest concrete situation in which that sentence is true. In computer science, the smallest case is usually a single object, a single equation or a single measurement. Check that every symbol or term in your sentence has a meaning in that case.

Example 2 — changing one variable

Take the situation from Example 1 and change exactly one quantity: double it, halve it, or set it to zero. Predict what should happen to Microsoft Analysis Services before you calculate. Comparing your prediction with the result is the fastest way to find out whether you understand the idea or only the words.

Example 3 — an exam-style question

Typical questions about Microsoft Analysis Services ask you to (a) state it precisely, (b) apply it to given data, and (c) explain a limitation. Practise writing all three answers in under five minutes; the third part is what separates a full-mark answer from an average one.

Applications of Microsoft Analysis Services

In research: Microsoft Analysis Services appears in computer science research whenever the underlying quantities have to be modelled precisely. Papers usually cite it as a starting assumption and then explore where it breaks down.
In technology and industry: Engineering practice reuses Microsoft Analysis Services in design rules, simulations and safety margins. Knowing the idea lets you read a specification sheet and understand why the numbers look the way they do.
In the classroom: Microsoft Analysis Services is common in secondary-school and first-year university syllabi. It links to neighbouring topics Business intelligence software, Data analysis software, Data warehousing products, so understanding it makes those chapters shorter.
In everyday life: Look for Microsoft Analysis Services outside the textbook — in sport, cooking, traffic, electronics or the sky above you. An example you found yourself is remembered far longer than one you were given.

Affiliate

Preply — study more efficiently by working with a personal tutor. 50% off.

How to study Microsoft Analysis Services in 20 minutes

Read the reference excerpt below once, without taking notes.
Close the page and write down what Microsoft Analysis Services means in your own words.
Compare your version with the excerpt and mark what you missed.
Work through the three examples above with pen and paper.
Explain Microsoft Analysis Services out loud to somebody else — or to Teacher Smith in the lgStudy chat.

Frequently asked questions

What is Microsoft Analysis Services in simple terms?

Why does Microsoft Analysis Services matter?

Because it connects several computer science ideas at once: it gives you a definition you can apply, a quantity you can calculate, and a way to check whether a result is plausible.

How should I study Microsoft Analysis Services?

Read the excerpt, restate it from memory, then work through the examples and applications listed on this page. The five-step study plan above takes about twenty minutes.

What does this page cover?

It gives you a compact reference excerpt plus original lgStudy explanations, examples, applications and study material on Microsoft Analysis Services.