Aggregating by Day of Week in Mondrian

Question

I am trying to extend the Pentaho example of a simple TimeDimension to aggregate by Day Of Week

This is the example provided:

<Dimension name="Time" type="TimeDimension">
  <Hierarchy hasAll="true" allMemberName="All Periods" primaryKey="dateid">
    <Table name="datehierarchy"/>
     <Level name="Year" column="year" uniqueMembers="true" levelType="TimeYears" type="Numeric"/>
     <Level name="Quarter" column="quarter" uniqueMembers="false" levelType="TimeQuarters"/>
     <Level name="Month" column="month" uniqueMembers="false" ordinalColumn="month" nameColumn="month_name" levelType="TimeMonths" type="Numeric"/>
     <Level name="Week" column="week_in_month" uniqueMembers="false" levelType="TimeWeeks"/>
     <Level name="Day" column="day_in_month" uniqueMembers="false" ordinalColumn="day_in_month" nameColumn="day_name" levelType="TimeDays" type="Numeric"/>
  </Hierarchy>
</Dimension>

(Reference here)

My modified Dimension looks like this:

<Dimension name="Time" type="TimeDimension">
  <Hierarchy hasAll="true" allMemberName="All Periods" primaryKey="date_id">
    <Table name="dates"/>
    <Level name="Year" column="year" uniqueMembers="true" levelType="TimeYears" type="Numeric"/>
    <Level name="Month" column="month" uniqueMembers="false" ordinalColumn="month" nameColumn="month" levelType="TimeMonths" type="Numeric"/>
    <Level name="Week" column="week" uniqueMembers="false" levelType="TimeWeeks"/>
    <Level name="Day" column="day" uniqueMembers="true" ordinalColumn="day_in_week" nameColumn="day_of_week" levelType="TimeDays" type="Numeric"/>
  </Hierarchy>
</Dimension>

I am running up against a challenge whereby Saiku (using as front end to the Mondrian Cube) insists that my Day column should always be based on the hierarchy of Year-Month-Week which is obviously fine for drill down but not if I just want aggregates by day (e.g. Average sales on a Monday versus a Tuesday). I've tried adding another dimension but no matter what I do I end up with N * day name records where N is the number of records I have (ideally I'd have 7 rows returned based on the relevant Measure)

What does the column day contain? An integer of 1 to 7? In that case, you can't set uniqueMembers to true because the keys are repeated across each week. — Luc
@Luc day was actually keyed as an integer for day of month, so it was throwing out the day_in_week ordinal and day_of_week name column. However, changing the column attribute of the level to the day_in_week ordinal makes sense, but still duplicates all my days - I think because of the hierarchy! — kez

Tomas Greif Tomas Greif · Accepted Answer · 2014-06-26T18:54:59

Did you try separate dimension with single level?

<Dimension name="Time" type="TimeDimension">
  <Hierarchy hasAll="true" allMemberName="All Periods" primaryKey="date_id">
    <Table name="dates"/>
    <Level name="Day" column="day" uniqueMembers="true" nameColumn="day_of_week" levelType="TimeDays" type="Numeric"/>
  </Hierarchy>
</Dimension>

I think this could be solved with Mondrian4 and Saiku3, but currently I don't think there are other options.

Aggregating by Day of Week in Mondrian

1 Answers