Aug 08, 2019 
16.8k
29
NovBlack Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook - SCHEDULE CALL
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
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. |
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.
In brief, you can say that dimension is a window to view the information in facts.
There are two popular models in SSAS: Multi-dimensional and Tabular Data Model.
| 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 |
| 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 |
Benefits:
Drawbacks:
SQL Server Training & Certification
Read: A Comprehensive SQL Server Tutorial Guide for Beginners & Experienced
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.
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.
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: How to Get Your Career as an SSIS Developer Rolling?
SELECT Measures.[Internet Sales Amount] ON 0,
[Customer].[Country].MEMBER On 1,
[Product].[Product Line].MEMBERS On 2
FROM [Adventure Works]
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.
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.
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.
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: Top 151+ SQL Interview Questions and Answers You Can't Miss
the formula_expression will vary depending upon the type of calculations. calculations are separated by commas.
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
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!
FaceBook
Twitter
LinkedIn
Pinterest
Email
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.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Different Types of SQL Keys: Example and Uses
367.3k
What is the Difference Between Control Flow & Data Flow in SSIS?
43.8k
What is Foreign Key in SQL? How to Set, Add, Create & Use of Foreign Key
255.6k
What is a CASE Statement in the SQL?
287.5k
Top 151+ SQL Interview Questions and Answers You Can't Miss
778.1k
Receive Latest Materials and Offers on SQL Server Course
Interviews
Aug 24, 2017
367.3k
