Logo - Skillwave Training
Go back to Courses

Dimensional Modeling: Getting Started with Excel Power Pivot

  • On Demand
Author: Ken Puls

Average Rating: (0) Reviews

This is the video portion of the "Dimensional Modeling in Power Pivot" seminar with Ken Puls. At the core of every good Power Pivot solution is the Data Model. But do you really know how to work with it properly? This on-demand course is intended to teach you the right way to build solid and scalable dimensional models.
Course

Dimensional Modeling: Getting Started with Excel Power Pivot

$249 Renews at $29 per year
(All prices in USD)
This Course contains:
7 hours of training content
9 Content Modules
Downloadable files and Handouts
Certificate of completion
DURATION:
7 hours
TOOLS:
Excel
SKILLS:
Data Preparation
Data Modeling

What you'll learn?

Review of the core data model benefits
Dimensional modeling terminology and theory
Dimensional modeling design and architecture
Recommended modeling practices
Wireframing report requirements
How to solve common join problems
Patterns to get your data in the correct format
Creating calendar tables on the fly
Linking tables with different date granularities
Linking tables with different date granularities
Data optimization rules to keep your models performant

Course Overview

What is Dimensional Modeling and why should you care?

The classic Excel PivotTable did a great job of letting us quickly pivot and slice data for years. There was a ton of logic built into the tool in order to make it easy for end users, but that ease of use had a cost: it didn’t scale to multiple data source tables. And worse, it actually kept you from learning the terms to scale your own knowledge.

At the core of every good Power Pivot solution is the Data Model. But do you really know how to work with it properly? What shape your tables should have? When you should split tables up, when you should flatten them, and how to manipulate the data on the fly to do so?

Course Description

Built by business professionals, for business professionals, this course is intended to teach you the right way to build solid and scalable dimensional models. Whether you are already building data models regularly, or are new to the concept of dimensional modeling, this course will give you all the tools you need to build the best self service business intelligence models possible.

After a review of the core benefits of the Power Query Data Model and reporting technologies in your favourite tool, we’ll look at some steps that you should consider when architecting your solution. These tips should help you crystalize what your audience requires, as well as clarify what data you need in order to get there. You’ll also learn how to identify if your data is “normalized” for consumption by the Data Model, and experience an example of cleaning up an ugly pivoted data set.

You’ll learn key concepts and terminology around data warehousing and dimensional modeling including Facts, Dimensions, Relationships, Schemas, Keys, and more.  You’ll learn why the PivotTable has been “too helpful”, and how it can impact your ability to extend your data models.

Armed with the background theory on modeling, it’s then time to get your hands dirty with hands-on examples of solving several “many to many” join problems. From composite keys to bridge tables, slowly changing dimensions to flattening snowflakes, you’ll learn which tools and techniques to use and when.

With the theory and recipes behind you, we’ll then jump into one of the most exciting parts of the journey: looking at complex real-world cases that seem built to defy you. Not only will you learn how to apply dimensional modeling rules to solve the challenges, but you’ll also learn why – just sometimes – you may need to violate some of those recommended practice rules as well.

This is an exciting course, as it pairs two of self service business intelligence’s most powerful features (Power Query and the Data Model) together in one place, using each for what they were designed to do. You’ll leave armed with not only the experience, but handy reference cards to apply these techniques to your own data, and to determine, “Is this a Power Query job, or a DAX job?”

Who this course is for?

This course is designed for Data Professionals who have some experience with designing self-service business intelligence models in Excel.

Ideally, you should have encountered one or more of the following problems in the real world:

  • Triggered a “Relationship Between Tables may be needed” error on an Excel PivotTable
  • Been told you cannot create a relationship between tables because each column contains multiple unique values
  • Received an error upon refreshing your Data Model because a column contains multiple values
  • Created a relationship backwards in your Data Model
  • Discovered a value that doesn’t seem to filter properly when drilling in to a PivotTable

Course Formats

On Demand
This is an on-demand course, delivered via pre-recorded video modules that you can consume at your own pace. It includes all required sample files and practice sets.

Course Content

