Dynamic dimensions

What is the best way to handle dimensions that change depending on user parameters? For example, lets say you want "Months Since Inception" to be a dimension, because you want to bucket by it (i.e. 0 to 50 Months, 51 to 100 Months, etc.) but this is calculated by a date parameter given when the user queries the database. For example, the user plugs in a date, say June 2008. Then it uses this date to determine the months between the Inception Date and the date parameter, and that becomes the bucket.

Update the fact row. I don't know anyway around this. It might not be too bad. I currently have an accumulating snapshot with ~200K rows per partition on Oracle that performs pretty well.

After giving this some thought, I came to the conclusion that, although it would be nice to pick any date in history for the "as of" date, it wouldn't be practical. First of all, a 10 million row fact table would grow to over a billion rows if you duplicated it for each day of the year for the past 10 years. Keep in mind that in my example I wasn't tracking history per se, like some fact tables do, but rather I am trying to get at an "as of" date for the report. If the user picks June 2008, it would display fact records up to June 2008. If they picked July 2008, it would show the same fact records, but including the month of July. If they picked December 22, 1999, it would show all facts up to Dec 22, 1999, and so on. This would require duplicating the 10 million rows for each day because, and this is the crux of the problem, the Months Since dimension would be different depending on which date they picked. And as far as I know, dimensions keys don't magically change depending on some filtering criteria

I think my options are to define a set of "as of" periods that users typically use, such as "As Of Today", "As Of Last Month", "As Of Last Year", and then only duplicate the facts for those periods, so 10 million would increase to 40 million (not really, since the further back in time you go, some fact rows will drop off).

The other option is to create a table with only the As of Date key/Months Since Inception key/TransactionID, and that table can be very large, depending on the number of As Of Dates you want to track. But then you can join this table to other fact tables by TransactionID using a view to get all the data you need, while saving storage space.

Am I missing something? If the requirement is to get a 'balance' as at a specific point in time, why wouldn't you just use your date prompt in your reporting tool as a cut-off, and summarize all activity up to that date. Granted you've got quite a few records in the table, but nothing that couldn't be managed by your DB engine. Plus depending on your toolset, the result could then be used for a drilldown analysis of activity generating the balance. (I'm thinking 'Bank Account' here, but correct me if I'm wrong).

You could use date functions to calculate the other date in the selection range, or you could have a month count in your date dimension table. Month count would be calculated by assigning a sequential count chronologically in the dimension table. Given any date you can calculate the month count range you need to select.