AnsweredAssumed Answered

issue with mondrian and aggregate tables

Question asked by Erwan JEZEQUEL on Feb 2, 2018

I would like to use aggregate tables because I have a need of semi-additive measures.

I am running pentaho-server-ce-7.1.0.0-12.

 

I have created a sample dataset to illustrate my issue.

 

I have a fact_test with id_date, id_hour and a measure measure1 (the aggregation is max).

The aggregate table is fact_test_agg with id_date and a measure measure1.

 

I have the following content in fact_test :

id_date     id_hour     measure1

20180202     1000     1

20180202     1100     1

20180202     1200     1

 

I have the following content in fact_test_agg :

id_date     measure1

20180202     5

 

=> note : so if I use only the dimension date, I want the measure1 to be 5 and not 1

 

If I run this query with a clean cache :

with set [~ROWS] as '{[date.mois].[jour].Members}'

select NON EMPTY {[Measures].[measure1]} ON COLUMNS,

  NON EMPTY [~ROWS] ON ROWS

from [test]

I get the expected result 5.

 

But If run first theses queries in this order with a clean cache (saiku is used as client):

first query without measure :

with set [~COLUMNS] as '{[heure].[heure].Members}'

  set [~ROWS] as '{[date.mois].[jour].Members}'

select NON EMPTY [~COLUMNS] ON COLUMNS,

  NON EMPTY [~ROWS] ON ROWS

from [test]

next

with set [~COLUMNS] as '{[heure].[heure].Members}'

  set [~ROWS] as '{[date.mois].[jour].Members}'

select NON EMPTY Crossjoin([~COLUMNS], {[Measures].[measure1]}) ON COLUMNS,

  NON EMPTY [~ROWS] ON ROWS

from [test]

next

with set [~ROWS] as '{[date.mois].[jour].Members}'

select NON EMPTY {[Measures].[measure1]} ON COLUMNS,

  NON EMPTY [~ROWS] ON ROWS

from [test]

I get the not expected result of 1.

 

Is it normal?



Schema :

<Schema name="schema">

  <Dimension type="TimeDimension" visible="true" highCardinality="false" name="date">

    <Hierarchy name="mois" visible="true" hasAll="true" primaryKey="id_date">

      <Table name="dim_date" schema="public">

      </Table>

      <Level name="annee" visible="true" column="year" type="Integer" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">

      </Level>

      <Level name="mois" visible="true" column="date_month_str" ordinalColumn="month" type="String" uniqueMembers="true" levelType="TimeMonths" hideMemberIf="Never">

      </Level>

      <Level name="jour" visible="true" column="id_date" nameColumn="date_day_str" type="Integer" uniqueMembers="true" levelType="TimeDays" hideMemberIf="Never">

      </Level>

    </Hierarchy>

  </Dimension>

  <Dimension type="TimeDimension" visible="true" highCardinality="false" name="heure">

    <Hierarchy name="heure" visible="true" hasAll="true" primaryKey="id_hour_minute">

      <Table name="dim_hour_minute" schema="public">

      </Table>

      <Level name="heure" visible="true" column="hour" ordinalColumn="hour"  type="Integer" uniqueMembers="true" levelType="TimeHours" hideMemberIf="Never">

      </Level>

      <Level name="minute" visible="true" column="minute" ordinalColumn="minute"  type="Integer" uniqueMembers="false" levelType="TimeMinutes" hideMemberIf="Never">

      </Level>

    </Hierarchy>

  </Dimension>

  <Cube name="test" visible="true" cache="true" enabled="true">

<Table name="fact_test" schema="public">

       <AggName name="fact_test_agg">

    <AggFactCount column="fact_count"/>

    <AggForeignKey factColumn="id_date" aggColumn="id_date"/>

    <AggMeasure name="[Measures].[measure1]" column="measure1"/>

  </AggName>

  </Table>

    <DimensionUsage source="date" name="date" visible="true" foreignKey="id_date">

    </DimensionUsage>

    <DimensionUsage source="heure" name="heure" visible="true" foreignKey="id_hour">

    </DimensionUsage>

    <Measure name="measure1" column="measure1" datatype="Integer" aggregator="max" visible="true">

    </Measure>

  </Cube>

</Schema>

Outcomes