SQL Server Analysis Services Work Flow
page 2 of 3
by Nidal Arabi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 17424/ 24
Article Contents:

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.

View Entire Article

User Comments

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

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

©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-07-23 4:55:41 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search