[Mondrian] Reusing snowflaked (time) dimension with different granularity

Jan Van Besien janvanbesien at gmail.com
Mon Mar 5 09:25:51 EST 2012


Hi all

I have a shared time dimension which is snowflaked into two tables. One 
table ("superday") contains Year, Month and Day. Another table 
("subday") contains Day, Hour, 5-Minute. Like this:

  <Dimension type="TimeDimension" name="Time">
   <Hierarchy name="Default" hasAll="true" primaryKey="epoch" 
primaryKeyTable="subday">
    <Join leftKey="fk_day_key" rightKey="day_key">
     <Table name="subday"/>
     <Table name="superday"/>
    </Join>
    <Level name="Year" table="superday" column="year_key" type="Numeric" 
uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
    </Level>
    <Level name="Month" table="superday" column="month_key" 
nameColumn="month_name" type="Numeric" uniqueMembers="false" 
levelType="TimeMonths" hideMemberIf="Never">
    </Level>
    <Level name="Day" table="superday" column="day_key" type="Numeric" 
uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
    </Level>
    <Level name="Hour" table="subday" column="hour" type="Numeric" 
uniqueMembers="false" levelType="TimeHours" hideMemberIf="Never">
    </Level>
    <Level name="FiveMinute" table="subday" column="five_minute" 
type="Numeric" uniqueMembers="false" levelType="TimeUndefined" 
hideMemberIf="Never">
    </Level>
   </Hierarchy>
  </Dimension>

I am using this dimension in a number of cubes, this works well.

Now I want to add a cube in which I use this dimension only in its 
montly granularity. So the facts in these cube have a value once per 
month only.

I tried like this:

  <Cube name="FactPerMonthExperiment" cache="true" enabled="true">
   <Table name="fact_per_month"/>
   <DimensionUsage source="Time" name="Time" level="Month" 
foreignKey="fk_month"/>
   <Measure name="test" column="test" aggregator="sum"/>
  </Cube>

But this results in:

Level '[Time.Default].[Year]' of cube 'Table:
   alias=subday
   relation=subday
   Columns:
   Condition:
     left=`fact_per_month`.`fk_month`
     right=`superday`.`month_key`
' is invalid: table 'superday' is not found in current scope
, star:
RolapStar:
   Table:
     alias=fact_per_month
     relation=fact_per_month
     Columns:
     Table:
       alias=subday
       relation=subday
       Columns:
       Condition:
         left=`fact_per_month`.`fk_month`
         right=`superday`.`month_key`

I am guessing it has someting to do with one of the join optimizations 
Mondrian does, but I haven't figured it out yet.


Thanks in advance and kind regards,
Jan


More information about the Mondrian mailing list