SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 How to create Measure to Sum multiple Columns		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Manivannan.kariamal
Starting Member

India
9 Posts
Posted - 01/02/2013 :  13:02:50  Show Profile  Reply with Quote
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

India
48114 Posts
Posted - 01/03/2013 :  01:28:40  Show Profile  Reply with Quote
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 like

Product SalesAmt ...

ie store products as rows in which you can add as much product as you can by just adding another row

in that case you'll just have a single relationship between fact and dim table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Manivannan.kariamal
Starting Member

India
9 Posts
Posted - 01/03/2013 :  05:00:38  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48114 Posts
Posted - 01/03/2013 :  06:21:32  Show Profile  Reply with Quote
then. you've to map each of those columns to DimProduct in cube which makes product kind of role playing dimension

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000