Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

SQL Server SSAS (Multidimensional MDX) - an Introduction

Name: SQL Server SSAS (Multidimensional MDX) - an Introduction
Rating: 4.6 (2522 reviews)

Create cubes from databases, analyse them in Excel, SSRS etc. using SSAS MDX (a Business Intelligence tool)

Created byPhillip Burton • over one million students so far, I Do Data Limited

Last updated 2/2026

English

EnglishSpanish [Auto],

What you'll learn

Create cubes in SSAS, based on fact and dimension tables.
Process the cubes, and analyse them in Excel, SSRS, and access them in SSMS.
Learn some of the more advanced items, such as translations.
Learn MDX language to query your cubes.

Course content

13 sections • 92 lectures • 7h 54m total length

Introduction2:25
Hello, and thank you for joining me in this course. I'll be introducing myself, and looking at what we will cover in this course.
Welcome to Udemy0:35
Introduction to Udemy3:05
Learn how to navigate this SQL Server SSAS (Multidimensional MDX) introduction course on Udemy, adjust playback speed and volume, access subtitles, notes, resources, Q&A, announcements, and obtain your certificate.
Do you want auto-translated subtitles in more languages?1:20
Which version of Visual Studio should I install?0:42

Downloading SQL Server Developer 20228:15
It used to be that we had to install a cut-down version of SQL Server. Instead, let's now install a version with the full functionality of the Enterprise edition - for personal use only, though.
Installing SQL Server Developer 202210:22
Now let's go through the process of installing SQL Server. I'll also go through the various editions of SQL Server (e.g. 2008, 2012).
Installing SQL Server Management Studio4:51
Now that's the back engine has been installed, we've got to install SSMS (SQL Server Management Studio).
Installing Visual Studio 20226:12
Install Visual Studio 2022 to work with SQL Server Analysis Services, Integration Services, and Reporting Services. Learn the 64-bit Windows requirements, hardware needs, download steps, and the free community edition.
Installing SSAS in Visual Studio 20223:19
Install the SSAS extension for Visual Studio 2022, download the Microsoft Analysis Services Projects 2022, close and relaunch Visual Studio, and create MDX analysis or Analysis Services projects.
Is your Visual Studio a trial version? No!1:36
Learn that Visual Studio 2019 Community Edition is not a trial. Update the licence and sign in with a Microsoft account to obtain a full licence.
AdventureWorks0:11
Downloading AdventureWorks3:40
We are going to use two versions of AdventureWorks - the first we will use to create our first cube, and the second will be for a practice activity. Let's download them now.
Investigating AdventureWorks in SSMS.5:34
Let's have a look at AdventureWorks, using SQL Server Management Studios, and have a look at the tables used.

Creating our first project3:19
Every cube needs to be creating within a project. Let's create our first project.
Looking at our working environment, and Facts, Measures, Dimensions and Cubes7:51
Now that we've got a project, let's have a look around at the various panes. Also, just as T-SQL has its own terminology (such as "window functions"), SSAS has its own words. Let's get used to facts, measures, dimensions and cubes.
Creating a Data Connection6:47
Now that we've got a project, we need to connect to some data. Let's create a data connection with our AdventureWorks database.
Creating a Data Source View6:25
Just like a painter needs colors, a cube designer needs tables. Let's add some tables into a Data Source View.
Creating a Cube5:58
We've got a data source and a dimension - let's now create a cube, and pull a couple of tables into the cube.
Your Windows User Name and Password0:29
Creating a role, and playing with the cube5:34
So on my computer, I find that I can't see the cube that I've just created. Let's add a role for my account, and change the impersonation for the data source, and see whether that solves the problem. Then let's take a quick look at the cube.
Looking at the cube using Excel2:25
Now we've got a cube, let's look at it. First of all we'll open Excel from Visual Studio directly. Then we'll go to Excel 2016 and we'll add a database connection, and create a PivotTable, and see how that is different from traditional PivotTables. We'll do the same again in Excel 2013 to see how that it is different.
Adding an extra table, and Creating a dimension4:35
Let's add an extra table and create a dimension from this table.
Updating the cube, and using Excel again.5:28
Now we've created a dimension, let's re-process the cube, and then view it in Excel again.
Looking at the cube using SSMS7:00
Not only can you use T-SQL in SSMS, you can also use MDX, creating SELECT statements from your cube. But be warned - the syntax may look like T-SQL, but it is actually quite different.
Visual Studio 2022 update0:23
Looking at the cube in SSRS4:43
What else can we do with cubes? Why don't we create a report as well, using SQL Server Reporting Services.

