Time dimensions on multiple date columns

[robbika]

If it helps in the answer I am using MONDRIAN as my OLAP to mysql interface, although this problem may just be OLAP.

I have a fact table that has multiple date columns.
I can make a time dimension but it has to be joined to a particular date column. But I want to produce output such that there is a navigable time dimension and there are multiple measures on more than one date column.

The only way I can see to do this is to take all the date columns and put them into another table that has a single date column and a key back to the fact table.

Or is there another way?

 

[johnherman]

The way you mentioned is the best way, and gives the added benefit of being able to define each date with more than one value. For instance, the calendar date of October 1, 2006 can also be the beginning of a fiscal year. Or, if you have a business based heavily on quarters, you can designate each day as belonging to a certain week of the month, quarter, and year. (There are 13 weeks in a quarter but only 4 in a month).

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright