Multiple Columns of Fact pointing to same dimension

Hi All,
Here is my scenario.
My fact table has the following
TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId, Amount
The dimension table has
FiscalYear,FiscalQuarter,FiscalMonth,Fis
calWeek,FiscalWeekId(lowest grain)
Now i need to have dimension built individually on each of the fact table co
lumns (TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId). Th
e three id(s) will have different values for the same row.
To implement this i need to build three dimensions on the same relational ta
ble. Basically In dimension build editor pick up the same table thrice and b
uild three individual dimensions. The editor will now have one fact table, a
nd the dimension table in t
hree different aliases.
Join as follows
Fact.TransactionFiscalWeekId = dimAlias1.FiscalWeekId
Fact.BilledFiscalWeekId= dimAlias2.BilledFiscalWeekId
Fact.PaymentFiscalWeekId= dimAlias3.PaymentFiscalWeekId
Questions.
1. Is there any way for me to use a single shared dimension FiscalPeriod and
achieve the same functionality?
2. Any better alternative design for the above functionality?
Thanks a lot!!
LalithaYou can use views to reference the same relational table more than once
Ray Higdon MCSE, MCDBA, CCNA
--
"Lalitha" <lalithas512@.hotmail.com> wrote in message
news:08F4F84C-CD32-42FF-9F22-B9A5DB42F2C5@.microsoft.com...
> Hi All,
> Here is my scenario.
> My fact table has the following
> TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId, Amount
> The dimension table has
> FiscalYear,FiscalQuarter,FiscalMonth,Fis
calWeek,FiscalWeekId(lowest grain)
> Now i need to have dimension built individually on each of the fact table
columns (TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId).
The three id(s) will have different values for the same row.
> To implement this i need to build three dimensions on the same relational
table. Basically In dimension build editor pick up the same table thrice and
build three individual dimensions. The editor will now have one fact table,
and the dimension table in three different aliases.
> Join as follows
> Fact.TransactionFiscalWeekId = dimAlias1.FiscalWeekId
> Fact.BilledFiscalWeekId= dimAlias2.BilledFiscalWeekId
> Fact.PaymentFiscalWeekId= dimAlias3.PaymentFiscalWeekId
> Questions.
> 1. Is there any way for me to use a single shared dimension FiscalPeriod
and achieve the same functionality?
> 2. Any better alternative design for the above functionality?
> Thanks a lot!!
> Lalitha|||Thanks for the response.
In relational tables part, i am clear. we can just form three different dime
nsions based on same underlying relational table.
But My idea is to use a single shared dimension.
The reason being a change in Fiscal hierarchy will involve change in one pla
ce alone then.
If i have three separate dimensions, then all three has to be changed.
Can i do something like three different private virtual dimensions
using a single shared dimension Fiscal Period?
Thanks
Lalitha