MDX with Microsoft Analysis Services: How to ask questions and get accurate answers from your Data

Module 1: The Microsoft Business Intelligence Stack

Lessons

• What Business Intelligence can do for your organization
• What to watch out for in implementation
• The Scope and what types of tools address each?

Lab : The Microsoft Business Intelligence Stack

• Examine and run a prebuilt SQL Integration Services Package that performs an Extract Transform and Load into a Data mart.

Module 2: Getting Started with MDX. We walk then run. Learn this and you won’t trip.

Lessons

• MultiDimensional VS Tabular Space
• Data Warehouse Data Mart
• MDX Introduction
• The Editors

Lab : Getting Started with MDX. We walk then run. Learn this and you won’t trip.

• Familiarization with the SQL Server Management Studio Query Editor
• Familiarization with the Business Intelligence Development Studio Query Editor
• SQL Profiler
• Basic MDX

Module 3: The all-important Dimensions. The bearers of truth.

Lessons

• Dimensions
• Dimension Attributes
• Hierarchies
• Referencing Members
• Tuples
• Cells
• Sets
• Set Functions

Lab : The all-important Dimensions. The bearers of truth.

• Dimension Properties
• Dimension Attributes
• Single Dimension Hierarchies
• Multidimensional Hierarchies
• The All Member
• The Members function
• Referencing Members
• Syntax errors
• Partial Tuple references
• Multiple Axis
• Crossjoin
• Auto-Exists
• Exists
• Removing Duplicate Tuples

Module 4: Navigation and the secrets of relatives!

Lessons

• Navigation in reporting
• Relatives
• Navigational Functions
• Controlling Sorting
• Filtering the results
• How to combine a set

Lab : Navigation and the secrets of relatives!

• Navigating a Hierarchy
• Locating Immediate relatives
• Using the members function effectively
• Hierarchize Function

Module 5: MDX Calculations.

Lessons

• Expressions
• Calculated members
• Formatting output through the With clause
• Dynamic Expressions
• IIF function
• Statistical Functions
• Determining which tuples satisfy a parameter in a report

Lab : MDX Calculations.

• Explore variations of the With Clause
• Create Members
• Calculate Percentages
• Advanced Formatting

Module 6: Working with Time. Multiple calendars, divergent horizons.

Lessons

• Time Dimension
• Multiple calendars
• Time based Functions
• ParallelPeriod
• OpeningPeriod
• ClosingPeriod
• LastPeriod
• Year-To-Date
• Calculated Measures and a time Dimension
• Comparing Periods
• Sum Function
• Aggregate Function
• Max and Min Function in Time

Lab : Working with Time. Multiple calendars, divergent horizons.

• ParallelPeriod
• OpeningPeriod
• ClosingPeriod
• LastPeriod
• Year-To-Date
• Calculated Measures and a time Dimension
• Comparing Periods
• Sum Function
• Aggregate Function
• Max
• Min
• Use Crossjoin and ParallelPeriod

Module 7: Business Insights. Now let’s put it to work and get some situational answers.

Lessons

• Or logic
• And Logic
• Combining Or and And logic from different hierarchies
• Logical And with members from the Same
• Using the NonEmpty function
• Moving Averages
• Last date with data
• ParallelPeriod for Multiple Dates
• Testing current context
• Options of the Descendants function
• Ranking Values

Lab : Business Insights. Now let’s put it to work and get some situational answers.

Module 8: Where else will MDX work? SQL Reports

Lessons

• Create a SSRS report
• Create a connection into the cube
• Create a MDX query with the query designer
• Create a custom query
• Pass a parameter
• PerformancePoint Services Dashboard Designer (Not installed PowerPoint and Video demonstration)

Lab : Where else will MDX work? SQL Reports

• Create a SSRS report
• Create a connection into the cube
• Create a MDX query with the query designer
• Create a custom query
• Pass a parameter

Module 9: KPIs and MDX in the Business Intelligence Development Studio Editor

Lessons

• Use Business Intelligence Development Studio to create a named calculation in the cube
• Use Business Intelligence Development Studio to create KPIs

Lab : PerformancePoint Services

• Create a named calculation
• Create a Leaf KPI
• Create an Objective KPI
• Utilize IIF logic in a KPI