Microsoft SQL Server 2014 - Designing BI Solutions: MDX Queries and Performance


Overview/Description
Target Audience
Prerequisites
Expected Duration
Lesson Objectives
Course Number
Expertise Level


Overview/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).

Target Audience
Database and business intelligence administrators and developers who are responsible for designing a BI infrastructure, IT professionals interested learning how to design BI infrastructures and how they're related to data systems, and individuals interested in taking the Designing Business Intelligence Solutions with Microsoft SQL Server exam (70-467)

Prerequisites
None

Expected Duration (hours)
3.0

Lesson Objectives

Microsoft SQL Server 2014 - Designing BI Solutions: MDX Queries and Performance

  • 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 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
  • create MDX calculations to obtain the desired results
  • 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
  • monitor SQL Server instances to optimize performance in different scenarios

    • Course Number:
    md_dbis_a06_it_enus

    Expertise Level
    Expert