Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Oracle timestamp

  1. #1
    Join Date
    Apr 2007
    Posts
    21

    Post Oracle timestamp

    Hi,

    I want to see oracle timestamps in a pivot table.
    I tried

    <Dimension name="TRANSACTION_TIMESTAMP" type="TimeDimension">
    <Hierarchy hasAll="true" allMemberName="All timestamp">
    <Level name="TRANSACTION_TIMESTAMP" uniqueMembers="false" levelType="TimeYears" type="Numeric">
    <KeyExpression>
    <SQL dialect="oracle">to_number(to_char(TRANSACTION_TIMESTAMP,'YYYY'))</SQL>
    </KeyExpression>
    </Level>
    <Level name="TRANSACTION_TIMESTAMP" uniqueMembers="false" levelType="TimeMonths" type="Numeric">
    <KeyExpression>
    <SQL dialect="oracle">to_number(to_char(TRANSACTION_TIMESTAMP,'MM'))</SQL>
    </KeyExpression>
    </Level>
    <Level name="TRANSACTION_TIMESTAMP" uniqueMembers="false" levelType="TimeDays" type="Numeric">
    <KeyExpression>
    <SQL dialect="oracle">to_number(to_char(TRANSACTION_TIMESTAMP,'DD'))</SQL>
    </KeyExpression>
    </Level>
    </Hierarchy>
    </Dimension>

    but then I get java.sql.SQLException: ORA-01481: Ungültiges Modell für Zahlenformat" means invalid model for number format

    I also tried
    <Dimension name="TRANSACTION_TIMESTAMP">
    <Hierarchy hasAll="true" allMemberName="All timestamp">
    <Level name="TRANSACTION_TIMESTAMP" uniqueMembers="false" type="String">
    <KeyExpression>
    <SQL dialect="oracle">to_char(TRANSACTION_TIMESTAMP,'dd.mm.yyyy hh24:mi:ss')</SQL>
    </KeyExpression>
    </Level>
    </Hierarchy>
    </Dimension>

    but then I get "ungültige Zahl" means invalid number

    Can someone help me?

    Thanks!
    Jan

  2. #2
    Join Date
    Apr 2007
    Posts
    21

    Post views...

    I found hints in other threads:

    - I could use views like described in
    http://forums.pentaho.org/showthread.php?t=47764

    - I read something about an "extensible mechanism for writing out date, time, and timestamp literals. So, in 2.3 this may work out-of-the-box".
    But it's not clear to me what version of Mondrian is in Pentaho 1.6 and how it would work.
    http://forums.pentaho.org/showthread.php?t=51365

    I also found interesting threads about hours, minutes, seconds. Why they are not in TimeDimension...
    http://forums.pentaho.org/showthread.php?t=47826
    http://forums.pentaho.org/showthread.php?t=26110

    I'll use the views, but if someone knows another solution or why I got the exceptions, please let me know.

    Thanks!
    Jan
    Last edited by mec_muc; 11-14-2007 at 06:37 PM.

  3. #3
    Join Date
    Nov 1999
    Posts
    1,618

    Default

    My hunch is the easiest way to get timestamp values out of oracle and into mondrian is as numbers. If you can convert to milliseconds since the epoch (Jan 1 1970) then they will be compatible with java.util.Date values.

    Try (timestamp_value - '1970-1-1') * 24 * 60 * 60 * 1000

    I haven't tried it, but you get the general idea.

    Julian

  4. #4
    Join Date
    Mar 2009
    Posts
    1

    Default format

    use format attribute of the level node.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.