Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model)
4.2 (17 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
156 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model) to your Wishlist.

Add to Wishlist

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

Visualise Data in Power Pivot for Excel or SQL Server Analytical Services w/ DAX functions in PowerPivot. 70-779, 70-768
Bestselling
4.2 (17 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
156 students enrolled
Created by Phillip Burton
Last updated 6/2017
English
Current price: $10 Original price: $45 Discount: 78% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 5.5 hours on-demand video
  • 5 Articles
  • 4 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Extract data from multiple tables.
  • Create connections between the tables.
  • Develop the model using DAX functions.
  • Create analyses in Excel.
View Curriculum
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 is the target audience?
  • 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.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
85 Lectures
05:18:33
+
Welcome
7 Lectures 23:10

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

Preview 02:08

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:13

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:30
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:11

Practice Activity Number 1 - Solution
02:57
+
Using the Tabular Model (SSAS in SQL Server)
10 Lectures 32:43
Introduction
02:01


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

Attaching AdventureWorksDW
03:57

Creating and deploying our first model in SSAS
04:57

Adding additional tables
02:28


Practice Activity Number 2
00:21

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:53
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:32

Do you want more practice setting your data model?
00:06
+
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
4 More Sections
About the Instructor
Phillip Burton
4.4 Average rating
3,235 Reviews
45,567 Students
25 Courses
Best Selling Instructor - over 45,000 students so far

Phillip is a Computing Consultant providing expert services in the development of computer systems and data analysis. He is a Microsoft Certified Technology Specialist. He has also been certified as a Microsoft Certified Solutions Expert for Business Intelligence, Microsoft Office 2010 Master, and as a Microsoft Project 2013 Specialist.

He enjoys investigating data, which allows me to maintain up to date and pro-active systems to help control and monitor day-to-day activities. As part of the above, he also developed and maintained a Correspondence Database in Microsoft Access and SQL Server, for viewing job-related correspondence (110,000 pdfs in one job) by multiple consultants and solicitors.

He has also developed expertise and programmes to catalogue and process and control electronic data, large quantities of paper or electronic data for structured analysis and investigation.

He is one of 9 award winning Experts for Experts Exchange's 11th Annual Expert Awards and was one of Expert Exchange's top 10 experts for the first quarter of year 2015.

His interests are working with data, including Microsoft Excel, Access and SQL Server.