Grab Deal : Flat 30% off on live classes + 2 free self-paced courses! - SCHEDULE CALL

- SQL Server Blogs -

Step By Step SSAS Tutorial For Beginners

Introduction of SSAS

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.

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

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.

Types of Models in SSAS

  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.

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.

SQL Server Training & Certification

Read: How to Compare MongoDB and DynamoDB?
  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

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:

{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:

Read: What is a CASE Statement in the SQL?

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.

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:

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

Read: A Comprehensive NoSQL Tutorial Guide for Beginner: Learn Step-by-Step

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.

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

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!



fbicons FaceBook twitterTwitter google+Google+ lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    JanBask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

3 days 22 Mar 2024

QA Course

QA

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

Upcoming Class

2 days 21 Mar 2024

Salesforce Course

Salesforce

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

Upcoming Class

3 days 22 Mar 2024

Business Analyst Course

Business Analyst

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

Upcoming Class

3 days 22 Mar 2024

MS SQL Server Course

MS SQL Server

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

Upcoming Class

3 days 22 Mar 2024

Data Science Course

Data Science

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

Upcoming Class

10 days 29 Mar 2024

DevOps Course

DevOps

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

Upcoming Class

4 days 23 Mar 2024

Hadoop Course

Hadoop

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

Upcoming Class

10 days 29 Mar 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

4 days 23 Mar 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

18 days 06 Apr 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

31 days 19 Apr 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

10 days 29 Mar 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews