[wdmmg-discuss] Aggregated vs per-capita statistics

Wed Apr 14 14:48:59 UTC 2010

Hi Alastair,

I think you may have encountered the "Average of Children" problem.

http://download.oracle.com/docs/cd/B28359_01/olap.111/b28124/aggregate.htm

The interesting point with this dataset is that it is regional, and
therefore the regional dimension must have an effect on the population,
which would affect this calculation! You really are hitting the sorts of
problems that MDX and OLAP are designed to help with. A fact table for
transactions and a fact table for regional populations, connected by a
region dimension would allow the problem of averaging "per capita" to be
solved with ease.

I know this might be annoying, but you might be guilty of re-inventing the
data warehouse! I can't recommend this book enough:

http://www.ralphkimball.com/html/booksDWT2.html

Cheers,
Donny.

On 14 April 2010 15:27, Alistair Turnbull <apt1002 at goose.minworks.co.uk>wrote:

> (For interest only).
>
> One of the new requirements for the aggregator is to calculate
> spending-per-capita figures. This is a two step process:
>
> 1. *Sum* spending and population over all irrelevant variables.
>
> 2. *Divide* spending by population.
>
> It is important to do the sum before the divide. After doing the division,
> no further aggregation is possible, unless the divisor (population) is
> constant along the axis you're summing over. I have made a little
> spreadsheet (attached) to help me understand what's going on.
>
> This phenomenon means the aggregation sometimes has to be done exactly
> right first time by the data store; there's no later opportunity to do
> further custom aggregation of any sort in the presentation layer.
>
> The question I'm battling with is whether this forces the data store to
> understand anything it doesn't already understand. My main worry is
> hierarchically organised keys such as COFOG, POG and NUTS (region). I really
> wanted to confine the hierarchical structure to the presentation layer as
> much as possible. However, if we want to partially aggregate over some key,
> by which I mean aggregate up to some depth in the tree, then maybe the store
> needs to understand the tree structure too...
>
> There are so many directions in which Dave and I can push this particular
> part of the design. I hope we'll find a way to keep it simple. I'm not sure
> what it is yet. :-(
>
>        Alistair
> _______________________________________________
> wdmmg-discuss mailing list
> wdmmg-discuss at lists.okfn.org
> http://lists.okfn.org/mailman/listinfo/wdmmg-discuss
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.okfn.org/pipermail/openspending/attachments/20100414/40c2b2e5/attachment.html>