Implementing Calculations Using MDX - PowerPoint PPT Presentation

1 / 17
About This Presentation
Title:

Implementing Calculations Using MDX

Description:

Tea and Coffee are Siblings. Tea and Columbian are Descendants of Drinks ... Coffee. Columbian. Comparing to Spreadsheet Formulas. Defining Calculated Members ... – PowerPoint PPT presentation

Number of Views:152
Avg rating:3.0/5.0
Slides: 18
Provided by: downloadM
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com