SQL Server Analysis Services Concepts
 
Published: 22 Sep 2008
Abstract
In this series of articles, Nidal explains how Microsoft has done a great job in the world of business intelligence using Microsoft SQL Server Analysis Services (SSAS). The author deeply examines the concept of OLAP Services and Unified Dimensional Model. He also points out where SSAS fits in SQL Server and also the requirements to work with it in a step-by-step manner.
by Nidal Arabi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 27685/ 78

Introduction

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.

OLAP Services

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.

Unified Dimensional Model

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.

Where Does SSAS Fit in Microsoft SQL Server

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

What You Need to Work with SSAS

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

Summary

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.



User Comments

Title: bbb   
Name: bbb
Date: 2012-05-10 6:48:04 AM
Comment:
Yes Good
Title: aaa   
Name: aaa
Date: 2008-09-26 12:32:09 AM
Comment:
Yea Good






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-03-09 5:58:00 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search