postgresqlolapmondriansaiku

Defining time dimension in Mondrain Schema?


I am trying to define time dimension in Mondrian schema. In Mondrian time dimension it require 3 level-type must be Years, Quarters, Months.

But my table contain only one date field. So how it is possible?

Can I use postgreSQL query in Mondrian? So I can use 3 query to select Years, Quarters and Month from single date field.


Solution

  • Create simple table with only one date column:

    create table tmp_cube as (select generate_series('2011-01-01'::date, '2012-01-01'::date, '1 day')::date gs);
    

    Create dummy cube:

    <Schema name="New Schema1">
      <Cube name="Test" visible="true" cache="true" enabled="true">
        <Table name="tmp_cube" schema="public" alias="">
        </Table>
        <Dimension type="TimeDimension" visible="true" foreignKey="gs" name="Time Dimension">
          <Hierarchy name="New Hierarchy 0" visible="true" hasAll="true" primaryKey="gs">
            <View alias="test_view">
              <SQL dialect="generic">SELECT gs, extract(year from gs) as year, extract(quarter from gs) as quarter, extract(month from gs) as month FROM tmp_cube</SQL>
            </View>
          <Level name="Year" visible="true" column="year" type="Integer" internalType="int" uniqueMembers="false" levelType="TimeYears">
            </Level>
            <Level name="Quarter" visible="true" column="quarter" type="Integer" uniqueMembers="false" levelType="TimeQuarters">
            </Level>
            <Level name="Month" visible="true" column="month" type="Integer" uniqueMembers="false" levelType="TimeMonths">
            </Level>
          </Hierarchy>
        </Dimension>
        <Measure name="Count Rows" column="gs" aggregator="count" visible="true">
        </Measure>
      </Cube>
    </Schema>
    

    Now I see in Saiku:

    enter image description here