MS SQL Server OLAP - Multiple dimensions from single dimension table

Author

Multiple dimensions from single dimension table

GrahamS

2005-11-29, 9:24 am

Hi,

Cross posted on Datawarehouse as well - sorry didn't know which was
relevant....

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

Jéjé

2005-11-29, 9:24 am

with AS2000 you have to create 2 dimensions (copy/paste the first one) and
use views has the source
to create a view in the database like:
create view Dim_CollectionPeriod

as
Select * from Periods

the "CollectionPeriod" dimension will use this view while the first period
dimension use the the orginal table.

now go in your cube, add the 2 dimensions, link the original periods table
to the billing column, connect the view Dim_CollectionPeriod
to the
collection column of your fact table.

does this helps you?

"GrahamS" <GrahamS@discussions.microsoft.com> wrote in message
news:1AFACE76-488D-4945-ACAE- 946FAB293155@microso
ft.com...
> Hi,
>
> Cross posted on Datawarehouse as well - sorry didn't know which was
> relevant....
>
> 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, 11:23 am

JeJe,

Thanks for the quick response ;-).

Yes this did help. I have done it this way now and its working.

I was hoping that there would be a better way maybe using virtuatl
dimensions - so as to reduce the processing load by adding even more
dimensions :-O.

I need to use the same scheme for other dimensions which have a similar
problem and have created views for these as well now :-O.

Thanks again

regards

Graham

"JÃ©jÃ©" wrote:

> with AS2000 you have to create 2 dimensions (copy/paste the first one) and
> use views has the source
> to create a view in the database like:
> create view Dim_CollectionPeriod

> as
> Select * from Periods
>
> the "CollectionPeriod" dimension will use this view while the first period
> dimension use the the orginal table.
>
> now go in your cube, add the 2 dimensions, link the original periods table
> to the billing column, connect the view Dim_CollectionPeriod
to the
> collection column of your fact table.
>
> does this helps you?
>
> "GrahamS" <GrahamS@discussions.microsoft.com> wrote in message
> news:1AFACE76-488D-4945-ACAE- 946FAB293155@microso
ft.com...
>
>
>

Sponsored Links