Title: Implementing Calculations Using MDX
1Implementing Calculations Using MDX
2Dimension 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
3Comparing to Spreadsheet Formulas
4Defining 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
5Every Cell Has a Name
All State
Canada
Mexico
USA
Dollars
Units
All Product
Bread
Dairy
Meat
6One Dimension Single Coordinate
x3
February
7Two Dimensions Double Coordinate
(x3, y4)
(January, Sphinx Bagels)
8Three Dimensions Triple Coordinate
(x4, y2, z3)
(Washington, January, Sphinx Bagels)
9Multidimensional Coordinate Terminology
- 4 Dimensions Quadruple Coordinate
- 5 Dimensions Quintuple Coordinate
- 6 Dimensions Sextuple Coordinate
- 7 Dimensions Septuple Coordinate
- Generic Term Tuple
10Tuple 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)
-
11Complete 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
12Tuples and Unspecified Dimensions
(Sales Units)
(State.CurrentMember,Time.Calendar.CurrentMember,
Product.CurrentMember,Sales Units)
- Unspecified DimensionsUse the Current Member
13Tuples and Regular Cells
(All State, All Time, Dairy, Sales
Dollars)
- Tuple Comes from Row, Column, and Filter
14Percent of Total Calculations
15Creating 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
16Using Functions within Calculated Members
- Used for Complex Calculations
- Included in the Calculated Member Builder
- Located in Specific Folders
17Understanding 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
18Introducing Solve Order
0.67
1.33
4.00
2.67
400
150
5.33