Best way to create and use a derived attribute of a dimension?

[#1]

Looking for some advice please?

I'm building a 'Future Cash Receipts chart' by projecting when all the open receivables will be received by using each customer's past payment patterns to project a 'expected payment date'. To do this I need to use the 'Avg Days Pay' for each customer to project when each outstanding receivable will be paid. I'll apply this to each open receivable and plot the Receivable $s against 'expected payment date'.

My question is this -- in deriving the 'Expected Payment Date' should I try to build the entire calculation into a calculated (date) dimension in the chart? Or should I use scripting to pre-calculate the 'avg-days-to-pay'' for each customer and join to the Open Receivables table to store the "expected Payment Date' for each receivable in a table? Then the chart is very simple.

The calculation is static (does not need to reflect the user selections) so the scripting approach is technically workable, but it seems more 'elegant' to build it into the chart more dynamically (in case the requirements change in the future).

Is it even possible to do it completely in the chart?

The data volumes are quite small (<100,000 Open Receivables) so I don't think performance needs to drive the decision (or does it?).

What's the collective wisdom on a best practice for this type of analysis?

Paul

[Anonymous · Accepted solution]

Paul,
I doubt the "collective wisdom' is much help in a particular situation. Given the description, I'd do it in the script.

[Oleg_Troyansky]

Paul,

it might be possible to do in the chart, using a lot of AGGR() and Set Analysis and such, but the end result would be "heavy" and hard to maintain...

If you don't lose the granularity of the data, I'd say that everything that can be calculated in the load time, and not in the run time, should be scripted.

cheers,

[#4]

Thanks Michael and Oleg,

I figured it would be easier in the script -- but I wondered if I was 'cheating' by using the 'brute-force' approach and not using the power of the UI effectively. I guess sometimes the simplest way isn't cheating (bad) -- it's avoiding unnecessary complexity (good).

regards,

Paul