December 29, 2011 at 7:56 am
My client wants either a measure or a calculated dimension to handle the following in a sales cube:
a customer buys a certain product on a specific day and will not purchase it again for a certain count of days, or may not purchase it again at all. Not purchasing it again is seen as a lost sale, if it is longer than say 90 days since the last purchase.
How can I
1. calculate the days between events?
2. days from the last event to now?
3. work this into a meaningful dimension or measure? Maybe the same principle as the ageing of customer or vendor outstanding amounts work?
Any help appreciated.
January 3, 2012 at 8:02 am
This is difficult to answer because more information would be useful. As a general rule, however, in an OLAP environment you want as much information pre-calculated as possible. For example, I have a field to tell me how many days an order has been open, which is updated nightly. This makes it easy to do average time open and so one. This is something I wouldn't do in an OLTP environment.
As it relates to your question, is it possible to simply record that information in a field? As far as the time from now, is it few enough records that you can update it nightly?
January 5, 2012 at 3:37 pm
battery_acid_h (12/29/2011)
My client wants either a measure or a calculated dimension to handle the following in a sales cube:a customer buys a certain product on a specific day and will not purchase it again for a certain count of days, or may not purchase it again at all. Not purchasing it again is seen as a lost sale, if it is longer than say 90 days since the last purchase.
How can I
1. calculate the days between events?
2. days from the last event to now?
3. work this into a meaningful dimension or measure? Maybe the same principle as the ageing of customer or vendor outstanding amounts work?
Any help appreciated.
Use date diff to calculate days, built in function. GetDate() is the standard function for "Today"
I have "lag" fields in my fact tables - number of days 'lag' from date x to date y. These are created in SSIS towards the end of the process (have to have both dates) using derived column to create the field and use DateDiff in an expression to populate it.
We also implement aging using a DimDay dimension table that covers every date of use to our business (today and backwards 5 years).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply