Hands On Lab: Building Your First Queries and Calculations with SQL Server 2008 R2 Analysis Services MDX

This Hands On Lab will show you how to write basic SQL Server 2008 R2 Analysis Services MDX queries.

Overview

This lab will work with MDX in two distinctly different ways. First, in Exercise 1, you will explore how to write MDX statements to retrieve query results from a cube. Second, in Exercise 2, you will extend beyond the knowledge of writing of MDX statements to enrich a cube with calculations and a KPI.

Objectives

The objectives of these exercises are to:

• Explore MDX query statements
• Define Cube calculations, including:
  • Calculated members
  • A named set
  • Scoped assignments
• Define a Cube Key Performance Indicator (KPI)

System Requirements

You must have installed the following items to complete this lab:

• Microsoft SQL Server 2008 R2:
  • Database Engine
  • Analysis Services
  • SQL Server Business Intelligence Development Studio
  • SQL Server Management Development Studio
• SQL Server AdventureWorks2008 R2 sample databases
  • AdventureWorksDW2008R2

Setup

All the requisites for this lab are verified using the Configuration Wizard. To make sure that everything is correctly configured, follow these steps.

1. Launch the Configuration Wizard for this lab by double-clicking the Dependencies.dep file located under the Source\Setup folder of this lab. Install any pre-requisites that are missing (rescanning if necessary) and complete the wizard.

Cleanup

1. There is no need to cleanup if you intend to continue the sequence of labs in this training kit.
2. To restore the original state of the AdventureWorksDW2008R2 SQL Server database and delete the Sales Analysis Analysis Services database, execute the Cleanup.cmd script located under the Setup folder in the Source folder of this lab.

Exercises

This Hands-On Lab comprises the following exercise:

1. Exploring MDX Query Writing
2. Enriching the Reseller Sales Cube with Calculations

Estimated time to complete this lab: 45 minutes.