SSAS Tutorial

This SSAS Tutorial for beginners Guide is a detailed introduction to SQL Server Analysis Services and is highly useful for beginners. SSAS is a tool offered by Microsoft and an analytical processing engine in Business Intelligence. It allows the creation of a database that acts as an OLAP database or SSAS cube tutorial. It allows data analysis using different dimensions, the creation of cubes from data marts, data exploration in data warehouses, and data modeling, etc. SSAS is a multi-dimensional OLAP server as well as an analytics engine that allows you to play with voluminous data easily.

These days demand for SSAS MDX professionals is increasing almost every passing day. Companies are hiring expert OLAP cube developers or SSAS cube tutorial at attractive salaries. If you also want to get into the SSIS marketplace, then join the SSAS online Training and improve your overall chances of getting hired by leading companies.

History and Evolution of SSAS

• OLAP features were first included in SQL Server 7, but later it was purchased by an Israel Company named Panorama.
• In a short time, SSAS became the most used OLAP engines because it was introduced as the part of SQL Server.
• After some time, SSAS was completely renovated with the release of SQL Server 2005 in the market.
• The latest version of SSAS offers a feature of cubes with the Scope statement. You will learn about important SSAS terminologies in future sections.

• SSAS 2008 and SSAS 2012 are mainly concerned with scalability and query performance.
• In Microsoft excel 2012, one new feature was added Power Pivot that used a local instance of SSAS to increase the overall query performance.

Key Features of SSAS

The important features of SSAS are highlighted below:

The other important features of SSAS can be given as:

• It ensures data integrity through automatic data backups.
• There is no additional requirement of software; a normal internet connection and a web browser are sufficient.
• The organizational data is kept secure through powerful SSAS solutions.
• The tool can be accessed anytime, anywhere with the help of an internet connection.

SSAS Architecture

SSAS ETL tool has a three-tier architecture that consists of RDBMS, SSAS, and the client. Let us discuss each of them in brief below.

1. 1. RDBMS: Relational Database Management System: RDBMS allows the collection of data from multiple sources like excel sheet, database, files using an ETL tool like SSAS.
2. 2. SSAS: SQL Server Analysis Services: Aggregated data from RDBMS is pushed into SSAS cubes using analysis service projects. The cube will generate an analysis database further; once the database is ready, it can be used for multiple purposes.
3. 3. Client: Clients may access data using dashboards, portals, scorecards, etc.

Important SSAS Terminologies

• Data Source: It is similar to a connection string that can be used to establish a connection between the analysis database and the RDBMS.
• Data Source View: it is a logical model of the data source.
• Cube: It is the basic unit of storage. It is a collection of data that has been aggregated to allow queries to return the data quickly.
• OLAP: it is made up of data cubes that contain measures and dimensions. It includes almost all members in a hierarchical relationship. In simple terms, it a specific set of rules that helps you in determining how specific cells are computed in a specific sparse cube, and its measure values are rolled up inside those hierarchies.
• Dimensions: it offers the context surrounding a business process event. In easy words, it offers who, what, and where of a fact statement. For example, in the case of sales fact tables you can say that dimension is a window to view the information in facts.

• Level: It is a type of summary that can be retrieved from the single dimension is called the level.
• Fact Table: It is the most important part of a dimensional table. It contains measurements, facts, foreign keys for the dimensional table. The best example of the Fact table is Payroll Operations.

• Dimensional Table: A dimensional table contains dimensions of a fact. They can be joined to a fact table using a foreign key. Dimension tables are denormalized tables offer characteristics of facts with the help of some attributes. It defines limits for dimensions that contain one or more hierarchical relationships.
• Measure: There are one or more tables for each fact table that should be analyzed properly.
• Schema: A database schema supports the database management system and its structure can be given in a formal language. The term schema means the organization of the data as a blueprint in such a way how the database is constructed.
• MDX: It is a query language to retrieve the data from multidimensional tables. We will discuss MDX in detail in future sections.

Types of Models in SSAS

There are two popular models in SSAS: Multi-dimensional and Tabular Data Model.

1. 1. Multi-dimensional Data Model: The multidimensional data model is made up of a data cube. It is a group of dimensions that allows you to query the value of cells using cubes and dimensions. It defines a set of rules that measures values rolled up within hierarchies and how specific values are computed in a sparse cube.
2. 2. Tabular Data Model: It organizes data into related tables that are not designated as tables and facts and development time is very less with tabular data models because related tables can serve both roles.

Tabular vs. Multidimensional Data Models

Pros and Cons of Using SSAS:

Benefits:

• The resource contention can be avoided with the source system.
• It is an ideal tool for numerical

