SSAS Custom additive behavior

dear experts, need scenario

i have fact table data follows.

product	productquantity		productcompnent	productcomponentprice
a1cycle	100		tyre	10
a1cycle	100		tube	5
a1cycle	100		seat	20
a1cycle	100		chain	30
a1cycle	100		handle	10
a1cycle	100		bell	50
a1cycle	100		break	10
b1cycle	200		tyre	10
b1cycle	200		tube	5
b1cycle	200		seat	20
b1cycle	200		chain	30
b1cycle	200		handle	10
b1cycle	200		bell	50
b1cycle	200		break	10

the fact table linked product dimension (containing product-hierarchy , attribute hierarchy product ) , productcomponent dimension. factcount count measure in measuregroup simple row count in fact.

tricky part : prductquantity on product level/grain - meaning total quantity of product.  in above example a1cycle has productquantity of 100 whereas b1cycle has productquantity of 200 (it repeated on rows of product)

i have created  simple cube product dimension attached measuregroup aggregation sum, when create pivot using product dimension , productquantity measure

product    productquantity   factcount

-------------------------------------

a1cycle      700                     7

b1cycle       1400                   7

total           2100                    14

the expected output should (i don't need factcount measure, showing illustration)

product    productquantity        factcount

------------------------------------------

a1cycle            100                  7

b1cycle             200                 7

total                 300                 14

i cannot change aggregation type sum max etc, if put filter choosing a1cycle , b1cycle should show me 100 + 200 = 300 result (and not 200 if take aggregation max)

i have productcompnent dimension related measuregroup.

when create report filter on productcompnent = tyre , product on rows looks follows, correct output

(productcompnent = tyre )

produt                productquantity  factcount

---------------------------------------------

a1cycle                100                   1

b1cycle                 200                   1

total                     300                   2

my question : how can make sure when browses productquantity against product sees correct output

product productquantity   factcount

-----------------------------------

a1cycle              100              7

b1cycle               200              7

total                   300              14

i tried writing scope statement

scope ( measures.productquantity);

this = iif(product.product.currentmember product.product.levels(0).item(0),

measures.productquantity,

measures.productquantity / measures.factcount);

end scope;

after doing start getting correct output on rows @ least (since divides 700/700 = 100 , 1400/700=200, right output).

however issue arises total/all member. still shows 2100 (and not 300).

product productquantity   factcount

-----------------------------------

a1cycle              100              7

b1cycle               200              7

total                   2100              14

if change scope statement

scope ( measures.productquantity);

this = iif(product.product.currentmember product.product.levels(0).item(0),

measures.productquantity / measures.factcount,

measures.productquantity / measures.factcount);

end scope;

the member shows me total of (700 + 1400)/(7 + 7 ) = 2100/14 = 150 (and not 300)

product productquantity   factcount

-----------------------------------

a1cycle              100              7

b1cycle               200              7

total                   150              14

i think correct output come if first divides , aggregates ie (700/7 + 1400/7 ) = 100 + 200 = 300

any suggestions highly appreciated.

thanks in advance

hi

i think, best case of grain issue , simpler handle @ etl load @ cube.

e.g:

1. using row_number function on product i.e. a1cycle, can 1,2,3 etc

2. using case statement set productquantity on row_number 1 , make 0

3. in ssas, solve issues automatically

rownumber  product      productquantity    productcompnent     productcomponentprice    1          a1cycle      100                tyre                10  2          a1cycle      100                tube                5   rownumber  product      productquantity    productcompnent     productcomponentprice    1          a1cycle      100                tyre                10  2          a1cycle      0               tube                   5

---

prav

SQL Server  >  SQL Server Analysis Services