Let's speak about analysis services.
I have this problem with rollup and aggregation functions.
I've a fact table with 2 dimensions and 2 measures like
this:
DATE ID_PART MS_COST MS_SELL
01/2004 A05AA110 100 1000
01/2004 A05AA120 150 1000
02/2004 A05AA110 300 3000
02/2004 A05AA120 100 3000

dimensions are:
DM_Time: standard time dimension with only month and year
DM_PartNumbers: parent-child dimension like this
ID_Part ID_Parent
A05AA110 A05
A05AA120 A05
.... ...

The data in the fact table tell us that I have sold in
January 1000 items of the group A05 that are made of part
number A05AA110 and part number A05AA120; the two single
part numbers have generated a cost of 100 the first and
150 the second.
So the total cost generated by the group A05 for the total
time is (100+150+300+100 = 650) but the total number of
sold items for the group A05 in the entire period is only
1000 + 3000 = 4000 not 1000+1000+3000+3000 = 8000 because
I have to count only one time the sold quantity for the
same group.
In other words I've to manage the PartNumber dimension in
different way if either I'm working on the cost measure or
on the sold items measures. For the time dimension the way
of aggregate is always sum
can you help me?
thanks alot
dario