Dimension with multiple FK in fact table when extracting data from star schema

[#1]

Hi,

What's the best practice when extracting data from a star schema and the fact table contain many fk from the same Dimension : Ex

Time dimension with multiple use in a fact table

to avoid lookups i extract the same dim with different name . is it a good idea ? should i change my star model and add a Type Dimension

(ex : Type date )?

Thank you

[johnw]

If I understand the question, the two things you mention are probably the most common solutions, and which is better is specific to the situation, or both may be equally good.

Multiple date fields:
- can easily select combinations like "ordered on August 1, 2009, and shipped on August 15, 2009"
- cannot easily select activities for a date, like "all orders and shipments on August 15, 2009"
- have to load multiple calendars
- takes a huge amount of space on screen if you want to be able to select every field from every calendar

One date field, distinguished by type:
- cannot easily select combinations like the above
- can easily select activities for a date by not specifying the type
- only need to load one calendar
- takes much less space on screen to be able to select every calendar field

I've used both approaches.

[#3]

Thank for your reply your arguments are clear and accurate