Hi Team,

Currently we have some measures that are defined by age, meaning there are dollars owed that are between 0 to 30 days old, some that are 31 to 60 days old, and so on. These are discrete measures, like this:

A/R $ 0-30 days
A/R $ 31-60 days

We have some companion measures that show similar information, but as a % of total. So, the dollars from 0-30 days are divided by the total dollars to show what percent of all outstanding dollars are 0-30 days old. On the measure loader screen for these % of total measures, the numerator and denominator looks something like this:

NUMERATOR=IF <0-30> then AMOUNT else 0;
DENOMINATOR=AMOUNT;

Now we would like to turn all the dollar measures into one measure and all the percentage measures into one measure, and use a dimension to provide the aging breakout. I am very clear on how this will work for the dollar measures; the data needs to look like this:

AGING_DIM_KEY  AMOUNT
0_30           100
31_60          100
61_90          100
91_180         100
181_365        100
366_PLUS       100

So, in this case the total AR will be 600, and I would like to use that for the denominator of the percent of total measures. However, I cannot quite see how I would make that happen with an aging dimension. The closest that I've been able to come to designing this is to make it data driven:

AGING_DIM_KEY  NUMERATOR  DENOMINATOR
0_30           100        600
31_60          100        600
61_90          100        600
91_180         100        600
181_365        100        600
366_PLUS       100        600

The measure loader screen would look like this:

NUMERATOR=NUMERATOR;
DENOMINATOR=DENOMINATOR;

The obvious problem is that the NUMERATOR can aggregate at a higher dimension level, but the DENOMINATOR cannot. At the top level of the aging dimension, the NUMERATOR and DENOMINATOR should equal each other, but in this case it would be 600 / 3600.

I wonder if this would work:

NUMERATOR=NUMERATOR;
DENOMINATOR=MIN.DENOMINATOR;

I am also open to any suggestions about this that I haven't though of, please.

Cheers,

Joey

Hi Team,

I've been thinking about this a lot, and have come to realize that my idea of putting using DENOMINATOR=MIN.AMOUNT won't work. This is on the measure loader screen, so it would only kick in during measure load, not during query run-time like I need.

So, that leaves me to wonder about the alternate aggregation features of PMF. I found one mention in the pmf_doc.pdf file about something called External Aggregation. This is what it says:

   
External Aggregation: Select whether to allow aggregate measure values using external tables
with pre-populated rollup values. Choose one of the following: Y (Yes) N (No). The default is N (No).

External Aggregation MFD: Type the name of the Master File to use for external aggregation.

So, this sounds like it might be a viable path, but I need to know more about this. The name and feature description implies to me that I can load a table with values at every level of a dimension versus loading values at the lowest level and relying on summarized aggregation. If so, this is likely a viable solution for what I'm trying to do here.

Is there anywhere I can read a little more about this? I searched the pmf manual, the install guide and the developer's guide, but could only find the above entry. The New Features (5.2) document spoke of pre-aggregated cubes, and I wonder if that is something similar to External Aggregation:

Integration with Pre-Aggregated Cubes
To further support extra large and complex metric pools, PMF 5.2 has been enhanced to
support external pre-aggregated cubes. This allows PMF to merge externally aggregated
metrics with normal PMF managed metrics in a way that allows them to fully
participate in PMF capabilities and remains transparent to the consumer.

I wonder if it's possible to pre-aggregate values along one dimension and allow straight roll-up along the others? I suspect not. If not, then that means I need to calculate the % of total value for each and every dimension intercession point, and store it in a table. I suppose I will need high level roll-up values, but how would I key the dimension values? PMF dimension source tables are keyed at the lowest level and then PMF generates internal keys for the higher levels.

Would appreciate your help figuring this out.

Cheers,

Joey

Hi Joey,

We have not been ignoring you

Let me deal first with the nature of this "Aging" dimension. If I am following you, the Aging dimension is a one level dimension. Next we have a Measure (or Measures) which use this dimension. These Measure(s) are percentage Measures along the Aging dimension, but they do not appear to add up properly when we remove this dimension from the sort (i.e, if we sum up along all values of this dimension). What I am not following is why you would ever remove this dimension from the sort. By definition, if you add up all values along this dimension, then you you always get 100%. This 100% applies across any additional dimensional sorts you might want. So if you are adding up all Aging values for Location EAST, Department XYZ, for 2011Q2, you will still get 100%.

Am I understanding this correctly? If so, then you might be able to handle this as a customization. Check the sorts - if the Aging dimension is not one of the sorts, then the Actual is 100% (I assume the Targets would also have to add up to 100% as well).

Next, you are asking about External Aggregation - which means your data is stored in a pre-aggregated cube that is not maintained by PMF. Your suspicions are correct in that you need to store the value of every dimensional intersection in the external data store including all higher level aggregations. However, you do not need to use PMF's internal key values. Instead your external store must have the same dimensions and dimensional values that you are using inside PMF. In other words, the external data store must be a cube that mirrors the PMF_CUBE_VW inside of PMF. So if you are doing a report and you just want information for location 'EAST', PMF passes a "WHERE LOCATION_LEVEL01_VALUE EQ 'EAST'" to the external data store.


Eric