SQL Server SSAS (Multidimensional MDX) - an Introduction
4.4 (1,001 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
5,368 students enrolled

SQL Server SSAS (Multidimensional MDX) - an Introduction

Create cubes from databases, analyse them in Excel, SSRS etc. using SSAS MDX (a Business Intelligence tool). Exam 70-768
Bestseller
4.4 (1,001 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
5,368 students enrolled
Created by Phillip Burton
Last updated 5/2020
English
English
Current price: $55.99 Original price: $79.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 8 hours on-demand video
  • 13 articles
  • 5 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
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.
Course content
Expand all 91 lectures 07:59:56
+ Introduction
4 lectures 06:31

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.

Preview 02:39
Welcome to Udemy
00:35
Introduction to Udemy
02:00
Which version of Visual Studio should I install?
01:17
+ Installing SQL Server
9 lectures 40:03

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.

Preview 06:09

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).

Preview 09:45

Now that's the back engine has been installed, we've got to install SSMS (SQL Server Management Studio), Visual Studio, and SSDL (SQL Server Data Tools). It takes around 30 minutes, but here's the edited version.

Preview 05:16
Installing Visual Studio 2019
04:52
Installing SSAS in Visual Studio 2019
03:00
Is your Visual Studio a trial version? No!
01:36
AdventureWorks
00:11

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.

Preview 03:40

Let's have a look at AdventureWorks, using SQL Server Management Studios, and have a look at the tables used.

Investigating AdventureWorks in SSMS.
05:34
+ Creating our first cube
11 lectures 58:51

Every cube needs to be creating within a project. Let's create our first project.

Creating our first project
03:19

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.

Looking at our working environment, and Facts, Measures, Dimensions and Cubes
07:51

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 Connection
05:33

Just like a painter needs colors, a cube designer needs tables. Let's add some tables into a Data Source View.

Creating a Data Source View
06:25

We've got a data source and a dimension - let's now create a cube, and pull a couple of tables into the cube.

Creating a Cube
05:58

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.

Creating a role, and playing with the cube
05:34

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.

Preview 02:25

Let's add an extra table and create a dimension from this table.

Adding an extra table, and Creating a dimension
04:35

Now we've created a dimension, let's re-process the cube, and then view it in Excel again.

Updating the cube, and using Excel again.
05:28

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.

Looking at the cube using SSMS
07:00

What else can we do with cubes? Why don't we create a report as well, using SQL Server Reporting Services.

Looking at the cube in SSRS
04:43
+ Creating a new cube, and enhancing it.
10 lectures 01:02:03

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 - Let's do it again!
07:33

As a reminder, here are your instructions.

Preview 00:14

Let's run through, and create this second cube.

Practice Activity - The Solution
07:05

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.

Updating dimensions, and creating translations
12:48

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 new table into the Data Source View, and replacing it with a query
05:59

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 - two levels
06:08

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.

Adding a hierarchy - three levels
07:14
Introduction to Practice Activity 3
02:53
Practice Activity Number 3
00:22
Practice Activity Number 3 - Solution
11:46
+ Creating MDX Statements - Measures
8 lectures 37:35
MDX Resources
00:13
Default measure, and using the WHERE clause for measures
09:47
Adding measures onto the Column axis, and creating a Set of measures
07:08
"Go"
00:12
Creating a temporary measure using MDX
08:12
Creating a permanent measure using Visual Studio
05:42
Practice Activity Number 4
00:19
Practice Activity Number 4 - Solution
06:01
+ Creating MDX Statements - Dimensions
9 lectures 55:35
Referring to Dimensions, Hierarchies, Levels and Members
08:23
Quiz - referring to Dim Date dimensions, hierarchy, levels and members
04:00
Visual Studio - changing the appearance and order of months of the year
10:21
Referring to multiple members and Using the Range : operator
07:29
Creating a tuple
09:57
Changing the aggregation using tuples
07:35
Practice Activity Number 5
00:25
Practice Activity Number 5 - Solution
03:18
+ Hierarchies
6 lectures 29:49
Parents and children
05:39
Ancestors and Ascendants
04:47
Descendants, firstchild and lastchild
03:55
  • prevmember
  • nextmember
  • lag
  • lead
  • head
  • tail
  • cousin
Siblings functions
09:26
Practice Activity Number 6
00:31
Practice Activity Number 6 - Solution
05:31
+ Date hierarchies
5 lectures 27:11
Getting YTD and MTD to work
08:41
LastPeriods - going into the past
04:24
ParallelPeriod, and combining with YTD
06:16
Practice Activity Number 7
00:19
Practice Activity Number 7 - Solution
07:31
+ Creating and joining sets
6 lectures 30:08
topcount, toppercent, bottomcount, bottompercent
06:45
Creating sets
06:25
UNION and RANK
04:47
Intersect and Except
04:28
Practice Activity Number 8
00:23
Practice Activity Number 8 - Solution
07:20
+ Functions
6 lectures 44:44
Subcubes
08:05
Hierarchize
05:33
Order
07:04
Filter
05:53
format_string
08:47
Formatting using custom coding
09:22
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 2017 for FREE. Previous versions cost $100.
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.

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 to get information about? 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.

It will assist with the "Design a multidimensional business intelligence (BI) semantic model" section of Microsoft exam 70-768 "Developing SQL Data Models".

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.