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:

Dim myserver
Dim db
Dim vcube
Dim fromdim
Set myserver=createobject("dso.server")
myserver.connect("MyServer")
Set db = myserver.mdstores("MyDatabase")
Set vcube = db.mdstores("MyVirtualCube")
Set fromdim = vcube.dimensions.addnew("End Date")
Vcube.update

• 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:

SUM({
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… 

I was thinking about the similarities between smart tags and actions the other day, and had the following idea – wouldn’t it be cool if there was a smart tag which made all your cube, dimension, level and member-based actions available outside the OLAP environment? If you’ve defined an action on your cube which allows you to do something like click on a customer name and then see that customer’s details in your CRM system, it seems a shame that you can only do this in an AS client that supports actions. You’d want to be able to do this whenever you typed that customer’s name in a Office document wouldn’t you? And if you had an action which returned a resultset you could get the smart tag to embed that directly in the document, say as a table in Word. The possibilities are endless!

I guess what you’d have to do is write a smart tag dll… maybe something to try when I’m less busy…