Practice Activity - Let's do it again!7:33
Now it's your turn. Let's create a second cube, this one using the AdventureWorksDW file. We'll go through what you have got to do - and then, over to you.
Practice Activity - your instructions0:14
As a reminder, here are your instructions.
Practice Activity - The Solution7:05
Let's run through, and create this second cube.
Updating dimensions, and creating translations12:48
Existing dimensions can be modified, by adding additional attributes. But what if one of the attributes you want to add it merely a translation of an existing one? Instead of addition translation attributes, let's translate an attribute instead.
Adding a new table into the Data Source View, and replacing it with a query5:59
Now that we've got the cube working, let's add another table into the DSV, and then after trying to create a new dimension, we'll replace this table with a query.
Adding a hierarchy - two levels6:08
Hierarchies can be very useful in organising data, such as dates into years, months and days, and cities into countries, states and cities. Let's create a 2-level hierarchy from our date dimension.
Adding a hierarchy - three levels7:14
Now let's change our hierarchy from 2 levels (year - date) to 3 levels (year - month - date). It's now at easy as it sounds. Let's find out what problems we encounter.
Introduction to Practice Activity 32:53
Create a new PracticeCube using the same data source, recreate a data source view with DimDate, DimGeography, DimReseller, DimSalesTerritory, and FactResellerSales, then define four measures and build hierarchies.
Practice Activity Number 30:22
Practice Activity Number 3 - Solution11:46
Build a multidimensional ssas cube from an existing data source, creating a data view and dimensions (dimDate, dimGeography, dimReseller) with measures like order quantity and sales amount, plus translation support.

MDX Resources0:13
Default measure, and using the WHERE clause for measures9:47
Explore how MDX uses the where clause to select measures in a multidimensional cube, and set the default measure in SSAS via Visual Studio.
Adding measures onto the Column axis, and creating a Set of measures7:08
Learn how to add multiple measures to the column axis in SSAS MDX, create a set of measures with curly brackets, and avoid mixing measures with dimensions.
"Go"0:12
Creating a temporary measure using MDX8:12
Learn how to create a temporary MDX measure that shows daily balance by computing units in minus units out, using with member and create member with axis zero.
Creating a permanent measure using Visual Studio5:42
Create a permanent measure in a multidimensional cube by adding a calculated member in Visual Studio, using MDX, then process and deploy for use in Excel or SSRS.
Practice Activity Number 40:19
Practice Activity Number 4 - Solution6:01
Explore MDX measures in SSAS: set default or arbitrary measures, create a calculated member for average sales amount, and reprocess the cube to query with measures.

Referring to Dimensions, Hierarchies, Levels and Members8:30
Explore how to reference dimensions, hierarchies, levels, and members in an MDX query, using hard and soft brackets, All expressions, and specific members like colour and Black.
Quiz - referring to Dim Date dimensions, hierarchy, levels and members4:00
Delve into dim date concepts in MDX, exploring hierarchies, levels, and members, and troubleshoot common errors when querying calendar year and month members within the cube.
Visual Studio - changing the appearance and order of months of the year10:21
Learn to customize the month dimension in SSAS by adding a month year field, using a user-friendly name, and ordering by the date key for correct chronology.
Referring to multiple members and Using the Range : operator7:29
Getting rid of NULLs - using Non Empty4:07
Use non empty to remove nulls in mdx by filtering month number of year members, manage all and grand total in a set, and eliminate zeros.
Creating a tuple9:57
Learn to build MDX queries by combining calendar year and calendar quarter on axes using a tuple, with crossjoin or multiply equivalents and non empty filters to remove empty results.
Changing the aggregation using tuples7:35
Explore how to create a new member using tuple syntax in mdx, count distinct items with distinctcount, and narrow a measure by calendar years, the date key level, or colours.
Practice Activity Number 50:25
Practice Activity Number 5 - Solution3:18
Learn to build MDX queries with the Total Product Cost measure on columns and English Country Region Name on rows, including all members, ranges, and non empty filtering.

Parents and children5:39
Learn to navigate MDX hierarchies by querying month and date levels, moving up and down with members and children, and tracing parent relationships within a calendar year hierarchy.
Ancestors and Ascendants4:47
Explore how the MDX ancestor and ascendants functions navigate hierarchies, moving to a calendar year level or up a fixed number of levels, then to the top of the hierarchy.
Descendants, firstchild and lastchild3:55
Explore how MDX navigates hierarchies using firstchild, lastchild, and descendants to reach specific levels, explain why .children cannot chain across multiple members, and clarify ancestor versus descendant behavior.
Siblings functions9:26
prevmember
nextmember
lag
lead
head
tail
cousin
Practice Activity Number 60:31
Practice Activity Number 6 - Solution5:31
Practice activity 6 demonstrates building an mdx query in ssas to explore the date dimension, using default member, .parent, .children, lag, lead, and key-based ordering for November 2012.

