Dimensional Modeling for the Excel and Power BI Pro

• Read original article here

Dimensional Modeling for the Excel and Power BI Pro
On-Demand
Author: Miguel Escobar Matt Allington Ken Puls
5
(14 Reviews)
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.
Course
Dimensional Modeling for the Excel and Power BI Pro
$270 regular price $229 sale price Renews at $69 per year
(All prices in USD)
12h 30m of training content
11 Content Modules
Review of the core data model benefits
Dimensional modeling terminology and theory
Dimensional modeling design and architecture
Recommended modeling practices
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 Overview
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
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
Two updated videos for previously-released Overview of the Data Model module:
Relating the Tables
Phase 3 of Dimensional Modeling for the Excel Pro released, including the following modules:
Solving Specific Business Issues
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
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
What is This Course All About?
Overview of the Data Model
5 Lessons
Steps to Successful Business Intelligence
Identifying the Business Process
Structuring Data for Self Service BI
Exercise: Designing Tables From Wireframes
Solution: Designing Tables From Wireframes
Determining Unique Columns Required for the Model
Collecting and Normalizing Model Data
Dimensional Modeling Terminology and Techniques
5 Lessons
Methodology for Creating a Data Model
4 Lessons
Designing a Data Model – Model-Driven Approach
Assess Datasets
Sketch and Prototype in Excel
Dimensional Modeling Recipes
Creating Calendars from StartDate to EndDate
Fiscal Periods for 12-Month Standard Year Ends
Adjustments for 12-Month Non-Standard Year Ends
Fiscal Periods for Non-Standard 12-Month Year Ends
Adjustments for 364-Day Calendars
Fiscal Periods for 364-Day Calendars
Slowly Changing Dimensions
The Slowly Changing Dimension Issue
Filling Blank Cells with Dates
Option 1: Surrogate Keys Without Meaning
Option 2: Surrogate Keys With Meaning
Creating the Merging Bridge Table
Replacing the Fact Table’s Primary Key
Storing Dimensions on Fact Tables
Many to Many Joins
Many to Many with 1:Many in Excel
Many to Many Physical Relationships
Many to Many Virtual Relationships in Excel
Solving Specific Business Issues
Stock on Hand Count Reporting (Excel)
Status Reporting (Excel)
How the Vertipaq Engine Works
6 Tips for Data Model Optimization
Power Query Optimization
Tools for Testing Model Performance
Rate the Course
Course Feedback
How subscription works?
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
Miguel Escobar
Microsoft MVP
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).
Matt Allington
Microsoft MVP
Matt Allington is one of the founding partners of Skillwave.Training, and the owner and principal consultant at Excelerator BI Pty Ltd. 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 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.
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.
FAQ
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 cancel my subscription?
You may cancel your subscription at any time, and you will continue to have access to all the course materials until your expiry date. To cancel your subscription, go to the Subscriptions of your Account Dashboard and select the subscription to cancel. After your subscription expires, you will no longer have access to the course materials, including the downloadable Recipe Cards.
How do I renew my subscription?
Our system will automatically renew your subscription for another year on your expiry date using the billing information on file. About a month (4 weeks) before your current expiry 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.
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.
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.
Reviews
Dimensional Modeling for the Excel and Power BI Pro
5
14 reviews for Dimensional Modeling for the Excel and Power BI Pro
Rated 5 out of 5
Cristian (verified owner) –
March 11, 2021
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 Power BI or Excel Power Pivot data model.
This course starts from basics and slowly goes into more advanced topics in a very detailed manner. There are lot of hands-on exercises to assess your learning and real-world sample projects to apply your knowledge. Support material and lectures are very well prepared and the overall quality is just amazing!
I would definitely recommend this course for anyone new to Power BI or Excel Power Pivot but also for any current user who really want to understand Data Modeling and improve one’s skills.
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 after trial-and-errors and hundreds of hours of unstructured learning.
Rated 5 out of 5
Raisur (verified owner) –
May 16, 2021
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.
Rated 5 out of 5
Greg Stoner (verified owner) –
June 16, 2021
This course has been immensely helpful in teaching me how to think about data- proper data structure data 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.
Rated 5 out of 5
Mohammad (verified owner) –
June 21, 2021
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.
Compared to some other providers, the courses may be more affordable but I would ask the platform to offer the budget-constrained learners occasional special prices.
Rated 5 out of 5
Steffen (verified owner) –
June 26, 2021
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!
Your become 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!
Rated 5 out of 5
Normand (verified owner) –
June 27, 2021
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.
Rated 5 out of 5
Kasper (verified owner) –
Great course ! really informative and great learning. Fantastic teachers!
Rated 5 out of 5
Alicja Siwak (verified owner) –
June 28, 2021
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
Rated 5 out of 5
Yoash (verified owner) –
June 29, 2021
Great course and together Supercharge Power BI make the difference, easy to understand videos make life easy.
Rated 5 out of 5
Nick Burns (verified owner) –
June 29, 2021
This course gave me great fundamentals on how to construct sustainable models that can be easily expanded.
Rated 5 out of 5
Thomas (verified owner) –
June 30, 2021
I did both legs. Well structured course that helps understanding the technical merits and it is essential for the understanding. A course everyone should take.
Rated 5 out of 5
Solar (verified owner) –
June 30, 2021
Great course, even though the study history disappeared after the web updates, but the Customer service manager contacted me a few times to explain the solutions. They can revive the history and this will not happen any more. Very nice staff, the content always very valuable. I learned a lot from Skillwave and SQLBI, the instructors are very talent in BI world! It is good for all level learners to give a taste!
Rated 5 out of 5
Stanton (verified owner) –
June 30, 2021
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.
Rated 5 out of 5
Melinda C. (verified owner) –
May 26, 2022
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.