Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Dimension lookup/update and the generated _ktl columns

  1. #1

    Default Dimension lookup/update and the generated _ktl columns

    Hello to all:

    I am a new user of Spoon.

    I have existing columns which qualify for the "technical key field", "version field", "date range start field", and "table date range end". Why does Spoon insist on generating new ones? How should I force Spoon to use the existing columns?

    What is the correct term for these generated fields ending in _ktl? I had difficulty finding information on the Internet and in the documentation regarding these columns.

    ALTER TABLE CONTACT_DIM ADD ( contact_key_KTL NUMBER(10) ) ;
    UPDATE CONTACT_DIM SET contact_key_KTL=contact_key;
    ALTER TABLE CONTACT_DIM DROP ( contact_key )
    ;
    ALTER TABLE CONTACT_DIM ADD ( contact_key NUMBER(10) ) ;
    UPDATE CONTACT_DIM SET contact_key=contact_key_KTL;
    ALTER TABLE CONTACT_DIM DROP ( contact_key_KTL )
    ;
    ALTER TABLE CONTACT_DIM ADD ( EFFECTIVE_DATE_KEY_KTL NUMBER(5) ) ;
    UPDATE CONTACT_DIM SET EFFECTIVE_DATE_KEY_KTL=EFFECTIVE_DATE_KEY;
    ALTER TABLE CONTACT_DIM DROP ( EFFECTIVE_DATE_KEY )
    ;
    ALTER TABLE CONTACT_DIM ADD ( EFFECTIVE_DATE_KEY NUMBER(5) ) ;
    UPDATE CONTACT_DIM SET EFFECTIVE_DATE_KEY=EFFECTIVE_DATE_KEY_KTL;
    ALTER TABLE CONTACT_DIM DROP ( EFFECTIVE_DATE_KEY_KTL )
    ;
    CREATE INDEX idx_CONTACT_DIM_lookup
    ON CONTACT_DIM
    (
    CONTACT_ID
    )
    ;
    Attached Images Attached Images   

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

    Cool _ktl

    The _KTL fields are being created TEMPORARILY to allow for the data type change.
    On Oracle that is unfortunately the safest way to modify the data type.

    A SQL wizard such as yourself surely can see that the column gets dropped again in the script.

    Also, the SQL proposed is just a suggestion. I surely hope you're not blindly executing any and every SQL just for the fun of it?

    Finally, the data type you are using is probably different as the input for the fields Spoon is trying to change. Look closely if you didn't make a mistake and that the target lengths and precisions of the fields are sufficient to store the source data in.

  3. #3

    Default

    Hello Matt and thank you for the response...

    So Spoon is insisting that it wants a NUMBER datatype with a specific precision, such as NUMBER(10), and not a generic NUMBER datatype, without precision.

    Why does Spoon propose a datatype, NUMBER(10), that supports fewer than the maximum number of digits supported by NUMBER?

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

    Default

    Because it's a lot cleaner that way.
    NUMBER itself has a 38 digit precision forcing software to use BigDecimal (arbitrary precision) data types to read back.
    That is not required at all.

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.