How should one define a second date hierarchy in SSAS?

Question

I have a time table and a sales fact table with multiple dates (order received date, ship date).

I want to define a hierarchy on the order received date and another hierarchy on the ship date.

The cube editor in visual studio allows me to define multiple relationships on these table, but I don't see a way in the hierachy editor to define which relationship to use for the hierarchy.

Am I missing something, or am I supposed to insert the time table into the model twice, which single relationships on each?

Thanks, --sw

Meff Meff · Accepted Answer · 2013-02-22T14:26:25

Wouldn't this be where you use role-playing dimensions? So you have your date dimension, with all it's hierarchies, then you join that again to the fact table on the ship date, and now you have a "Date (Ship Date)" dimension with all the Date dimension hierarchies.

http://www.msbiguide.com/2012/05/role-playing-dimensions-in-ssas/

http://msdn.microsoft.com/en-us/library/ms174487(v=sql.90).aspx

http://kimballgroup.forumotion.net/t1165-role-playing-dimensions

You only create the Date dimension once by the way, it just gets a new alias in the cube for each time it role-plays.

How should one define a second date hierarchy in SSAS?

1 Answers