• Online, Self-Paced
Course Description

Multidimensional expression, MDX, is an industry standard query language used to retrieve data as well as perform data definition and data manipulation operations for Analysis Services. MDX can be used in various tools to create ad hoc or reusable scripts to work with Analysis Services cubes. An important part of ensuring a BI solution is operating efficiently is monitoring the performance of the different SQL Server instances being used, including SQL Server Analysis Services, the data warehouse, and Reporting Services. In this course, you will learn how to create MDX calculations to obtain the desired result set. You will also learn how to monitor the performance of the SQL Server instances to get optimal performance. This course is one of a series in the Skillsoft learning path that helps individuals prepare for the Designing Business Intelligence Solutions with Microsoft SQL Server exam (70-467).

Learning Objectives

MDX Calculations

  • start the course
  • describe the key concepts in MDX
  • describe the basic structure of an MDX query
  • describe the common MDX functions
  • use MDX set functions
  • use MDX tuple functions
  • use the CALCULATE and SCOPE statements as well as the THIS function
  • create query-scoped calculated members using the WITH keyword
  • create session-scoped calculated members using the CREATE MEMBER statement
  • perform a simple case statement to compare an expression to a set of simple expressions to return specific values
  • perform a searched case statement to evaluate a set of Boolean expressions to return specific values


Practice: Execute MDX Queries

  • create MDX calculations to obtain the desired results


Manage Performance

  • use Data Collector to monitor a SQL Server instance by gathering performance-related data
  • use Performance Monitor to troubleshoot data warehouse performance issues
  • use Resource Governor to balance load resources for an ETL load process
  • use Performance Monitor to troubleshoot SSAS performance
  • use SQL Server Profiler to analyze and optimize performance of MDX queries
  • use SQL Server Profiler to analyze and optimize performance of DAX queries
  • use Performance Monitor to monitor SSRS performance
  • configure memory settings for Reporting Services to ensure optimal performance
  • configure caching for a report and monitor its affects using Performance Monitor


Practice: Monitor SQL Server Performance

  • monitor SQL Server instances to optimize performance in different scenarios


Framework Connections

The materials within this course focus on the NICE Framework Task, Knowledge, and Skill statements identified within the indicated NICE Framework component(s):

Specialty Areas

  • Data Administration