SQL Server SSAS (Multidimensional MDX) - an Introduction from Udemy

What's inside

Learning objectives

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.

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.

Syllabus

Hello. I'll be introducing myself, and introducing the course.

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 Udemy

Introduction to Udemy

Do you want auto-translated subtitles in more languages?

Which version of Visual Studio should I install?

We'll install the Developer edition of SQL Server - for free. This includes everything we need for SSAS.

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.

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

Now that's the back engine has been installed, we've got to install SSMS (SQL Server Management Studio).

Installing Visual Studio 2022

Installing SSAS in Visual Studio 2022

Is your Visual Studio a trial version? No!

AdventureWorks

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.

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

We'll use AdventureWorks to create our first cube, going through a lot of features of SSAS using Visual Studio.

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

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.

Now that we've got a project, we need to connect to some data. Let's create a data connection with our AdventureWorks database.

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

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 Password

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.

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.

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

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

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 update

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

Now it's your turn... It's down to you to create a cube based on a Data Warehouse. Then we can enhance it.

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.

As a reminder, here are your instructions.

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

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.

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.

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.

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 3

Practice Activity Number 3

Practice Activity Number 3 - Solution

Creating MDX Statements - Measures

MDX Resources

Default measure, and using the WHERE clause for measures

Adding measures onto the Column axis, and creating a Set of measures

"Go"

Creating a temporary measure using MDX

Creating a permanent measure using Visual Studio

Practice Activity Number 4

Practice Activity Number 4 - Solution

Creating MDX Statements - Dimensions

Referring to Dimensions, Hierarchies, Levels and Members

Quiz - referring to Dim Date dimensions, hierarchy, levels and members

Visual Studio - changing the appearance and order of months of the year

Referring to multiple members and Using the Range : operator

Getting rid of NULLs - using Non Empty

Creating a tuple

Changing the aggregation using tuples

Practice Activity Number 5

Practice Activity Number 5 - Solution

Hierarchies

Parents and children

Ancestors and Ascendants

Descendants, firstchild and lastchild

prevmember
nextmember
lag
lead
head
tail
cousin

Practice Activity Number 6

Practice Activity Number 6 - Solution

Date hierarchies

Getting YTD and MTD to work

LastPeriods - going into the past

ParallelPeriod, and combining with YTD

Practice Activity Number 7

Practice Activity Number 7 - Solution

Creating and joining sets

topcount, toppercent, bottomcount, bottompercent

Creating sets

UNION and RANK

Intersect and Except

Practice Activity Number 8

Practice Activity Number 8 - Solution

Functions

Subcubes

Hierarchize

Order

Filter

format_string

Formatting using custom coding

Key Performance Indicators

Introduction to KPIs

Revisiting parallelperiod

The CASE statement

IIF - an alternative to CASE

IsEmpty

Introduction to Practice Activity 10

Practice Activity Number 10

Practice Activity Number 10 - Solution

Adding KPIs into your SSAS Cube

How complicated can your KPI definition go?

Using Cubes in Excel and SSRS

Opening a cube in Excel, both through Visual Studio and making a connection

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Teaches fundamentals in OLTP and OLAP databases, which are essential for handling structured data

Explores SSAS and MDX Cubes, which are utilized in various industries for data analysis

Covers cube creation, data processing, and analysis using practical examples

Emphasizes hands-on exercises and interactive materials, allowing learners to apply concepts directly

Provides a solid foundation for beginners and helps intermediate learners enhance their skills in data analysis

Utilizes AdventureWorks database, which is widely used in data analysis and is familiar to many learners

Reviews summary

Well received intro to sql server ssas

According to students, this introduction to SQL Server SSAS is well received. Learners say this course's videos are well paced and helpful in building a foundation in MDX. Reviewers appreciate the instructor's teaching style, finding that their explanations make complex topics easy to understand.

Videos are paced appropriately and helpful for learning MDX.

Instructor is engaging and provides clear explanations.

"Well thought course – your personal teaching technique makes life so easy that anyone could understand you easily and gain ample knowledge."

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in SQL Server SSAS (Multidimensional MDX) - an Introduction with these activities:

Install SSAS Development Environment

Show steps

Installing the necessary development environment will ensure you have the tools you need to complete the hands-on activities and projects in this course.

Show steps

Download and install the SSAS development environment.
Verify that the installation is successful.

Read "The Data Warehouse Toolkit 3rd Edition"

Show steps

Reading this comprehensive guide to data warehousing and dimensional modeling will provide a strong theoretical foundation for understanding the concepts and techniques covered in this course.

View The Data Warehouse Toolkit on Amazon

Show steps

Read the book thoroughly.
Take notes and highlight key concepts.

Review T-SQL Syntax

Show steps

Refreshing your knowledge of T-SQL syntax will help you recall the fundamentals and prepare you to use MDX and SQL effectively throughout the course.

Show steps

Review T-SQL syntax.
Practice writing some basic T-SQL queries.

Five other activities

Expand to see all activities and additional details

Show all eight activities

Write a Summary of the Different Types of Cubes

Show steps

Writing a summary of the different types of cubes used in data warehousing and SSAS will help you solidify your understanding of the strengths and use cases of each cube type.

Show steps

Research the different types of cubes.
Write a concise and informative summary.

Solve a List of Built-in Functions

Show steps

