SQL Server Analysis Services (SSAS) is a part of the Microsoft SQL Server platform that was introduced in SQL Server 2000 and has been greatly enhanced since Microsoft SQL Server 2005. It provides the platform on which you, as developer, can develop business intelligence solutions using the most advanced tools available in the market.

Concepts

Let us first introduce some concepts that are essential to any developer to begin creating any new project.

SSAS provides a platform for enterprise reporting and data analysis. It is composed of two main core services:

·         Online Analytical Processing (OLAP) which is designed for storing and retrieving large amounts of data to be used for ad-hoc reporting and analysis.

·         Data mining which provides for data analysis using models to uncover trends and patterns in your data.

The OLAP services in SSAS are advanced in the sense of tools and utilities provided to you to create your own project. SSAS allows you to generate an Analysis services database in a way designed for business analytics and reporting. You can create OLAP cubes (will be more clear to you as we proceed in the series) that are optimized for data aggregation on large amounts of data and provides much more advanced reporting to the end user (much more flexible than producing a fixed page reporting to be paper printed).

Some of the nice features that are available with SSAS are pretty amazing. For example, proactive caching can detect data in your database and update your OLAP cube directly if you have a back end database as MS SQL (as the transaction is happening) removing the need for a temporary database (please note that this feature is only available in the enterprise edition of MS SQL).

Another feature that was introduced with the 2005 version of SSAS is KPI (Key Performance Indicators). We can create, manage and display key business metrics which provide using formulas information about the business trends. An example of KPI would be the Current Year Sales/Previous Year Sales.

SSAS also provides business intelligence wizards that are very helpful in guiding you to create complex tasks such as currency conversion.

Last but not least, SSAS provides the developer with a very powerful tool called MDX scripting to allow the user to create queries against OLAP cubes. The MDX is very similar to SQL except that it has cubes as data sources and dimensions as their selection criteria.

Data Mining is also a service that has been introduced with SQL server with some already built in algorithms which we can extend or create custom algorithms of your own. Also note that it is easy to integrate data mining into the Analysis workflow. Do not worry about this now; it will be extensively explained in future articles.

A nifty feature (a very important logical layer) is provided with SQL Server 2005, and later versions, is the UDM (Unified Dimensional Model).

The UDM is used by the AS to access the underlying data to create OLAP cubes and data mining models. This model combines the best of the features of Relational and Dimensional models. The UDM will be explained through examples in the future articles.

If you take a look of the picture downloaded from the Microsoft Technet website, you can see that SSAS is only a part of a group.

Figure 1: MS SQL Server Components (High Level)

If you are going to develop a business intelligence solution, you will definitely use two if not three of these components.

The Analysis Services component is an essential one (the subject of talk). The second is the core of MS SQL which is Integration Services. In order to feed AS with data, you need the IS (integration services) to transfer data from any input source (like an SQL Database, Oracle Database, Text Files, etc.) into the AS database. The third component that is of interest to you would be Reporting Services to deliver your OLAP cubes in a presentable way to your end user, but you could use Microsoft Office, SharePoint or even any third part tool.

You could also use notification services to tell you the status of jobs (Data Import, Cube Generation, etc.).

You have to install one the following versions of Microsoft SQL Server (Standard, Developer, and Enterprise). Please note that the developer edition can even install on Windows XP or Vista and it is only used for development purposes.

After installing Microsoft SQL Server, you get the three tools.

1.    Business Intelligence Development Studio

2.    SQL Server Management Studio

3.    SQL Server Reporting Services

You can always substitute the third choice with one or any combination of the following.

·         SharePoint

·         Microsoft Office (specially Excel)

·         Any 3rd Party tool

In this article an overview of SSAS was provided and some general concepts were introduced. Next, I will elaborate on the building components of an SSAS database. See you in the next article.