Go back to Courses

Dimensional Modeling for the Excel and Power BI Pro

  • On-Demand
Author: Miguel Escobar Matt Allington Ken Puls
At the core of every good Power Pivot or Power BI 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 for the Excel and Power BI Pro

$270 regular price $216 sale price Renews at $69 per year
(All prices in USD)
This Course contains:
12h 30m of video content
11 Content Modules
Downloadable files and Handouts
Support and Discussion groups
Certificate of completion
DURATION:
12h 30m
TOOLS:
Excel
Power BI
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
Case studies for industry issues
Dealing with slowly changing dimensions
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 or Power BI 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?”

And the best part of all of this? Every registration will include both the Excel and Power BI versions of the course, so we’ve got you covered no matter which platform you are using.

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 or Power BI.

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 or Power BI visual
  • Created a Many to Many relationship in Power BI

Course Update Log

Phase 1 of Dimensional Modeling for the Excel Pro released, including the following modules:

  • Introduction
  • Overview of the Data Model

Architecting Business Intelligence Solutions

Phase 2 of Dimensional Modeling for the Excel Pro released, including the following modules:

  • Dimensional Modeling Terminology and Techniques
  • Relating Tables
  • Dimensional Modeling Recipes
  • Slowly Changing Dimensions
  • Many to Many Joins

Two updated videos for previously-released Overview of the Data Model module:

  • Relating the Tables

Aggregating Data with DAX

Phase 3 of Dimensional Modeling for the Excel Pro released, including the following modules:

  • Solving Specific Business Issues
  • Performance and Optimization

Updated Power Query Recipe cards added to Dimensional Modeling Recipes module

Links added to corresponding course videos

Phase 4 (final phase) of Dimensional Modeling for the Excel Pro released, including the following module:

  • Methodology for Creating a Data Model

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

Course Introduction
2 Lessons
Getting Started
What is This Course All About?
Overview of the Data Model
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 for the Model
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
Extracting Facts with Measures
Relating Tables
4 Lessons
Relationship Types
Relationship Schemas
Relationship Issues
Preventing Filtering Issues
Methodology for Creating a Data Model
4 Lessons
Designing a Data Model – Model-Driven Approach
Assess Datasets
Categorize and Discover & Analyze
Sketch and Prototype in Excel
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 Standard Year Ends
Adjustments for 12-Month Non-Standard Year Ends
Fiscal Periods for Non-Standard 12-Month Year Ends
Adjustments for 364-Day Calendars
Fiscal Periods for 364-Day Calendars
Slowly Changing Dimensions
7 Lessons
The Slowly Changing Dimension Issue
Filling Blank Cells with Dates
Option 1: Surrogate Keys Without Meaning
Option 2: Surrogate Keys With Meaning
Creating the Merging Bridge Table
Replacing the Fact Table’s Primary Key
Storing Dimensions on Fact Tables
Many to Many Joins
4 Lessons
Many to Many Patterns
Many to Many with 1:Many in Excel
Many to Many Physical Relationships
Many to Many Virtual Relationships in Excel
Solving Specific Business Issues
4 Lessons
Header Detail Tables (Excel)
Stock on Hand Count Reporting (Excel)
Status Reporting (Excel)
Custom Time Intelligence
Performance and Optimization
5 Lessons
Model Performance and Stability
How the Vertipaq Engine Works
6 Tips for Data Model Optimization
Power Query Optimization
Tools for Testing Model Performance

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 authors

Miguel Escobar

Miguel Escobar

Microsoft MVP
Miguel Escobar is an Excel specialist turned Business Intelligence expert using the latest tools from Microsoft – Power BI and Power Query. He is the co-author of Master your Data with Excel and Power BI (formerly M is for (Data) Monkey), a blogger, and a Youtuber of powerful Excel tricks. Before joining the Power Query team at Microsoft, he helped found Skillwave.Training and was previously recognized as a Microsoft MVP and a Microsoft Certified Trainer (MCT).

Matt Allington

