Can anyone of you fine people help me out on this (there may be a snickers bar for the correct answer!!!)
I'm having a bit of a problem with aggregation in FM. For example I've got a measure called GP% which is gross profit / net sales (both measures straight from the DB).
The calculation works great at a detail level i.e. when I take off the auto group/summarise function. But when I leave the auto group on the figures are way off. It looks like the calculation is being done at the lowest level (of the db not the query) and then being rolled up (i.e. summed) to the relevant level in the query rather than the calculation being done at the relevant query level.
I've tried numerous combinations of regular and semi aggregation but nothing seems to work.
I can get around this by totalling both sides of the calcuation (i.e. total(gross profit) / total(net sales). That seems to roll up OK but it's generating an analystical function using partition by which our DBA really doesn't like.
Does anybody have any idea how I can get around this and put a smile back on the face of my DBA?
As always thanks for your help.
Isn't there a property that says if the calculation should be performed before or after aggregation?
BTW, if you have some cash and want to put a really big smile on your DBA's face, look at HyperRoll, which takes care of all your aggregate needs before it hits your TDS.
Percentages are non-additive measures by nature, which means that you cannot perform the division and then expect figures to roll up to the correct value:
| gross profit sales |
| 200 1000 |
| 100 400 |
| 50 300 |
| 20 100 |
The proper calc is not 200/1000 + 100/400 + 50/300 + 20/100
= .2+.25+.167+.2 = .65+.167 = .817 = 81.7 %
but:
200+100+50+20 / 1000+400+300+100 = 370 / 1800 = 20.5%
The nitty-gritty is that totalled percentages are
ALWAYS calculated as the last step, as you have already succesfully defined.
Obviously the totalization is done for each group value, hence the ' partition by' phrase. Nothing out of the ordinary there..
We know, we know, but I do recall that with cubes it was quite easy to achieve, by selecting a different rollup timing. So maybe a DMR would help here?
I'm a square SQL guy ;D Not too familiar with cubes.
Being accustumed to server-client reporting for about 10 years with Business Objects we had it easy: simply fetch the totals for each measure for each set of dimensions and performing the percentage at the client.
The downside of Web enabled reporting is that you need all your calculations done at the server side which triggers more complex SQL.
(like partition by constructs)
However if you examine your Cognos SQL you will notice expressions like:
xsum(measure) for .............. or xsum(measure) at ...........[
No RDBMS can handle that syntax other than by using partition by constructs.
I'd say it is in the nature of the product, so DBA's need to adjust.
Everybody thanks for your input. I was rapidly coming to the same conclusion. I don't see anything fundamentally wrong with using the partition by construct (as long as it isn't used everywhere). Thanks for confirming the point.
Simon