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