PowerPivot Workshop

18th October 2012 - 19th October 2012

Who is it for?

This course is aimed at Excel and BI professionals who want to develop models in PowerPivot with Excel 2010.

What you will learn

The workshop outline, in detail, is as follows:

Introduction
What is PowerPivot?
What is a PivotTable?
PowerPivot setup

Basic PowerPivot Concepts
Formatting Numbers
Understanding Calculated Columns
Using Lookup Tables
Understanding Measures
Handling Many Tables
Refreshing Data
Using Slicers

Introduction to DAX
DAX Data Types
DAX Operators
Calculated Column Examples
Measure Examples
Introduction to the Execution Context (Row and Filter)
Choosing Between Calculated Columns and Measures
Handling Errors in DAX
Common DAX Function Examples

Understanding Data Models
What is a Data Model?
Why PowerPivot Users need Data Modelling?
Difference Between Classical and PowerPivot PivotTables
Physical and Logical Data Models
Normalization and Denormalization
Empty and Default Values
The SQL Query Designer as a Data Modelling Tool
Different kinds of Joins
Setting Relationships Manually

Loading Data and Models
Understanding Data Connections
Using Existing Connections
Loading Tables from SQL Server
Filtering Options
Loading and Detecting Relationships
Loading from Views
Loading from Access
Loading Data from Analysis Services
Using Linked Tables
Loading from Excel Workbooks
Loading from Text Files
Using Copy & Paste Operations
Loading From OData Feeds
Loading From SharePoint

Understanding Evaluation Context
What is Row Context?
What is Filter Context?
The EARLIER and EARLIEST Functions

CALCULATE
Introduction to the Calculate function
Examples of how to use the Calculate function
Detailed Analysis of Calculate Behaviour

The Calendar Table in PowerPivot
Why a Calendar Table is Useful
Creating a Calendar Table with Excel
Common Calendar Calculations: Year To Date, Same Period Last Year, Previous Year Growth
Semi Additive Measures

Mastering Pivot Tables and Excel Cube Formulas
Flattened Pivot Tables
Using Custom Sorting and Filtering
Computing Ratios and Percentages
Defining Sets
Using Excel Cube Formulas

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

Advanced Data Model Patterns
Banding DAX to resolve Complex Relationships
Using Many To Many Relationships

SharePoint Integration
PowerPivot for SharePoint
PowerPivot Gallery
Publishing an Excel Workbook
Parameters Pane
PowerPivot Data Refresh
Data Connections
User Credentials

Key benefits

This course will enable you to build your own PowerPivot models, create calculations using DAX and build a complete reporting solution using Excel 2010 and Sharepoint 2010.

This course is taught under license from SQLBI (www.sqlbi.com)

Cost: £Ł499 + VAT until August 31, Ł549 + VAT after that +VAT

Trainer

Chris Webb

Chris Webb is the owner of Crossjoin Consulting, and is a consultant specialising in Analysis Services, MDX, PowerPivot and DAX. He is a co-author of "Expert Cube Development with SQL Server 2008 Analysis Services", "MDX Solutions with Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase" and "SQL Server Analysis Services 2012: The BISM Tabular Model" and blogs at http://cwebbbi.wordpress.com/

Bob Phillips

Bob Phillips is an Excel MVP and a well-known figure in the UK Excel community.

Location

LMG Training and Development
15 Risborough Street
Southwark
SE1 0HG

legal disclaimer

Cancellations must be submitted in writing, either via email or by post. Registrants whose cancellations are received at least 2 weeks before the beginning of the course are entitled to a full refund minus a £80 processing fee. No refunds will be given to registrants who cancel less than 1 week before the beginning of the course or who fail to attend. Substitutions for paid registrants may be made at any time. However, written notice is required and for complete accuracy must include all pertinent changes for the substituting attendee.

In the extraordinary case where the course is cancelled a full refund will be given to all paid registrants. Crossjoin Consulting will not refund any other amount paid by registrants to other companies, including travel expenses and hotel reservations.

Chris Webb's Introduction to MDX course was well-run, informative and good value. It gave me a good understanding of the key principles and ideas and how to apply them to real world situations. It was great environment to learn in, with Chris giving excellent practical exercises and having the depth of knowledge to answer to any group questions.

Calvin Ferns

Consultant, Adatis

Chris has an incredible ability to explain even the most complex logic of Analysis Services and MDX in an easy and funny way. He is one of the best Microsoft Business Intelligence trainers I've ever met.