T2101

The PowerPivot Workshop

The three-day workshop covers PowerPivot topics, starting from basic concepts and moving on up to complex data models and DAX formulas. The workshop covers basic SharePoint configuration and usage. Although focused on PowerPivot integration, the workshop does not cover all the SharePoint topics that are needed to install a SharePoint server. This would need a complete course by itself and should be targeted to a more technical audience.

Kursen hålls på svenska.

Målgrupp:

Excel superusers - Users that employ Excel on a daily basis and want to exploit the full power of PowerPivot to improve their reporting capabilities

IT and BI Professionals - Professionals that are in charge of the data warehouse management of their company and need to understand how to provide support to analysts who need to extract data from PowerPivot.

Innehåll:

Introduction

What is PowerPivot?
What is a PivotTable?
- Example with classical Excel PivotTable
PowerPivot setup
- Example with PowerPivot

Basic PowerPivot Concepts

Formatting Numbers
Handling Technical and Useless Columns
Understanding Calculated Columns
Using Lookup Tables
Understanding Measures
Handling Many Tables
Refreshing Data
Using Slicers

Introduction to DAX

Calculation Foundations
- DAX Data Types
- DAX Operators
Calculated Columns Examples
Measures Examples
Introduction to the Execution Context (Row and Filter)
Choosing Between Calculated Columns and Measures
Handling Errors in DAX
Common DAX Functions Examples
- Statistical Functions
- Logical Functions
- Information Functions
- Mathematical Functions
- Text Functions
- Date and Time Functions

Understanding Data Models

What is a Data Model?
Why PowerPivot Users need Data Modeling?
- Difference Between Classical and PowerPivot PivotTable
Physical and Logical Data Models
- Normalization and Denormalization
- Empty and Default Values
- Understanding How and When to Denormalize
The SQL Query Designer as a Data Modeling Tool
- Different kinds of Joins
- Setting Relationship Manually
- Understanding OUTER Joins

Loading Data and Models

Understanding Data Connections
- Different Kind of Connections
- Using Existing Connections
Loading Tables from SQL Server
- Filtering Options
- Loading and Detecting Relationships
- Loading from Views
Loading from Access
- Issues in the Query Designer
- Best Practices
Loading Data from Analysis Services
- The MDX Query Designer
- Handling of Keys in SSAS
- Common Issues with SSAS 2000
- OLAP cube or DataMart?
Using Linked Tables
Loading from Excel Workbooks
Loading from Text Files
- Using the Schema.INI configuration
Using Copy & Paste Operations
Loading From Data Feeds
- Reporting Services Reports
- Internet Data Feeds
Loading From SharePoint
- Loading From SharePoint Reports
- Loading From SharePoint Excel Workbooks
- Loading From PowerPivot in Excel Workbooks
- Loading From SharePoint List

Understanding the Evaluation Context

What is the Row Context
What is the Filter Context
- Filter Context on a Single Table
- Updating the Filter Context on a Single Table
Evaluation Context in Detail
- Filter Context + Row Context
Updating the Filter Context
- Restricting the Context
- Enlarging the Context
- Moving From Row Context To Filter Context
Evaluation Context And Relationships
- Row Context for Multiple Tables
- Filter Context for Multiple Tables
- Updating the Filter Context with Relationships
The EARLIER and EARLIEST Functions
- What is the EARLIER Function
- Examples of usage of EARLIER
- The EARLIEST Function
- Examples of usage of EARLIEST

CALCULATE

Introduction to Calculate
Examples of CALCULATE
Detailed Analysis of CALCULATE Behavior

The Calendar Table in PowerPivot

Why a Calendar Table is Useful
- Attribute Consolidation
- Common Calendar Calculations
Creating a Calendar Table with Excel
- Excel Tips to Create a Calendar Table
- Handling Holydays for One Country
- Handling Holydays for Multiple Countries
- Working Days Calculation
Common Calendar Calculations
- Year To Date
- Quarter To Date
- Month To Date
- Same Period Last Year
- Other Custom Aggregation Function and CALCULATE
- Delta Over Previous Year
Creating a Period Table
- Simplify Browsing with a Period Table
- DAX pattern for Period Table
Semi Additive Measures
- Closing Balance Over Time
- The FirstNonBlank and LastNonBlank Functions
- The OPENINGBALANCE and CLOSINGBALANCE Functions
- Balance Updated at Transactions

Mastering Pivot Tables

Different Kind of Pivot Tables
- Classical Pivot Table
- Pivot Table on SSAS OLAP cubes
- Pivot Table on PowerPivot
- Flattened Pivot Table
Using Custom Sorting
- Example with the Calendar Table
- Implementing Custom Sorting
Computing Ratios and Percentage
Implementing Custom Aggregation
Creating Excel Dashboards
Using Linked Tables on PivotTables
Adding new Data to Existing Tables
Defining Sets
- The User Interface
- The MDX Editor to Create Static Sets
- The MDX Editor to Create Dynamic Sets
- Dynamic Sets at work
- Static Sets of Measures

Advanced DAX Patterns

Advanced Ratio and Percentage
Computing Standard Deviation
Ranking Over a Measure
ABC and Pareto Analysis
Event in Progress Pattern

Advanced Data Model Patterns

Banding
- Band Expansion
- Pure DAX Banding
- Banding with CALCULATE
Courier Simulation
- Data Loading and Transformation
- DAX to resolve Complex Relationships
- Using Many To Many Relationships

SharePoint Integration