Categories

Our Workshops

Microsoft PowerPivot

Microsoft Office Suite

Description

ONE-DAY WORKSHOP

As with any Excel workbook, in PowerPivot for Excel you can add data from a wide variety of data sources, including a relational database, a multidimensional database, data feeds, an Excel table, or a Reporting Services report.  You can also add data from files on your local computer, and use data that you download from the Web.

However, unlike Excel, you can then create relationships between these data to form a single data set, and then perform analyses against this data. You are not limited to one million rows either -- PowerPivot enables you to add and work with millions of rows of data locally, depending on the actual physical memory available on your desktop computer.

Supported Data Sources

PowerPivot supports importing data from SQL Server databases, Analysis Services cubes, Access databases, Excel worksheets, text files, data feeds, and more.

DETAILED WORKSHOP OUTLINE

PowerPivot

Supported Data Sources

  • What is an Access Database?
  • Set up a new table
  • Set Primary and Foreign keys
  • Primary Key
  • Foreign Key
  • Relationship types
  • Referential integrity

Creating a Pivot Table in the Excel Window

  • Creating a Pivot Table
  • Add Fields to the Row or Column Areas
  • Work with Page fields (Report Filter)
  • Remove Fields from the PivotTable

Add Data by Using the Table Import Wizard

  • Set up a connection to an Access database
  • Filter the Table Data prior to Importing
  • Filter the table data prior to importing
  • Import the Selected Table and Column Data
  • Import the selected table and column data
  • Set up a connection to an Access database using SQL
  • Use a Query to Select the Data to Import
  • Use the query designer tool to select the data to import
  • Copy and paste from an external Excel worksheet
  • Create a linked table

Save Your PowerPivot Workbook

Set up Relationships between Tables

  • Why Set up Relationships?
  • Review Existing Relationships
  • Review existing relationships

Set up New Relationships between Data from Separate Sources

  • Set up your first relationship
  • Set up more relationships between the data from Access and Excel

Set up Relationships in Diagram View

  • The Diagram View
  • Tables in Diagram View

Set up a Calculated Column

Using DAX (Data Analysis Expressions)

  • What is DAX?
  • Why is DAX so important?
  • Syntax
  • Set up a calculated column for Total Profit
  • Set up calculated columns for related data

Set up a Hierarchy in a Table

Set up a Hierarchy

  • Set up a hierarchy from the context menu
  • Set up a hierarchy from the button in the table header

Edit a Hierarchy

Delete a Hierarchy

Set up a PivotTable from PowerPivot Data

  • Add a PivotTable to Your Analysis
  • Add another PivotTable to Your Analysis

Delete a PivotTable

Set up a PivotChart

Delete a PivotChart

Work with Slicers in a PivotTable

Add Slicers to a PivotChart

Set up a Measure and KPI

KPIs

Add a Perspective

Use the Slicers and KPIs to Analyse your PowerPivot Data

______________________________________________________________________________________________________________________________________________________________

On-Site Workshops

  • All workshops are offered in-house at your offices to a minimum of 5 delegates
  • You provide the venue and refreshments, Front Foot supply all the workshop material – including laptops for computer-based training sessions

Remote Workshops

  • All workshops are offered remotely to a minimum of 5 delegates
  • Front Foot supply all the electronic workshop material

________________________________________________________________________________________________________________________________________________________________

Workshop Dates