Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model)
4.5 (294 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.
2,120 students enrolled

Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model)

Visualise Data in Power Pivot for Excel or SSAS with DAX. 70-779, 70-768 exams. Business Intelligence PowerPivot tool.
Bestseller
4.5 (294 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.
2,120 students enrolled
Created by Phillip Burton
Last updated 4/2020
English
English
Current price: $69.99 Original price: $99.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 5.5 hours on-demand video
  • 6 articles
  • 8 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
  • Extract data from multiple tables.
  • Create connections between the tables.
  • Develop the model using DAX functions.
  • Create analyses in Excel.
Requirements
  • You need to be able to use Microsoft Excel, at least at a basic level - but the more, the better.
  • If you are going to use SSAS, then you need to know how to install programs.
Description

Reviews:

"In just the first few lessons, I have already been able to apply what I have learned at my job. Thank you for this course!" -- Cynthia Smith

"The Instructor is easy to understand and he gives exercises to test your understanding which is what I prefer rather than just listening to lectures. I will definitely take more of Mr Burton's classes as I like his teaching style." -- Tamuka Mazana

------------------

This course covers one of the most useful add-ins in Microsoft Excel, PowerPivot, and the Tabular model in SQL Server Analytical Services.

We'll activate PowerPivot if you have the right version of Excel, and if not, then we'll install SQL Server on your computer, which can do the same job. We'll install the Developer edition, which used to cost $100, for free.

After PowerPivot is activated, or SQL Server is installed, we'll create our first analysis in Excel in just a matter of minutes.

After only these first few minutes, you will be streets ahead of anyone who doesn't know anything about Power Pivot.

After this introduction, we'll go into some detail into PowerPivot or SSAS - the initial data, and the various options that are available to you. We will go into advanced options that most people don't even know about, but which are very useful.

Then we'll develop DAX formulas, to further extend the model. A lot of the formulas are based on Excel formulas, but there are several additional things too.

By the end, you will be an Expert user of both PowerPivot and SSAS, able to create reliable analyses which are able to be drilled-down quickly, and you'll be able to help others with their data analysis.

It will assist with the Model Data section of Microsoft exam 70-779 "Analysing and Visualizing Data with Microsoft Excel".

It will also assist with the "Design a tabular BI semantic model" and "Develop queries using Data Analysis Expressions (DAX)" section of Microsoft exam 70-768 "Developing SQL Data Models".

Who this course is for:
  • This course is for people who want to create analyses.
  • You either want to do this in Excel, or you want to learn about SQL Server Analytical Services (Tabular edition).
  • This course isn't for you if you want to learn SSAS (MDX version) - there are other courses for this.
Course content
Expand all 89 lectures 05:28:12
+ Welcome
8 lectures 23:32

Hello. I'll introduce myself and what we will be learning in this course.

Preview 02:08
Welcome to Udemy
00:35

Before we start, let's have a quick look around Udemy, and find out how you can get in touch with me.

Introduction to Udemy
02:00
What will we learn?
03:09
Software Requirements - PowerPivot
06:38
Source data
02:14
Installing PowerPivot on Excel 2010
04:05
+ Creating our first model using PowerPivot (Excel)
7 lectures 29:31
Creating our first model in PowerPivot
03:38
Using our first model in Excel
03:42
Creating and deploying our first model in Excel 2010
04:41

For big datasets (over 8,000,000 rows), make sure the data is sorted on key fields - that reduces the amount of indexing required.

Adding a second table - Importing Data from SQL Server
08:19
Importing Data from SQL Server
06:02
Practice Activity Number 1
00:12
Practice Activity Number 1 - Solution
02:57
+ Using the Tabular Model (SSAS in SQL Server)
10 lectures 32:31
Introduction
02:01

We should then restore the AdventureworksDW database into SSMS so that we can use it in SSAS. Let's see how to attach databases (and please restore the DW database).

Attaching AdventureWorksDW
04:29
Creating and deploying our first model in SSAS
04:57
Adding additional tables
02:28
Practice Activity Number 2
00:23
Practice Activity Number 2 - Solution
03:43

Beware that field types might change - how to prevent this using Data Validation

Importing Data from Excel
03:10
Having problems importing from Excel?
07:34
+ Developing the Data Model
9 lectures 36:52
Practice Activity Number 3
00:20
Filtering, sorting and formatting fields
07:11

When you leave PowerPivot and go into the Excel data and then go back in PowerPIvot, it automatically refreshes.

Refreshing (or processing) data
04:08
Creating quick measures
05:09
Perspectives
06:57
Hierarchies
07:04
Practice Activity Number 4
00:31
Do you want more practice setting your data model?
00:05
+ Pivot Tables functionality
5 lectures 18:27
PivotTable structure, and non-Visual Totals
06:05

Why would you want to show no data in rows or columns? When you are looking for gaps, for absence of data (for no sales of a product, for example).

Show no data in rows and columns
02:58
Create running totals
02:23
Slicers and Timelines
03:15
Using formulas to query Data Model (Advanced)
03:46
+ An introduction to DAX functions, including Logical functions
8 lectures 28:40
DAX functions - An Introduction
02:54
Calculated columns - an introduction
02:42

+ - * / & and implicit conversion

Basic operators
04:29

BLANKs cascade into future calculations - but you can use ISBLANK to test for them explicitly.

BLANK and ISBLANK
05:00
AND, OR and NOT
03:46
SWITCH
04:09

FALSE, TRUE, IFERROR

Other functions
02:47
+ Statistical functions
6 lectures 20:46
Measures - an introduction
03:58

SUM, COUNT, COUNTA, AVERAGE, AVERAGEA, MIN, MINA, MAX, MAXA, MEDIAN

Aggregation of columns
04:23

SUMX, COUNTX, AVERAGEX, MINX, MAXX, MEDIANX

Aggregation of calculations
04:40
DISTINCTCOUNT
03:32
RANKG.EQ
01:09

STDEV.P, STDEV.S, VAR.P, VAR.S, STDEVX.P, STDEVX.S, VARX.P, VARX.S, 

Other functions
03:04
+ Mathematical functions
5 lectures 16:49

CEILING, ISO.CEILING, FLOOR, INT, MROUND, ROUND, ROUNDDOWN, ROUNDUP, TRUNC

Rounding functions
05:50
Division functions - MOD and QUOTIENT
01:31
SIGN (and use with SWITCH) and ABS
03:54

EXP, LN, LOG, LOG10, SQRT, SQRTPI, POWER

Exponential functions
01:48


CURRENCY, FACT, PI, RAND, RANDBETWEEN


Other functions
03:46
+ Text functions
4 lectures 18:20

FIND, SEARCH

Text searching
05:44

LEFT, LEN, MID, RIGHT, REPLACE

Text extraction and substitution
05:13

FIXED,  FORMAT, VALUE

Text conversion
06:02

CONCATENATE, EXACT, LOWER, UPPER, REPT, TRIM




Other functions
01:21
+ Information Functions
2 lectures 05:11
ISERROR and LOOKUPVALUE
03:22
Other functions
01:49