SQL Server Analysis Services 2014/2012 - Advanced Data Analysis and Optimization

Course:  SSASAO
Duration:  3 Days
Level:  II
Course Summary

The course aims to broaden your knowledge about Analysis Services to its maximum level for the creation of advanced OLAP solutions, based on terabyte-sized data warehouses. The course focuses on advanced work with the dimensions, facts and measure groups. It also covers optimization of OLAP cubes and MDX queries, creation of real-time cubes, performance scalability, and advanced administration of Analysis Services. The course covers in depth the practical problems that arise when developing and deploying larger OLAP projects. It?s vital for mastering Analysis Services OLAP cubes.

« Hide The Details
Topics Covered In This Course

Dimensions In-depth

  • Regular dimensions
  • Referenced dimensions
  • Fact dimensions
  • Many-to-many dimensions
  • Data mining dimensions
  • Slowly changing dimensions - Type I, II and III
  • Junk dimensions
  • Fixing dimension design problems
  • Handling very large dimensions

Advanced Dimension Design

  • Custom rollups
  • Parent-child hierarchies and unary operators
  • Naming templates for the levels of parent-child hierarchies
  • Ragged hierarchies
  • Sorting members in dimensions - simple and complex
  • Usage of All Members, Default Members and Unknown Members
  • Configuration of error handling
  • Storage mode of dimensions
  • Grouping dimension members - automatic, custom
  • Dimension Intelligence
  • Account Intelligence
  • Time Intelligence
  • Time dimension and its configuration
  • Dimension writeback
  • Advanced modeling with many-to-many dimensions
  • Linked dimensions
  • Calculation dimensions

Advanced Work With Facts

  • Facts created from dimensions
  • Using multiple fact tables
  • Using multiple fact tables with different granularity of facts
  • Connecting fact tables to dimensions by composite attributes (multiple columns)
  • Calculated Facts
  • Adding custom calculations into cubes
  • Currency conversion and exchange rates
  • Cell Writeback
  • Writeback of other than the end members - the equal, weighted and incremental allocation
  • Lazy Aggregation
  • By Account aggregation
  • Linked measure groups


  • Creating local and remote partitions
  • Optimizing processing and query performance using partitions
  • Storage modes and their settings
  • Proactive caching
  • Configuring and designing aggregations
  • Usage-based optimization of aggregations
  • Merging partitions

Real-time OLAP cubes

  • Long Latency Scenario
  • Caching after changing data
  • Caching using timed updates
  • Average Latency Scenario and MOLAP modes
  • No Latency Scenario and ROLAP storage

Optimizing OLAP cubes

  • Internal architecture of SSAS and OLAP cubes
  • Optimization of solutions design
  • Tuning dimensions and attributes
  • Optimizing Processing
  • Incremental processing
  • Identifying bottlenecks
  • Processing parallelism
  • Scaling performance
  • Optimization of SSAS server settings

Optimizing MDX Queries

  • Calculation model
  • Architecture of query processing
  • Tools for analysis and performance tuning
  • Monitoring queries
  • Analysis of the performance problems with queries
  • Optimization techniques
  • Cache warming
  • Scale-out using read-only databases

Advanced SSAS Administration

  • Online mode of cube modification
  • Server-side management of partitions
  • Using a .NET libraries for extending SSAS and deployment on the server
  • Monitoring resources and activity
  • Connecting to SSAS over HTTP connection
  • Failover clustering for SSAS
  • Offline cubes
What You Can Expect

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

  • Use all advanced dimension features
  • Redesign large dimensions and data warehouses correctly
  • Reuse existing MDX code to avoid repeating the same expressions
  • Work with facts on an advanced level
  • Enable write-back for what-if analysis
  • Configure cube storage and processing correctly
  • Create real-time OLAP cubes
  • Optimize performance of cubes and MDX queries
  • Use advanced SSAS features
Who Should Take This Course

Data analysts, BI developers and anybody else, who wants to master Analysis Services for developing advanced analytical solutions over large amounts of data, without spending ages doing that.

Recommended Prerequisites

Basic knowledge of developing and querying Analysis Services cubes.

Training Style

Instructor led, with hands-on workshops.

« Hide The Details
Related Courses
Code Course Title Duration Level
SQL Server Reporting Services with BI Tools Part 1 - Report Builder, Data Tools, Cubes, and Tabular Reporting
5 Days
SQL Server Analysis Services 2014/2012 - Tabular model with DAX
3 Days
Accelerated Microsoft Business Intelligence: From SQL 2008 through SharePoint and Office 2007
4 Days
SQL Server Analysis Services 2014/2012 - Advanced DAX Analytics
2 Days
SQL Server Reporting Services with BI Tools Part 2 - Report Server Admin, Extensive Tabular Modeling/Reporting, and C#/VB Extensions
5 Days

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.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]