advanced mdx subtitle (arial regular 20 point) xx month 200x (arial regular 16 point)

Advanced MDX

Subtitle (Arial regular 20 point)

XX Month 200X (Arial regular 16 point)

Presentation titlePage 2

Agenda

► Conditional Calculations► The tools► Time balance► Variances

► Advanced Member Formulae► Time-based metrics► The generic time design► Using fiscal year crossover time design► Creating analytics dimensions

► Advanced Queries► Select members based on properties► Suppress missing data► Add custom members and sets► Send query output to a file

Presentation titlePage 3

Conditional Calculation Tools

► Inline if (IIF): Returns one of two possible results based on a single test expression

► Case: Returns one of multiple possible results based on multiple test expressions► Simple Case► Searched Case

Presentation titlePage 4

IIF Conditional Statements

► Provides one conditional test, which returns Boolean value

► Includes two possible results► True action► False action

► Uses Missing keyword to return #MISSING► Does not support Else branchingIIF (

[Gross Sales] > 900000 AND IsLevel ([Sales People].CurrentMember, 0), [Gross Sales]*.035, Missing

)

Commission value

Presentation titlePage 5

Simple Case Statement

► Single expression to evaluate► Multiple possible matches and results

Case [Product].CurrentMember.[Drive Size]When [40] Then 1.01When [60] Then 1.03When [80] Then 1.08When [100] Then 1.25Else 1.1

End

Marketing budget percentage

Presentation titlePage 6

Searched Case Statement

► Multiple expressions to evaluate► Similar to IF with ELSEIF branching in calculation scripts

CaseWhen [Net Sales] > 100000 Then .05When [Net sales] > 10000 Then .03Else .01

End

Commission percentage

Presentation titlePage 7

Boolean Functions and Conditional Operators

Leverage functions and operators:

Functions Is IsAncestor IsSibling IsGeneration IsLevel IsLeaf IsUDA

Logical Operators NOT AND OR XOR

Comparison Operators > >= < <= = <>

Presentation titlePage 8

Deriving Variances

► Standard subtraction does not reflect favorable or unfavorable

► Conditional logic for variance and variance percent► UDA for sign flip

► Variance formula:► (Actual - Budget) for non-expense► (Budget - Actual) for expense

► Variance % formula:► ((Actual - Budget) / Budget) for non-expense► ((Budget - Actual) / Budget) for expense

Actual Budget Variance Variance %Gross Sales 100 120 -20 -16.67Discounts (UDA: Expense) 100 120 20 16.67

Presentation titlePage 9

Agenda

► Conditional Calculations► The tools► Time balance► Variances

► Advanced Member Formulae► Time-based metrics► The generic time design► Using fiscal year crossover time design► Creating analytics dimensions

► Advanced Queries► Select members based on properties► Suppress missing data► Add custom members and sets► Send query output to a file

Presentation titlePage 10

Time Dimension Design

Time designs:► Generic time design► Fiscal year crossover time design

Metrics:► Period-to-date► Year-over-year► Period-to-period► Rolling averages

Presentation titlePage 11

Generic Time Designs

Require at least two dimensions:► A time dimension with generic time periods► Discrete years represented in a scenario dimension or in

a separate years dimension

Two-Dimension Design

Three-Dimension Design

Presentation titlePage 12

Fiscal Year Crossover Designs

Require one of the following:► A continuous, single time

dimension with year-specific time periods

► A continuous, single time dimension with duplicate names

Year-Specific Time Periods

Presentation titlePage 13

Calculating Time-based Metrics

► Period-to-date► Year-over-year► Period-to-period► Rolling averages

Presentation titlePage 14

Calculating Period-to-Date

► Sum of specific periodsQTD May (calculated in Time dimension)

► Sum of PeriodsToDateYTD Units (calculated in Measures dimension)

Sum( [Apr], [May] )

Sum( PeriodsToDate ( Generation.[Total Year]), [Period].CurrentMember ), [Units])

Presentation titlePage 15

Calculating Year-over-Year

► Year-over-year (generic time design)Y/Y FY06 (calculated in the Years dimension)

► Year-over-year (fiscal year crossover design)Y/Y Units (calculated in Measures dimension)

OR[FY06] - [FY05] IIF(IsUDA([Measures], "Expense"), [FY05] - [FY06],[FY06] – [FY05])

([Time].CurrentMember, [Measures].[Units]) - (ParallelPeriod([Time].Generations(2)), [Measures].[Units])

Presentation titlePage 16

