I have 3 Fact Tables and 5 Dim table and in all the tables i have date column and i have a date table. do i needto join the date table to all the fact and dim tables by creating alias table for each of them or just connecting to fact table will enough in creating a universe.
bobtom (BOB member since 2008-01-17)
It depends on what roles the dates play, are those semantically different dates such as (billing date, delivery date, due date, etc.)? Then you will have to alias your date table accordingly.
If not, then you are dealing with a conformed date dimenison, and you can join the fact tables to the very same date dimension table.
Andreas 
(BOB member since 2002-06-20)
And if the date table is conformed dimension and it will be joined to every fact table then you very likely need to use contexts - one context for each fact table.
Marek Chladny 
(BOB member since 2003-11-27)
I’m currently in the position of trying to join dates in my tables to my calendar table to get fiscal calendar information.
If you trunc(date) in the join, does that have any effect on the indexes?
Eileen King 
(BOB member since 2002-07-10)
Yes. You’ll need to use function based indexes.
Or get them to give me a date key on the tables instead of the full Oracle date with timestamp!
Eileen King 
(BOB member since 2002-07-10)
I was just going on the basis that you couldn’t change the tables as you mentioned earlier this week. Datekey, unless it’s already there as an integer column, will still involve functions, again negating indexes. 
I can’t make changes now. However, when it runs like…I will be suggesting changes then!
Just about every measure needs to be created with time frames. The dates are going to be a very big part of this universe. Of course, the Fiscal Year begins March 1 just to make things really interesting!
Eileen King (BOB member since 2002-07-10)
Are all tables connected by integer joins?
If they’re on date then you’re going to struggle.
We’ve just seen that happen here - I’ve had an integer datekey added and queries are flying back in seconds rather than minutes now.
At this point they are joined on full dates. I’m truncating the dates in the joins where necessary. I’ve given them the information on functional indexes and that is the plan for now.
I will look forward to the different refresh times now, with the functional indexes, and hopefully, with the date keys in the future!
Eileen King (BOB member since 2002-07-10)