Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Kettle Trancate with Oracle

  1. #1

    Default Kettle Trancate with Oracle

    Hi everyone,
    I was using MySQL as a target database(data warehouse) and now i'm using Oracle 9.2. In mysql when i'm updating my dimensions i activate the option "trancate" in the output table step, the dimensions contains primary keys which are referenced as foreign keys in the fact table. In mysql it works well there is no problem but in oracle Kitchen displays an error message saying that it's impossible to truncate the dimensions because their keys are referenced by the fact table as foreign keys.
    In mysql also sometimes i had some problems "deadlock found when trying to..." but i added a new job entry (SQL script) which deletes from the fact table before updating the dimensions and it worked well.
    Note: i used on delete cascade during the creation of the fact table, so i thought that it will be possible to truncate the dimensions and that will be cascaded to the fact table.

    So what is the solution?

    Thanks in advance!

    Best Regards
    hamma

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

    Default

    It's an Oracle "problem", not a Kettle one. Drop referenteriel integrity on your oracle tables?... They are usually not used in data warehousing.

    Regards,
    Sven

  3. #3

    Default

    Thanks for the quick reply. what do you mean with they are not usually used in data warehousing?. Do u mean that i don't need to create foreign keys (no integrity constraints? ) in my data warehouse ?

    thanks in advance!
    hamma

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

    Default

    In most datawarehouses I know there is referentiel integrity but almost never explicitly enforced (the way you process/load data should ensure you have integrity), on huge tables it's just not doable.

    In Oracle you do have those nice non-enforced referentiel integrity constraints.

    In any case for your original problem... you can't truncate a table if you still enforce referentiel integrity on it. And I have no idea why you would want to delete from a fact table before inserting.

    Regards,
    Sven

  5. #5

    Default

    Thanks alot. i'm new to datawarehousing, i'm asked to evaluate kettle, i designed one star schema with measures and dimensions. my dimensions are at high level (commune, province, year and stuff like that) so i don't have kimball slowly changing dimensions neither junk dimensions. I designed a simple model to start.
    - to update my dimensions i extract and transform data (agregation, calculation, distinct, sort, ....) and then i truncate my dimension table inserting the new data
    - once the dimensions have been filled in i fill the fact table Extracting, transforming the data and doing a join with the dimension tables in order to have the fact key (union of dimensions keys: integers auto_incremented), before to fill the fact table i truncate it also.

    So what do u think of this process? and what can i use instead of output table step? is it possible to use update table step instead of it?

    Thanks in advance!
    hamma

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

    Default

    Fun for a toy example, but in reality no-one is truncating his dimensions/fact tables before loading Insert/update you can also use... but your problem remains... you can't truncate a table when you're still enforcing integrity on some of its rows.

    Regards,
    Sven

  7. #7

    Default

    now I use Insert/update step and it's working well (i don't need to truncate my tables)

    Thanks for the help!
    Last edited by hammamr; 10-16-2007 at 11:18 AM.

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.