Create virtual dimension based on column value

Create virtual dimension based on column value

Post by New2Ola » Wed, 15 May 2002 03:24:03



I am using Analysis Services and currently have a client
dimension. The table used to build this dimension has a
flag that identifies priority clients (prty_clnt_fg). I
have exposed this column by adding a member property and
would now like to build a virtual dimension that is based
on the value of this member property. i.e where
prty_clnt_fg = 1. Initially I merely created a view of the
base table and created a new dimension based on the view
which I thought would work. But when building the cube it
used this dimension in the join which resulted in
retrieving only the subset of clients (priority_clients).
Is there a best practise to follow for doing this as I
come across this problem often.
 
 
 

Create virtual dimension based on column value

Post by [MS] Hai-Wei » Wed, 15 May 2002 10:56:15


Hi ,

Based on my understanding, what you need is a virtual dimension with two
members: Is_Priority_client and Not_Priority_client. In the dimension
table, the prty_clnt_fg has two possible values: 1 and 0. Am I correct?

If it's true, you could:

1. Create a view on the dimension table, using "case when ... then...
else..." to convert the column prty_clnt_fg from 1/0 to a character column
with the values Is_Priority_client\Not_Priority_client.
2. Create the client dimension on the view, adding a member property based
on the new prty_clnt_fg column.
3. Create the virtual dimension based on the member property.

This posting is provided 'AS IS' with no warranties, and confers no rights.

Regards,
Haiwei Xu

 
 
 

Create virtual dimension based on column value

Post by New2Ola » Thu, 16 May 2002 02:45:58


Thanks for your help. That worked fine.
Quote:>-----Original Message-----
>Hi ,

>Based on my understanding, what you need is a virtual
dimension with two
>members: Is_Priority_client and Not_Priority_client. In
the dimension
>table, the prty_clnt_fg has two possible values: 1 and 0.
Am I correct?

>If it's true, you could:

>1. Create a view on the dimension table, using "case
when ... then...
>else..." to convert the column prty_clnt_fg from 1/0 to a
character column
>with the values Is_Priority_client\Not_Priority_client.
>2. Create the client dimension on the view, adding a

member property based

- Show quoted text -

Quote:>on the new prty_clnt_fg column.
>3. Create the virtual dimension based on the member
property.

>This posting is provided 'AS IS' with no warranties, and
confers no rights.

>Regards,
>Haiwei Xu

>.