Problem with TimeDimension on Mondrian 3

Help
JimmyVan
2009-05-14
2013-04-29
  • JimmyVan

    JimmyVan - 2009-05-14

    I am using Mondrian, and I’d tried to define a TimeDimension as below.

    -------------------------------------------------------

    <Dimension type="TimeDimension" foreignKey="ID" name="lastupdate" caption="lastupdate">

    <Hierarchy name="lastupdate" hasAll="true" caption="lastupdate">

    <Table name="V_REPORT_CFG_COMPUTER" schema="SM7TEST">

    </Table>

    <Level name="lastupdate_year" column="LAST_UPDATE" type="Date" uniqueMembers="false" levelType="TimeYears" hideMemberIf="Never" caption="year">

    </Level>

    <Level name="lastupdate_month" column="LAST_UPDATE" type=" Date" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never" caption="Month">

    </Level>

    <Level name="lastupdate_day" column="LAST_UPDATE" type=" Date" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">

    </Level>

    </Hierarchy>

    </Dimension>

    -------------------------------------------------------

    The column LAST_UPDATE is a Date type in Oracle.

    I expect that the hierarchy will be 3 levels:

    2009

      -5

        -14

    But when I execute the following MDX:

    select {[Measures].[CICOUNT]} ON COLUMNS,

    [lastupdate].[All lastupdates].Children ON ROWS

    from [cubetest2]

    I got “2009-5-14” at the first level, and can’t drill down on it.

    Am I wrong in define the cube? I can't define the column as Date?

     
    • Alexander

      Alexander - 2009-05-14

      Hello, I have seen your example and I can see you are using the same Column LAST_UPDATE on all the levels.

      as you cann see on the documentation you should have a separated column so mondrian can group YEARS, MONTHS, and the one you have that is DAY
      please see http://mondrian.pentaho.org/documentation/schema.php#Time_dimensions

      an alternative idea if you do not what to create all de difetent columns in your table (it is recomended for index performance)
      you can follow the following idea

      <Dimension name="Time" foreignKey="customer_id">
        <Hierarchy hasAll="true" primaryKey="customer_id">
          <Table name="customer" />
          <Level name="Gender" column="gender" uniqueMembers="true">
            <KeyExpression>
              <SQL dialect="generic">customer.gender</SQL>
            </KeyExpression>
          </Level>
        </Hierarchy>
      </Dimension>

      <Dimension type="TimeDimension" foreignKey="ID" name="lastupdate" caption="lastupdate"> 
      <Hierarchy name="lastupdate" hasAll="true" caption="lastupdate"> 
          <Table name="V_REPORT_CFG_COMPUTER" schema="SM7TEST"> 
          </Table> 
          <Level name="lastupdate_year"   uniqueMembers="false" levelType="TimeYears" hideMemberIf="Never" caption="year">
             <KeyExpression>
              <SQL dialect="generic">to_char(LAST_UPDATE,'YYYY')</SQL>
            </KeyExpression>
          </Level> 

          <Level name="lastupdate_month"   uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never"  caption="Month"> 
             <KeyExpression>
              <SQL dialect="generic">to_char(LAST_UPDATE,'YYYY-MM')</SQL>
            </KeyExpression>
          </Level> 
          <Level name="lastupdate_day" column="LAST_UPDATE" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never"> 
          </Level> 
      </Hierarchy>
      </Dimension>

      hope this helps you

       
      • JimmyVan

        JimmyVan - 2009-05-15

        Thanks a lot! I am gonna have a try.

         

Log in to post a comment.