PDA

View Full Version : Specifying Aggregate Formulas in Metadata



kevin.haas@openbi.com
04-07-2008, 12:37 PM
I'm using Metadata 1.6.0.162. 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
(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:

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

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

SELECT
Contact.groupfield AS COL0
,sum(totalfield1) / sum(totalfield2) AS COL1
FROM
dim_contact Contact
,fct_product_usage "Usage Metrics"
WHERE
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.

MattCasters
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.

Matt

kevin.haas@openbi.com
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). :)

Kevin

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

Matt