SSAS

Microsoft SQL Server Analysis Services, or SSAS is an online analytic processing (OLAP) and data mining tool in Microsoft SQL Server. Companies use this tool to analyse information that can be spread between multiple databases or in separate tables or files.

Microsoft has put many business intelligence and data storage services into the SQL server package. These services include integration services, reporting services as well as analytical services. Analytical services include the OLAP group and mining-functionality data and offer two alternative views: multidimensional and tabular.

Multidimensional ways of storing

Microsoft Analysis Services takes a neutral stance regarding MOLAP (i.e. multidimensional OLAP) against ROLAP (or OLAP session) in the debate on OLAP products. It offers the MOLAP, ROLAP and HOLAP (i.e. hybrid OLAP) acces, which can be used within a single model.

Ways of storing partition

• MOLAP, i.e. multidimensional OLAP – actual data and aggregations are processed, stored and indexed using a special format that is optimised for multidimensional data.
• ROLAP, i.e. relational OLAP – actual data and aggregations remain in the session data source, which eliminates the need of further processing.
• HOLAP, i.e. hybrid OLAP – this mode allows the storage of actual data in a session data source, but it pre-processes aggregations and indexes which then stores in a special format that is optimised for multidimensional data.

Ways of storing dimensions

• MOLAP – atributes and dimensions hierarchy are processed and stored in a special format.
• ROLAP – dimension atributes aren’t processed and remain in the original session data source

When to use the multidimensional model

• You work with a large volume of data with complex requirements.
• You need to use data mining.
• You need to use functionalities, such as events, translations and others.
• You need security at the lowest level (Call level security).

When to use the tabular model

• You have a short development cycle. You want to deliver something very quickly and get instant feedback from the end users.
• The data model is relatively simple. Tables don’t necessarily be facts and dimensions.
• End-users ask for high volumes of detailed data and the performance of these enquiries is a critical factor. (Using the ROLAP multidimensional model, nearly the same can be achieved, but due to the fact, that the tabular model works in-memory, it can be used to achieve higher speed and better performance).
• If data are vast and don’t fit into the memory, the use of DirectQuery equivalent to ROLAP storage mode in traditional multidimensional models, can be considered. With DirectQuery mode, data are queried directly from the session database, each time they are accessed. On the other hand, DirectQuery also has many limitations, such as the fact, that a session database must be a SQL Server or a SQL Server PDW (Parallel Data Warehouse).

API and object models

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

Querying

• XML for analysis – the lowest-level API. It can be used on any platform and in any language supporting HTTP and XML.
• OLE DB for OLAP – the OLEDB extension. It is based on COM and is suitable for programs in C or C++ on Windows.
• ADOMD. The ADO extension based on the COM automatisation and suitable for programs in VB on Windows.
• ADOMD.NET – the ADO.NET extension is based on .NET and suitable for programs with managed code on CLR platforms.
• ADO.NET Entity Framework – Entity Framework and LINQ can be used over ADOMD.NET (it is essential to use SSAS Entity Framework Provider).

Management and administration

• DSO – for AS 2000. Based on COM automatisation and suitable for VB programs on Windows platform.
• AMO – for AS 2005 and later versions. Based on .NET and suitable for programs with managed code on the CLR platform.