Binary Aggregation

(From the archives July 1997)

I finally came up with the solution. As we looked at the numbers on the paper reports for headcounts, I recognized that none of the numbers were tying. Every cell contained a possible double count depending on the rollup. Only zero level numbers were reasonable. I reasoned that at every level of aggregation, what we wanted was a yes or no by individual.

Binary Aggregation

The solution is to create a member formula for Heads which performs the equivalent of a binary aggregation at every level of aggregation of every dimensions but the top level of the Individual dimension.

In order to get unique heads as a measure do the following.

First, you must have a dimension for individuals and input records by individual.

Input a dummy measures field called Hits by adding a "1" as a column in your input file by modifying your load rule.

 Calc your database normally.

 When you report against such a database you'll get a problem with double counting, unless you drill down to the individual level.

 For example,

                                                       

Individual	Dim X	Dim Y	Hits
A	1	0	1
A	1	1	1
A	1	0	1
A	0	1	1
B	1	0	1
B	0	0	1

 

 

Notice that if you query Hits for XY = {1,0} you will get 3 despite the fact that there are only 2 distinct individuals. (A hits twice), Unless you break out the individual dimension, you can't know that A hit twice.

 

The solution is to aggregate Hits at the descendants of the Individual dimension according to the following logic:

hits = (if <>#missing) 1, #missing

Fix on the descendants of Individual, then aggregate the Individual dimension across all the other dimensions, then on a third pass, aggregate the Individual dimension by itself. I forget the actual syntax i wrote in the calc script but it worked like a charm. Thus reporting at the top level of Individual, you get distinct heads. Now you can rename the Hits measure to Heads.

At this point you can export the database and get rid of the Individual dimension, if you like.