Teach on Udemy

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

Learn More

Your cart is empty.

Keep shopping

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

Name: Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model)
Rating: 4.7 (712 reviews)

Visualise Data in Power Pivot for Excel or SSAS with DAX. 70-779, 70-768 exams. Business Intelligence PowerPivot tool.

Highest Rated

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

Last updated 12/2024

English

EnglishPolish [Auto],

What you'll learn

Extract data from multiple tables.
Create connections between the tables.
Develop the model using DAX functions.
Create analyses in Excel.

Course content

14 sections • 92 lectures • 5h 30m total length

Welcome2:26
Hello. I'll introduce myself and what we will be learning in this course.
Welcome to Udemy0:35
Introduction to Udemy3:05
Before we start, let's have a quick look around Udemy, and find out how you can get in touch with me.
Do you want auto-translated subtitles in more languages?1:20
What will we learn?3:09
Software Requirements - PowerPivot6:38
Source data2:14
Activating PowerPivot on Excel 2013 and 20162:43
Enable Power Pivot in Excel 2013 and 2016 by enabling the Microsoft Power Pivot for Excel COM add-in through File Options Add-ins, then select and activate it.
Installing PowerPivot on Excel 20104:05
Install PowerPivot for Excel 2010 by downloading SQL Server PowerPivot (choose the 32-bit or 64-bit version matching your Excel), close Excel, and enable the PowerPivot tab.

Creating our first model in PowerPivot3:38
Create your first Power Pivot model across Excel 2013/2016, Excel 2010, and SQL Server SSDT, loading data into the data model from text files such as Adventureworks internet sales.
Using our first model in Excel3:42
Create your first Power Pivot model in Excel, connect it to a pivot table, choose fields like sales amount and due date, and explore drill-down and formatting options.
Creating and deploying our first model in Excel 20104:41
Create and deploy your first data model in Excel 2010 by importing a text file into the Power Pivot window, building a pivot table, and using fields, slicers, and grouping.
Adding a second table - Importing Data from SQL Server8:19
For big datasets (over 8,000,000 rows), make sure the data is sorted on key fields - that reduces the amount of indexing required.
Importing Data from SQL Server6:02
Import and model data from SQL Server to create a three-table PowerPivot view, linking product subcategory to product and sales, then build a pivot table to summarize total sales.
Practice Activity Number 10:12
Practice Activity Number 1 - Solution2:57

Installing SQL Server 2022 Developer and Visual Studio 20221:02
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:28
Installing SQL Server Management Studio (SSMS)4:51
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.
Installing Visual Studio 20226:12
Installing SSAS in Visual Studio 20223:19
Install the SSAS extension for Visual Studio 2022 from the marketplace, then close and reopen Visual Studio to create MDX analysis and Analysis Services multidimensional or tabular projects.
Is your Visual Studio a trial version? No!1:36

Downloading AdventureWorksDW2:00
Attaching AdventureWorksDW4:29
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).
Visual Studio 20220:12
Creating and deploying our first model in SSAS4:57
Adding additional tables2:28
Add a second table from the same source, import dim product alongside fact internet sales, and drag to create a one-to-many relationship in diagram or model view.
Introduction to Practice Activity1:46
Import two tables from SQL Server and a text file, join them with product subcategory and product category keys, and create a sum of sales amount measure for category pivot.
Practice Activity Number 20:23
Practice Activity Number 2 - Solution3:43
Import data into SSAS, connect dim product subcategory and product category tables, and analyze in Excel with pivot tables showing sales amounts.
Importing Data from Excel3:10
Beware that field types might change - how to prevent this using Data Validation
Having problems importing from Excel?7:34
Learn to fix Excel import issues in the Visual Studio tabular model by installing the 2007 data connectivity components and the Microsoft Access Database Engine, addressing 64/32-bit conflicts.

Practice Activity Number 30:22
Filtering, sorting and formatting fields7:11
Hiding columns from Client Tools, and other column tools5:26
Refreshing (or processing) data4:08
When you leave PowerPivot and go into the Excel data and then go back in PowerPIvot, it automatically refreshes.
Creating quick measures5:09
Perspectives6:57
Hierarchies7:04
Create and order hierarchies in Power Pivot (Excel) and SSAS tabular DAX model by dragging fields into a single-column hierarchy; hide originals to avoid clutter.
Practice Activity Number 40:31
Do you want more practice setting your data model?0:05

PivotTable structure, and non-Visual Totals6:05
Show no data in rows and columns2:58
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).
Create running totals2:23
Learn to create running totals and cumulative sales by year in Power Pivot tables using value field settings, with formatting to highlight zero years and simplify analysis.
Slicers and Timelines3:15
Explore slicers and timelines in Excel to filter pivot tables and the Power Pivot model, connect them to multiple pivot tables, and build an interactive dashboard with charts.
Using formulas to query Data Model (Advanced)3:46

DAX functions - An Introduction2:54
Calculated columns - an introduction2:42
Basic operators4:29
+ - * / & and implicit conversion
IF2:53
BLANK and ISBLANK5:00
BLANKs cascade into future calculations - but you can use ISBLANK to test for them explicitly.
AND, OR and NOT3:46
SWITCH4:09
Use a DAX switch function to map sales territory keys to descriptions (northwest, northeast, central, southeast, southwest, outside the US), replacing lengthy if statements.
Other functions2:47
FALSE, TRUE, IFERROR

Measures - an introduction3:58
Aggregation of columns4:23
Explore standard aggregations—sum, count, count A, average, max, min, and median—in Power Pivot and SSAS, and learn to sum expressions via calculated columns and measures.
Aggregation of calculations4:40
SUMX, COUNTX, AVERAGEX, MINX, MAXX, MEDIANX
DISTINCTCOUNT3:32
Learn how to use distinct count in PowerPivot and SSAS tabular data models to count unique sales order line numbers by category, leveraging measures and DAX for accurate aggregation.
RANKG.EQ1:09
Other functions3:04
STDEV.P, STDEV.S, VAR.P, VAR.S, STDEVX.P, STDEVX.S, VARX.P, VARX.S,

Rounding functions5:50
CEILING, ISO.CEILING, FLOOR, INT, MROUND, ROUND, ROUNDDOWN, ROUNDUP, TRUNC
Division functions - MOD and QUOTIENT1:31
Explore division functions in Power Pivot (Excel) and SSAS Tabular DAX model by using mod to get remainders and quotient with a 5,2 example, and compare it to int's behavior.
SIGN (and use with SWITCH) and ABS3:54
Explore how abs and sign functions handle positive, negative, and zero values, and use sign with switch to classify product cost and sales amount comparisons.
Exponential functions1:48
EXP, LN, LOG, LOG10, SQRT, SQRTPI, POWER
Other functions3:46

CURRENCY, FACT, PI, RAND, RANDBETWEEN

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

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

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

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 or SQL Server 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.

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

What you'll learn

Explore related topics

Course content

Welcome9 lectures • 26min

Creating our first model using PowerPivot (Excel)7 lectures • 30min

Installing SQL Server 2022 Developer and Visual Studio 20227 lectures • 36min

Using the Tabular Model (SSAS in SQL Server)10 lectures • 31min

Developing the Data Model9 lectures • 37min

Pivot Tables functionality5 lectures • 18min

An introduction to DAX functions, including Logical functions8 lectures • 29min

Aggregation functions6 lectures • 21min

Mathematical functions5 lectures • 17min

Text functions4 lectures • 18min

Requirements

Description

Who this course is for: