SQL Server Analysis Services with MDX

Course:  SSASMDX
Duration:  5 Days
Level:  II
Course Summary

This course starts by building an understanding of the business intelligence platform enabled by SQL Server and the Microsoft Office System, highlighting the role of Analysis Services. It continues by creating a simple multidimensional OLAP cube and progressively adding features to help improve, secure, deploy, and maintain an Analysis Services database. Core Analysis Services 2008 features and capabilities, including dimension, cube, and aggregation design wizards are covered. In depth coverage of using MDX with Analysis Services to deliver high-performance business intelligence solutions is included. The student will learn how to assemble time-based business metrics and then customize an Analysis Services cube through the MDX scripts.  Depending on student backgrounds, advanced MDX queries will be covered as time allows.

« Hide The Details
Topics Covered In This Course

Business Intelligence: A Data Analysis Foundation

  • Introducing Business Intelligence
  • Multidimensional Data Analysis
  • Understanding a Dimensional Data Warehouse
  • Multidimensional OLAP

Understanding OLAP and Analysis Services

  • Understanding OLAP
  • Understanding Analysis Services
  • Analysis Services and the Microsoft Business Intelligence Platform
  • Analysis Services Tools

Accessing Source Data

  • Creating a Business Intelligence Solution
  • Creating a Data Source
  • Creating a Data Source View

Creating Dimensions

  • Previewing Dimension Data
  • Creating a Standard Dimension
  • Creating a Time Dimension
  • Creating a Parent-Child Dimension

Creating a Cube

  • Previewing Cube Data
  • Using the Wizard to Create a Cube
  • Deploying and Browsing a Cube
  • Using the Cube Designer to Modify a Cube

Creating Advanced Measures and Calculations

  • Using Aggregate Functions
  • Using MDX to Retrieve Values from a Cube
  • Creating Calculated Members
  • Calculation Scripting
  • Creating KPIs

Advanced Dimension Design

  • Dimension Usage
  • Creating Reference Dimensions
  • Creating a Fact Dimension
  • Creating a Many-to-Many Dimension

Working with Account Intelligence

  • Designing a Financial Analysis Cube
  • Working with Account Intelligence
  • Creating an Account Dimension

Currency Conversion and Multiple Languages

  • Supporting Foreign Currency Conversion
  • Supporting Foreign Language Translation

Interacting with a Cube

  • Implementing Actions
  • Using Writeback to Modify Analysis Services Data

MDX Fundamentals

  • What is Business Intelligence?
  • The Dimensional Model
  • Implementing the Dimensional Model
  • The MDX Language

Using the MDX Query Editor

  • SQL Server Management Studio
  • The MDX Query Editor
  • Building a Simple MDX Query
  • Exploring the Step-by-Step Cube
  • Building a More Complex Query

Understanding Tuples

  • N-dimensional Space
  • Cube Space
  • Accessing Data with Tuples
  • Understanding Cells
  • Working with Partial Tuples
  • Building Tuples with User-Hierarchies

Working with Sets

  • Set Basics
  • Understanding the SELECT Statement
  • Building Sets with Functions
  • Limiting Sets

Working with Expressions

  • Expression Basics
  • Calculated Members
  • Building Dynamic Expressions
  • Resolving Contextual Conflicts
  • Building Complex Expressions

Building Complex Sets

  • Assembling Ordered Sets
  • Retrieving the First or Last Tuples of a Set
  • Filtering Sets
  • Combining Sets
  • Performing Advanced Set Construction

Performing Aggregation

  • Performing Summation
  • Calculating Averages
  • Identifying Minimum and Maximum Values
  • Counting Tuples in Sets

Navigating Hierarchies

  • Accessing Immediate Relatives
  • Accessing Extended Relatives
  • Navigating within a Level

Working with Time

  • Understanding the Time Dimension
  • Calculating an Accumulating Total
  • Performing Period-over-Period Analysis
  • Combining Time-Based Metrics

Enhancing the Cube with T-SQL

  • Understanding the MDX Script
  • Constructing Calculated Members
  • Assembling Named Sets

Implementing Dynamic Security

  • Understanding Dynamic Security
  • Implementing Attribute-Hierarchy Restrictions
  • Implementing Cell-Level Restrictions

Building Reports

  • Connecting to Analysis Services
  • Designing the Dataset
  • Adding Parameters to the Dataset
  • Presenting the Data in the Report

Tuning Considerations

  • Statement optimization
  • Performance monitoring
  • Tools for tuning

Implementing Security

  • Understanding Roles
  • Securing Administrative Access
  • Securing Data Access

Designing Aggregations

  • Understanding Aggregation Design
  • Using the Aggregation Design Wizard
  • Using the Usage-Based Optimization Wizard

Managing Partitions and Database Processing

  • Working with Storage
  • Managing Analysis Services Processing
  • Working with Partitions

Managing Deployment

  • Deployment Overview
  • Deployment Mechanics
  • Deployment Using Business Intelligence Development Studio
  • Deployment Using the Deployment Wizard
  • Understanding Deployment Scripts
  • Migrating Databases and Disaster Recovery

Advanced Monitoring and Management Tools

  • Monitoring Analysis Services Using Windows Reliability And Performance Monitor
  • Monitoring Analysis Services Using SQL Server Profiler
  • Analysis Services Dynamic Management Views

Advanced MDX Topics (as many as time allows)

  • Iterating on a set using recursion
  • Dissecting and debugging MDX queries
  • Optimizing MDX queries using the NonEmpty() function
  • Implementing logical AND on members from the same hierarchy
  • Combining two hierarchies into one
  • Forecasting using the linear regression
  • Forecasting using the periodic cycles
  • Allocating the non-allocated company expenses to departments
  • Calculating the number of days from the last sales to identify the slow-moving goods
  • Analyzing fluctuation of customers
  • Displaying members without children (leaves)
  • Displaying members with data in parent-child hierarchies
  • Implementing the Tally table utility dimension
  • Displaying random values
  • Displaying a random sample of hierarchy members
  • Displaying a sample from a random hierarchy
  • Performing complex sorts
  • Using recursion to calculate cumulative values
What You Can Expect

At the end of this course, students will be able to:

  • Understand OLAP and Analysis Services
  • Create Cubes
  • Design Dimensions
  • Retrieve Data from Analysis Services
  • Implement Security
  • Design Aggregation
  • Manage Partitions and Database Processing
  • Manage Deployment
  • Construct and execute MDX queries.
  • Work with tuples, sets, and expressions.
  • Build complex scripts to retrieve the data users need including aggregation functions and navigating data hierarchies.
  • Create MDX-driven reports in SQL Server Reporting Services
  • Work with advanced MDX scripts
Who Should Take This Course

This course is designed for Developers, Designers, or anyone interested in working with data warehouse data.

Training Style

Instructor led with 50% lecture and 50% lab.

« Hide The Details
Related Courses
Code Course Title Duration Level
2 Days
Implementing and Maintaining SQL Server 2008 Analysis Services and Business Intelligence Solutions
5 Days
Data Warehousing (DW) – Advanced SQL Queries & Performance
2 Days
ORACLE 10g / 11g BI DISCOVERER PLUS for End Users
2 Days
Data Warehousing Fundamentals
3 Days
SQL Server Performance and Tuning for Data Warehouses
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]