1 / 59

Introduction to Solving Business Problems with MDX

Introduction to Solving Business Problems with MDX. Robert Zare and Tom Conlon Program Managers Microsoft. Agenda. MDX basics Time series analysis Multidimensional Navigation Snapshot data analysis. What is MDX?. MDX is M ulti D imensional E X pressions

glyn
Download Presentation

Introduction to Solving Business Problems with MDX

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to Solving Business Problems with MDX Robert Zare and Tom Conlon Program Managers Microsoft

  2. Agenda • MDX basics • Time series analysis • Multidimensional Navigation • Snapshot data analysis

  3. What is MDX? • MDX is Multi Dimensional EXpressions • MDX is the syntax for querying an Analysis Services database • MDX is part of the OLE DB for OLAP spec • MDX is the key for to utilizing the advanced analytical capabilities of Analysis Services

  4. Comparison to SQL

  5. MDX basics • MDX allows easy navigation in the multi dimensional space • It “understands” the MD concepts of cube, dimension, level, memberand cell • It is used for • Queries – full statements (SELECT…FROM) • Business modeling – definingcalculated members using MDX Expressions – not a full statement

  6. MDX Queries vs. MDX Expressions • MDX Queries • Full statements (SELECT…FROM) • Most often generated by end-user query tools and applications such as Excel or Data Analyzer • MDX Sample App deals in queries • MDX Expressions • Partial MDX statements • Define an analytical object such as calculated member, or named set • Return a single value (which may be a set)

  7. MDX myth “Only developers need to know MDX” • Fact: MDX is used everywhere: • Calculated members • Security settings • Custom member formula • Custom level formula • Actions • Named Sets • Calculated Cells

  8. MDX constructs • Members: an item in a hierarchy • [John Doe] • [2001] • [2001].[Q1].[Jan] • Tuple: an intersection of 2 or more members • ([Product].[Drink].[Beverages], [Customers].[USA]) • ([Product].[Non-Consumable], [2001]) • Sets: a group of tuples or members • {[John Doe], [Jane Doe]} • { ( [Non-Consumable], USA ), ( Beverages, Mexico ) } • [2001].Children • TopCount(Store.[Store Name].Members, 10, Sales)

  9. Every cell has a name... Groceries Products Appliances Clothing 1997 1998 1999 Time 2000 2001 Measures Sales Cost Units

  10. Every cell has a name... (Products.Clothing, Measures.Units, Time.[2000]) Groceries Products Appliances Clothing 1997 1998 1999 Time 2000 2001 Measures Sales Cost Units

  11. Every cell has a name... (Products.Clothing, Measures.Units, Time.[2000]) (Products.Clothing, Measures.Sales, Time.[1999]) Groceries Products Appliances Clothing 1997 1998 1999 Time 2000 2001 Measures Sales Cost Units

  12. Every cell has a name... (Products.Clothing, Measures.Units, Time.[2000]) (Products.Clothing, Measures.Sales, Time.[1999]) (Products.Groceries, Measures.Cost, Time.Year.[1997]) Groceries Products Appliances Clothing 1997 1998 1999 Time 2000 2001 Measures Sales Cost Units

  13. The current cell: CurrentMember (Products.Clothing, Measures.Units, Time.[2000]) = (Products.CurrentMember, Measures.CurrentMember, Time.CurrentMember) Groceries Appliances Clothing 1997 1998 1999 2000 2001 Sales Cost Units

  14. Naming cells with relative references... Clothing ? 2000 Sales

  15. Naming cells with relative references... (Products.Clothing, Measures.Sales, Time.[2000].PrevMember) Clothing ? 2000 ? Sales

  16. ? Naming cells with relative references... (Products.Clothing, Measures.Sales, Time.[2000].PrevMember) (Products.Clothing, Measures.Sales, Time.[2000].NextMember) Clothing ? 2000 ? Sales

  17. ? Naming cells with relative references... (Products.Clothing, Measures.Sales, Time.[2000].PrevMember) (Products.Clothing, Measures.Sales, Time.[2000].NextMember) (Products.Clothing, Measures.Sales.PrevMember, Time.[2000].Lag(3)) OR (Products.Clothing, Measures.Sales.PrevMember, Time.[2000].Lead(-3)) Clothing ? 2000 ? Sales

  18. Calculated members • Calculated members add significant power to cubes • Pre-define complex business logic (so that user does not have to) • Computed at run-time

  19. Agenda • MDX basics • Time series analysis • Multidimensional Navigation • Snapshot data analysis

  20. 1. How did Sales this period compare with Sales in the previous period?

  21. 1. How did Sales this period compare with Sales in the previous period? (Time.CurrentMember, Measures.CurrentMember) =

  22. 1. How did Sales this period compare with Sales in the previous period? (Time.CurrentMember, Measures.CurrentMember) = (Time.CurrentMember, Measures.Sales) -

  23. 1. How did Sales this period compare with Sales in the previous period? (Time.CurrentMember, Measures.CurrentMember) = (Time.CurrentMember, Measures.Sales) - (Time.CurrentMember.PrevMember, Measures.Sales)

  24. Calculated measure: [Sales Growth]

  25. 2. How did Sales in the current period compare with Sales in the same period last year?

  26. (Time.CurrentMember, Measures.Sales)- (Time.CurrentMember.Lag(12), Measures.Sales) 2. How did Sales in the current period compare with Sales in the same period last year?

  27. 2. How did Sales in the current period compare with Sales in the same period last year? (Time.CurrentMember, Measures.Sales)- No! (Time.CurrentMember.Lag(12), Measures.Sales) (ParallelPeriod(Year,1,Time.CurrentMember), Measures.Sales)

  28. Calculated measure: [Sales Growth Y/Y]

  29. 3. What have my sales been since the beginning of the year?

  30. + Time.Feb,Measures.Sales 3. What have Sales been since the beginning of the year? + Time.Jan,Measures.Sales Time.Mar,Measures.Sales ...

  31. + Time.Feb,Measures.Sales Sum(YTD(Time.CurrentMember), Sales) 3. What have Sales been since the beginning of the year? + Time.Jan,Measures.Sales NO! = Time.Mar,Measures.Sales

  32. Calculated measure: [YTD Sales]

  33. Agenda • MDX basics • Time series analysis • Multidimensional Navigation • Snapshot data analysis

  34. Navigating the hierarchy(The Family Tree)

  35. Parents

  36. Parents Time.[2000].Parent

  37. Parents Time.[2001].Parent Time.[2000].Parent

  38. Children Time.[2000].FirstChild

  39. Children Time.[2000].FirstChild

  40. Children Time.[2000].Children

  41. Descendants Descendants( Time.[2000], Quarter)

  42. Descendants Descendants( Time.[2000], Month)

  43. Descendants Descendants(Time.[2000].[Jan], Month)

  44. Descendants Descendants(Time.[2000].[Jan], Month)

  45. Agenda • MDX basics • Time series analysis • Multidimensional Navigation • Snapshot data analysis

  46. The Inventory Problem • A set of inventory snapshots over time • Dimensions • Products: (All), Family, Category, Name • Warehouses: (All), Warehouse • Time: Year, Quarter, Month • Store: (All), Country, City, Store • Measures • Quantity [default aggregation=sum] • The problem: • Measures are not additive over time

  47. The inventory problem: Semi-additive measures Problem: Quantity is not additive over time

  48. Business solutions… • Average quantities in each time period • Opening and closing balances for each time period • Minimum and maximum inventory levels in a time period

  49. 1. Average over time • Sum of quantities over all months in the period, divided by the number of months in the period • Sum( months in the period , Quantity) / Count( months in the period ) • Sum(Descendants( Time.CurrentMember,[Month]), Quantity) / Count(Descendants( Time.CurrentMember,[Month])) • Avg( Descendants( Time.CurrentMember,[Month]), Quantity)

  50. Calculated measure: [Average balance]

More Related