SQL Server Analysis Services 2014/2012 - Advanced DAX Analytics

Course:   SSASAA
Duration:   2 Days
Level:   II
On our website at:   http://www.verhoef-training.com/courses/SSASAA.html
 
Course Summary

The course will teach the delegates how to use PowerPivot and Analysis Services tabular models for highly advanced analyses. The course focuses particularly to the analytical DAX language built-in to the PowerPivot and tabular models. It contains piles of practical, real-world examples, designed for the delegates to practice all the functionality step by step. At the end, they?ll know how to combine all parts of DAX language for various advanced, but also non-standard analyses.

Topics Covered In This Course

Analytical PowerPivot Functionality

  • Aggregate indicators and calculated fields
  • Data hierarchies - analysis by year, month, day, product lines, departments, regions, ...
  • Key Performance Indicators - KPIs
  • Horizontal and vertical slicers
  • Linking slicers to multiple PowerPivot pivot tables and graphs

Formulas in DAX Language

  • Introduction to the DAX language - Data Analysis Expressions
  • Syntax of formulas and functions
  • Handling errors in the formulas
  • Operators in DAX
  • Data types in PowerPivot
  • Functions for working with dates and times
  • Information functions ?  Functions for controlling flow of logic
  • Mathematical functions
  • Text functions
  • Filtering, evaluation and statistical functions - overview
  • Time Intelligence functions - overview
  • FORMAT function - custom output formatting

Statistical Functions

  • Regular statistical functions - SUM, MIN, MAX, AVERAGE, VAR, STDDEV ...
  • Custom variations of statistical functions - SUMX, COUNTAX, MAXX, ...
  • Ranking - RANKX, RANK.EQ
  • Sampling - SAMPLE ,TOPN
  • Data aggregation - SUMMARIZE, COUNTROWS, DistinctCount
  • Running value calculations ? year-to-date calculations, by product categories, by hierarchies, ...
  • Combination of data from multiple tables - CROSSJOIN, GENERATE and GENERATEALL
  • ADDCOLUMNS and ROW functions

Filtering and Evaluating Functions

  • Logical flow of DAX calculations and its influencing
  • Execution Context, Filter Context and Row Context
  • Changing the calculation scope using the CALCULATE function
  • Filtering of data ? the FILTER function
  • Extending the calculation scope over the filtered data ? functions of ALL, ALLEXCEPT, ...
  • Informational function about applied filters
  • Retrieving data from other tables - RELATED, RELATEDTABLE, LookupValue
  • The CALCULATETABLE function
  • Recursive calculations - EARLIER, EARLIEST
  • DISTINCT and VALUES functions
  • Functions for working with parent-child hierarchies - PATHxxx
  • Combining filtering and evaluation functions
  • Advanced usage of filtering functions with another functions
  • Custom filtering

Time Intelligence Functions - Data Analysis Across Time Periods

  • Time table
  • Working with time - DATEADD, DATESBETWEEN, DATESINPERIOD
  • Functions for moving in time - NEXTMONTH, NEXTYEAR, PreviousDate, ...
  • Balance at the beginning / end of the period - OPENINGBALANCExxx, CLOSINGBALANCExxx
  • Parallel time periods - ParallelPeriod, SAMEPERIODLASTYEAR
  • First and last dates for the displayed periods - FirstDay, LASTDATE, ...
  • Beginnings and ends of the selected periods - ENDOFMONTH, STARTOFQUARTER, ...
  • Cumulative time periods - DATESYTD, TOTALYTD, TOTALQTD, ...
  • Combination with filtering and evaluation functions
  • Fixing bugs in time intelligence functions with alternative workarounds

Advanced Analytical Techniques

  • Running totals
  • Moving averages
  • Universal calculation formula
  • Dynamic value ranges and banding
  • Designing many-to-many (M: N) relationships between tables
  • Comparing planned vs. current numbers
  • Parameterization of the data model
  • Calculating missing data
  • Correcting erroneous data
  • Analysis of the shopping cart, ABC analysis, scoring of customers
  • Reporting simultaneously in multiple currencies and in the consolidated currency
What You Can Expect

Upon successful completion of this course, students will be able to:

Use advanced PowerPivot features for analytics

Who Should Take This Course

Advanced data analysts who want to get maximum from analytical power of DAX language in Tabular models and PowerPivot.

Recommended Prerequisites

Basic knowledge of PowerPivot or Tabular model, and DAX language.

Training Style

Instructor led, with hands-on workshops.

Related Courses
Code Course Title Duration Level
SSASTM
SQL Server Analysis Services 2014/2012 - Tabular model with DAX
3 Days
I
Details
MSBI
Accelerated Microsoft Business Intelligence: From SQL 2008 through SharePoint and Office 2007
4 Days
I
Details
SSASAO
SQL Server Analysis Services 2014/2012 - Advanced Data Analysis and Optimization
3 Days
II
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.