I have a fact table which has 10 product columns (prod1,prod1sale,prod2,prod2sale...prod10,prod10sale). distinct set of products appear in these columns. so I have created one dimension called DimProduct and referred this to all 10 columns of the fact table.
Now, in the cube, I should have only one Dimension for product and when it is browsed, it should show the sum of sales in 10 columns (prod1sale+prod2sale .. +prod10sale) for that product. is it possible to do this?
actually my base table is having 10 products in the columns. no new products will be added. so I have derived fact table with 10 product column as it is. apart from products column there are many other columns which are also included in the fact table. there are many dimensions linked to those columns so I couldnt convert the columns into rows and link it to single dimension.