[Mondrian] Re: Errors with CalculatedMember and SQL in Measure

m h sesquile at gmail.com
Fri Sep 26 14:12:27 EDT 2008


These problems seemed to have stemmed from the aggregation table I
defined in my cube:

Here's the complete cube, why can't it find the measures?


  <Cube name="Sales_Cube" cache="true" enabled="true">
    <Table name="week_sales_fact">
      <AggName name="agg_mond_dept_week_sales_fact">
	<!-- Required column for mondrian -->
	<AggFactCount column="FACT_COUNT"/>
	<AggMeasure name="[Measures].[Sales]" column="Sales"/>
	<AggMeasure name="[Measures].[Units]" column="Units"/>
	<AggMeasure name="[Measures].[Cost]" column="Cost"/>
	<AggMeasure name="[Measures].[Margin]" column="Margin"/>
	<AggMeasure name="[Measures].[Allowances]" column="Allowances"/>
	<AggLevel name="[Time_Dim].[Year]" column="Year"/>
	<AggLevel name="[Time_Dim].[Quarter]" column="Quarter"/>
	<AggLevel name="[Time_Dim].[Month]" column="Month"/>
	<AggLevel name="[Time_Dim].[Week]" column="Week"/>
	<AggLevel name="[Product_Dim].[Department]" column="Department"/>
      </AggName>
    </Table>

    <DimensionUsage name="Time_Dim" source="Time_Dim" foreignKey="adweekid"/>
    <DimensionUsage name="Store_Dim" source="Store_Dim" foreignKey="stornum"/>
    <DimensionUsage name="Product_Dim" source="Product_Dim" foreignKey="upc"/>
    <DimensionUsage name="Vendor_Dim" source="Vendor_Dim" foreignKey="vendnum"/>

    <Measure name="Sales" column="sales" aggregator="sum"
formatString="$#,###"/>
    <Measure name="Units" column="units" aggregator="sum" formatString="#,###"/>
    <Measure name="Cost" column="cost" aggregator="sum" formatString="#,###"/>
    <CalculatedMember name="Margin" dimension="Measures">
      <Formula>[Measures].[Sales] - [Measures].[Cost]</Formula>
      <CalculatedMemberProperty name="FORMAT_STRING" value="$#,###"/>
    </CalculatedMember>
    <Measure name="Allowances" aggregator="sum" formatString="$#,###">
      <SQL dialect="postgres">
	week_sales_fact.units * (coalesce(week_sales_fact.dealamt, 0) +
coalesce(week_sales_fact.purbillback, 0) +
coalesce(week_sales_fact.scanbillback))
      </SQL>
    </Measure>
  </Cube>

cheers

-matt



On Thu, Sep 25, 2008 at 12:09 PM, m h <sesquile at gmail.com> wrote:
> Hey Folks-
>
> Running on Ubuntu 8.4 with Postgres.  Am trying to run cmdrunner to
> generate aggregate tables (have 80+ gigs of data).
>
> I've got the following measures defined in my cube:
>
>    <Measure name="Sales" column="sales" aggregator="sum"
> formatString="$#,###"/>
>    <Measure name="Units" column="units" aggregator="sum" formatString="#,###"/>
>    <Measure name="Cost" column="cost" aggregator="sum" formatString="#,###"/>
>    <CalculatedMember name="Margin" dimension="Measures">
>      <Formula>[Measures].[Sales] - [Measures].[Cost]</Formula>
>      <CalculatedMemberProperty name="FORMAT_STRING" value="$#,###"/>
>    </CalculatedMember>
>    <Measure name="Allowances" aggregator="sum" formatString="$#,###">
>      <SQL dialect="postgres">
>        week_sales_fact.units * (coalesce(week_sales_fact.dealamt, 0) +
> coalesce(week_sales_fact.purbillback, 0) +
> coalesce(week_sales_fact.scanbillback))
>      </SQL>
>    </Measure>
>
> When I try to run the "cube" command from cmd runner I get the following:
>
>
>> cube
> 0    [main] ERROR mondrian.rolap.aggmatcher.AggTableManager  -
> Sales:NameTableDef:TableDef:Measure: Context
> 'Sales:NameTableDef:TableDef:Measure': Failed to find Measure name
> 'Margin' for cube 'Sales'.
> 4    [main] ERROR mondrian.rolap.aggmatcher.AggTableManager  -
> Sales:NameTableDef:TableDef:Measure: Context
> 'Sales:NameTableDef:TableDef:Measure': Measures does not have a
> measure named 'Allowances'
> 4    [main] ERROR mondrian.rolap.aggmatcher.AggTableManager  -
> Sales:NameTableDef:TableDef:Measure: Context
> 'Sales:NameTableDef:TableDef:Measure': Failed to find Measure name
> 'Allowances' for cube 'Sales'.
> Exception in thread "main" mondrian.olap.MondrianException: Mondrian
> Error:Too many errors, '3', while loading/reloadin aggregates.
>
> I'm not sure what's wrong.  I based Margin off of the "Profit"
> CalculatedMember in FoodMart.xml.  Also am not sure why it's
> complaining about "Allowances".  My serarching only leads to the
> javadocs....
>
> Any ideas?  (or gently prodding me in the direction that I'm missing) ;)
>
> cheers,
>
> -matt
>




More information about the Mondrian mailing list