1 / 49

DAT312 troduction to Solving Business Problems with MDX II

DAT312 troduction to Solving Business Problems with MDX II. Robert Zare Program Manager Microsoft. Agenda. What is MDX? Moving Average Filters Setting the default member Member Properties. You should already know…. What is MDX Members, Tuples, Sets

ravi
Download Presentation

DAT312 troduction to Solving Business Problems with MDX II

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. DAT312troduction to Solving Business Problems with MDX II Robert Zare Program Manager Microsoft

  2. Agenda • What is MDX? • Moving Average • Filters • Setting the default member • Member Properties

  3. You should already know… • What is MDX • Members, Tuples, Sets • How to create a simple calculated measure • Common MDX functions • CurrentMember, PrevMember, Lag, Lead, ParallelPeriod • Parent, Child, Ancestors, Descendants • Difference between an MDX Query and an MDX Expression

  4. Knowing MDX After today’s talk Knowledge Required You are here Functionality

  5. Agenda • What is MDX? • Moving Average • Filters • Setting the default member • Referencing Member Properties

  6. Moving averages • Business problem: provide a trend line against which to measure current values

  7. Computed measure: 3- month moving average sales • For every month, take the average of this month, last month, month before • Avg({This month, last month, month before}, [Measures].[Unit Sales]) MA3 Jun MA3 Apr Jan Feb May Mar Jun Apr MA3 May

  8. Calculated measure: 3-month moving average sales • For every month, take the average of this month, last month, month before • Avg({This month, last month, month before}, [Measures].[Unit Sales]) • Avg([Time].CurrentMember.Lag(2): [Time].CurrentMember, [Measures].[Unit Sales])

  9. Simple Moving Average

  10. Improve the calculation • Typical business scenario has different moving average periods for different levels • 30-day • 3-month • 2-quarter • 1-year

  11. Complex moving average: the approach • Avg([Time].CurrentMember.Lag(2): [Time].CurrentMember, [Measures].[Unit Sales]) • The parameter of the Lag function needs to change based on the level we’re looking at • If month: Lag(2) : Current • If quarter: Lag(1) : Current • If year: Lag(0) : Current

  12. Complex moving average: The solution • Level function • Tells us where we are in the hierarchy • [Time].CurrentMember.Level • Iif function • iif( <<condition>>, <<if true>>, <<if false>>) • Can return a number or a string but not both • Iif(Time.CurrentMember.Level IS Time.Month, 2, …) • Avg(Time.CurrentMember.Lag( Iif(Time.CurrentMember.Level IS Time.Month,2, Iif(Time.CurrentMember.Level IS Time.Quarter,1, 0)) ) : Time.CurrentMember, [Measures].[Unit Sales])

  13. Complex Moving Average

  14. Moving Average: Lessons • Moving average is simple to compute in MDX • Range (:) operator allows us to specify numerous continuous members without listing each explicitly. • Level function returns location (level object) within a hierarchy • Iif function is used to define a measure that is computed differently, for example at different levels of the hierarchy

  15. Agenda • What is MDX? • Moving Average • Filters • Setting the default member • Referencing Member Properties

  16. Filters • Filter function returns a set that meets a certain criteria: • Enables targeting the calculation at a given subset of the population • For example, where <<some value>> is greater than <<X>> • Syntax: Filter (<<set>>, <<condition>>) • Business problem: • Which periods have had many products whose sales fell from the previous period

  17. Our solution plan • Which products had a drop in sales? • How many of those products were there? • How many products total do we have? • Define a calculated measure that divides the two values

  18. Which products… • We’re looking for a set • The set is composed of products (no matter where we happen to be in the product hierarchy) • Descendants function will return a set of Products (names) : • Descendants([Product].CurrentMember, [Product Name])

  19. … had a drop in sales? • We’re still looking for a set; the subset of the previous set that experienced a drop in sales • Filter function returns a set that meets a certain criteria: • Sales this period less than sales last period • Syntax: Filter (<<set>>, <<condition>>) • Filter( SetOfProducts, ([Time].CurrentMember, [Unit Sales]) < ( [Time].PrevMember, [Unit Sales] ) )

  20. Which products had a drop in sales? • Our complete Filter expression is: Filter( Descendants([Product].CurrentMember, [Product Name]), ([Time].CurrentMember, [Unit Sales]) < ([Time].Prevmember, [Unit Sales]))

  21. How many of those products were there? • How many members are in that set? • Count(Filter( Descendants([Product].CurrentMember, [Product Name]), ([Time].CurrentMember, [Unit Sales]) < ( [Time].Prevmember, [Unit Sales])) • How many total products total do we have? • Count(Descendants( [Product].CurrentMember, [Product Name])) • Each of these expressions could be stored as a separate calculated measure

  22. The whole calculated measure • Measures.[Pct Down Sales] = • Count(Filter( Descendants([Product].CurrentMember, [Product Name]), ([Time].CurrentMember, [Unit Sales]) < ([Time].Prevmember, [Unit Sales])))/Count(Descendants( [Product].CurrentMember, [Product Name]))

  23. Filters

  24. Filters: Lessons • Use Descendants to point a calculation at a specific level • Often the bottom-most level of the hierarchy • Use the Filter function to eliminate members from a set based on a comparison criteria • Construct complex measures a piece at a time

  25. Agenda • What is MDX? • Moving Average • Filters • Setting the default member • Referencing Member Properties

  26. Setting the default member • The default member for any dimension is the some member in the highest level • Usually “(All)” • Problem: • Users often want something else, especially for Time • Solutions: • Change the default member to be a different (fixed) member and re-edit as needed • Make the default member dynamic!

  27. Setting the default member in the Time dimension • 1997 is the default member in our example • We would like to see something more recent • Would ([Time].[1998]) be good enough ?

  28. What about automatically displaying the Last Year? • How to dynamically find out the last year? • LastSibling function • [Time].[1997].LastSibling • “Last” follows the sort order of the dimension

  29. ...and the Last Month ? • LastChild function • Last Quarter (Q4) is the last child of the last year (1998) • Last Month (December) is the last child of the last quarter • We now know how to do Last Year ! • ([Time].[1997].LastSibling.LastChild.LastChild) • Or better yet: • Tail([Time].[Year].Members,1).Item(0).LastChild.LastChild

  30. Dynamic Default Member

  31. Last Month: Problem! • There’s no data for December 1998! • What we really want is the “Last Month for which we have data” • aka “Last Non Empty Month”

  32. Last Non Empty Month • Can leverage the Filter and IsEmpty functions • IsEmpty( <<expr>> ) • Our Solution plan: • Get all the months • Filter on those which have data • Take the last month in the set

  33. Last Non Empty Month • Get all months: • [Time].[Month].MEMBERS • Filter those which are Non Empty: NOT IsEmpty([Time].CurrentMember) • IsEmpty – Returns TRUE when the cell doesn’t have data (empty) • Filter( [Time].[Month].MEMBERS, NOT IsEmpty(Time.CurrentMember))

  34. Last Non Empty Month • We need last element from the set: • Tail( <set>, n ) – returns last n elements of the set • Tail( Filter(...), 1 ) – returns the (1) last element from the set • Now we need to extract the member from the set: • .Item(n) function – returns nth element of the set as a member (zero based) • Solution: • Tail( Filter(...), 1 ).Item(0)

  35. Side note • The answer again: • Tail(Filter(time.year.Members, NOT IsEmpty(time.currentmember)),1).Item(0) • But, this can still give you irrelevant data if you budget into the future • A better (but not perfect) solution is to check the [Category].[Actual] values: • tail(filter(time.year.members, not isempty([category].[actual])),1).item(0)

  36. Dynamic Default Member Part II

  37. Default Member: Lessons • MDX can be used to create dynamic default members • IsEmpty() returns true if the evaluated cell has no data • Tail() returns a subset of elements from the end of a set • Use .Item() to “extract” member(s) from a set • Once again, build complex expressions incrementally

  38. Agenda • What is MDX? • Moving Average • Filters • Setting the default member • Referencing Member Properties

  39. Member Properties • Contain additional information about members • Customers: Gender, Marital Status, Address, E-mail, Education, Credit Card type • Stores: Store Size, Manager, Type • Some properties might make sense as virtual dimensions (Gender), others do not (Address)

  40. How to query a member property • <<member>>.Properties(“propertyname”) function • Always returns a string Customer.CurrentMember.Properties(“Gender”)

  41. Using Member Properties to set Custom Dimension Security • Business problem: Only allow access to Stores that have an area < 21,000 sqft • You have account managers who are individually responsible for either small, medium, or large store accounts.

  42. Solution plan • Get all the stores • [Store Name].MEMBERS • For each store, get its sqft • Store.CurrentMember.Properties(“SQFT”) • Use VBA function Val() to convert string to number • Val(Store.CurrentMember.Properties(“SQFT”)) • Filter out these stores where sqft is less than 21,000

  43. Solution Filter( [Store].[Store Name].MEMBERS, Val(Store.CurrentMember.Properties("Store SQFT")) < 21000)

  44. Member Property-based Security

  45. Member Properties: Lessons • Use .Properties to create expressions that query on a given member property. • Use member properties as a Filter() criteria • Use of external VBAfunctions in MDX is seamless • MDX can be used to restrict cube access in very imaginative ways

  46. Summary • MDX is used everywhere • Calculated members • Default member selection • Custom Security • More... • MDX constructs quickly become complex • But, it’s not that hard • Construct expressions incrementally

  47. More Information • Books • Step by Step Analysis Services • Jacobsen • Microsoft OLAP Solutions • Thomsen, Spofford, Chase • MDX Solutions • Spofford • Online • Public Newsgroup • Microsoft.public.sqlserver.olap • MSN Community • http://groups.msn.com/MicrosoftOLAPServicesUsersCommunity/_homepage.msnw?pgmarket=en-us • Microsoft.com • http://microsoft.com/sql/evaluation/bi/default.asp

  48. Don’t forget to complete the on-line Session Feedback form on the Attendee Web sitehttps://web.mseventseurope.com/teched/

More Related