1 / 29

MDX Overview

MDX Overview. What Is MDX?. MDX is M ulti D imensional E X pressions 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 all advanced analytical capabilities of Analysis Services. Comparison To SQL. MDX Basics.

arich
Download Presentation

MDX Overview

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. MDX Overview

  2. 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 forOLAP spec • MDX is the key for all advanced analytical capabilities ofAnalysis Services

  3. Comparison To SQL

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

  5. MDX Queries vs. MDXExpressions • MDX Queries • Full statements (SELECT…FROM) • Usually generated by a query tools and applications such as Excel • MDX Sample App deals in queries • MDX Expressions • Partial MDX statements • Define a calculated member, or a set, or member properties, etc. • Returns a single value (which maybe a set)

  6. MDX Myth • Fact: MDX is used everywhere: • Calculated members • Security settings • Custom member rollup • Custom level rollup • Actions • Define named “Sets” • Calculated cells

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

  8. Every Cell Has A Name... Groceries Products Appliances Clothing 1997 1998 1999 Time 2000 2001 Measures Sales Cost Units

  9. Every Cell Has A Name... Groceries Products Appliances Clothing 1997 1998 1999 Time 2000 2001 Measures Sales Cost Units (Products.Clothing, Measures.Units, Time.[2000])

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

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

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

  13. Naming Cells With Relative References... Clothing ? 2000 Sales

  14. Naming Cells With Relative References... (Products.Clothing, Measures.Sales, Time.[2000].PrevMember) Clothing ? 2000 ? Sales

  15. ? Naming Cells With Relative References... (Products.Clothing, Measures.Sales, Time.[2000].PrevMember) (Products.Clothing, Measures.Sales, Time.[2000].NextMember) Clothing ? 2000 ? Sales

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

  17. 1. How Did Sales This Period Compare With Sales In The Previous Period?

  18. 1. How Did Sales This Period Compare With Sales In The Previous Period? (Time.CurrentMember, Measures.CurrentMember) =

  19. 1. How Did Sales This Period Compare With Sales In The Previous Period? (Time.CurrentMember, Measures.CurrentMember) = (Time.CurrentMember, Measures.Sales) -

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

  21. Results For Question 1:

  22. 2. How Did Sales In The Current Period Compare With Sales In The Same Period Last Year?

  23. (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?

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

  25. Results For Question 2:

  26. 3. What Have My Sales Been Since The Beginning Of The Year?

  27. Results For Question 3:

  28. Other useful family function • .Parent • .Children • Ancestor() • Cousin() • .FirstChild • .LastChild • .FirstSibling • .LastSibling

  29. Questions

More Related