Hitachi Vantara Pentaho Community Forums
Results 1 to 19 of 19

Thread: dimension update/lookup

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

    Default dimension update/lookup

    I get this error while loading a dimension using dimension update/lookup. Is there something wrong that I am doing ? This is my first dimension using kettle

    my table is more like create table backup_status (backup_status_key number, backup_name varchar2(64)) with backup_status_key being the primary key.


    backup_status.0 - Finished processing (I=2, O=0, R=0, W=2, U=0, E=0
    2006/04/14 14:40:51 - backup_status.0 - ERROR : Because of an error, this step can't continue:
    Error inserting 'unknown' row in dimension [BACKUP_STATUS] : SELECT count(*) FROM BACKUP_STATUS WHERE backup_status_key = 0
    ORA-01747: invalid user.table.column, table.column, or column specification

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

    Default RE: dimension update/lookup

    ORA-01747: invalid user.table.column, table.column, or column specification
    Cause: A column name was specified improperly in the current SQL statement.
    Action: Check the statement's syntax, especially references to column names, and retry the statement.

    Field backup_status_key probably doesn't exist then.

    A transformation verify would probably catch this as well.

    HTH,
    Matt

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

    Default RE: dimension update/lookup

    OK, do you know that you can just hit the SQL button and it the step will generate the SQL for you?

    All the best,
    Matt

  4. #4
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: dimension update/lookup

    It does exist and the transform points to the right table & database. I can do sqlplus and describe the table

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

    Default RE: dimension update/lookup

    Well, my experience in that regard is that you can't really argue with Oracle.

    In any case, the create table statement you show in the GP post is not a create table for a slowly changing dimension. If you refuse to let Kettle create the table for you, you should at least make sure you get all the fields in the table that the step proposes.

    All the best,
    Matt

  6. #6
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: dimension update/lookup

    Yeah, looks like kettle is trying to find all these fields that I don't have in my table and I do not want to create them. I just have technical key field defined. wouldn't that work ?

    -- Error message : No key fields are specified. Please specify the fields use as key for this dimension.

    Thanks!!!

  7. #7
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: dimension update/lookup

    That raises an interesting question, what should I be using to create a SCD v/s a very basic one

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

    Default RE: dimension update/lookup

    If you just want a Type I (always update) then use a Insert/Update step.
    Specify the key to use, the fields to update and off you go.

    If you want a Junk dimension, use Combination Lookup.

    Matt

  9. #9
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: dimension update/lookup

    Insert/Update step does not give the option of a technical key filed option and thats one of the things I would like to do

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

    Default RE: dimension update/lookup

    That doesn't make sense really. Why would you define a technical key if you already have a key?
    Maybe you need the Combination Lookup (Junk Dimension) step?

    Matt

  11. #11
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: dimension update/lookup

    I am using the combination lookup and get the following error:

    2006/04/18 10:35:47 - s_backup_status.0 - Finished processing (I=2, O=0, R=0, W=2, U=0, E=0
    2006/04/18 10:35:47 - dw - SQL with return keys: INSERT INTO backup_status( null, STATUS_NAME) VALUES (? )
    2006/04/18 10:35:47 - d_device_backup_status.0 - ERROR : Because of an error, this step can't continue:
    Unable to prepare combi insert statement :
    INSERT INTO backup_status( null, STATUS_NAME) VALUES (? )
    Unsupported feature

    looks like the parameter & values in the jdbc statement are having a mismatch... Any Ideas ?

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

    Default RE: dimension update/lookup

    Maybe you just forgot to specify the technical key field name?
    Next time, you can perhaps share the XML of the step with the rest of us?
    Trust me, it's easier that way.

  13. #13
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: dimension update/lookup

    Here is the xml:

    <?xml version="1.0" encoding="UTF-8"?>
    <transformation>
    <info>
    <name>D_device_backup_status</name>
    <directory>/core</directory>
    <log>
    <read></read>
    <write></write>
    <input></input>
    <output></output>
    <update></update>
    <connection></connection>
    <table></table>
    <use_batchid>N</use_batchid>
    <use_logfield>N</use_logfield>
    </log>
    <maxdate>
    <connection></connection>
    <table></table>
    <field></field>
    <offset>0.0</offset>
    <maxdiff>0.0</maxdiff>
    </maxdate>
    <size_rowset>350</size_rowset>
    <sleep_time_empty>1</sleep_time_empty>
    <sleep_time_full>1</sleep_time_full>
    <dependencies>
    </dependencies>
    </info>
    <notepads>
    </notepads>
    <connection>
    <name>oltp</name>
    <server>kinglouie</server>
    <type>MSSQL</type>
    <access>Native</access>
    <database>purge_citi</database>
    <port>1433</port>
    <username>cmdomain</username>
    <password>Encrypted 2be98afc86aa7f2e48e1aa269ce86a6d9</password>
    <servername></servername>
    <data_tablespace></data_tablespace>
    <index_tablespace></index_tablespace>
    <attributes>
    <attribute><code>CUSTOM_DRIVER_CLASS</code><attribute></attribute></attribute>
    <attribute><code>CUSTOM_URL</code><attribute></attribute></attribute>
    <attribute><code>SAPClient</code><attribute></attribute></attribute>
    <attribute><code>SAPLanguage</code><attribute></attribute></attribute>
    <attribute><code>SAPSystemNumber</code><attribute></attribute></attribute>
    </attributes>
    </connection>
    <connection>
    <name>dw</name>
    <server>localhost</server>
    <type>ORACLE</type>
    <access>Native</access>
    <database>myora</database>
    <port>1521</port>
    <username>dw</username>
    <password>Encrypted 2be98afc86aa7f2e4cb79ce10bef2abcd</password>
    <servername></servername>
    <data_tablespace></data_tablespace>
    <index_tablespace></index_tablespace>
    <attributes>
    <attribute><code>CUSTOM_DRIVER_CLASS</code><attribute></attribute></attribute>
    <attribute><code>CUSTOM_URL</code><attribute></attribute></attribute>
    <attribute><code>SAPClient</code><attribute></attribute></attribute>
    <attribute><code>SAPLanguage</code><attribute></attribute></attribute>
    <attribute><code>SAPSystemNumber</code><attribute></attribute></attribute>
    </attributes>
    </connection>
    <order>
    <hop> <from>s_device_backup_status</from><to>d_device_backup_status</to><enabled>Y</enabled> </hop> </order>

    <step>
    <name>d_device_backup_status</name>
    <type>CombinationLookup</type>
    <description></description>
    <distribute>Y</distribute>
    <copies>1</copies>
    <table>device_backup_status</table>
    <connection>dw</connection>
    <commit>2000</commit>
    <replace>Y</replace>
    <crc>N</crc>
    <crcfield>hashcode</crcfield>
    <fields>
    <key>
    <name>STATUS_NAME</name>
    <lookup>STATUS_NAME</lookup>
    </key>
    <return>
    <name>device_backup_status_key</name>
    <use_autoinc>Y</use_autoinc>
    </return>
    </fields>
    <sequence></sequence>
    <GUI>
    <xloc>355</xloc>
    <yloc>128</yloc>
    <draw>Y</draw>
    </GUI>
    </step>

    <step>
    <name>s_device_backup_status</name>
    <type>TableInput</type>
    <description></description>
    <distribute>Y</distribute>
    <copies>1</copies>
    <connection>dw</connection>
    <sql>SELECT STATUS_NAME FROM DEVICE_BACKUP_STATUS_VIEW
    </sql>
    <limit>0</limit>
    <lookup></lookup>
    <execute_each_row>N</execute_each_row>
    <GUI>
    <xloc>156</xloc>
    <yloc>132</yloc>
    <draw>Y</draw>
    </GUI>
    </step>

    </transformation>

  14. #14
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: dimension update/lookup

    I do have the technical key filed name in there.

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

    Default RE: dimension update/lookup

    On Oracle you need to give it a SEQUENCE name to work with.
    If you give it a name say SEQ_TABLENAME and press the SQL button, the create statement will be generated for you.

    Using sequences on Oracle is the safest way to ensure that you have unique ID&#39;s.

    Cheers,
    Matt

  16. #16
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: dimension update/lookup

    what do I do with other databases (mysql or sql server) then ?

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

    Default RE: dimension update/lookup

    Hold on, I tried it without the sequence name and it&#39;s also working.
    So I guess someone already made a fix for that problem ;-)
    Try the latest development version if you feel like it.

    Matt

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

    Default RE: dimension update/lookup

    see below

  19. #19
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: dimension update/lookup

    I got the latest one (2.3 from april 15th) and I still see the issue. Any ideas ? Clean-up cache or something like that ?

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.