Microsoft SQL Server Analysis Services

Microsoft SQL Server Analysis Services (SSAS) is one of the technologies from the Microsoft Business Intelligence which also includes Integration Services and Reporting Services. The Analysis Services is used as an online analytical and transactional processing (OLAP) tool as well as tool for data mining in Microsoft SQL Server. The tool is used to analyze huge amounts of data that are spread across numerous databases, different files, and tables and apply the data to business decisions.  The service can also be applied to creating two or multidimensional business models.

 

SQL Server Analysis Services include two models – Tabular and Multidimensional. Tabular Model is commonly used for team and personal analysis and Multidimensional Cube Model is used for corporate analysis. Multidimensional and tabular solutions are built with SQL Server Data Tools. Both solutions ensure high-performance analytical databases that can be easily integrated with Reporting Services reports, Excel, and other Microsoft applications and can be used by different client applications that support Analysis Services.

 

The key differences between multidimensional and tabular models at a high level can be described in this way:

  • Tabular solutions utilize relational modeling constructs such as relationships and tables for modeling data, and they use the in-memory analytics engine for storing data and making calculations. Most of the model is stored in RAM.
  • Data mining and multidimensional solutions utilize OLAP modeling constructs such as dimensions and cubes as well as ROLAP, MOLAP or HOLAP storage that store pre-aggregated data on disk.

 

For a new project, the tabular approach should be considered first because it is faster to design, deploy, and test and it works better with the latest Microsoft BI applications.

 

Users can apply data mining algorithms in Analysis Services to discover patterns in their data, to forecast trends, make recommendations and create rules as well as analyze the sequences of certain events in complex sets of data to gain new insights and make intelligent decisions.