Dimensional Modeling for the Excel and Power BI Pro

Authors: Ken Puls, Matt Allington, Miguel Escobar

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. Registration includes both the Excel and Power BI versions of the course.

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

Dimensional Modeling for the Excel and Power BI Pro

$270Renews at $69 per year
(All prices in USD)
  • 12h 30m of training content
  • 11 modules
  • Downloadable files and Handouts
  • Support and Discussion
  • Certificate of Completion

Dimensional Modeling for the Excel and Power BI Pro

$270

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

Sample Lessons

Not sure if this course is right for you?

Visit our Learning Journey page to compare courses by skill level and area of specialization.

Reviews

Dimensional Modeling for the Excel and Power BI Pro

Average Rating

4.97 / 5

4.96/5
Carlton Seymour
Carlton Seymour
January 7, 2023
Read More
Wow! Wow! Wow! Just finished the Power BI side of this course and will now start the Excel version. Learnt so much and just cannot wait to start implementing them in my work projects.
Ken Muse
Ken Muse
December 5, 2022
Read More
Impressive coverage of data modeling in general and best practices for Power Query / Power BI. You distilled multiple complex topics into a format that is very approachable for someone that is trying to master the topic. Outstanding course. I've already recommended a few of these courses to some firms that I believe will benefit from them.
Melinda C.
Melinda C.
May 26, 2022
Read More
I loved the patient and thorough way things were explained and shown by example, and the quick responses to questions asked. Thank you for creating courses to share your knowledge with others. Keep up the great work.
Stanton Berlinsky
Stanton Berlinsky
June 30, 2021
Read More
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.
Peter Scobbie
Peter Scobbie
October 4, 2022
Read More
I just love the presentation style, enthusiasm exudes from the voices involved, especially that of Ken. The annual renewal is so important to keep going and use the courses as a reference library of topics which get forgotten because of lack of use in day to day work.
Rodger Moran
Rodger Moran
November 23, 2022
Read More
The whole training experience is based on the combination of relevancy of of any lesson, key concepts, example, and the speed of the instruction. It is amazingly perfect... for me, anyway.
Eric Hartwig
Eric Hartwig
January 2, 2023
Read More
I liked the highly detailed material covered in each class. Keep up the great work and I look forward to more courses as they become available!
Alenka
Alenka
September 7, 2022
Read More
I liked the comprehensive course materials, the lecturers, and the immediate business usability.
Marco vd. L.
Marco vd. L.
August 9, 2024
Read More
I loved the set-up of the topics, the speed and the calm voice of the teacher. Thank you for sharing this knowledge. It helps a lot with my next step in the world of Power BI.
Stephanie H.
Stephanie H.
August 9, 2024
Read More
It was easy to follow along and understand. Lots of examples presented. It is nicely broken into small modules that could be completed when I had a few extra minutes in a day.
Peter G.
Peter G.
August 2, 2024
Read More
A very thorough and entertaining step-by-step tutorial.
Rajender S.
Rajender S.
July 28,2024
Read More
Each and every topic very well defined and easy to understand. The course provided a comprehensive understanding of dimensional modeling techniques and their practical applications in Power BI. The hands-on exercises were particularly beneficial, allowing me to apply the concepts in real-world scenarios. Overall, the training has significantly enhanced my data modeling skills in Power BI.
Zineddine Reggani
Zineddine Reggani
October 17, 2023
Read More
Everything is well crafted: the content, the examples, the built-in forum and instructors. Thank you.
Rafal Jagniewski
Rafal Jagniewski
June 14, 2023
Read More
It was presented in a clear and thoughtful way. I loved the effort to make people understand the process and underlying concepts. Keep it up guys 🙂
David P. Voss
David P. Voss
February 9, 2023
Read More
I liked everything about the course! 1) The format of first providing the theory and recipe then following up with an example. 2) The structure of beginning with the (base) introduction of a data model, table relationships (joins) and then the finer points. 3) Pointing out the time savings and increased functionality of building better models.
Xinning
Xinning
October 26, 2022
Read More
This course taught me a lot of tips and tricks on how to build a good data model.
Gary Clutterbuck
Gary Clutterbuck
September 26, 2022
Read More
All Skillwave Training courses provide really clear instructions and great advice, along with useful examples that are adaptable to your own situations.
John R.
John R.
September 12, 2022
Read More
I like that the lessons have the clear explanations with visual steps, and the right duration length makes it easy to follow and absorb. The instructors genuinely want to help improve your skills, and deliver well explained concepts, with step by step visual demonstrations. Great place to learn.
Jennifer Abell
Jennifer Abell
June 17, 2021
Read More
I liked that this course showed the difference between Excel & Power BI, and explained query folding and how to minimize size of model. Truly, this course was the first time I've gotten a good understanding of what query folding is, when to expect it to happen and when not, as well as how to keep it going for as long as possible. For me, that was the most valuable part of the course.
Thomas
Thomas
June 30, 2021
Read More
I did both legs [Excel and Power BI]. Well structured course that helps understanding the technical merits and it is essential for the understanding. A course everyone should take.
Solar
Solar
June 30, 2021
Read More
Great course, very nice staff, the content always very valuable. I learned a lot from Skillwave, the instructors are very talented in BI world! It is good for all level learners to give a taste!
Yoash
Yoash
June 29, 2021
Read More
Great course and together with Supercharge Power BI, makes the difference. Easy to understand videos make life easy.
Kasper
Kasper
June 28, 2021
Read More
Great course, really informative and great learning. Fantastic teachers!
Alicja Siwak
Alicja Siwak
June 28, 2021
Read More
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.
Normand
Normand
June 27, 2021
Read More
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.
Mohammad
Mohammad
June 21, 2021
Read More
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.
Greg Stoner
Greg Stoner
June 16, 2021
Read More
This course has been immensely helpful in teaching me how to think about data - proper data structure 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.
Raisur
Raisur
May 16, 2021
Read More
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.
Nick Burns
Nick Burns
June 29, 2021
Read More
This course gave me great fundamentals on how to construct sustainable models that can be easily expanded.
Steffen Fischer
Steffen Fischer
June 26, 2021
Read More
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! Good 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!
Cristian
Cristian
March 11, 2021
Read More
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 data model. There are lot of hands-on exercises. Support material and lectures are very well prepared. I would definitely recommend this course. 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.
Previous
Next

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

Additional support materials added:

  • A PDF handout containing all of the lesson slides for the course. This file is the same for both the Excel and Power BI versions of the course.
  • Two zip folders containing all of the examples files for both the Excel and Power  BI versions of the course. This allows you to download the examples all at once, instead of through the individual module and lesson pages.

The set of Power Query Recipes included with this course has been updated.

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

Expand All
Module Content
0% Complete 0/1 Steps

How does the subscription work?

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

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.
Matt Allington

Matt Allington

Matt Allington is one of the founding partners of Skillwave.Training, and the founder of Excelerator BI 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 eight-time 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.
Miguel Escobar

Miguel Escobar

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

FAQs

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 renew my subscription?

If you subscription is set to auto-renew, our system will automatically renew your subscription for another year on your renewal date using the billing information on file. If you subscription is set to manual renewal, our system will generate a manual invoice and email it to you on the renewal date. About a month (4 weeks) before your next renewal 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.

How do I cancel my subscription?

You may cancel your subscription at any time, and you will continue to have access to all of your course materials until your expiry date. To cancel your subscription, go to the Billing page from your Account Dashboard and select the subscription to cancel from the My Subscriptions area. Once in the Subscription Details page, you can then choose to cancel your subscription from the Actions field. After your subscription expires, you will not longer have access to the course materials, including the downloadable Recipe Cards.

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. Please note that because Skillwave is registered in Canada, we are required by Canada Revenue Agency to collect the 5% Goods & Services Tax (GST) on registrations made by Canadians.

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.

Recommended Courses

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

  • Excel
  • Power BI
27h 26m
24 Modules
$350
11h 30m
22 Modules
$495