Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Manivannan.kariamal
Starting Member
9 Posts |
 Posted - 2013-01-02 : 13:02:50
|
| Hi,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?Thanks in advance for your responses. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-03 : 01:28:40
|
| why are products maintained as columns? this is not scalable as addition of each new product will reauire adding new columns.Why not make table likeProduct SalesAmt ...ie store products as rows in which you can add as much product as you can by just adding another rowin that case you'll just have a single relationship between fact and dim table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Manivannan.kariamal
Starting Member
9 Posts |
Posted - 2013-01-03 : 05:00:38
|
| Thanks for your reply,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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-03 : 06:21:32
|
| then. you've to map each of those columns to DimProduct in cube which makes product kind of role playing dimension------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
How to create Measure to Sum multiple Columns