hi all,
The source of my collection cube has a structure like this,
document_no date amt t ype
101 2005-1-1 20000.00 1
202 2005-1-2 1000.00 2

There are 5 types. RCT, JV, CR, DR, Claim. In any case, each row can be one
of these 5 types.
In my collection cube these 5 are measures. (RCT, JV, CR, DR, Claim)
I need these 5 as columns in my olap table. Each row will have one of the
values > 0 and all other as 0.
Now I have created a view using Union and where condition .

*******************
Create View V_Collection
As
select col_inv_id, col_dat_id,
'RCT' = col_amt, 'JV' = 0 ,'CRE' = 0, 'DEBT' = 0, 'Claim' = 0 from
TF_collection
where Col_typ = 1
union
select col_inv_id, col_dat_id,
'RCT' = 0, 'JV' = col_amt ,'CRE' = 0, 'DEBT' = 0, 'Claim' = 0 from
TF_collection
where Col_typ = 2
..... cont.
***********

Is thsi the right way ? or should I keep my table structure to give me same
effect as the view?

thanks and regards,
kshitij.

There might be a design question about whether your fact should have one
measure or 5, but that is not what you are asking... you could create a view
like this,,, it might perform better than your union...
create view dbo.v_collection as

select col_inv_id, col_dat_id,
case type When 1 Then col_amt Else 0 as 'RCT',
case type When 2 Then col_amt Else 0 as 'JV' ,
case type When 3 Then col_amt Else 0 as 'CRE' ,
case type When 4 Then col_amt Else 0 as 'DEBT' ,
case type When 5 Then col_amt Else 0 as 'Claim'
from TF_collection


Hope this helps
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)

I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"k_s" <ks (AT) discussions (DOT) microsoft.com> wrote

Thanks for the reply Wayne,
Your query is not working, giving error "Incorrect syntax near the keyword
'as'.

based on these measures. If u have ne suggestions,plz give me.

Thanks,


"Wayne Snyder" wrote:

about error Incorrect syntax near the keyword 'as'. need END to Case
example:
case type When 1
Then col_amt
Else 0
End
as 'RCT'

good luck
"k_s" <ks (AT) discussions (DOT) microsoft.com> escribió en el mensaje
news:67F70FF7-57DB-4A9E-BB9C-29128F765B8D (AT) microsoft (DOT) com...