• The tool enables the discovery of data patterns that is not apparent immediately using data mining features.
• It offers an integrated and unified view of business data reporting, analysis of KPIs (Key Performance Indicators), and scorecards.
• It offers online analytical process feature (OLAP) from different data sources.
• It allows users to analyze data using multiple tools like SSRS or Excel.

Drawbacks:

• Once you have decided on a data model either Tabular or Multidimensional, you cannot change it to other versions.
• It is not possible to merge data between Tabular or multidimensional cubes.
• The tabular data model may be risky if project requirements change mid-way through the project.

Best Practices of Using SSAS:

• Aggregations should be defined well in a proper way.
• Optimize cubes and measure group design too.
• Use partition methods
• MDX should be written effectively.
• The query engine cache should be used efficiently.
• Try to avoid the tabular data model until it is not required.

CUBES AND CREATION OF CUBES IN SSAS:

Cubes are multidimensional data structures that store data in the form of cubes using facts and dimensions. A Cube in SSAS is used for faster and more in-depth analysis of data. Data is stored in cubes for dividing large volumes of data which will help in retrieving the accurate data in a short period.In short, the cubes are used in SSAS to organize the data so that accessing and retrieving of required data based on the query will be easy.

Steps to be followed to create an OLAP Cube in SSAS:

• A data warehouse is created in the Microsoft SQL Server studio.
• Create a new analysis service project in the Business Intelligence Development Studio, 
• Create a new data source by using right-click on the data sources option in Solution Explorer.
• Choose connections button or create a new connection followed by the next button
• Select Inherit->Next->Finish.This step will create the data source.
• Right-click on the data source view option in Solution Explorer.
• Click on Next-> Select the data source->Click on Next.
• Move the fact table onto the right pane and click on the Add related tables button.
• Click next and enter the data source view name followed by the Finish button. This will create the Data Source View.

• Create a new cube in Solution explorer by using right-click on Cubes.
• Click on Next->next->Select the fact table->Select the measures-> Next
• Select the dimension table-> Enter cube name followed by Finish. This step will complete the creation of the cube in SSAS.

LANGUAGES USED IN SSAS:

Let us have a quick review of the languages used in SSAS. SSAS uses three different languages as listed below:

• MDX (Multidimensional Expressions): MDX is a query language used in SSAS which is designed for OLAP databases. MDX language is an extension of SQL Language where the data is retrieved from the SSAS cubes. It allows queries and access to the multidimensional data.MDX language is found to be different in terms of implementations and conceptualization.MDX queries are used to access the data in the Server Analysis Service Cube.
  
• DMX (Data Mining Extensions): DMX is the language used to create and work with the Data Mining models in SQL Server Analysis Service.DMX language includes DDL (Data Definition Language) statements, DML(Data Manipulation Language) statements, functions, and operators as well.

• XMLA (XML For Analysis): XMLA is the language that is mainly used to perform administrative tasks. These tasks include restore and backup, move and copy the database, and many more. XMLA is a protocol used to establish communications between the client and server connections.

ADVANTAGES OF SSAS:

• Analysis of data is made easy through SSAS. It is considered as an ideal tool for numerical analysis.
  Speed and compatibility is high.
• Gives a unified and structured view of all the information related to the organization, which includes reporting analysis.
• SSAS provides accurate data when a query is placed as it uses cubes for managing the data.
• Resource contention can be avoided using SSAS.

DISADVANTAGES OF SSAS:

• The versions cannot be changed. Once you have decided to go through using one particular model, either Tabular or Multidimensional, the version can be changed.
  Merging is not allowed between the tabular and multidimensional cubes.
• The requirements might change during the project implementation process. The tabular data model is found to be risky if the requirements change during the mid-way of the project.

SSAS Dimensions

1. Introduction to Dimensions

SSAS dimensions are groups of attributes based on columns from tables or views in a data source view. Dimensions exist independent of a cube, can be used in multiple cubes, can be used multiple times in a single cube, and can be linked between Analysis Services instances. A dimension that exists independent of a cube is called a database dimension and an instance of a database dimension within a cube is called a cube dimension. A dimension cannot be added directly to a cube until it is added as a database dimension.

• Properties: Name column: e.g. Key column is ProductID, while Name Column is Product Name (it helps you to view and understand the data).
• Why you should have a key in each and every dimension table? Because without the key attribute, we cannot join the dimension and fact table, and therefore, cannot analyze the data on the dimensional level.

2. Dimension Structure

Each dimension contains a key attribute. The key attribute is the attribute in a dimension that identifies the columns in the dimension main table that are used in foreign key relationships to the fact table. Typically, the key attribute represents the primary key column or columns in the dimension table. You can define a logical primary key on a table in a data source view which has no physical primary key in the underlying data source. We can add additional columns at this level.

