SQL Server Analysis Services Work Flow
 
Published: 16 Sep 2008
Abstract
The Analysis services project has a series of predefined steps that you can follow to create your end result. In this article, Nidal examines the work flow steps of SQL Server Analysis Services in detail. He begins with a short introduction to SQL Server Analysis Services and then provides comprehensive explanation of the concept with the help of related screenshots.
by Nidal Arabi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 19768/ 57

Introduction

Business intelligence is a word of mouth for every business user. In this series of articles I will begin explaining of how Microsoft has done a great job in the world of business intelligence using Microsoft SQL Server Analysis services. I will try to explain as step-by-step as possible the process. Please note the articles will start somewhat theoretical then move to more practical as we go along.

About SQL Server Analysis Services (SSAS)

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 a developer, can develop business intelligence solutions using the most advanced tools available in the market.

Work Flow

The SSAS project includes several components that should be present in order to develop an Analysis project. The Picture below describes the process in general.

Figure 1 - General overview of the Analysis Services Project

Your work environment may include several databases (OLTP - Online Transaction Processing database) that store information in an optimized way for Atomic transactions (Insert, Update, etc.). This would describe the first rectangle which includes databases for different departments in an organization. You may also have created a data warehouse database that you would fill each end of the day with data from other various systems. Note that the interaction between the OLTP databases and the data warehouse database could be developed using the SSIS (SQL Server Integration Services). Also, the interface between the first and the second rectangle is SQL Server Integration Services is simply abbreviated as IS.

Please note that your current databases does not need to be MS SQL based, however, it would be of great help to you to be MS SQL based especially if you are going to use proactive caching and other built in features between the MS SQL database engine and SSAS engine.

Once you have your data sources available for Analysis project and created them in your model, you can start by creating your Unified Dimensional Model (UDM). You can find more about these concepts in the previous article called Analysis Services (Concepts). If you take a look in the middle rectangle, this is where the most important things take place. I am going to stress here that the UDM is only a logical model of the databases and does not affect in any way the underlying databases (or data sources). So for example, if you create a new query in the data source views, it is not stored in the original database. It is also the case if you establish a new relationship between your tables.

As you can see in the UDM model, you must create data source views as the first step, which is simply a logical data model that specifies your data coming from various sources.

Using the data source views, you create your own new queries (and save them as views) that can contains new calculations or even in the same table create a new calculated fields. Also included in the data source views is the option to delete and create relationships between different tables.

Using the UDM model, you can create the dimensions (think of dimensions like primary code tables like branch or currency table). The dimensions can be as simple as one table or as complex to include many tables per one dimension.

Based on the created dimensions as well as at least one fact table that include the data to be represented as report (usually a transaction table), we can start generating a new Online Analytical Processing (OLAP) cube that would filter the data using the dimensions. SSAS allows you to create KPI (Key performance indicators) as well as include different languages translation (Not for the fact data).  Once the OLAP cube is present, you can use different reporting tools including, but not limited to, SQL Server reporting Services (SSRS), Microsoft Office (especially Excel) as well Microsoft SharePoint.

3rd party tools for reporting are also available, but they will not be discussed in this series.

Summary

In this article, an overview of SSAS workflow was provided. Please note the following:

·         The original data does not need to be stored in Microsoft SQL Server.

·         SSAS can read data from OLTP or data warehouse databases.

·         OLAP Cubes are stored in SSAS databases.

·         OLAP are not relational whereas OLTP are relational databases.

With this summary, I hope that the foundation has been laid down for going deeper into the UDM before creating your first SSAS project.



User Comments

Title: Nice Article   
Name: Kalesh
Date: 2011-07-12 6:21:53 AM
Comment:
Nice Article. Its very useful






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


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-06-25 7:55:46 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search