SSAS User Handbook

If you are looking for a tool to automate maintenance of the SQL Server database which can perform deeper and faster analysis of data. Then SSAS can be taken into consideration. This cheat sheet will guide you with the basic concepts which are required to get started with it. It is a handy reference sheet for the beginners and also the one having knowledge about the BI tools.

Go for this in-depth job-oriented MSBI Training Course now!

Watch this MSBI Tutorial full course for Beginners video


Youtube subscribe

You can also download the printable PDF of this SSAS cheat sheet.
ssas Cheat sheet

SQL Server Analytical Services (SSAS):

SSAS is a technology from Microsoft Business Intelligence stack, which is used to develop online Analytical Processing (OLAP) solutions. It can also be used to create cubes using data from data marts/ data warehouse for faster and efficient data analysis.

Cubes:

Cubes are the multi-dimensional data sources which have two basic constituents named dimensions and facts (measures). Dimensions are referred to as Master Tables and facts are referred to as measurable details

Have a look at this blog on Top 7 MSBI Advantages over other BI Tools and clarify your doubts related to it!

Multi- Dimensional expressions (MDX):

MDX is a query language that is used to query a cube

Key features of SSAS:

  • Speed: It takes less time to respond to a query due to the aggregation of the facts
  • Data Analysis: Allows multi-dimensional analysis facilitated by the cubes
  • Automatic Link and display: It provides the facility to automatically link and displays the report
  • Good data model: For better business reporting and analysis a good data model can be created

Become a Business Intelligence Architect

MDX (Multi-dimensional expressions):

It is a query language used for retrieving data from multidimensional databases like OLAP databases

Multi-dimensional database:

It is referred to as a cube which is a foundation of multi-dimensional databases and each cube typically contains more than two dimensions

OLAP cube:

OLAP cube is a technique which is used to hold the data in an optimized form and used to analyze the data with quick response

Check out the top SAS Interview Questions to learn what is expected from SAS professionals!

Star schema:

A schema where every dimension present in the Data Source View (DSV) is directly linked or related to fact or measurable table. It consists of a DE normalized data and can be used in small companies with small databases

Snowflake schema:

It is a schema where some dimensions are linked directly to a fact table and some are indirectly linked to fact tables. It consists of a normalized data and can be used in large companies with big databases

Become a Power BI Master

Star Flake:

It is a hybrid structure which contains a combination or star (DE normalized data) and snowflake (normalized data) schema

Check out the top MSBI Interview Questions to learn what is expected from MSBI professionals!

Data Source Views (DSV):

DSVs enable to create logical view of only the tables involved in the data warehouse design
Key features of SSAS

SSAS Architecture

MDX Expressions:

Statement Expression
To calculate Simple Gross Profit [Measures].[Sales Amount] – [Measures].[Total Product Cost]
To calculate sales in a particular country e.g. (Canada) ([Measures].[Sales Amount], [Customer].[Country].&[Canada])
To calculate year to date sales of any level of date hierarchy AGGREGATE(

PeriodsToDate( [Date].[Calendar Hierarchy].[Year],

[Date].[Calendar Hierarchy].CurrentMember ),            ([Measures].[Sales]))