Solving a list of built-in functions will help improve your mastery of the syntax and basic concepts of MDX by testing your ability to apply functions in a practical scenario.

Show steps

Review the available MDX built-in functions.
Practice applying these functions in a practical scenario.

Discuss the Role of KPIs in SSAS

Show steps

Engaging in discussions with peers will help you gain diverse perspectives, identify areas where you need to improve your understanding, and build confidence in working with cubes.

Show steps

Find a study partner or group.
Schedule regular peer sessions.

Create a Pyramid Chart

Show steps

Creating a pyramid chart in SSAS or another business intelligence tool will help you visualize and analyze hierarchical data, such as sales by region or product category, in a visually appealing and intuitive way.

Browse courses on Charting

Show steps

Learn about the concept of a pyramid chart.
Follow a tutorial to create a pyramid chart using SSAS or a third-party tool.

Dashboard for a Business Department

Show steps

Creating a dashboard for a specific business department will allow you to apply your knowledge of cubes to a real-world scenario, enhancing your practical skills and understanding of business intelligence.

Show steps

Identify a business department and its data needs.
Design and develop a dashboard.

Career center

Learners who complete SQL Server SSAS (Multidimensional MDX) - an Introduction will develop knowledge and skills that may be useful to these careers:

Data Analyst

Data Analysts collect, transform, and analyze data from a variety of sources to identify trends and patterns. They use their findings to make recommendations that can help businesses improve their operations, make better decisions, and increase their profits. This course can help you develop the skills you need to become a successful Data Analyst, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for Data Analyst

Business Intelligence Analyst

Business Intelligence Analysts use data analysis to help businesses make better decisions. They identify trends and patterns in data, and develop reports and visualizations that help stakeholders understand the data. This course can help you develop the skills you need to become a successful Business Intelligence Analyst, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for Business Intelligence Analyst

Data Scientist

Data Scientists use data analysis to solve complex problems. They develop models and algorithms to predict future outcomes, and they use their findings to make recommendations that can help businesses improve their operations, make better decisions, and increase their profits. This course can help you develop the skills you need to become a successful Data Scientist, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for Data Scientist

Database Administrator

Database Administrators are responsible for the maintenance and performance of databases. They ensure that data is stored securely and efficiently, and that it is available to users when they need it. This course can help you develop the skills you need to become a successful Database Administrator, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for Database Administrator

Data Warehouse Developer

Data Warehouse Developers design and develop data warehouses. They work with data analysts and business stakeholders to identify the data that needs to be stored in the data warehouse, and they develop the ETL (extract, transform, load) processes that are used to populate the data warehouse. This course can help you develop the skills you need to become a successful Data Warehouse Developer, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for Data Warehouse Developer

ETL Developer

ETL Developers develop the ETL (extract, transform, load) processes that are used to populate data warehouses. They work with data analysts and business stakeholders to identify the data that needs to be extracted from source systems, and they develop the transformations that are needed to clean and prepare the data for loading into the data warehouse. This course can help you develop the skills you need to become a successful ETL Developer, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for ETL Developer

Data Architect

Data Architects design and develop the data architecture for organizations. They work with business stakeholders to identify the data that needs to be collected and stored, and they develop the data models and schemas that are used to organize and manage the data. This course can help you develop the skills you need to become a successful Data Architect, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for Data Architect

Software Engineer

Software Engineers design, develop, and maintain software applications. They work with users to identify the requirements for the software, and they develop and test the software to ensure that it meets the requirements. This course can help you develop the skills you need to become a successful Software Engineer, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for Software Engineer

Systems Analyst

Systems Analysts work with businesses to identify and solve problems. They analyze business processes and systems, and they develop recommendations for improvements. This course can help you develop the skills you need to become a successful Systems Analyst, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for Systems Analyst

Project Manager

Project Managers plan and manage projects. They work with stakeholders to identify the goals of the project, and they develop and execute the project plan. This course can help you develop the skills you need to become a successful Project Manager, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for Project Manager

Business Analyst

Business Analysts work with businesses to identify and solve problems. They analyze business processes and systems, and they develop recommendations for improvements. This course can help you develop the skills you need to become a successful Business Analyst, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for Business Analyst

Data Engineer

Data Engineers build and maintain the data infrastructure that is used to store and process data. They work with data analysts and business stakeholders to identify the data that needs to be stored and processed, and they develop the systems and processes that are needed to do so. This course can help you develop the skills you need to become a successful Data Engineer, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for Data Engineer

Database Designer

Database Designers design and develop the databases that are used to store and manage data. They work with data analysts and business stakeholders to identify the data that needs to be stored in the database, and they develop the database schema that is used to organize and manage the data. This course can help you develop the skills you need to become a successful Database Designer, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for Database Designer

Information Security Analyst

Information Security Analysts protect the data and systems of organizations from unauthorized access, use, disclosure, disruption, modification, or destruction. They develop and implement security policies and procedures, and they monitor and investigate security breaches. This course can help you develop the skills you need to become a successful Information Security Analyst, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for Information Security Analyst

IT Auditor

IT Auditors evaluate the security and efficiency of IT systems. They review IT systems and processes, and they make recommendations for improvements. This course can help you develop the skills you need to become a successful IT Auditor, including how to create and use cubes in SSAS, perform data analysis using MDX, and create reports using SQL Server Reporting Services.

See salaries and explore the career path for IT Auditor