Table alias when creating a MeasureExpression in Mondrian 4

Question

I'm trying to create an indicator that is the distinct count of customers who have placed an order. The relevant part of the Mondrian schema is as follows:

<Measure name="Active Customers" formatString="#" aggregator="distinct-count">
  <MeasureExpression>
    <SQL dialect="mysql">
      CASE WHEN <Column name ='placed_count'/> &gt; 0 THEN 1 END
    </SQL>
  </MeasureExpression>
</Measure>

For some reason, the column name is not being replaced by its column name including the table alias. I can check that in the log:

Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while loading segment; sql=[select `v_dm_calendar`.`calendar_date` as `c0`, count(distinct CASE WHEN  > 0 THEN 1 END) as `m0` from `v_dm_calendar` as `v_dm_calendar`, `fc_customer_activity_sportsbook` as `fc_customer_activity_sportsbook` where `fc_customer_activity_sportsbook`.`bet_date` = `v_dm_calendar`.`calendar_date` and `v_dm_calendar`.`calendar_date` = '2015-03-30' group by `v_dm_calendar`.`calendar_date`]
        at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:972)
        at mondrian.olap.Util.newInternal(Util.java:2404)
        at mondrian.olap.Util.newError(Util.java:2420)
        at mondrian.rolap.SqlStatement.handle(SqlStatement.java:353)
        at mondrian.rolap.SqlStatement.execute(SqlStatement.java:253)
        at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:350)
        at mondrian.rolap.agg.SegmentLoader.createExecuteSql(SegmentLoader.java:625)
        ... 8 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '> 0 THEN 1 END) as `m0` from `v_dm_calendar` as `v_dm_calendar`, `fc_customer_ac' at line 1    

Am I missing something in the syntax? I could not find anything neither in the official documentation nor in a Mondrian book.

Answer 1

It would be defined like this in Mondrian 3:

<MeasureExpression>
    <SQL dialect="generic">
        <![CDATA[case when placed_count > 0 then 1 else 0 end]]>
    </SQL>
</MeasureExpression>

In Mondrian 4 you need to add calculated column to the declaration of a fact table and create a measure based upon it:

<Table name="??">
    <ColumnDefs>
        <CalculatedColumnDef name="active_customers">
            <ExpressionView>
                <SQL dialect="generic">
                    (case when <Column name="placed_count"/> &gt;
                    0 then 1 end)
                </SQL>
            </ExpressionView>
        </CalculatedColumnDef>
    </ColumnDefs>
</Table>

<Measure name="Active Customers" aggregator="distinct-count" column="active_customers" formatString="#">

For more information compare a section 3.2 Measures in Mondrian 4 and Mondrian 3 documentation.