SSAS – SQL Server Analysis Services

Microsoft SQL Server Analysis Services (SSAS) provides interactive analytics processing (OLAP) and intelligent data analysis functions for business intelligence applications. Analysis Services support OLAP, allowing you to develop, manage and create multidimensional structures that contain data collected from other sources, such as relational databases. For data mining applications, Analysis Services enables you to develop, create and visualize data mining models built from other data sources using a wide range of standard data mining algorithms.

Architecture of SQL Server Analysis Services : Server OLAP Tools

Microsoft SQL Server Analysis Services (SSAS) uses both server and client components to provide business analytics applications with OLAP and intelligent data analysis functions:

The server component of Analysis Services is implemented as a Microsoft Windows service. SQL Server Analysis Services supports multiple instances on a single computer, with each instance of Analysis Services implemented as a separate instance of Windows service.

Customers communicate with Analysis Services, which is treated as a web-based service, using the publicly available XML for analytics standard (XMLA), a SOAP-based protocol for command execution and response. Client object models are also provided through XMLA and can be accessed through a managed vendor such as ADOMD.NET, or through OLE DB’s own data providers.

Query commands can be executed using the following languages:

• SQL;
• multi-dimensional expressions – the query language of the industry standard oriented to the analysis;
• extensions of the data intellectual analysis – the query language of the industry standard oriented to the data intellectual analysis.

You can also use the Scripting Language for Analysis Services (ASSL) to manage objects in the Analysis Services database.

Analysis Services also supports a local cube core that allows applications on disconnected clients to view locally stored multidimensional data. Basic concepts of Analysis Services Analysis Services combines the best aspects of traditional OLAP-based analysis and relational reporting, allowing developers to define a single data model, called a Unified Multivariate Model (UDM), for one or more physical data sources.

All end-user requests from OLAP, reporting, and custom business intelligence applications access data from the underlying data sources through a unified multidimensional model that provides a single business presentation of such relational data.

Analysis Services provides a large set of intelligent data analysis algorithms that allow business users to perform intelligent data analysis to identify specific patterns and trends. These intelligent data analysis algorithms can be used to analyze data through a unified multidimensional model or directly from the physical data warehouse.

Key features of Microsoft SQL Server Analysis Services (SSAS)

Microsoft SQL Server Analysis Services (SSAS) is the basic platform for the development of business intelligence systems. SSAS 2008 provides high performance applications and scalability to millions of records and thousands of users.

Components of Microsoft BI Solution

The BI solution is based on a scalable platform designed for data integration, building data warehouses, analyzing accumulated data and building reports. The core of BI-solution is SQL Server platform. Components:

SQL Server Database Engine

SQL Server Database Engine – Scalable, high-performance DBMS, capable of storing large amounts of data resulting from the consolidation of data into a single repository for analysis and reporting.

SQL Server Integration Services

SQL Server Integration Services – Platform for performing operations of extraction, conversion and loading, which provides filling the storage and its synchronization with data from different sources that are used by business applications of the organization.

SQL Server Analysis Services

SQL Server Analysis Services – Provides the ability to build OLAP solutions, including the ability to calculate key performance indicators (KPIs). SQL Server Reporting Services – A reporting tool designed to create, publish and distribute detailed business reports for both internal and external purposes.

UDM

SSAS are based on Unified Dimensional Model (UDM), which allows different types of client applications to access data from both relational and multidimensional databases without using separate models for each database type.

The basis of UDM is an attribute-based measurement architecture, which allows you to group the properties that determine the functioning of the business in one dimension and separate these properties from the rules of measurement navigation – hierarchies.

UDM provides the ability to use multiple data sources to create a multidimensional model. UDM can be used to create a single view of relational and multidimensional data including business objects, business analytics, computation and metrics; it creates an intermediate logical level between the physical relational database used as the data source and the corporate cube and measurement structures used to process user requests. The UDM model can be imagined as the core of an OLAP system. One of the key advantages of the UDM model is the ability to combine the flexibility and functionality of a traditional relational report generation model with the powerful analytical tools and superior performance of a classic OLAP system model.

Scalability and performance of SSAS

Due to high scalability, SSAS services allow to work with terabyte databases, and with thousands of users. To ensure the work of a large number of users, to avoid conflicts in the use of resources and reduce costs, there is a possibility of horizontal scaling services SSAS. Horizontal scaling is to increase computing power and storage capacity in order to store and synchronize multiple versions of data, but at the same time, SSAS services allow you to organize shared access to read information from one database of services from multiple servers, eliminating the need for redundant resources.

SSAS services are multidimensional structures that provide high-speed access to large volumes of pre-joined data and allow end users to obtain the business data of interest in real time. SSAS services store business data in MOLAP format, providing a high degree of optimization and compression capability.

Presentation of data source

SSAS provides an abstraction level above a relational data source – the representation of the data source. The data source view allows you to define tables from the relational database for use in a multidimensional model. In addition, the Data Source view object allows you to create calculated columns and views over relational tables.

Integration with Microsoft Office System 2007

The Microsoft BI platform provides integration capabilities with Microsoft Office System 2007 family products in particular:

• Microsoft Office Excel allows you to view data stored in SSAS OLAP cubes by building dynamic Microsoft PivotTable views, which does not require the installation of additional software;
• Microsoft Office Word allows you to view reports generated by Reporting Services;
• Microsoft Office Visio allows you to visualize solution trees, dependency trees, cluster charts and other data mining technology models;
• Microsoft Office SharePoint Server allows you to create a single user interface to view and manage reports generated with the help of Reporting Services.

It is often necessary to develop multilingual solutions. Typically, the data itself is the same for the whole world, but the metadata – cube, measures, names and measurement levels, key performance indicators (KPIs) will be different for each language used. The translations allow different metadata values to be set for different languages and the solutions to be adapted to the international context. Financial information must also be localised to present results in the appropriate currency. SSAS translation and automatic currency conversion capabilities allow localized analysis data to be displayed in the users’ native language.

Introduction to SQL Server Analysis Services (SSAS)