Excel Data Model 2016, 2019, Microsoft 365 Apps for Enterprise (PowerPivot)

Goals

Create a Data Model. Manage the Data Model. Create simple calculations

Prerequisite

Very good Excel knowledge

Contents

Definitions

• Data model, measures, dimensions

Solutions for large data volumes

• The problem
• OLAP, BI, data cubes

The PowerPivot approach

• Advantages
• Limits
• Issues

First Use

• PowerPivot tab and window
• Getting Data – the various available sources
• Create a quick analysis

Further analysis

• Aggregation principles
• Subtotals and grand totals
• Filtering the data
• Simple calculations
• Simple layout

Data Model Manipulation

• Data view and diagram view
• Creating calculated columns (IF, ISBLANK, RELATED)
• Deleting unneeded data (reduce file size)
• Hiding columns/tables (make the model more simple for the end user)
• Adding new tables to an existing PowerPivot data model
• Sorting a column based on the contents of another column
• Creating measures in the data model (SUM, AVERAGE, DISTINCTCOUNT)