Solving Business Problems with MDX in SQL Server 2008

3-Days (COURSE-BI-MDXBUSINESS) Description This hands-on, instructor led course will guide you step-by-step through the Analysis Service 2008 Multi Dimensional eXpression (MDX) OLAP query language. You will learn a practical approach to solve real-world problems using MDX expressions, calculations and queries, which will allow users to extract valuable information. For details on booking courses or seminars, please visit our booking page.  Target Audience This course is intended for: Anyone who uses Microsoft Analysis Services and wants to get a good understanding of MDX Anyone who creates OLAP Reports Consultants building OLAP Solutions Developers building OLAP Tools Controllers using cubes and wanting to get more sophisticated information MDX users wanting to understand more details People upgrading  to SQL Server 2008 Prerequisites Before attending this course, it is recommended that students have the following skills: Basic understanding of Analysis Services The Students should know dimensions, attributes, cubes, hierarchies based on Analysis Services 2005 or 2008  Course Objectives Upon completion of this course, the student will be able to: Understand the basic Concepts of MDX Will be able to query Data from a Cube Understand how to Navigate the Hierarchies Will be able to Create Calculated members Will be able to solve business requirements using MDX Will know how to optimize MDX Queries Will be able to write Queries for Reporting Services Reports Will be able to define Security Roles Will be able to create and use KPIs Will be able to Use Cell Calculations and Scoped Assignments Course Summary Outline Day 1 - Introduction and Basics Module 01: MDX Basics Basic Concepts Tuples as Address in Cube Sets LAB 01A: Using Management Studio to Query Data Use BI Development Studio to define Calculations Module 02:  Basic Calculations and Queries Basic Calculation LAB 02A: Basic Queries Basic Calculations Key difference to SSAS 2000 MDX (using Hierarchies/Attributes) Using Tuples LAB 02B: Tuples in Calculations Tuples in Sets (Dimensionality) Review MDX Syntax Module 03:  Navigation in Trees Relationships LAB 03A: Calculations using Tuples and Relative Tree Navigation Conditions using IIF Null Handling Real World Formulas (Ratios  ...) Element Name, Uniquename ... Review Module 04:  Sets Introduction Set Functions Filter (Search for Items, Filter by values,…) TopCount / BottomCount (Show top Customers,...) Order Aggregation (Sum, Min, Max,...) Rank LAB 04A: Querying Sets Real World Examples LAB 04B: Real World Queries Speed Issues LAB 04C: Sets and Execution Speed Review DAY 2 Module 05:  Common Calculations Percent Contributions Allocations LAB 05A: Allocations Averages (simple, weighted, moving) Time Based References LAB 05B: Time Based References Aggregations Boundary Condition LAB 05C: Aggregations Module 06:  MDX Scripting Scope / Sub Cube Special Aggregations LAB 06A: Using MDX Scripting Module 07:  Advanced MDX Topics Date Calculation String Operations LAB 07A: Date and String Handling Visual Totals Dynamic MDX (STRTOSET() ,...) Solve Order  / Calculation Pass LAB 07B: Dynamic MDX Autoexists Nonempty Handling Module 08:  Optimizing MDX For 2005 For 2008 Day 3 - Advanced Topics  Module 09:  Creating and Querying KPIs Introduction Examples LAB 09A: Using and Defining KPI Module 10:  Using MDX in Setting Analysis Services Security Introduction Roles Dynamic Security (based on UserName) LAB 10A: Security Module 11:  Reporting Services Introduction Limitation for MDX in Reporting Services Sub Selects Building MDX Based Reports Best Practice LAB 11A: Queries in Reporting Services Parameter Handling How To use Parameter / technical details Depending Parameter Practice LAB 11B: Parameter Handling in Reporting Services Calculations in Reporting Services Module 12:  Using MDX in Special Situations Actions Drillthrough Creating Local Cubes for Off-Line Browsing Writing Back to a Cube LAB 12: Using Client Features