Schedule

Training Course Schedule
Analyzing Data Using PowerPivot and Programming in DAX

Code: ED17B08
Type: IT InstructorLed Courses
Category: Microsoft Office 2013
Course: Analyzing Data Using PowerPivot and Programming in DAX
Date: 7 Dec 2017 08:15 - 13 Dec 2017 16:00
The analysis of large data volume demands the use of modern tools in order that the analysis is done in a very effective way. People from the Statistics, IT, Risk Management and Control Departments as well as Financial Controllers and Analysts need to gain new skills in order to be able to respond to the new demands for controlling, monitoring and assessment of risks in Cyprus.
PowerPivot is a new ADD-IN in Microsoft Excel 2010 & 2013. PowerPivot allows the user to connect to a database and use its related tables to analyze the data. The user may also create new fields in the tables in order to calculate new data based on existing data in the tables. It uses a new programming language (DAX) which is quite easy to use especially for experienced users of Excel Formulae and Functions.
The PowerPivot Add in is a revolution in data analysis since (compared with “traditional” Pivot Tables) it allows the use of many related tables for data analysis and it also provides fast processing due to the use of In-Memory-storage which is much faster.


Duration: 21 hours (3 days)
Location: EDITC & MMC Conference Center, 16 Imvrou Street, 1055 Nicosia
Language: Greek
Attendance: 4-16
 

Price: €650.00
Subsidy: €357.00
Total: €293.00
Topics

Module 1. Introduction to PowerPivot
 

Using a PivotTable on an Excel table

Using PowerPivot in Microsoft Office 2013

  • Adding information to the Excel table
  • Creating a data model with many tables
  • Understanding relationships
  • Understanding the data model
  • Querying the data model

The PowerPivot add-In

  • Using OLAP tools and converting to formulas

Understanding PowerPivot for Excel 2013

Creating a Power View report


Module 2. Using the unique features of PowerPivot

Loading data from external sources

  • Creating a PowerPivot PivotTable

Using the DAX language

  • Creating a calculated column
  • Creating a calculated field
  • Computing complex aggregations like Distinct Count
  • Refreshing the PowerPivot data model

Module  3. Introducing DAX

Understanding DAX calculations

  • DAX syntax
  • DAX data types
  • INSIDE DAX DATA TYPES
  • DAX values
  • INTELLISENSE

Understanding calculated columns and fields

  • Calculated columns
  • Calculated fields
  • DIFFERENCES BETWEEN CALCULATED COLUMNS AND CALCULATED FIELDS

Choosing between calculated columns and measures

  • CROSS-REFERENCES

Handling errors in DAX expressions

  • Conversion errors
  • Arithmetical operations
  • Error or Missing Values
  • EMPTY VALUES IN EXCEL
  • Intercepting errors

Formatting DAX code

  • HELP WITH FORMATTING DAX

Common DAX functions

  • Aggregate functions
  • Logical functions
  • Information functions
  • Mathematical functions
  • Text functions
  • Conversion functions
  • Date and time functions
  • Relational functions

Using basic DAX functions

Module  4. Understanding data models

Understanding the basics of data modelling

  • Producing a report without a data model
  • Building a data model

More about relationships

Understanding normalization and denormalization

Denormalizing within SQL queries

  • The PowerPivot query designer
  • When to denormalize tables

Understanding over-denormalization

Understanding OLTP and data marts

  • Querying the OLTP database
  • Data marts, facts, and dimensions
  • Star schemas
  • Which database is the best to query?
  • Using advanced relationships

Module  5. Loading data

Understanding data sources

Loading from a database

  • Loading from a list of tables
  • Loading relationships
  • Selecting related tables
  • Loading from a SQL query
  • Loading from views

Opening existing connections
 

Loading from Access

Loading from SQL Server Analysis Services

  • Using the MDX editor
  • Handling of keys in the OLAP cube
  • Loading from a tabular database