Microsoft MVP
Matt Allington is one of the founding partners of Skillwave.Training, and the owner and principal consultant at Excelerator BI Pty Ltd. in Australia. He is an expert in Power BI and DAX training, and has 30+ years of experience in using data to deliver business value. Matt is an author, blogger, and Microsoft MVP that specializes in Power BI, Power Pivot, and Power Query. He brings his business and IT experience to solve general business data problems quickly and efficiently.

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.
What support is included with the course? How do I ask questions?
All users with a current course subscription receive access to our private Discussion Forum, where they can ask questions related to the course materials, as well as how to apply those teachings to their own work. This forum is also intended to act as a place for students to practice their own learnings by helping others with those questions. While the focus of this forum is primarily aimed at allowing students to learn by helping each other, they are monitored by our course authors as well. For technical issues related to the site, course access, or other questions regarding your account, please get in touch through our Contact Us page.
How do I cancel my subscription?
You may cancel your subscription at any time, and you will continue to have access to all the course materials until your expiry date. To cancel your subscription, go to the Subscriptions of your Account Dashboard and select the subscription to cancel. After your subscription expires, you will no longer have access to the course materials, including the downloadable Recipe Cards.
How do I renew my subscription?
Our system will automatically renew your subscription for another year on your expiry date using the billing information on file. About a month (4 weeks) before your current expiry date, you will receive an email from us reminding you that the renewal is coming up and to confirm your billing details, so your access is uninterrupted.
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.
What payment methods do you accept? What currency is used?
We accept credit card payments through our secure online payment portal, Stripe. If you would like to discuss other payment options, please get in touch through our Contact Us page. All transactions are charged in US Dollars.
What is your refund policy for this course?
We think you will love our training; however, if you are unsatisfied with the training for any reason, we offer a 14-day 100% money-back guarantee, as long as you have completed less than 25% of the course. All we ask in return is that you tell us why you were unsatisfied with the training. We do not offer refunds if you have completed over 25% of the course and/or it is more than 14 days after the purchase date.
Can an account be shared with or transferred to another user?
Logins, downloads, and other materials are for the exclusive use of the registered user only. Sharing of credentials is in violation of our site policies and may result in termination of product and/or site access without notice.

Reviews

Dimensional Modeling for the Excel and Power BI Pro

5
(13 Reviews)
5
4
3
2
1

13 reviews for Dimensional Modeling for the Excel and Power BI Pro

  1. Cristian (verified owner)

    The course is amazing and has very clear explanations on every concept and process.
    It allowed me, a non-data warehousing background user, to understand Dimensional Modeling and most of all HOW and WHY this is important when building a Power BI or Excel Power Pivot data model.

    This course starts from basics and slowly goes into more advanced topics in a very detailed manner. There are lot of hands-on exercises to assess your learning and real-world sample projects to apply your knowledge. Support material and lectures are very well prepared and the overall quality is just amazing!

    I would definitely recommend this course for anyone new to Power BI or Excel Power Pivot but also for any current user who really want to understand Data Modeling and improve one’s skills.
    I wish I had this course available at the beginning of my Self Service BI Journey … it would have saved me a lot of headaches after trial-and-errors and hundreds of hours of unstructured learning.

  2. Raisur (verified owner)

    This a great stunning course for those who are coming from Excel background. Very well organized and structured course. It blends data modeling and power query skills for modeling in a coherent and structured way. I highly recommend the course.

  3. Greg Stoner (verified owner)

    This course has been immensely helpful in teaching me how to think about data- proper data structure data on the front end leads to getting the answers I need in a timely manner with much less work and no heartburn. I knew zero about dimensional modeling before discovering Skillwave, and now I can’t wait to learn more every time I log in. My only regret is that I hadn’t stumbled upon this A-Team of instructors years ago.

  4. Mohammad (verified owner)

    A natural extension from Excel is Power BI stack. As dimensions are what we use to slice and dice our fact table(s), this course can add great value to the skill set of the learner. This is the second course I got from Skillwave platform and now I am in progress of this course and I can say the instructors (for ex, Ken Puls) explain the concepts in a cogent manner.

    Compared to some other providers, the courses may be more affordable but I would ask the platform to offer the budget-constrained learners occasional special prices.

  5. Steffen (verified owner)

    If you search for a course of dimensional/data modeling, this is the right course for you. The course explains the concepts of dimensional modeling precise and clear!
    Your become explanations and suggestions on how you should prepare your data model with a lot of “wow this is how it works” moments.
    I can recommend the course to everyone who is interested in Power Pivot in Excel or Power BI! This course had helped me lot to understand how I have to structure my data right!

  6. Normand (verified owner)

    Finally a course that suggests an effective method for structuring a sustainable model. If you want an approach that will follow you the rest of your career, this is the course to take.

  7. Kasper (verified owner)

    Great course ! really informative and great learning. Fantastic teachers!

  8. Alicja Siwak (verified owner)

    Amazing course, very well structured. Trainers explain in a very precise way why we need data modeling and what are the best practices for creating a sustainable model. Truly great content, money well invested

  9. Yoash (verified owner)

    Great course and together Supercharge Power BI make the difference, easy to understand videos make life easy.

  10. Nick Burns (verified owner)

    This course gave me great fundamentals on how to construct sustainable models that can be easily expanded.

  11. Thomas (verified owner)

    I did both legs. Well structured course that helps understanding the technical merits and it is essential for the understanding. A course everyone should take.

  12. Solar (verified owner)

    Great course, even though the study history disappeared after the web updates, but the Customer service manager contacted me a few times to explain the solutions. They can revive the history and this will not happen any more. Very nice staff, the content always very valuable. I learned a lot from Skillwave and SQLBI, the instructors are very talent in BI world! It is good for all level learners to give a taste!

  13. Stanton (verified owner)

    Understanding dimensional modeling is a critical piece of the Power BI and Power Pivot puzzle. The Dimensional Modeling course teaches both what it is and how to apply it. Using video and hands-on examples, Skillwave delivers comprehensive training. All I can say is Skillwave’s Dimensional Modeling rocks it! Thank you, Ken, Matt, and Miguel.

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