Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

Step By Step SSAS Tutorial For Beginners

Introduction of SSAS

This SSAS Tutorial 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. It allows data analysis using different dimensions, 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 at attractive salaries. If you also want to get into SSIS marketplace, then join the SSAS online Training and improve your overall chances of getting hired by leading companies.

SQL Server Curriculum

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:

Features Description
Data Analysis Facts aggregation results in shorter query response time.
Speed The multi-dimensional analysis is allowed through cubes
Automatic displays It can link and display reports automatically.
Supreme Data Models Capable of generating good data models for better analysis and business reporting.

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 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. 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. 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. 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, dimensions could be:
    • Who: Customer?
    • Where: Location?
    • What: Product name?

In brief, 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. 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. 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.
SQL Server quiz

Tabular vs. Multidimensional Data Models

Parameters Tabular Data Model Multidimensional Data Model
Memory In-memory storage File-based Storage
Structure Loose structure Rigid Structure
Best Features There is no need to move data from the source. It works just the best when data is stored into a star schema.
Type of Models Relational and DAX models Dimensional and MDX models
Complexity Simple Complex
Size Small in Size Larger in Size

SSAS vs. PowerPivot

Features SSAS PowerPivot
About SSAS is corporate BI PowerPivot is Self-service BI.
Deployment Deploy to SSAS Deploy to SharePoint
USES Visual Studio Projects Excel Projects
Size Limited Memory Size Capacity limited to 2GB only
Partition Support SSAS supports partitioning PowerPivot does not support partitioning
Query Types DirectQuery and Vertipaq Only Vertipaq queries are allowed
Admin tools Server admin tools Excel and Share admin tools
Security Dynamic Security Levels Workbook File Security

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.

A detailed introduction to MDX

Till the time, we have used MDX multiple times in our blog, but what is it exactly? MDX is a query language that is used to retrieve data from multidimensional databases. It is used to query multidimensional data from analysis services and supports two distinct modes. SSAS is not a proprietary language but a standards-based query language that is used to retrieve data from OLAP databases. It is a part of the OLAP specification sponsored by Microsoft. Although MDX has a similar syntax to SQL, still it is significantly different.

MDX Queries

A multidimensional database is typically referred to as a cube and is the foundation of the multidimensional database, and each cube contains one or more dimensions. The next important term in MDX is set. A set may contain zero, one, or more tuples. A set with zero tuples is named as an empty set. An empty can be written as:

Read: SQL Server Developer & Database Administrator Salary Structure

{customer.Country.Australia, Customer.Country.Canada, Customer.Country.Australia}

Further, the basic syntax of an MDX query can be given as:


[WITH <formula_expression> [, <formula_expression> ....]]
SELECT [<axis_expression>,[<axis_expression> ....]]
FROm [<cube_expression>]
[WHERE [Slicer_expression]]

Keywords like “with, select, where, from” that can be used with the expression are clauses. In MDX query, if anything is given within square brackets, it means it is optional and can be skipped if not required. In the above expression, we can see that “with” and “WHERE” clauses are options while SELECT and FROM clauses are mandatory. The “WITH” clause is generally used for custom calculations and operations.

Select statement and axis expression in MDX Query

The SELECT statement in the MDX query is used to retrieve a subset of multidimensional data within a cube. In SQL, the SELECT statement can specify which column to be included and which can be skipped. This is the reason, select statement analyzes two-dimensional data in the SQL and MDX query analysis of multidimensional data.

When it is two-dimensional data, it means there are two axes, X and Y. you can retrieve data either from the X-axis or Y-axis. At the same time, the MDX query gives you the capability of retrieving data from one, two, or more axis. The basic syntax of SELECT statement in MDX query can be given as:

SELECT [<axis_expression>,[axis_expression> ...]]

The axis expression specifies the dimension you are interested in viewing or retrieving. These dimensions are named as axis dimensions because data from these dimensions is projected on to the corresponding axis. The basic syntax of AXIS expression in MDX query can be given as:

<axis_expression>:=<set>ON(axis | AXIS (axis number) | axis number)

Here, you can retrieve multi-dimensional result sets. A set is a collection of tuples that is used to form an axis dimension. It gives you the capability to define up to 128 axes in the SELECT statement. The first five axes are aliases and named as ROW, COLUMN, SECTION, PAGE, CHAPTER, etc. Axes can also be given as numbers that allow you to specify more than five dimensions in the SELECT statement. Let us understand the concept with the help of an example below.