Loading from SharePoint

Using linked tables

Loading from Excel files

Loading from text files

Loading from the Clipboard

Loading from a report

Loading from a data feed

Loading from Windows Azure Marketplace

  • Suggest related data

Refreshing connections

Module  6. Understanding evaluation contexts

Introduction to evaluation contexts

  • Understanding the row context
  • THERE ARE ALWAYS TWO CONTEXTS

Testing your evaluation context understanding

  • Using SUM in a calculated column
  • Using fields in a calculated field

Creating a row context with iterators

Understanding FILTER, ALL, and context Interactions

Working with many tables

  • Row contexts and relationships
  • Filter context and relationships
  • Introducing VALUES
  • Introducing ISFILTERED and ISCROSSFILTERED

Evaluation contexts recap

Creating a parameter table

Module  7. Understanding CALCULATE

Why is CALCULATE needed?
 

CALCULATE examples

  • Filtering a single column
  • Filtering with complex conditions

Using CALCULATE inside a row context

Understanding circular dependencies

CALCULATE rules

Understanding ALLSELECTED

Module  8. Using hierarchies

Understanding hierarchies

  • When to build hierarchies
  • Building hierarchies
  • Creating hierarchies on multiple tables
  • Performing calculations using hierarchies

Using parent/child hierarchies

Module  9. Using Power View

What is Power View?
 

Power View basics

  • Using the Filters pane
  • Decorating your report

Understanding table, matrix, and cards

  • Using the matrix visualization
  • Using the card visualization
  • Using a table as a slicer

Using charts

  • Using the line chart
  • Using the pie chart
  • Using the scatter chart
  • Using maps

Understanding drill-down

Using tiles

Understanding multipliers

Using Power View effectively



Who Should Attend
Anyone who wishes to analyze data be connecting to a Database to retrieve vast amounts of data to analyze with PowerPivot Tables.

Purpose
The analysis of large data volume demands the use of modern tools in order that the analysis is done in a very effective way. People from the Statistics, IT, Risk Management and Control Departments as well as Financial Controllers and Analysts need to gain new skills in order to be able to respond to the new demands for controlling, monitoring and assessment of risks in Cyprus.
PowerPivot is a new ADD-IN in Microsoft Excel 2010 & 2013. PowerPivot allows the user to connect to a database and use its related tables to analyze the data. The user may also create new fields in the tables in order to calculate new data based on existing data in the tables. It uses a new programming language (DAX) which is quite easy to use especially for experienced users of Excel Formulae and Functions.
The PowerPivot Add in is a revolution in data analysis since (compared with “traditional” Pivot Tables) it allows the use of many related tables for data analysis and it also provides fast processing due to the use of In-Memory-storage which is much faster.


Objectives

Upon completion the participants will be able to use the PowerPivot tool to:

  • Create Data Models
  • Import and manipulate data
  • Create and edit relationships
  • Create Reports
  • Graph Data
  • Use DAX Functions
  • Build Hierarchies


Prerequisites
Participants should have a very good knowledge of Excel and especially of Pivot Tables and Formulae and Functions. Prior to participating in this course, participants are encouraged to take up an Excel Expert course.

Methodology

The course is 100% practical. The topics are delivered with short presentations by the instructor followed by a  step-by-step demonstration by the instructor and repetition by the students, examples and discussions on how a feature may be used with real life examples and practice through written exercises.



Equipment
The classrooms are equiped with computers (one for each student) projector, Internet access, whiteboard, flipchart and printing facilities.
Each student has a step by step book.


TimeTable

Thursday, 07/12/2017
Monday, 11/12/2017
Wednesday, 13/12/2017




Related Documents
Lealfet and registration form

Find Training Programmes
Name
Type
v
Category
v
Period
v
-
v
 

© EDUCATION & INFORMATION TECHNOLOGY CENTRE (EDITC). All Rights Reserved. Developed by CMP POLYMEDIA LTD