Platform Introduction
3 Lessons
Course Examples
Course Introduction
Who is Ken Puls?
Introduction to Power Pivot
5 Lessons
Where PivotTables Fail
Enter the Data Model
Relating the Tables
Aggregating Data with DAX
Filtering, Ordering and Sorting
Architecting Business Intelligence Solutions
9 Lessons
Steps to Successful Business Intelligence
Identifying the Business Process
Wireframing Report Requirements
Determining the Model Grain
Structuring Data for Self Service BI
Exercise: Designing Tables From Wireframes
Solution: Designing Tables From Wireframes
Determining Unique Columns Required
Collecting and Normalizing Model Data
Dimensional Modeling Terminology and Techniques
5 Lessons
Facts and Dimensions
Preparing For Dimensional Modeling
Designing Dimension Tables
Designing Fact Tables
Creating Facts (Measures)
Relating Tables
4 Lessons
Relationship Types
Relationship Schemas
Effect of Relationship Issues
Preventing Filtering Issues
Dimensional Modeling Recipes
11 Lessons
Composite Key Joins
Bridge Tables
Flattening Snowflaked Dimensions
Creating Calendar Dimensions
Creating Calendar Boundaries
Creating Calendars from StartDate to EndDate
Fiscal Periods for 12-Month Dec 31 Year Ends
Modifications for 12-Month Non-Std Year Ends
Fiscal Periods for 12-Month Non-Std Year Ends
Modifications for 364 Day Calendars
Fiscal Periods for 364 Day Calendars
Slowly Changing Dimensions
6 Lessons
Slowly Changing Dimensions
Filling Blanks with Dates
Option 1: Surrogate Keys Without Meaning
Option 2: Surrogate Keys With Meaning
Creating the Bridge Query
Replacing the Fact Table’s Foreign Key
Development Tips
2 Lessons
Model Performance and Stability
How the Vertipaq Engine Works
Help Resources
6 Lessons
Free Help Forums
Free Articles and Blogs
Master Your Data
Power Query Recipes
Power Query Academy
Free Newsletters

How subscription works?

This course is built on an annual subscription format. Your registration comes with 12 months of access to the course materials, so you can refer back to the videos and example files whenever you like. Your subscription also gives you access to our private Discussion Forum, where you can ask questions related to the course materials, as well as how to apply those teachings to your own work. This forum is an invaluable resource that also allows students to practice their own learnings by helping others with those questions.

On your annual renewal date, your subscription will be automatically renewed with the credit card information on file, unless you cancel your subscription beforehand. You will be emailed a renewal reminder about one month before the renewal date. Please see the FAQs below for information about subscription renewals and cancellations.

About the author

Ken Puls

Ken Puls

Microsoft MVP, FCPA, FCMA
Ken Puls is one of the founding partners of Skillwave.Training, and the President and Chief Training Officer of Excelguru Consulting Inc. in Canada. He is a Chartered Professional Accountant, blogger, author, and trainer with over 20 years of business and IT experience. His passion lies in exploring tools to turn data into information, and teaching others how to use them. Ken is a Microsoft MVP and a leading expert in Excel, Power Query, and Power BI.

FAQ

How do I access the course materials? Are they downloadable?
The videos are streamed on-demand and may be watched as often as you like. However, they are not downloadable. The example files and handouts for each lesson are downloadable.
Where are the course videos hosted?
All the videos for the course are hosted on Vimeo. Please note that some networks block access to Vimeo, in which case the videos will not be watchable. You may want to double-check that your network allows access to Vimeo before registering in the Academy, to ensure that you will be able to see the videos.
How do I receive my Certificate of Completion?
After all the modules and lessons have been completed, you will be able to download the Certificate of Completion from the My Courses area of your Account Dashboard.

Reviews

Dimensional Modeling: Getting Started with Excel Power Pivot

0
(0 Review)
5
4
3
2
1

Reviews

There are no reviews yet.

Only verified customers who have purchased this product may leave a review.

Recommended Courses

Continue your learning journey with our other training offerings. We’ll help you take your skills to the next level.