SELECT Measures.[Internet Sales Amount] ON COLUMNS,
[Customer].[Country].MEMBER On ROWS,
[Product].[Product Line].MEMBERS On PAGES
FROM [Adventure Works]

The same statement can also be written as:


SELECT Measures.[Internet Sales Amount] ON 0,
[Customer].[Country].MEMBER On 1,
[Product].[Product Line].MEMBERS On 2
FROM [Adventure Works]

AXIS Dimensions

When we are defining a SELECT statement, we build axis dimensions. A SELECT statement specifies a set of attributes for each dimension like ROW, COLUMN, and additional axes.

Read: How To Use The Exists Operator In The SQL?

FROM clause and CUBE specifications

The FROM clause in an MDX query specifies the cube from which you are going to retrieve the data. It is similar to the FROM clause in SQL that is used within a table. The FROM clause is necessary to define for each MDX query. The basic syntax of FROM clause for an MDX query can be given as:

FROM <cube_expression>

In the cube expression, you have to give the name of the cube that you want to use for retrieving data. In SQL, we can define multiple tables using the FROM clause. At the same time, an MDX query allows using a single cube only.

WHERE clause and SLICER specifications

In a relational database system, we issue a query that returns only a specific portion of data available within a table, a set of joined table, or joined databases, etc. It is accomplished through SQL statements that specify which data should be returned and which to avoid as the result of running query. Take the example of a product table that contains sales information about a product.

Product ID Product Line Color Weight Sales
1 Accessories Silver 5.00 200.00
2 Mountain Grey 40.35 250.00
3 Road Silver 50.23 2500
4 Touring Red 45.11 2000.00

Here is a simple query to return just two columns from the table:


SELECT ProductLine, Color
FROM Product

This query will return the Product Line, and Color column from the product table. The output will look like this:

Product Line Color
Accessories Silver
Mountain Grey
Road Silver
Touring Red

To display the whole table, you can use the syntax given below.


SELECT*
FROM Product

To limit the result set, you can add a WHERE clause and retrieve the selected data. Now, let us see how to write an MDX query for the same. In this example, the given table can be used as a Fact table and Dimension Table both. An MDX query against the cube that produces the same results as that of the SQL query is:


SELECT Measures.[Sales] On COLUMNS,
[Product].[Product Line]. MEMBERS on ROWS
FROM [ProductCubbe]
WHERE ([Product].[Color].[Silver])

The final output for the query will be:

ProdcutLine Sales
Accessories 200
Road 2500.00

The next important term is SLICER dimension that you build when working on a WHERE statement. It can be taken as a filter to remove unwanted members or dimensions.

WITH clause and calculated members

Often business needs involve calculations that must be formulated within the scope of a specific query. The MDX “WITH” clause provides you with the ability to compute such calculations within the scope of a query. Additionally, we can retrieve data outside the cube using the Lookup method. The WITH clause allows you to perform calculations like sets, calculated members, cell computations, etc. The basic syntax of MDX WITH clause can be given as:

Read: How to Increase the Speed of SQL Query Execution

[WITH <formula_expression> [,<formula_expression>...]]

the formula_expression will vary depending upon the type of calculations. calculations are separated by commas.

MDX Expressions

An MDX expression is a partial MDX statement that can be used to evaluate value. They are typically used in calculations and defining values for objects or default measures. It can be used to define security expression to allow or deny access. MDX expressions typically take a member, set, tuple as input and return a value. The basic syntax of an MDX expression can be given as:

Customer.[Customer Geography]. DEFAULTMEMBER

it will return the default member specified for the customer geography hierarchy of the customer dimension

The next important term is MDX function that can be used with MDX queries or functions as per the requirement. It can be called in multiple ways so you should learn it practically to use with MDX queries.

free SQL Server demo

Final Words:

This blog for SSAS Tutorial gives you the in-depth details about the SQL Server Analysis Services and MDX query language that is used to SSAS for data retrieval. Few concepts are easy as they are theoretical, and a few like MDX Language are tough to master without proper training.

For this purpose, you are recommended joining the SQL Server certification course form JanBask Training and learn practical aspects of all ETL tool, including SSIS, SSRS, and SSAS. The course will give you a sound idea of the tools and how to use them in your workplace.

In brief, you will become a skilled resource with proper training and education delivered by expert mentors. All the best for a successful career in the SQL Server domain!


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

2 days 14 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

3 days 15 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

3 days 15 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

4 days 16 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

2 days 14 Nov 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

-0 day 12 Nov 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

3 days 15 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

7 days 19 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews