Mondrian 10 查询时忽略聚合表问题求助

我在MYSCHEMA模式下有存储销售数据的事实表SALESSTATS，同时创建了同模式的聚合表MYAGG，但按公司查询营收时，Mondrian 10并未使用该聚合表。

日志信息 #

10:22:18,609 DEBUG [mdx] 152: with set [~ROWS] as '{[Company.Companies].[Company Code].Members}'
select NON EMPTY {[Measures].[Sales - Revenue]} ON COLUMNS,
  NON EMPTY [~ROWS] ON ROWS
from [Stats]

10:22:18,629 DEBUG [sql] 0: SqlTupleReader.readTuples [[Company.Companies].[Company Code]]: executing sql [select "COMPANY"."COMPANYID" from "MYSCHEMA"."COMPANY" as "COMPANY" group by "COMPANY"."COMPANYID" order by CASE WHEN "COMPANY"."COMPANYID" IS NULL THEN 1 ELSE 0 END, "COMPANY"."COMPANYID" ASC]
10:22:18,647 DEBUG [sql] 0: , exec 8 ms
10:22:18,652 DEBUG [sql] 0: , exec+fetch 20 ms, 14 rows
10:22:18,669 DEBUG [sql] 1: SqlStatisticsProvider.getColumnCardinality: executing sql [select count(distinct "COMPANYID") from "MYSCHEMA"."COMPANY"]
10:22:18,679 DEBUG [sql] 1: , exec 8 ms
10:22:18,680 DEBUG [sql] 1: , exec+fetch 11 ms, 1 rows
10:22:18,699 DEBUG [sql] 2: Segment.load: executing sql [select "COMPANY"."COMPANYID", sum("SALESSTATS"."REVENUE") as "m0" from "MYSCHEMA"."COMPANY" as "COMPANY", "MYSCHEMA"."SALESSTATS" as "SALESSTATS" where "SALESSTATS"."COMPANYID" = "COMPANY"."COMPANYID" group by "COMPANY"."COMPANYID"]

表结构说明 #

• SALESSTATS字段：TIME、COMPANY、REVENUE、PURCHPRICE、PURCHQTY、SOMETHING、SOMETHING2
• MYAGG聚合表：包含SALESSTATS所有字段，额外增加统计分组行数的NLINES字段

Schema配置 #

<Schema name="MyStats">

  <!-- Shared Dimensions -->

  <Dimension name="Company">
    <Hierarchy name="Companies" hasAll="true" allMemberName="_" allMemberCaption="All Companies" allLevelName="All Companies" primaryKey="COMPANY">
      <Table name="COMPANY" schema="MYSCHEMA"/>
      <Level name="Company Code" column="COMPANYID" type="Numeric" uniqueMembers="true"/>
      <Level name="Company Short Name" column="COMPSNAME" type="String" uniqueMembers="true"/>
      <Level name="Company Name" column="COMPNAME" type="String" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>

  <Dimension name="Time" type="TimeDimension">
    <Hierarchy name="Time" primaryKey="TIME" hasAll="true" allMemberName="_" allMemberCaption="All time" allLevelName="All time">
      <Table name="TIME" schema="MYSCHEMA"/>
      <Level name="Year" column="YEAR" levelType="TimeYears" type="Integer"/>
      <Level name="Quarter" column="QUARTER" levelType="TimeQuarters" type="Integer"/>
      <Level name="Month" column="MONTH" levelType="TimeMonths" type="Integer"/>
    </Hierarchy>
  </Dimension>

  <!-- Cubes -->

  <Cube name="Stats - Sales">
    <Table name="SALESSTATS" schema="MYSCHEMA">
      <SQL dialect="generic">
        USER = 'SOMEUSER'
      </SQL>
      <AggName name="MYAGG">
        <AggFactCount column="NLINES"/>
        <AggForeignKey factColumn="TIME" aggColumn="TIME"/>
        <AggForeignKey factColumn="COMPANY" aggColumn="COMPANY"/>
        <AggMeasure name="[Measures].[Sales - Revenue]" column="REVENUE"/>
        <AggMeasure name="[Measures].[Sales - Total Purchased Price]" column="PURCHPRICE"/>
        <AggMeasure name="[Measures].[Sales - Total Purchased Quantity]" column="PURCHQTY"/>
        <AggIgnoreColumn column="SOMETHING"/>
        <AggIgnoreColumn column="SOMETHING2"/>
      </AggName>
    </Table>

    <DimensionUsage name="Company" source="Company" foreignKey="COMPANY"/>
    <DimensionUsage name="Time" source="Time" foreignKey="TIME"/>

    <Dimension name="Charged Customer" foreignKey="CCUSTID">
      <Hierarchy name="Charged Customer" primaryKey="CCUSTID" hasAll="true" allMemberName="_" allMemberCaption="All Charged Customers" allLevelName="All Charged Customers">
        <Table name="CUSTOMERS" schema="MYSCHEMA"/>
        <Level name="Charged Customer Code" column="CCUSTID" type="Numeric"/>
        <Level name="Charged Customer Name" column="CCUSTNAME" type="String"/>
        <Level name="Charged Customer City" column="CCUSTCITY" type="String"/>
      </Hierarchy>
    </Dimension>

    <Measure name="Sales - Revenue" column="REVENUE" datatype="Numeric" aggregator="sum" formatString="#,###"/>
    <Measure name="Sales - Total Purchase Price" column="PURCHPRICE" aggregator="sum" formatString="#,###"/>
    <Measure name="Sales - Total Purchased Quantity" column="PURCHQTY" aggregator="sum" formatString="#,###"/>
  </Cube>

  <Cube name="Stats - Other">
    ...
  </Cube>

  <VirtualCube name="Stats" defaultMeasure="Sales - Revenue">
    <VirtualCubeDimension cubeName="Stats - Sales" name="Customer"/>
    <VirtualCubeDimension cubeName="Stats - Sales" name="Time"/>
    <VirtualCubeDimension cubeName="Stats - Sales" name="Company"/>

    <VirtualCubeMeasure cubeName="Stats - Other" name="[Measures].[Other - Other Measure]"/>
    <VirtualCubeMeasure cubeName="Stats - Sales" name="[Measures].[Sales - Revenue]"/>
    <VirtualCubeMeasure cubeName="Stats - Sales" name="[Measures].[Sales - Total Purchased Price]"/>
    <VirtualCubeMeasure cubeName="Stats - Sales" name="[Measures].[Sales - Total Purchased Quantity]"/>

    <CalculatedMember name="Sales - Average Purchased Price" hierarchy="Measures" formula="Round([Measures].[Sales - Total Purchased Price], 5) / Round([Measures].[Sales - Total Purchased Quantity], 5)" formatString="#,###0.00"/>
  </VirtualCube>
</Schema>

1. 虚拟立方体的聚合支持限制 #

当前查询使用的是VirtualCube Stats，Mondrian对虚拟立方体的聚合表支持有限，默认不会自动关联基础Cube的聚合配置。可以直接查询基础Cube Stats - Sales验证聚合是否生效，若生效则需在VirtualCube中显式配置聚合表关联。

2. 聚合表粒度与查询不匹配 #

MYAGG聚合表的粒度是TIME+COMPANY组合，但当前查询仅按Company维度聚合，Mondrian会认为该聚合表粒度比查询需求更细，无法直接复用。如果查询不需要时间维度，需创建仅按Company聚合的聚合表版本，或确保聚合表粒度与查询的维度粒度完全匹配。

3. Schema维度键映射错误 #

• Company维度的<Hierarchy>配置primaryKey="COMPANY"，但维度级别使用的是COMPANYID字段，存在不匹配，需将primaryKey改为COMPANYID，与Level的column保持一致。
• 聚合表的AggForeignKey中factColumn="COMPANY"，需确认事实表SALESSTATS的COMPANY字段是否与维度表的COMPANYID对应，若不对应则需调整映射关系。

4. 聚合表数据一致性问题 #

Mondrian会校验聚合表数据与事实表的一致性，若MYAGG的NLINES（分组行数统计）与对应分组下SALESSTATS的实际总行数不符，会拒绝使用该聚合表。需重新计算NLINES值，确保数据准确。

5. Mondrian配置与缓存问题 #

• 检查mondrian.rolap.aggregates.enable参数是否设置为true（默认开启，需确认配置文件）。
• 清理Mondrian元数据缓存，重启服务后重新测试，避免旧元数据导致聚合表未被识别。

6. 维度级别与聚合表关联问题 #

查询使用的[Company.Companies].[Company Code]级别对应COMPANYID字段，需确认聚合表的COMPANY字段存储的是COMPANYID值，否则会导致维度与聚合表无法关联，Mondrian会 fallback到事实表查询。

内容的提问来源于stack exchange，提问作者Chi_Iroh