Gain a hands-on experience in Power BI and Power Pivot for Excel and learn how to analyse data with the Analysing Data with Power BI and Power Pivot for Excel course and lab. This course aims to teach you the basic concepts of data modelling through practical examples that you are likely to encounter in your daily life. This course will be beneficial for an Excel user who uses Power Pivot for Excel, a data scientist using Power BI, or even for those who want to read an introduction to the topics of data modelling.
Lessons 1: Introduction
- Who this course is for?
- Organisation of this course
- Conventions
Lessons 2: Introduction to data modelling
- Working with a single table
- Introducing the data model
- Introducing star schemas
- Understanding the importance of naming objects
Lessons 3: Using header/detail tables
- Introducing header/detail
- Aggregating values from the header
- Flattening header/detail
Lessons 4: Using multiple fact tables
- Using denormalised fact tables
- Filtering across dimensions
- Understanding model ambiguity
- Using orders and invoices
Lessons 5: Working with date and time
- Creating a date dimension
- Understanding automatic time dimensions
- Using multiple date dimensions
- Handling date and time
- Time-intelligence calculations
- Handling fiscal calendars
- Computing with working days
- Handling special periods of the year
- Working with weekly calendars
Lessons 6: Tracking historical attributes
- Introducing slowly changing dimensions
- Using slowly changing dimensions
- Loading slowly changing dimensions
- Rapidly changing dimensions
- Choosing the right modelling technique
Lessons 7: Using snapshots
- Using data that you cannot aggregate over time
- Aggregating snapshots
- Understanding derived snapshots
- Understanding the transition matrix
Lessons 8: Analysing date and time intervals
- Introduction to temporal data
- Aggregating with simple intervals
- Intervals crossing dates
- Modelling working shifts and time shifting
- Analysing active events
- Mixing different durations
Lessons 9: Many-to-many relationships
- Introducing many-to-many relationships
- Cascading many-to-many
- Temporal many-to-many
- Using the fact tables as a bridge
Lessons 10: Working with different granularity
- Introduction to granularity
- Relationships at different granularity
Lessons 11: Segmentation data models
- Computing multiple-column relationships
- Computing static segmentation
- Using dynamic segmentation
- Understanding the power of calculated columns: ABC analysis
Lessons 12: Working with multiple currencies
- Understanding different scenarios
- Multiple source currencies, single reporting currency
- Single source currency, multiple reporting currencies
- Multiple source currencies, multiple reporting currencies
Appendix A. Data modelling 101
- Tables
- Data types
- Relationships
- Filtering and cross-filtering
- Different types of models
- Measures and additivity
Hands-on LAB Activities
Introduction to data modeling
- Exploring a Dataset
Using header/detail tables
- Aggregating Values from the Header Table
Using multiple fact tables
- Analysing Denormalised Fact Tables
- Understanding Model Ambiguity
Working with date and time
- Creating a Date Dimension
Tracking historical attributes
- Analysing Slowly Changing Dimensions
Using snapshots
- Analysing Snapshots
- Analysing Derived Snapshots
- Understanding the Transition Matrix
Analysing date and time intervals
- Understanding Temporal Data
- Analysing Events that Cross Dates
- Analysing Active Events
- Mixing Different Durations
Many-to-many relationships
- Exploring Many-to-Many Relationships
- Exploring a Temporal Many-to-Many Relationship
Working with different granularity
- Analysing Relationships at Different Granularity
Segmentation data models
- Analysing Calculated Physical Relationships
- Analysing Dynamic Segmentation
- Understanding ABC Analysis
Working with multiple currencies
- Producing a Report Containing Information With a Single Type of Currency
- Producing a Report in Multiple Currencies
Exam FAQs
FAQ's are not Available for this course.Summary
Standard:
Analysing Data - Power BI & Excel
Lessons:
13+ Lessons | 87+ Exercises | 50+ Quizzes | 43+ Flashcards | 43+ Glossary of Terms
Delivery Method:
Online
Language:
English