July 11, 2002 at 12:00 pm
Hello there,I've defined into Analysis services a new calculated member via formula like this :
int([GEOGRAPHY].Properties("prc"))/100*[Measures].[units sold]
where prc is a factor percentage (a multiplicative coefficient) stored into a GEOGRAPHY table (from which my geography dimension comes from ) and units sold are facts stored into a fact table.
This runs fine at leaf level for which only I've provided facts data.
But when I try to rollup into a parent child figure inside geography dimension It doesn't behave correctly because the system rollups before Units sold at that parent level and after this multiplies for the coefficient property at that level!
I've solved my problem with a recursive call like this, but it isn't very performant on a large amount of data (calculated member:=Unita):
iif (IsLeaf([GEOGRAPHY].CurrentMember),int
([GEOGRAPHY].Properties("prc"))/100*[Measures].[units sold],
sum([GEOGRAPHY].CurrentMember.Children,Unita))
If someone has another idea....
Many Many Thanks in advance to all!
Fabs
July 14, 2002 at 3:52 pm
Hello Fabs -
I think you have created the best solution possible given your source data structure. It seems your only other option would be to pre-calculate Unita in the relational source (leaf level) and load this value as the fact. From there, the Sum aggregation would give you the proper parent values.
While this may be a better performer at query time, it makes your relational source logic more complicated and slower.
Scot J Reagin
Scot J Reagin
sreagin@hitachiconsulting.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply