I"m attempting to design a cube which would handle a Measure dataset which has multiple time series data associated with it. This is in the context of financial contracts. The first time series is TradeDate, which is the date a product was financially settled. The other time series is ContractDate, or the date for which the contract is being traded in the future. In example...
TradeDate ContractDate Price
1/1/2005 11/1/2005 $5.00
1/2/2005 12/1/2005 $5.25
1/3/2005 12/1/2005 $5.50
Both the TradeDate & ContractDate time dimensions are recorded on the daily interval. I would like to set up my cube so that i can create aggregations across both time dimensions. In example (using data from above), i would like to create an average value which is defined by both the TradeDate & ContractDate...
TradeDate ContractDate Avg_Price
January 2005 November 2005 $5.00
January 2005 December 2005 $5.37
Any help would be greatly appreciated.
SSAS supports role playing dimensions. This means that one dimension can be added multiple times to a cube, each time for a different purpose.
To make use of role playing dimensions, create your date dimension once, but add it twice in the dimension usage tab of your cube. Give the cube dimensions appropriate names (trade date and contract date) and edit the relationship between the fact and dimension table (the ellipsis button) to the correct fields of your schema.