Calculating Period-to-Period

Is there a member prior to the current year OR is there a member prior to the current period?

Is there a member prior to the current period?

Units minus

Units for the prior time period Units for the closing period of the prior year

TRUE

TRUE

FALSE

FALSE

Missing

Presentation titlePage 17

Calculating Rolling Averages

Is the current period a level 0 member?

Are there at least 5 periods prior to the current period?

Average of

Sales for the latest 6 time periods in the current year

Sales for:[A] all periods through current period in the current year[B] the last six minus the count of current year periods in the previous year

TRUE

TRUE

FALSE

FALSE

Return #MISSING at upper levels

Presentation titlePage 18

Creating Analytics Dimensions

Non-specific formulas:► Ending balance► Period-to-date► Time variance

To accommodate:1. Place formulas in separate

dimension.2. Add required members.3. Create data load member.4. Save the outline.

Load data

Add formulas

Presentation titlePage 19

(

[Measures].[Inventory] )

Providing Time Balance Last with Data Functionality

Tail (

).Item(0).Item(0),

Filter ( Descendants ( [Period].CurrentMember, [Period].Levels(0) ), Not IsEmpty ([Measures].[Inventory]) )

1

2

3

Presentation titlePage 20

Agenda

► Conditional Calculations► The tools► Time balance► Variances

► Advanced Member Formulae► Time-based metrics► The generic time design► Using fiscal year crossover time design► Creating analytics dimensions

► Advanced Queries► Select members based on properties► Suppress missing data► Add custom members and sets► Send query output to a file

Presentation titlePage 21

Selecting Members Based on Properties

Leverage member properties:► UDAs (User-defined Attributes)► Attributes

Presentation titlePage 22

Selecting Members by UDA

The Uda function returns a set of members from the input dimension that all share the input UDA string value.

Presentation titlePage 23

Selecting Members by Attribute

The Attribute function selects members of a base dimension that share the same attribute from a particular attribute dimension.

Presentation titlePage 24

Suppressing Missing Data

Include the NON EMPTY keyword at the beginning of axis request

…NON EMPTY…

Presentation titlePage 25

Adding Custom Members and Sets

► Add custom members to hold calculation results not defined in the database

► Add custom sets as aliases for sets in calculations and SELECT clausesWITHSET set_name AS 'set' |MEMBER calculated_member_name AS 'numeric_value_expression'[, solve_order_specification]SELECT ...

Presentation titlePage 26

WITHMEMBER [Period].[Q3 Projected] AS'[Period].[Qtr 2] * 1.1'SELECT{ [Period].[Qtr 1], [Period].[Qtr 2], [Period].[Q3 Projected]}ON COLUMNS,{[Geography].Children }ON ROWSFROM [HyptekAS].[HyptekAS]WHERE ([Measures].[Net Sales], [Fiscal Year].[FY06])

Adding Custom Calculated Members

A new calculated member exists as a virtual member for the duration of

the query:

Presentation titlePage 27

WITHMEMBER [Period].[Q2 Variance] AS'[Period].[Qtr 2] - [Period].[Qtr 1]'MEMBER [Period].[Q2 Variance %] AS'[Period].[Q2 Variance] / [Period].[Qtr 2] * 100'SELECT{ [Period].[Qtr 1], [Period].[Qtr 2], [Period].[Q2 Variance], [Period].[Q2 Variance %]}ON COLUMNS,{[Geography].Children} ON ROWSFROM [HyptekAS].[HyptekAS]WHERE ([Measures].[Net Sales], [Fiscal Year].[FY06])

Defining Multiple Custom Members

You can define multiple calculated members in the WITH section:

Presentation titlePage 28

Adding Custom Sets

A custom set exists as a virtual set for the duration of the query:

WITH SET [Special Contracts] AS '{[Customer].[IBM], [Customer].[Dell], [Customer].[Radio Hut], [Customer].[Phoenix Electronics]}'MEMBER [Customer].[Total Special Contracts] AS 'Sum ( [Special Contracts], [Measures].[Units] )'SELECT Descendants([Period].[Qtr 2]) ON COLUMNS, {[Special Contracts], [Customer].[Total Special Contracts]} ON ROWSFROM [HyptekAS.HyptekAS]WHERE ([Measures].[Units], [Fiscal Year].[FY06])

Presentation titlePage 29

Sending Query Output to a File

Use the MaxL SPOOL command to log the output of an MDX Query to

a file.

MDX Query

MDX Report

MaxL Shell

MaxL Script