Excel, Power Pivot EX-031

Course Overview

Learn to use the Excel 2010 add-in PowerPivot to analyze large data sets.

Prerequisites

Syllabus

Set up a decision-making approach.

Define the objectives of the analysis.

Clarify the main decision-making principles:

• measure, size, cube, hierarchy

Model the information and build a relationship setup:

• field, table, relationship, primary key, foreign key.
• Check the quality of the data.
• Use PowerPivot to set up relationships among the data and analyze them.

Reminders about managing lists in Excel:

• put into table form, sort, filter, insert a pivot table.

Use different data sources:

• Excel, Access, web, text files (csv, txt …).
• Use SQL Assistant to filter data sources.
• Prepare the data to facilitate the analysis.
• Define the relationships.
• Create pivot tables and graphs based on PowerPivot.
• Use slicers in pivot tables.
• Use advanced PowerPivot functions.
• Add calculated columns and measures.
• Use DAX (data analysis expressions) language.

Use the main DAX functions:

• Classic functions (logical, text, date).
• Filtering functions.
• Time intelligence functions.
• Build and interpret a dashboard.

Construct graphical indicators:

• gauge, speedometer, signal lights.
• Build a dashboard in an Excel sheet, protect the layout, anticipate data updates.