We have a series of calculated members that we refer to as "Velocity", such as "Velocity Sales". The basic definition for these velocity measures is to get the total from the previous 63 business days and then annualize that total (i.e. multiply by 4), with the 63rd business day belonging to the previous month.
For example, Velocity Sales for any day in September 2006 would be totaling the sales $ for the business days in the range of 06/05/2006 through 08/31/2006, and then multiplying it by 4. Perhaps not surprisingly, these measures result in extremely poor performance.
Since I'm confused on the non-empty behavior, this property has not been set on any of the velocity measures.
Therefore, can I set the non-empty behavior for my velocity measures without risk of erroneous data? Based on extremely limited testing, enabling this property doesn't seem to cause any erroneous data, but I want to be confident before deploying it. Of course, enabling it results in a huge performance gain.
Below is the the definition for the [Velocity Sales] calculated member, just in case it can be of use:
SUM(Filter(Head(Tail(Filter({[Date].[Date].[Date].Members.Item(0).Item(0):Tail(Exists([Date].[Date].[Date].Members,
Tail(Existing [Date].[Month].[Month].Members,1).Item(0).Item(0).Lag(1)),1).Item(0).Item(0)},
[Date].[CP Business Day Indicator].MemberValue = 1),63),1).Item(0).Item(0)
:
Tail(Filter({[Date].[Date].[Date].Members.Item(0).Item(0):Tail(Exists([Date].[Date].[Date].Members,
Tail(Existing [Date].[Month].[Month].Members,1).Item(0).Item(0).Lag(1)),1).Item(0).Item(0)},
[Date].[CP Business Day Indicator].MemberValue = 1),1).Item(0).Item(0),[Date].[CP Business Day Indicator].MemberValue = 1),[Measures].[Sales USD])*4
I would really appreciate some insight!
Setting the Non Empty Behavior of the calculated measure above to, say, [Measures].[Sales USD] certainly wouldn't make sense, because there might be no sales on a given day (like a Sunday), yet the 63 prior business days considered for that day's velocity might well have sales.
If sparse cube data is a performance issue, you could apply NonEmpty() to the set of 63 days above, to filter out days with no data. Another obvious improvement would be to directly specify a CP Business Day Indicator attribute member, rather than applying Filter() with the "[Date].[CP Business Day Indicator].MemberValue = 1" condition - as also discussed in this paper:
http://www.sqlserveranalysisservices.com/OLAPPapers/MDX%20Performance%20Hintsv1.htm
>>
Use your attributes!
In SQL Server Analysis Services 2000, the filter function was a common means of selecting members from a set whose member property satisfied some condition; for example male customers could be expressed as :
Filter(Customer.name.Name.members, Customer.name.currentmember.properties(“Gender”) = “Male”)
Don’t do this in SQL Server Analysis Services 2005. Instead, create an attribute hierarchy Customers.Gender.Male and do this:
(Customers.Gender.Male, Measures.Sales)
Use NonEmpty Function
The NonEmpty function (new in SQL Server Analysis Services 2005) is optimized for removing empty tuples. So instead of doing this to get the customers who bought an Xbox,
Filter(Customer.Name.members, not IsEmpty( ([Measures].[Unit Sales], [Product].[Name].[Xbox])
Do this:
NonEmpty (Customer.Name.members, ([Measures].[Unit Sales], [Product].[Name].[Xbox]))
>>
|||Thanks for the suggestions. However, I still have a few questions.I implemented the following hierarchy called BusinessDays in my Date dimension that consists of:
[CP Business Day Indicator]
Month
Date (dimension key)
As a result, [Velocity Sales $] has now been defined as:
SUM(LASTPERIODS(63,TAIL(EXISTING BusinessDays.Month.MEMBERS).ITEM(0).ITEM(0).LAG(1).LASTCHILD),[Measures].[Sales USD])*4
Unfortunately, I don't see any dramatic performance improvemenst with this approach. Of course, setting the Non-empty behavior property to [Measures].[Sales USD] makes a world of a difference.I see what you're saying as to why setting the Non-empty behavior property doesn't seem to make sense, but based on further testing scenarios, I have not seen any erroneous data yet -- I have 2 measures that are defined the same, with one having the Non-empty behavior property set while the other one doesn't.
|||
Based on my understanding of Non Empty Behavior, I can't suggest using it here. For example, using Adventure Works:
>>
With
Member [Measures].[PrevMonthSales1] as
([Measures].[Sales Amount],
(Existing [Date].[Calendar].[Month]).item(0).item(0).Lag(1))
Member [Measures].[PrevMonthSales2] as
([Measures].[Sales Amount],
(Existing [Date].[Calendar].[Month]).item(0).item(0).Lag(1)),
NON_EMPTY_BEHAVIOR = '[Measures].[Sales Amount]'
select {[Measures].[Sales Amount],
[Measures].[PrevMonthSales1],
[Measures].[PrevMonthSales2]} on 0,
{[Date].[Calendar].[Month].&[2004]&
[Date].[Calendar].[Date].&[1106]} on 1
from [Adventure Works]
where [Product].[Product Categories].[Subcategory].&[19]
-
Sales Amount PrevMonthSales1 PrevMonthSales2
June 2004 $3,276.23 3157.57929999999 3157.57929999999
July 10, 2004 (null) 3276.22569999999 (null)
>>
Did you try to see if NonEmpty() can help performance - like:
SUM(NonEmpty(LASTPERIODS(63,
TAIL(EXISTING BusinessDays.Month.MEMBERS).ITEM(0).ITEM(0).LAG(1).LASTCHILD),
{[Measures].[Sales USD]}), [Measures].[Sales USD])*4
|||Deepak Puri wrote:
>>
Use your attributes!
In SQL Server Analysis Services 2000, the filter function was a common means of selecting members from a set whose member property satisfied some condition; for example male customers could be expressed as :
Filter(Customer.name.Name.members, Customer.name.currentmember.properties(“Gender”) = “Male”)
Don’t do this in SQL Server Analysis Services 2005. Instead, create an attribute hierarchy Customers.Gender.Male and do this:
(Customers.Gender.Male, Measures.Sales)
Using this method how could I combine multiple filters in the same set? For example I might want to include all males together with a hair colour brown. I would get dimenstionality errors if I set up different hierarchies for different attributes and then tried to combine them.
|||Using this method how could I combine multiple filters in the same set? For example I might want to include all males together with a hair colour brown. I would get dimenstionality errors if I set up different hierarchies for different attributes and then tried to combine them
(Customers.Gender.Male, Customers.HairColor.Brown) - this won't give you any dimensionality errors since Gender and HairColor are different attributes.
HTH,
Mosha (http://www.mosha.com/msolap)
|||I can get:
({[Product].[Product Hierarchy].[Product Group Level 1].&[Product Group] * [Product].[My Flag].[True])
to work but not:
({[Product].[Product Hierarchy].[Product Group Level 1].&[Product Group], [Product].[My Flag].[True])
Is this what you'd expect? Is it not possible to combine user defined hierarchies with attribute hierarchies?
Thanks.
|||Well - in both expressions you have extra { at the beginning - so none of them will pass syntax check, but other then that - looks like the first one will simply multiply the values, whereas the second one will construct the actual tuple. The second one should work - you can combine any hierarchies together in the tuple. Can you please give a complete example, preferably using Adventure Works for reproducability of your issue.
Thanks,
Mosha
|||This doesn't work for me. I get a dimensionality error. I'm sure I'm missing something obvious. If you could point me in the right direction I'd be grateful.
SELECT { [Date].[Calendar].DEFAULTMEMBER } ON COLUMNS ,
{[Product].[Style].[Unisex], [Product].[Product Categories].[Subcategory].[Mountain Bikes] } ON ROWS
FROM [Adventure Works]
WHERE ( [Measures].[Sales Amount])
|||To build a tuple you need to use ()'s not {}'s, i.e.
SELECT { [Date].[Calendar].DEFAULTMEMBER } ON COLUMNS ,
([Product].[Style].[Unisex], [Product].[Product Categories].[Subcategory].[Mountain Bikes] ) ON ROWS
FROM [Adventure Works]
WHERE ( [Measures].[Sales Amount])
HTH
Mosha (http://www.mosha.com/msolap)
|||That does the trick. Thanks v much.|||One more question. How can I reproduce this for sets? This code works. It doesn't work if I remove Filter from the beginning.
Filter([Product].[Product Hierarchy].[Product Grouping].[MyProduct].children, [Product].[Product Flag].[True])
|||Use the following:
Exists([Product].[Product Hierarchy].[Product Grouping].[MyProduct].children, [Product].[Product Flag].[True])
|||Doesn't return the same results as I get using Filter. I can stick with Filter.|||Of course it doesn't return same results as with Filter. Your Filter looks at cell values where Flag.True member evaluates cell value to Non-zero. I was under impression that you didn't want to look at cell values, but at dimension table and only return products which have Flag set to True.
HTH,
Mosha (http://www.mosha.com/msolap)