Getting YTD and MTD to work8:41
Learn to set up a proper date hierarchy in ssas, convert dim date to a time hierarchy, and create calculated measures using aggregate to produce year-to-date, month-to-date, and quarter-to-date totals.
LastPeriods - going into the past4:24
Explore how lastperiods moves back a chosen number of periods, such as seven days, for day-level or year-to-date aggregates, and how a minus sign moves forward with lag and lead.
ParallelPeriod, and combining with YTD6:16
Explore how parallel period lets you go back by year or month and compare year-to-date figures with last year using mdx in sql server ssas.
Practice Activity Number 70:19
Practice Activity Number 7 - Solution7:31
Configure the time dimension in SSAS, process the dimension and cube, and write MDX queries to compute year-to-date totals and parallel-period function results for order quantity.

topcount, toppercent, bottomcount, bottompercent6:45
Learn to use MDX functions topcount, toppercent, bottomcount, and bottompercent to rank items by a measure, with a practical MDX query using units in and English product name.
Creating sets6:25
UNION and RANK4:47
Combine 2012 and 2013 top items with union, then use rank on current member against siblings to compare units in and identify the top items across both years.
Intersect and Except4:28
Learn to compare items across years using intersect and except in MDX, identifying items in both sets and items not in the other set, with ranking and units as measures.
Practice Activity Number 80:23
Practice Activity Number 8 - Solution7:20
Learn to use MDX to identify top regions by order quantity across 2011 and 2012, create a country rank, and build sets with topcount and union.

Subcubes8:05
Explore how to build subcubes by embedding one MDX query into another, control totals with non visual totals, and understand how where clauses affect inner versus outer cubes.
Hierarchize5:33
Create named sets in MDX, use hierarchize to respect the cube hierarchy, and order colors by the hierarchy with an optional post.
Order7:04
Learn to build a basic MDX select, order by measures.units in with the order function, and control hierarchy with an optional third argument, including breaking hierarchy with B.
Filter5:53
Master the MDX filter function to remove nulls and zeros, refining a set of dates with conditions on [Measures].[Units In], such as >0 and between 1000 and 3000.
format_string8:47
Formatting using custom coding9:22
Learn to apply custom number formatting in pivot tables, using placeholders like 0 and #, and locale-specific thousand and decimal separators, plus scaling to kilobytes and megabytes to summarize data.

Requirements

You don't need to know or do anything before starting this course.
I'll show you have to download the Developer Edition of SQL Server 2022 for FREE.
You need to have a Windows computer, or use a Mac computer which can dual-boot into Windows or using Parallel Desktop with Windows. (However, it will not work under Windows ARM - for example, a Mac M1 or M3.)

Description

Reviews:

"Good Stuff Overall!!! In my opinion, the instructor did great with the "How-tos" which helped for sure in grasping the whole concept of how to create cubes, set up data source and source views, dimensions, add attributes etc." -- Lakeside David-Debo

"A fantastic course which gets you rolling very quickly and comfortably, thanks for the short and condensed knowledge delivery. Thanks, Phillip you made SSAS very simple for me." - Anup Kale

"This is really the perfect course for beginners! Easy to learn and very inspirational for further investigations in SSAS. Thank you very much, Phillip!" - Marina Barinova

Welcome to this course on SQL Server SSAS and MDX Cubes – an Introduction.

Please note: This course is not affiliated with, endorsed by, or sponsored by Microsoft.

You may have become experienced with creating SQL statements in SQL Server Management Studio. Building databases is ideal when you want to quickly add data – that’s why they are called OLTP – Online Transaction Processing – they are designed for speed for adding transactions.

But what if you want to get information out quickly? OLTP databases are not based designed for this. What you need instead is a process whereby data is pre-aggregated – in other words, a lot of the calculations you may write have been calculated before you ask for them. It saves a lot of time. It would also be useful if the end user didn’t have to bother with SQL queries, and could use something a bit more hands-on, although retaining something more advanced for advanced users. That’s where cubes come in, full of pre-aggregated data, and SQL Server Analytical Services– or SSAS – (Online Analytical Processing) allows you to make these cubes.

This course is designed for the complete beginner in Multidimensional cubes, or someone who wants to refresh their memory. We’ll create a cube to start with from an ordinary database, and then I’ll ask you to create one from a special database known as a Data Warehouse. We’ll export our cube in SQL Server Management Studio, and into SSRS – and we’ll even have a bit of a look at the more advanced way of querying that is MDX.

Who this course is for:

This is for you if you want to learn about SQL Server Analysis Services.
No previous experience using SSAS is necessary.
It would be useful if you have previously used SQL Server (T-SQL), but far from essential.

SQL Server SSAS (Multidimensional MDX) - an Introduction

What you'll learn

Explore related topics

Course content

Introduction5 lectures • 8min

Installing SQL Server9 lectures • 44min

Creating our first cube13 lectures • 1hr 1min

Creating a new cube, and enhancing it.10 lectures • 1hr 2min

Creating MDX Statements - Measures8 lectures • 38min

Creating MDX Statements - Dimensions9 lectures • 56min

Hierarchies6 lectures • 30min

Date hierarchies5 lectures • 27min

Creating and joining sets6 lectures • 30min

Functions6 lectures • 45min

Requirements

Description

Who this course is for: