Dimensional Modeling for the Excel Pro

Skillwave · May 25, 2020

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

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 Overview

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 Release Schedule

This course is currently in active development and so will be released on a phased basis over two months. Our targeted release schedule is as follows (please note that it is subject to change):

  • Phase 1 (first 3 modules) – June 11, 2020
  • Phase 2 (next 5 modules) – July 17, 2020
  • Phase 3 (next 2 modules) – August 24, 2020
  • Phase 4 (final module) – coming September 2020

What you get with the course

11+ Hours of Video Content (once all phases are released)

Downloadable Files and Handouts

Premium Content and Instructors

Support and Discussions Group

Course Certificate of Completion

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

The Instructors

This course is developed by Matt Allington, Ken Puls, and  Miguel Escobar, three of the world’s leading business intelligence experts. Read more about each of our trainers by clicking on their profile link.

Course Content

Expand All

About Instructor

Skillwave

Your #1 resource for training on Power BI, Power Query, Power Pivot, and Excel.

5 Courses

+98 enrolled
Not Enrolled

Course Includes

  • 11 Modules
  • 60 Lessons
  • Course Certificate