Implementing Calculations Using MDX

1
Implementing Calculations Using MDX
2
Dimension Family Relationships

• Drinks is the Parent of Tea and Coffee

• Tea and Coffee are Children of Drinks

• Tea and Columbian are Descendants of Drinks

• Tea and Drinks are Ancestors of Earl Grey

• Tea and Coffee are Siblings

• Lemon and Columbian are Cousins

• All are dimension Members

3
Comparing to Spreadsheet Formulas
4
Defining Calculated Members

• Can Belong to Any Dimension
• Calculate at Query Time
• Do Not Impact Cube Size
• Are Usually Defined by Stored Members
• Can Also Be Defined by
• Other calculated members
• Arithmetic operators
• Numeric constants
• MDX functions
• Can Make Use of External Function Libraries
• Appear as Stored Members to Clients

5
Every Cell Has a Name
All State
Canada
Mexico
USA
Dollars
Units
All Product
Bread
Dairy
Meat
6
One Dimension Single Coordinate
x3

• Geometry

February
7
Two Dimensions Double Coordinate
(x3, y4)

• Geometry

(January, Sphinx Bagels)
8
Three Dimensions Triple Coordinate
(x4, y2, z3)

• Geometry

(Washington, January, Sphinx Bagels)
9
Multidimensional Coordinate Terminology

• 4 Dimensions Quadruple Coordinate
• 5 Dimensions Quintuple Coordinate
• 6 Dimensions Sextuple Coordinate
• 7 Dimensions Septuple Coordinate
• Generic Term Tuple

10
Tuple Multidimensional Coordinate

• A Tuple Is a Multidimensional Cell Coordinate
• At most one member from a dimension
• Order of members does not matter
• If more than one dimension
• Separate members with commas
• Enclose tuple in parentheses
• How Do You Pronounce Tuple?
• Too-ple (like quadruple)
• Tup-ple (like quintuple)

11
Complete and Partial Tuples

• Complete Tuple
• One member from each dimension
• Required to retrieve a value
• Partial Tuple
• Omits some dimensions
• May have as few as one dimension
• Omitted Dimensions in Partial Tuple Use Current
  Member
• Current Member Defined by
• Row/column axes
• Slicer definition
• Default member

12
Tuples and Unspecified Dimensions
(Sales Units)
(State.CurrentMember,Time.Calendar.CurrentMember,
Product.CurrentMember,Sales Units)

• Unspecified DimensionsUse the Current Member

13
Tuples and Regular Cells
(All State, All Time, Dairy, Sales
Dollars)

• Tuple Comes from Row, Column, and Filter

14
Percent of Total Calculations
15
Creating Non-Measure Calculated Members

• Common Applications
• Calculate the difference between two members
• Calculate the average of multiple members
• Create rollups outside of the normal hierarchy
• Aggregation Behavior
• Do not roll up in the parent dimension
• Do not affect or add to cube aggregations
• Calculated Member Intersections
• Intersect with every member of every other
  dimension

16
Using Functions within Calculated Members

• Used for Complex Calculations
• Included in the Calculated Member Builder
• Located in Specific Folders

17
Understanding Other Calculation Methods

• Custom Rollup Formulas
• Override the Aggregate Function properties across
  a dimension level
• Apply to all members of a level
• Custom Member Formulas
• Replace the definition of a single member without
  affecting other members
• Enable members to aggregate with the other
  dimension members
• Calculated Cells
• Supply data values to existing cube cells based
  on MDX logic
• Allow for specific calculation scope and logic

18
Introducing Solve Order
0.67
1.33
4.00
2.67
400
150
5.33