3. Dimension Hierarchies

Hierarchies in SSAS optimize performance of cube by improving cube aggregations. There are two major types of hierarchies:

• Natural (automatically comes within the dimension)
• User-defined
  • Balanced: In a balanced hierarchy, all branches of the hierarchy descend to the same level, and each member’s logical parent is the level immediately above the member. (Year > Semester > Quarter > Month)
  • Unbalanced: In an unbalanced hierarchy, branches of the hierarchy descend to different levels. Parent-child hierarchies are unbalanced hierarchies. (Manager ID à Employee ID)
  • Ragged: In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member.

4. Attribute Relationships

Each and every non-key attribute in a dimension must be directly or indirectly related to Key attribute. Relationship between attributes can be changed to either “Flexible” or “Rigid”

• Flexible -> the relationship will change over time.
• Rigid -> the relationship will not change over time.

5. Dimension Translations

In SSAS a dimension translation is a language-specific representation of the name of a dimension, the name of an Analysis Services object or one of its members, such as a caption, member, or hierarchy level. Translations provide server support for client applications that can support multiple languages. Frequently, users from different countries view a cube and its dimensions. It is useful to be able to translate various elements of a cube and its dimensions into a different language so that these users can view and understand the cube.

6. Browser

Once the dimension is Processed and Deployed, the data and hierarchy behavior can be checked in Browser Pane.

SSAS Cube

1. Cube Structure

Measures: A measure represents a column that contains quantifiable data, usually numeric, that can be aggregated. Group of measures having same granularity forms a Measure Group.

Dimensions: All dimension added in Dimension folder can be used in instance of a Cube. Attributes that are added and processed at Dimension level are showed in Cube Dimension. Dimensions that are available at DSV are only pulled into Cube Structure. Cube dimensions can be edited to add new attributes or hierarchies.

2. Dimension Usage

After you got the inappropriate data after you processing the cube, the first place to check is the Dimension Usage. The second cube object Dimension Usage describes how a Dimension is related to Measure Group in Cube.

Dimension Keys are automatically related to respective Measure Group. If there is no relation between a Dimension and Measure Group, there cannot be any aggregations created across that Measure Group. A Dimension and Measure Group can have the following relations:

• No Relation (facts and dimensions are not connected, because no connecting keys)
• Regular (fact referencing dimensions, one-to-one or one-to-many relationship)
• Referenced (fact and dimension table is joined though an intermediate dimension table.)

• Many-to-Many (4 tables involved)
• Fact (a de-generated dimension that is any dimension member which is increasing with the fact measures could be included in a fact table. Indirectly, the dimension and the fact could be combined)

3. Calculations

We can add additional measures, dimension attributes, new calculated columns (called calculated members). A calculation is a Multidimensional Expressions (MDX) expression or script that is used to define a calculated member, a named set in a cube in SSAS. Calculations let you add objects that are defined not by the data of the cube, but by expressions that can reference other parts of the cube, other cubes, or even information outside the Analysis Services database.

4. KPIs (Key Performance Indicator)

In business terminology, a Key Performance Indicator (KPI) is a quantifiable measurement for gauging business success. A KPI is frequently evaluated over time. KPI components are:

• Value: An MDX numeric expression that returns the actual value of the KPI.
• Goal: An MDX numeric expression or a calculation that returns the target value of the KPI.
• Status: An MDX expression that represents the state of the KPI at a specified point in time. The status MDX expression should return a normalized value between -1 and 1.

• Trend: An MDX expression that evaluates the value of the KPI over time. The trend MDX expression enables a business user to determine whether the KPI is improving over time or degrading over time.

5. Partitions

Cube partitions are used by SSAS to manage and store data and aggregations for a measure group in a cube. Every measure group has at least one partition; this partition is created when the measure group is defined. When you create a new partition for a measure group, the new partition is added to the set of partitions that already exist for the measure group. A Cube in general stores aggregations, data and metadata. Based on the storage mode, the Cube Partitions are divided as

• Multi Dimensional Online Analysis Processing (MOLAP): Stores Data, Aggregations and Metadata in Cube.
• Relational Online Analysis Processing (ROLAP): Stores Data and Aggregations in Relational Database and metadata in Cube.
• Hybrid Online Analysis Processing (HOLAP): This storage mode is hybrid of MOLAP and ROLAP. In this mode Aggregations are stored in Cube, whereas Data and Metadata is stored in Relational Database.

6. Aggregations

Aggregations are pre-calculated summaries of data from leaf cells. Aggregations improve query response performance by preparing the answers before the questions are even asked. Dimensions and their hierarchies of attributes reflect the queries that can be asked of the cube. Aggregations are stored in the multidimensional structure in cells at coordinates specified by the dimensions.