View Full Version : Specifying Aggregate Formulas in Metadata

04-07-2008, 12:37 PM
I'm using Metadata I want to add an aggregate calculation for a ratio in the metadata, e.g:

groupfield, sum(totalfield1) / sum (totalfield2) as ratiofield

I followed guidance per this link: http://wiki.pentaho.org/display/PentahoDoc/02.+Pentaho+Metadata+Formulas
What I tried do to is to specify as a formula:

AggregationType = None
Name = RatioField
Formula = SUM(totalfield1) / SUM(totalfield2)
Exact = Yes

..and it generates SQL correctly as long as there is not group by field:

sum(totalfield1) / sum(totalfield2) AS COL0
fct_product_usage "Usage Metrics"

...but once a group by field is added:

Contact.groupfield AS COL0
,sum(totalfield1) / sum(totalfield2) AS COL1
dim_contact Contact
,fct_product_usage "Usage Metrics"
Contact.dim_contact_id = "Usage Metrics".dim_contact_id

The metadata does not recognize that I'm doing an aggregate in the formula, and the SQL is invalid (no group by clause). Is there another way to get this accomplished? Thanks.

04-07-2008, 03:12 PM
Nice catch, this is a problem, even in the latest dev version.

I created this JIRA case for it: http://jira.pentaho.org/browse/PMD-318

I don't know a quick workaround either... that is besides doing the ratio calculation in a view or in the ETL process.

Mmm, my main man Will says you can set an Aggregation on the calculated column as long as you check the "is exact" flag.
Try that one too.


04-07-2008, 03:29 PM
Thanks, Matt. Yeah, that's the case I tried, where the is exact flag is set. And it does handle the aggregation line of the sql correctly, but it forms the SQL without a group by clause even when there's something to group on.

I suppose it would have to parse sum, count, avg out of the formulas to know that you're doing an aggregate formula (or maybe just a check box that the user could specify that the formula is an aggregate formula?) and then trigger whatever normally forms the SQL with a group by like when you've selected an aggregation type that is not "None". I'm certainly oversimplifying, but just a couple thoughts.

Thanks for your help and for setting up the JIRA case. I thought I was crazy on this one (which of course still may be the case). :)


04-07-2008, 04:57 PM
Kevin, we'll fix this one for sure.
Just hang in there.