Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Specifying Aggregate Formulas in Metadata

  1. #1

    Default Specifying Aggregate Formulas in Metadata

    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

    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.

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    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

  3. #3

    Default

    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

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Kevin, we'll fix this one for sure.
    Just hang in there.

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.