490 likes | 606 Views
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
E N D
DAT312troduction 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 • 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
Knowing MDX After today’s talk Knowledge Required You are here Functionality
Agenda • What is MDX? • Moving Average • Filters • Setting the default member • Referencing Member Properties
Moving averages • Business problem: provide a trend line against which to measure current values
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
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])
Improve the calculation • Typical business scenario has different moving average periods for different levels • 30-day • 3-month • 2-quarter • 1-year
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
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])
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
Agenda • What is MDX? • Moving Average • Filters • Setting the default member • Referencing Member Properties
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
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
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])
… 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] ) )
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]))
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
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]))
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
Agenda • What is MDX? • Moving Average • Filters • Setting the default member • Referencing Member Properties
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!
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 ?
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
...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
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”
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
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))
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)
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)
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
Agenda • What is MDX? • Moving Average • Filters • Setting the default member • Referencing Member Properties
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)
How to query a member property • <<member>>.Properties(“propertyname”) function • Always returns a string Customer.CurrentMember.Properties(“Gender”)
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.
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
Solution Filter( [Store].[Store Name].MEMBERS, Val(Store.CurrentMember.Properties("Store SQFT")) < 21000)
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
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
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
Don’t forget to complete the on-line Session Feedback form on the Attendee Web sitehttps://web.mseventseurope.com/teched/