SQL Server Analysis Services (SSAS) : Part 1

The Microsoft Business Intelligence (BI) suite provides a very powerful technology called SQL Server Analysis Services (SSAS) to deliver OnLine Analytical Processing (OLAP) and data mining for business intelligence applications.

The purpose of analysis services is to turn data into information and to provide quick and easy access to that information for decision makers. SSAS provides OLAP by letting you design, create and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases and provides many data mining algorithms for mining data from data sources. So for delivering OLAP and data mining it uses client and server technologies.

The main idea of SSAS is to provide fast results from data sources when we apply a query because in order to make a decision we need data of various dimensions. OLAP does this by pre-calculating and storing aggregates. This requires a lot of disk space, but here it provides the ability to explore the data very quickly because we have already processed the data.

Now there are two basic questions that come into your mind:

1. Why
2. How

Am I right? So now I will answer both the questions as in the following:

1. Why: Most of us use relational databases, so in a relational database, data is stored and you can fetch the data by applying queries, generating reports and so on, but here if we think about queries and reports we can access data of certain things but by utilizing analysis services we can play with data, or slice and dice the data with very less effort.
   
   The most important thing about this is that you can provide the power of analysis of data to someone else who is not a DBA or a database professional or developer.
    
2. How: This is the most important question, how to use analysis services for OLAP and Data mining. Even I can't say, it is very easy to work on analysis services, it's a challenge in itself. There are many tutorials provided for this so you can use them. This is my first Article on SSAS so as soon as I get time I will soon write a full series of articles on this.
   
   Now I am starting the core part of analysis services. Here I am not jumping directly into the hands-on of how to create a cube at all. I am first providing an overview of the internal structure of SSAS.
   
   SSAS uses both client and server components to provide OLAP and data mining functionality:
   
   1. The Server component is implemented as a Microsoft Windows service. SSAS supports multiple instances on the same computer, so each instance is implemented as a separate instance of a Windows service.
       
   2. Clients communicate with analysis services using XMLA (XML for Analysis), a SOAP-based protocol for issuing commands and receiving responses, exposed as a web service.
       
   3. Query commands can be issued using the following languages:
      
      1. SQL: This is a structured query language, most of us know about it very well, so I will not describe it here.
      2. MDX (Multidimensional Expression): MDX is a query language to retrieve multidimensional data from SSAS. MDX is based on the XMLA specification.
      3. DMX (Data Mining Extensions): This language works with data mining models. You can use DMX to create a new structure of data mini models, to train these models, to browse and to manage and predict against them.
      4. AASL (Analysis Services Scripting Language): Most of the communication of the SSAS client happens using SOAP messages. AASL is the XML dialect used for these messages, made up of two parts; the first is DDL or object definition language, that defines and describes an instance of Analysis Services and the second is a command language that sends action commands, such as Create , Alter or Process to an instance of Analysis Services.

Since I have already said that SSAS is based on client and server components, I will now explain both components of the Architecture in detail.

1. Server Architecture: This runs as a Windows service. The Msmdsrv.exe application is a server component. This application consists of security, XMLA listener, query processor and other components that perform the following tasks:
   
   
   
   
2. Client Architecture: SSAS has a thin client Component Architecture. All queries and calculations are resolved by the server only. So for each request a server to client connection is required. There are several providers with SSAS to support various programming languages. These providers communicate using SOAP packets. You can better understand this by the following diagram:
   
   
   
   SSAS also has a web architecture with a fully scalable middle tier for deployment by both small and large organizations. SSAS also provides middle tier support for web services.
   
   Both client and server can communicate without using any provider. They can communicate by sending XMLA in a SOAP packet. Check the diagram given below for a better understanding.

          

I know many of you are feeling very boreD after reading so much theory. Thanks for your patience. I will now finally describe very the important keywords or I can say core concepts, that are the building blocks for SSAS. So if you have become to fell sleepy, now is the time to wake up and read these keywords very carefully because without this we are not able to understand SSAS.

Now without wasting any time I am explaining the following keywords:

1. Cube: This is basic unit of storage and analysis in SSAS. It is a multidimensional data structure that holds data that's been aggregated to return data quickly when a query fires.
   A Cube is a related set of measures and dimensions used to analyze data. A cube is essentially synonymous with a Unified Dimensional Model (UDM).
2. Dimension: Dimensions are a group of attributes based on columns of tables of a view. Dimensions are always independent of a cube so they can be used in multiple cubes. Dimensions are the criteria onto which analysis of business data is performed, like time, geography and so on. For example time can be years, months, quarterly, weekly and so on.
3. Dimension Table: This table contains the entire dimensions onto which you want to summarize your data, like in the case of a time dimension this table can contain Year, Month, Quarter, Week and so on.
4. Fact Table: A fact table contains the data that measures the organization's business operations. A key characteristic of a fact table is that it contains numerical data (facts) that can be summarized to provide information about the history of the operation of the organization. Any table that you are using for a function like sum or average can be called a fact table.
5. Measures: A Cube is based on one or more measures that are nothing but a column of facts table that you want to analyze.
6. Schema: A fact table and a dimension table are always related and this relation inside the cube forms a schema. There are multiple types of schema but the most common two are start and snowflake schema. I will write a separate article for explaining the various kinds of schema because here I don't want to complete all in short.

Here I have described all the basic concepts as much as possible in this article. Now I will define the objects or I can say indexes for our hands-on articles that I will cover in my next articles. But if required in between hands-on, I will post some theoretical articles like this so with hands-on we can also understand the concepts.

1. Create SSAS Project in BIDS
2. Create a data source
3. Create Data Source View
4. Create Dimension
5. Create Cube

These are the basic topics that I will cover in future articles and surely this list will increase as soon as I write more articles.

References: http://msdn.microsoft.com