Multiple dimensions from single dimension table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GrahamS - 29 Nov 2005 14:34 GMT

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

Graham

Reply to this Message

Mike Austin - 29 Nov 2005 20:27 GMT

Create and use a View for one of the dimensions.

HTH,

Mike

> Hi,
>
[quoted text clipped - 25 lines]
>
> Graham

Reply to this Message

GrahamS - 29 Nov 2005 22:27 GMT

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

> Create and use a View for one of the dimensions.
>
[quoted text clipped - 31 lines]
> >
> > Graham

Reply to this Message

Harsh - 06 Dec 2005 21:21 GMT

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

> Hi,
>
[quoted text clipped - 25 lines]
>
> Graham

Reply to this Message

Darren Gosbell - 13 Dec 2005 10:42 GMT

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.

Signature

Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

> May be you could use the table alias.
>
[quoted text clipped - 36 lines]
> >
> > Graham

Reply to this Message