Implementing Calculations Using MDX

1. Implementing Calculations Using MDX

2. Drinks Tea Lemon Earl GreyCoffee Columbian Drinks Tea Lemon Earl GreyCoffee Columbian Drinks Tea Lemon Earl GreyCoffee Columbian Drinks Tea Lemon Earl GreyCoffee Columbian Drinks Tea Lemon Earl GreyCoffee Columbian Drinks Tea Lemon Earl GreyCoffee Columbian Drinks Tea Lemon Earl GreyCoffee Columbian Drinks Tea Lemon Earl GreyCoffee Columbian 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. ([All Product], [All State],Dollars) (Meat, Mexico, Dollars) (Bread, USA, Units) Every Cell Has a Name All State Canada Mexico USA Dollars Units All Product Bread Dairy Meat

6. Cube 0 1 2 3 4 5 One Dimension — Single Coordinate x=3 • Geometry [February]

7. Comma and parentheses Order does not matter 5 5 4 4 • Cube 3 3 2 2 1 1 0 0 1 1 2 2 3 3 4 4 5 5 0 0 Two Dimensions — Double Coordinate (x=3, y=4) • Geometry ([January], [Sphinx Bagels])

8. 5 4 3 2 5 1 0 3 1 4 2 2 Oregon 3 1 4 5 3 Washington • Cube 2 1 0 3 1 2 2 4 3 1 4 5 Three Dimensions — Triple Coordinate (x=4, y=2, z=3) • Geometry ([Washington], [January], [Sphinx Bagels])

9. 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)

10. 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

11. Tuples and Unspecified Dimensions • Unspecified DimensionsUse the Current Member ([Sales Units]) (State.CurrentMember,Time.Calendar.CurrentMember,Product.CurrentMember,[Sales Units])

12. Tuples and Regular Cells • Tuple Comes from Row, Column, and Filter ([All State], [All Time], [Dairy], [Sales Dollars])

13. Percent of Total Calculations

14. 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

15. Using Functions within Calculated Members • Used for Complex Calculations • Included in the Calculated Member Builder • Located in Specific Folders

16. 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

17. Sales $ Sales $ Units Units Sales $ / Units Sales $ / Units 100 150 100 75 Bread Bread 300 75 400 150 Dairy Dairy Meat Meat 100 150 0.67 100 75 1.33 Dairy + Meat Dairy + Meat 300 75 4.00 Introducing Solve Order 0.67 1.33 4.00 2.67 400 150 5.33