Dimensional Modeling: Getting Started with Excel Power Pivot

Author: Ken Puls

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.

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

Dimensional Modeling: Getting Started with Excel Power Pivot

$249Renews at $29 per year
(All prices in USD)
  • 7 hours of training content
  • 9 modules
  • Downloadable files and Handouts
  • Certificate of Completion

Dimensional Modeling: Getting Started with Excel Power Pivot

$249

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

Expand All

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

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.

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.

Recommended Courses

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

11h 45m
19 Modules
$270