Multiple dimensions from single dimension table

Hi,
SQL 2000 AS.
I have a dimension table 'Periods' example :
1 = Jan 2005
2 = Feb 2005
3 = Mar 2005
etc. NB This is not a standard time dimension.
The table has a key on 'PeiodID' = 1, 2, 3 etc.
My Fact table has say 2 period entries per row (say Billing, Collection) -
these can be different per row - eg Billing = 2, Collection = 4 etc.).
How do I add my Period dimension twice but linked to different columns, so
that each one shows up with a different name (say BillingPeriod,
CollectionPeriod).
I tried to create separate dimensions 'BillingPeriod' and 'CollectionPeriod'
but these both display on the table view as 'Period'. I CAN link both key
fields to Period but then can't see how each one is differentiated '.
Many Thanks
Regards
GrahamCreate and use a View for one of the dimensions.
HTH,
Mike
"GrahamS" wrote:

> Hi,
> SQL 2000 AS.
> I have a dimension table 'Periods' example :
> 1 = Jan 2005
> 2 = Feb 2005
> 3 = Mar 2005
> etc. NB This is not a standard time dimension.
> The table has a key on 'PeiodID' = 1, 2, 3 etc.
> My Fact table has say 2 period entries per row (say Billing, Collection) -
> these can be different per row - eg Billing = 2, Collection = 4 etc.).
> How do I add my Period dimension twice but linked to different columns, so
> that each one shows up with a different name (say BillingPeriod,
> CollectionPeriod).
> I tried to create separate dimensions 'BillingPeriod' and 'CollectionPerio
d'
> but these both display on the table view as 'Period'. I CAN link both key
> fields to Period but then can't see how each one is differentiated '.
> Many Thanks
> Regards
> Graham
>|||Mike,
Yup - thanks for the reply - I tried this today and it works pretty well.
I was hoping for something maybe not so 'dimension intensive', as I actually
have a number of similar dimensions to 'double up on'.
Thanks again.
regards
Graham
"Mike Austin" wrote:
[vbcol=seagreen]
> Create and use a View for one of the dimensions.
> HTH,
> Mike
> "GrahamS" wrote:
>|||May be you could use the table alias.
for eg. select a.factID billingperiond.periodID,
collectionperiod.PeriodIDfrom Billingfact a, periods as
billingperiond,periods as collectionperiod where
a.billingperiodID=billingperiod.PeriodID and
a.collectionperiodID=collectionperiod.periodID
Thx
Harsh
"GrahamS" wrote:

> Hi,
> SQL 2000 AS.
> I have a dimension table 'Periods' example :
> 1 = Jan 2005
> 2 = Feb 2005
> 3 = Mar 2005
> etc. NB This is not a standard time dimension.
> The table has a key on 'PeiodID' = 1, 2, 3 etc.
> My Fact table has say 2 period entries per row (say Billing, Collection) -
> these can be different per row - eg Billing = 2, Collection = 4 etc.).
> How do I add my Period dimension twice but linked to different columns, so
> that each one shows up with a different name (say BillingPeriod,
> CollectionPeriod).
> I tried to create separate dimensions 'BillingPeriod' and 'CollectionPerio
d'
> but these both display on the table view as 'Period'. I CAN link both key
> fields to Period but then can't see how each one is differentiated '.
> Many Thanks
> Regards
> Graham
>|||You will need to create a view over the dimension table and use that as
the source for one of your dimensions otherwise when the cube gets
populated you will only get facts where the 2 dates are equal.
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <B42197EA-E216-4310-A284-4416BB23A034@.microsoft.com>,
Harsh@.discussions.microsoft.com says...
> May be you could use the table alias.
> for eg. select a.factID billingperiond.periodID,
> collectionperiod.PeriodIDfrom Billingfact a, periods as
> billingperiond,periods as collectionperiod where
> a.billingperiodID=billingperiod.PeriodID and
> a.collectionperiodID=collectionperiod.periodID
> Thx
> Harsh
> "GrahamS" wrote:
>
>