Multiple date dimensions

[renu123]

I have four different date attributes which describe an employee's certificate/learning activity.I have created a fact table Learning_Fact with associated dimensions.
As per data warehousing design principles, time wil be a dimension.In this particular case we have four such dimensions with identical structure.
I cannot join these four foreign keys (in Learning_Fact) to the same 'Time' table as SQL would interpret this 4 way simultaneous join as requiring that all of the dates be same(which is not the case).
When implemented, there will be one underlying 'time' table containing dates information. 4 virtual copies of this table will be created with the SQL SYNONYM command.
A view will have to be created on each copy in order to make the field names uniquely different.

With this scenario, I have two questions-
(i) Is this doable in Oracle.
(ii) How to represent this in ERWIN physical model? In ERWIN,when I create a synonym of a table, the synonym is not pictorially displayed.(though it can be seen in the model explorer) Also, when a view is created, it is represented as a dotted lined box in the diagram. I am not allowed to link the view to the table 'Learning_Fact' as only view relationships can be made with view entity(i.e. only the relationships which show the view is created from which tables).

Can anyone please respond asap.

Regards

 

[a199664]

I am a little puzzled by your architecture. Can you email me a simple diagram outlining this portion of your model.

A199664@hotmail.com

 

[jnicho02]

The four differnt fields do all link to the same Time table, so that's how they would be shown in ErWin. However, as you say, when you do a SQL join you must use aliases of the Time table for each join. This is normally something for the programmer to work out an isn't expressed on an entity relationship diagram. It might be possible to show the alias joins in ErWin somehow to be explicit.

Aliases are fairly standard, so I would say that Oracle can do this.



Jeremy Nicholson, Director of

http://www.firstlightassociates.co.uk

a UK-based Java and Data Warehousing consultancy

 

[renu123]

Thanks for your reply.
My whole problem is about representing this in ERWin.
As per your suggestion, I should create one entity and do the 4 way join with this entity.
Is that correct?

Regards

 

[Subhashab]

I would suggest creating view in ERWIN of the timde dimension and joining them to the four different fields