Hitachi Vantara Pentaho Community Forums
Results 1 to 11 of 11

Thread: Unable to retrieve value of auto-generated technical key

  1. #1

    Default Unable to retrieve value of auto-generated technical key

    Hi everyone,
    I'm evaluating Kettle 2.5.0, i tried to use the step to update a slowly changing dimension (Dimension lookup/update) but i couldn't succeed. It shows an error (in the bottom). I saw a previous replay from Matt to a problem similar to this one but in that situation the target database is DB2 but for me it's mysql. Matt said maybe the database driver version doesn't match with the database version. I deleted the embedded mysql driver from the directory "D:\pentaho\Kettle-2.5.0\libext\JDBC" and i replaced it by "mysql-connector-java-5.0.7-bin.jar" but i still have the same problem.

    did any one have this problem and succeeded after that and how?

    thanks in advance!

    hamma

    The error is the following:

    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.core.database.Database.dimInsert (Database.java:1277)
    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : be.ibridge.kettle.core.exception.KettleDatabaseException:
    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : Unable to retrieve value of auto-generated technical key : unexpected error:
    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.run (DimensionLookup.java:747)
    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.processRow (DimensionLookup.java:658)
    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.lookupValues (DimensionLookup.java:294)
    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.core.database.Database.dimInsert (Database.java:1277)
    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) :
    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.core.database.Database.dimInsert(Database.java:1288)
    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.lookupValues(DimensionLookup.java:294)
    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.processRow(DimensionLookup.java:658)
    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.run(DimensionLookup.java:747)
    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : Caused by: java.lang.NullPointerException
    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.core.database.Database.dimInsert(Database.java:1277)
    2007/09/20 15:40:51 - Dimension lookup/update.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : ... 3 more
    2007/09/20 15:40:51 - Caccia_source_connection - Statement canceled!
    2007/09/20 15:40:51 - Caccia_source_connection - Statement canceled!
    2007/09/20 15:40:51 - Dimension lookup/update.0 - Signaling 'output done' to 0 output rowsets.
    2007/09/20 15:40:52 - mysql_caccia3 - Connection to database closed!
    2007/09/20 15:40:52 - Dimension lookup/update.0 - Finished processing (I=1, O=0, R=1, W=0, U=0, E=1)
    2007/09/20 15:40:52 - Spoon - The transformation has finished!!




  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    You probably selected the wrong type for "technical key".

    Regards,
    Sven

  3. #3

    Default

    The type i'm using is "int", the DBMS is MySQL and i selected auto increment to be the way my technical key increases. Is that wrong ??

    Thanks in advance

    Best Regards
    hamma

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    Is your technical key autoincremented in the database, else it will not work.

    Regards,
    Sven

  5. #5

    Default

    Ah ok i thought that if u select autoincrement from the graphical interface of spoon that's enough. so i have to do my technical key autoincremented also in the database. I will do it on monday and i think it will work.

    Thanks alot
    hamma

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    Table maximum + 1 will work on any database. Sequences if they are supported (E.g. in Oracle). Auto incremented fields for e.g. MySQL and DB2 (but the latter assumes your field is in fact auto incremented in the database).

    All 3 do in theory the same, just the place where they get their numbers from are different.

    Regards,
    Sven

  7. #7

    Default

    I changed in my database the technical key to be autoincremented and it works well, except that there is a little problem: each time i update my dimension the technical key doesn't start with 1 but it starts with the last maximum value + 1, except for the first line which is strange (the first line technical key is 1 but the other fields are equal to null) I think it's a problem from mysql. So how can i change my dimension table structure to make the technical key starts with 1.

    thanks in advance

    Best Regards
    hamma

  8. #8
    Join Date
    May 2006
    Posts
    4,882

    Default

    If you have auto incrementation and there are rows in your table, it's to fair to assume the number will not start at 1.

    For the extra line with a number of 1, this is the default row to be taken when errors occur (and no you can't disable it). There's a check being done for a certain low key number (either 0 or 1 depending on database) and if that's not in there it will be added. You can change the null values to anything you like, like 'N/A', or something like that.

    Regards,
    Sven

  9. #9

    Default

    Thanks a lot for help, now my transformation is working well. But i didn't understand the utility of the first row that contains all fields null except the surrogate key at 1.

    Best regards
    hamma

  10. #10
    Join Date
    May 2006
    Posts
    4,882

    Default

    It's from Kimball theory It's the value to be used when you have no matching dimension record for a certain fact row. This avoids having NULL values.

    Regards.
    Sven

  11. #11

    Default

    Ah ok thanks a lot now i have no doubt it's from kimball that's ok.

    Best regards
    hamma

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.