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 your 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?

Why should you attend?

Developed by a CPA with real world experience who is also a renowned Business Intelligence expert, this course is intended to teach you the right way to build solid and scalable dimensional models.

You’ll learn key concepts and terminology around data warehousing and dimensional modelling 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.

This is an exciting course, as it pairs two of Excel’s most powerful features (Power Query and Power Pivot) 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? The material in this course is 100% portable to Power BI as well. You’re not learning for one program, but rather for two!

Who should attend?

Data professionals who are responsible for building business intelligence reports, whether you use Excel or Power BI.

Topics covered:

• How to use Power Query and Power Pivot together to build a functional dimensional model
• Review of the core Power Pivot benefit
• Dimensional modelling terms and techniques
• Determining model grain
• Solving common join problems when linking tables
• Creating calendar tables on the fly with Power Query
• Linking tables with different date granularity
• DAX patterns for solving true Many to Many joins
• Data optimization rules to keep your models performant
• Tips for working out business needs
• Structuring data for Self-Service BI solutions

Course format

This is a hands-on course. Participants should bring a laptop running one of the following:

• Excel 2013 Professional Plus or Excel 2013 ProPlus with the free Power Query add-in installed
• Excel 2016 or higher
• Excel 365

Prerequisite knowledge

At a minimum, participants must have experience using PivotTables, and should have exposure to Power Pivot is an asset, but not required.