|
Home > Archive > MS SQL Data Warehousing > December 2005 > Multiple dimensions from single dimension table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Multiple dimensions from single dimension table
|
|
| GrahamS 2005-11-29, 9:23 am |
| 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
| |
| Mike Austin 2005-11-29, 8:23 pm |
| Create 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 '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
>
| |
| GrahamS 2005-11-29, 8:23 pm |
| 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:
[color=darkred]
> 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,perio
ds as collectionperiod where
a. billingperiodID=bill
ingperiod.PeriodID and
a. collectionperiodID=c
ollectionperiod.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 '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
>
| |
| Darren Gosbell 2005-12-13, 7:23 am |
| 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@microso
ft.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,perio
ds as collectionperiod where
> a. billingperiodID=bill
ingperiod.PeriodID and
> a. collectionperiodID=c
ollectionperiod.periodID
>
> Thx
> Harsh
>
> "GrahamS" wrote:
>
>
|
|
|
|
|