Duration 2 Days

Who Should Attend?

Anyone who has a basic knowledge of, and regularly uses, Microsoft Excel.

Learning Objectives

By the end of this course you will be able to:

  • Understand the basic PowerPivot concepts
  • Use the SharePoint feature
  • Shape reports
  • Load and understand data and models
  • Use DAX.

Course Concept

  • Basic PowerPivot Concepts
    • Formatting numbers
    • Handling technical and useless columns
    • Understanding calculated columns and fields
    • Using Lookup tables and Slicers
  • SharePoint Integration
    • PowerPivot for SharePoint and Gallery
    • Parameters pane
    • Data connections
  • Power View
    • Creating a power view report and tiles
    • Inserting new views in power view
    • Type of charts and maps
    • Applying filters to power view reports
  • Shaping Reports
    • Defining KPIs
    • Creating hierarchies
    • Properties for power view reports
    • Named sets in Excel
    • Using perspectives
    • Drillthrough with PowerPivot
  • Loading Data and Models
    • Understanding data connections
    • Using existing connections, linked tables, Schema.INI configuration, Windows Azure Data Market, copy and paste operations
    • Loading tables from SQL servers and detecting relationships
    • Loading data from analysis services
    • Loading from views, Access, Excel, text files, data feeds, and SharePoint
    • Issues in the MDX Query Designer
    • Handling of keys in SSAS
    • OLAP cube or DataMart?
    • Reporting services reports
  • Understanding Data Models
    • Normalisation and denormalisation
    • Empty and default values
    • Understanding how and when to denormalise
    • SQL query designer as a data modelling tool
    • Different kinds of joins
    • Setting relationship manually
    • Understanding OUTER joins
  • Introduction to DAX