Aggregating values across a date range
Very often you’ll come across a requirement for users to be able to aggregate values across abitrary date ranges. For example, they might want to see the sum of Unit Sales between any two months, say Feburary and April 1997. While this is fairly easy to accomplish with a calculated member when you have control of the MDX, when your users want to use an off-the-shelf client it becomes more difficult – how do you allow people to pick a start date and an end date on your single time dimension?
The way I usually solve this is by creating two time dimensions, one for the start date and one for the end date. However if you hang both off the time key column in your fact table you’ll see that initially no data is returned unless you select the same members on both dimensions – a transaction can’t be in two months simultaneously. You can get round this in a calculated member and sum up the values between the two months, but its not very elegant. What you really want is for your start date dimension to act like a normal dimension and for the end date dimension to be present in the cube but have no effect on it, so then in your calculated member you can simply aggregate all the values between the currentmember on start date and the member on start date which equates to the currentmember on end date.
This is actually very easy to do in AS2005 as you can add a dimension to a measure group without it having any relationship to the fact table. In AS2K though you need to go through the following steps:
- Create your cube with one time dimension as usual, and call it Start Date
- Copy this dimension and paste it to the same database, and when asked to rename it call it End Date
- Create a virtual cube containing everything from your original cube.
- To add the End Date dimension to this virtual cube you can’t, unfortunately, use Analysis Manager, you have to use some DSO code. Here’s a bit of VBScript as an example:
Set db = myserver.mdstores("MyDatabase")
Set vcube = db.mdstores("MyVirtualCube")
Set fromdim = vcube.dimensions.addnew("End Date")
- Now you have a virtual cube with two time dimensions, but the End Date dimension does nothing. To automatically sum up the values of a measure between the selected start and end dates, you would create a calculated measure something like:
OPENINGPERIOD([START DATE].[LOWEST LEVEL], [START DATE].CURRENTMEMBER)
CLOSINGPERIOD([START DATE].[LOWEST LEVEL], LINKMEMBER([END DATE].CURRENTMEMBER, [START DATE]))
}, VALIDMEASURE(MEASURES.[MY MEASURE]))
One last thing to notice – since we’re now summing up a range of months rather than returning a single month, query performance might suffer. In which case it might be worth taking a look at my recent entries on tuning YTD-style queries…