Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Dimension Lookup/Update not working

  1. #1

    Default Dimension Lookup/Update not working

    Hello!

    I'm new to both Pentaho and this forum but a short search didn't help me so I dare to ask for help: I've got a transformation with 2 independent parts; both table inputs that go directly to a Dimension Lookup/Update object (so, 4 objects altogether). Everything is set up and should be working just fine as it looks... Unfortunately, it doesn't.

    I tried to change different settings, e.g. I now increase the technical key by choosing the "use table maximum +1" option instead of a sequence (which gave another error) but I don't get any further than this.

    It's pentaho 4.3.0 and both the input as the Dimension L/O objects connect to Oracle 11gR2.

    Here's an image of the transformation:
    Name:  pentaho.jpg
Views: 114
Size:  22.2 KB

    Finally, here's the error-logging:
    2012/08/24 14:31:43 - Spoon - Transformation opened.
    2012/08/24 14:31:43 - Spoon - Launching transformation [load DWH dimensions]...
    2012/08/24 14:31:43 - Spoon - Started the transformation execution.
    2012/08/24 14:31:43 - load DWH dimensions - Dispatching started for transformation [load DWH dimensions]
    2012/08/24 14:31:43 - Transformation metadata - Natural sort of steps executed in 0 ms (4 time previous steps calculated)
    2012/08/24 14:31:43 - Staging Input - Dimensions.0 - Finished reading query, closing connection.
    2012/08/24 14:31:43 - Staging Input - Accounts.0 - Finished reading query, closing connection.
    2012/08/24 14:31:43 - Staging Input - Dimensions.0 - Finished processing (I=85, O=0, R=0, W=85, U=0, E=0)
    2012/08/24 14:31:43 - Staging Input - Accounts.0 - Finished processing (I=186, O=0, R=0, W=186, U=0, E=0)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Because of an error this step can't continue:
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Error inserting/updating row
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : General error
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Error inserting/updating row
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : General error
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1413)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1327)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1315)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.dimInsert(DimensionLookup.java:1088)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.lookupValues(DimensionLookup.java:513)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.processRow(DimensionLookup.java:232)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at java.lang.Thread.run(Unknown Source)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Caused by: java.sql.SQLException: General error
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(Unknown Source)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1362)
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : ... 7 more
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Because of an error this step can't continue:
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Error inserting/updating row
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : General error
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Error inserting/updating row
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : General error
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1413)
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1327)
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1315)
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.dimInsert(DimensionLookup.java:1088)
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.lookupValues(DimensionLookup.java:513)
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.processRow(DimensionLookup.java:232)
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at java.lang.Thread.run(Unknown Source)
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Caused by: java.sql.SQLException: General error
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(Unknown Source)
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1362)
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : ... 7 more
    2012/08/24 14:31:43 - Dimension handling - Accounts.0 - Finished processing (I=1, O=0, R=1, W=0, U=0, E=1)
    2012/08/24 14:31:43 - load DWH dimensions - load DWH dimensions
    2012/08/24 14:31:43 - Dimension handling - Dimensions.0 - Finished processing (I=1, O=0, R=1, W=0, U=0, E=1)
    2012/08/24 14:31:43 - load DWH dimensions - load DWH dimensions
    2012/08/24 14:31:43 - Spoon - The transformation has finished!!
    2012/08/24 14:31:43 - load DWH dimensions - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Errors detected!
    2012/08/24 14:31:43 - load DWH dimensions - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Errors detected!
    2012/08/24 14:31:43 - load DWH dimensions - load DWH dimensions
    2012/08/24 14:31:43 - load DWH dimensions - load DWH dimensions

    I hope this is all the information needed, however, if something is missing I will be happy to provide it!

    All help is much appreciated!
    /Daniel

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    A "General error" from Oracle... I guess we could expect that.

    Something is hiding the real error, perhaps the ODBC driver you're using?
    Could you switch to JDBC?

  3. #3

    Default

    Hello again!

    Just wanted to let you know that it now works. I changed the connection from ODBC to JDBC and it runs like a charm.
    I have to admit it was my fault from the beginning. I have 2 different schemas in the Oracle-DB, staging and warehouse. When implementing the staging I had similar problems - first I didn't get JDBC to work, then ODBC had errors, so I put some more effort into JDBC. Getting it to work, all problems just disappeared. So now, I had the same thing, but with the other connection. Stupid me that I didn't change it to JDBC directly...

    Sorry for bothering you but thanks for your kind help!

Tags for this Thread

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.