Alternate year to date expression AGGREGATE (
YTD ([Date Order].[Calendar].CurrentMember),            Measures.[Sales Amount])
To check the product ranking IIF (Product.Product.CurrentMember IS
Product.Product.[All],NULL,
IIF (Measures.[Sales Amount] = 0, NULL, RANK(Product.Product.CurrentMember, ORDER (Product.Product.Members, Measures.[Sales Amount]))))
Sales from 365 days ago (ParallelPeriod([Invoice Date].[Date Hierarchy].[Date], 365, [Invoice Date].[Date Hierarchy].CurrentMember), [Measures].[Sales Amount])
Sales done in the previous period (Measures.[Sales Amount], [Date Order].[Calendar].CurrentMember.PrevMember)
To view top 10 selling products TopCount (Product.Product.Product.Members,10,Measures.[Sales Amount])
To display the average sales from three years till date Avg({ParallelPeriod( [Date].[Date].[Year], 3,StrToMember(“[Date].[Date].&[” + Format(now(), “yyyyMMdd”) + “]”)) :StrToMember(“[Date].[Date].&[” + Format(now(), “yyyyMMdd”) + “]”)} , [Measures].[Sales Count])
Drillthrough Action caption ‘Get Sales Details for’+[Product].[Product].CurrentMember.Member_Caption
To change SSAS Calculation text color IIF([Measures].[Profit Percentage] < .40, 255 , 0)
Changing a calculation using scope statement SCOPE ([Measures].[Scope Profit]);THIS = ([Measures].[Sales Amount] – [Measures].[Standard Product Cost]);END SCOPE;
Clear ration value when at all levels SCOPE ([Customer].[Customer Geography].[All], Measures.RatioOverParent);THIS = NULL
SSAS KPI Value Expression [Measures].[Sales Amount] * 1.2
SSAS KPI Goal Expression Case
When IsEmpty
(ParallelPeriod
([Date Order].[Fiscal].[Fiscal Year],
1,[Date Order].[Fiscal].CurrentMember))
Then [Measures].[Sales Amount]
Else 1.10 *
([Measures].[Sales Amount],
ParallelPeriod([Date Order].[Fiscal].[Fiscal Year],
1,[Date Order].[Fiscal].CurrentMember))
End
SSAS KPI Status Expression Case
When KpiValue( “Sales Revenue YTD” ) / KpiGoal( “Sales RevenueYTD” ) > 1
Then 1
When KpiValue( “Sales Revenue YTD” ) / KpiGoal( “Sales Revenue YTD” ) <= 1
And
KpiValue( “Sales Revenue YTD” ) / KpiGoal( “Sales Revenue YTD” ) >= .85
Then 0
Else -1
End
SSAS KPI Trend Expression Case
When IsEmpty
(ParallelPeriod
([Date Order].[Fiscal].[Fiscal Year],
1,[Date Order].[Fiscal].CurrentMember))
Then 0
When VBA!Abs
((KpiValue( “Sales Revenue YTD” )- (KpiValue( “Sales Revenue YTD” ),
ParallelPeriod( [Date Order].[Fiscal].[Fiscal Year],                1, [Date Order].[Fiscal].CurrentMember)))          /(KpiValue( “Sales Revenue YTD” ),            ParallelPeriod
( [Date Order].[Fiscal].[Fiscal Year],
1,[Date Order].[Fiscal].CurrentMember))) <=.02
Then 0
When (KpiValue( “Sales Revenue YTD” )- (KpiValue( “Sales Revenue YTD” ),
ParallelPeriod
( [Date Order].[Fiscal].[Fiscal Year], 1,
[Date Order].[Fiscal].CurrentMember)))
/(KpiValue( “Sales Revenue YTD” ),
ParallelPeriod( [Date Order].[Fiscal].[Fiscal Year],1,[Date Order].[Fiscal].CurrentMember)) >.02
Then 1
Else -1
End

Download a Printable PDF of this Cheat Sheet

With this, we come to an end of SSAS Cheat sheet. To get in-depth knowledge, check out our interactive, live-online SSAS Training here, that comes with 24*7 support to guide you throughout your learning period. Intellipaat’s SSAS online training equips you with various analysis techniques, discovery trends and building OLAP cubes for generating business insights.

Course Schedule

Name Date Details
MSBI Certification Training Course 30 Mar 2024(Sat-Sun) Weekend Batch
View Details
MSBI Certification Training Course 06 Apr 2024(Sat-Sun) Weekend Batch
View Details
MSBI Certification Training Course 13 Apr 2024(Sat-Sun) Weekend Batch
View Details