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

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

Fakta

Kurs
T2101
Längd
3 dagar
Pris
18.900 kr (exkl. moms)

Malmö

Bookmark and Share

Förkunskaper

The potential participant has a good experience of Excel, some knowledge of relational database theory is recommended but not strictly required.

Kursmaterial

Kursmaterial från Microsoft på engelska ingår.

Kontakta oss
för mer information:

08 - 587 116 10 (Stockholm)
031 - 773 07 90 (Göteborg)
040-662 20 60 (Malmö)
info@informator.se