Description

This training will teach you to master the MDX (Multi-Dimensional eXpressions) language to query your multidimensional data. You will see how to design scripts, query SQL Server 2005/2008 Analysis Services cubes and add business calculations to these cubes.

Who is this training for ?

For whom ?

Developers, business intelligence project managers, advanced users having to create business calculations and query OLAP solutions in the SQL Server 2005/2008 environment.

Prerequisites

Training objectives

Write basic queries in MDX

language Develop advanced indicator calculation formulas in MDX

language Use sorting and classification instructions for data with MDX

Modify data in a cube with MDX language

Training program

• General overview of the MDX
  • Reminder of the principles of OLAP.
  • Definition of the language.
  • The underlying mechanisms.
  • The different uses of MDX with Analysis Services.
  • Presentation of the basic concepts of the language (member, tuple, set, hierarchy.
  • ).
  • Practical work Examples of queries and use cases.
• MDX Query Design
  • Using the query development tool.
  • Statement syntax ?'SELECT'.
  • Writing complex queries.
  • Use of key words and functions of the MDX language.
  • Practical work Getting started with the development tool.
  • Writing simple and complex MDX queries.
• Calculated Members and Named Games
  • Definition and usage scenarios for calculated members.
  • Definition and usage scenarios for named sets.
  • Syntax for writing calculated members and named sets .
  • Practical work Example of creating business calculations (margin, relative contribution, reallocation).
• Navigation and selection
  • Navigation in dimension hierarchies.
  • Handling NULL and division by 0.
• Sorting and classification of data
  • Perform sorting, ranking, rankings on members via the MDX.
  • Usage scenario.
  • Practical work Use of sorting and sorting instructions classification of data with MDX.
• Scripting de cubes SQL Server Analysis Services avec MDX
  • Adding business calculations and functionalities to OLAP cubes with MDX.
  • Usage scenarios.
  • Practical work Writing a script for customize a cube.
• Changing the multidimensional environment
  • Writing to OLAP cubes (Writeback): creation, update.
  • Modification, deletion of a cube with MDX.
  • Using transactions.
  • Practical work Updating data with MDX.