|
SQL Server Analysis Services 2014/2012 - Advanced DAX Analytics
Course: SSASAA
Duration: 2 Days
Level: II
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.
« Hide The Details
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
- Calculate advanced statistics over their data
- Use advanced filtering of the data for the calculations
- Control the scope of calculations
- Use time intelligence for period-to-period comparisons
- Leverage advanced analytical techniques
- Overcome some limitations of Tabular models
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.
« Hide The Details
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.
|
|
Notify me the next time this course is confirmed!
|
