Has anyone leveraged the transformation process on an import to do a weighted average?

[OSAdmin]

Originally posted by Sam Richards

3/29/2018

Has anyone leveraged the transformation process on an import to do a weighted average? I am thinking this is possible leveraging the derivative mappings but I wanted to confirm. For example if I have 5 loans that have an outstanding balance of different amounts and all have different rates. In the transformation process these then loans are going to have a many to one mapping all going to one market. What I would load into the cube for the market would be an weighted average based off of the loan balance.

[OSAdmin]

Originally posted by Chul Smith

You might be able to do it without derivative rules by loading the rate to an attribute field and then doing a calculation directly on the cube member using a combination of cube and stage data.
I think you could also do it using stage cache - loops through the stage and aggregates on the fly. I'm looking at XFR_ExecuteStageCacheQuery in GolfStream
That might be a better idea if i could do it all in stage, because the we can have up to approx 20k loans so i think it would be a beast of a calc to do it inside the cube because i am assuming that you would have to create some sort of rule to leverage the mappings to go back to stage to pull each individual loan balance that got mapped to the one market then divide that by the cube market balance to calc the weighted average then take that and multiply that by the rate from stage by loan, then sum all of those to get the aggregated rate at the market level?
i will look at that in derivative rule in Golfstream, I think that might work