Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: MySQL SCD

  1. #1

    Default MySQL SCD

    Hi,
    I have one Kimball SCD, first I use an input table step (where i write my query manually) the query extracts data from four tables doing a join among these four tables. the four tables are:

    -anagrafica: contains anagraphical data about the hunters (id, name, surname, address,....)
    -comune: contains the list of communes (its primary key is referenced as a foreign key in anagrafica table)
    - province: the list of provinces (its primary key migrates as a foreign key in commune table)
    - region: the list of regions (its primary key migrates as a foreign key in province table)

    anagrafica:43885 row
    commune: 10884 row
    province: 107 row
    region: 22 row

    The target SCD table will contain 43885 row.
    The structure of my SCD table is the following:

    create table cacciatoreDim1(
    id_cacciatoreDim int auto_increment, /*surrogate key*/
    id_cacciatore int,
    nome char(36),
    cognome char(36),
    sesso char(1),
    res_indirizzo char(70),
    desc_comune char(70),
    desc_provincia char(30),
    desc_regione char(30),
    date_from date,
    date_to date,
    version int,
    primary key (id_cacciatoreDim));

    to load this table it takes 29minutes is that normal? and how can i speed up the performances or is that ok?

    note:
    Cache size in rows=0
    Commit size =100
    I run the transformation on the local host, also the repository, db source and db target are all on MySQl on the local host.

    I use Intel P IV, 1GB RAM, 3.2 GHZ CPU.

    Thanks in advance!

    Best Regards

    Hamma

  2. #2
    Join Date
    Mar 2007
    Posts
    216

    Smile

    Hi,

    Quote Originally Posted by hammamr View Post
    (...)
    Cache size in rows=0
    Commit size =100
    (...)
    Try to increase commit size to 1000 and post a transformation screenshot because it helps.

    a+, =)
    -=Clément=-

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

    Default

    Put an index on the natural key(s) id_cacciatore I assume.
    If you hit the SQL button you see the required indexes.

  4. #4

    Default

    now i have a strange problem, when i execute my transformation it inserts data into the SCD table but it shows this error in the log:
    38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Incorrect table name 'test_scd.cacciatoreDim1'
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.Connection.execSQL(Connection.java:3256)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:870)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.Statement.createResultSetUsingServerFetch(Statement.java:520)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.Statement.executeQuery(Statement.java:1133)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.DatabaseMetaData$6.forEach(DatabaseMetaData.java:3436)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.DatabaseMetaData$IterateBlock.doForAll(DatabaseMetaData.java:76)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.mysql.jdbc.DatabaseMetaData.getIndexInfo(DatabaseMetaData.java:3419)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.core.database.Database.checkIndexExists(Database.java:2534)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookupMeta.getSQLStatements(DimensionLookupMeta.java:1541)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookupDialog.create(DimensionLookupDialog.java:1290)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookupDialog.access$600(DimensionLookupDialog.java:75)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookupDialog$6.handleEvent(DimensionLookupDialog.java:488)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:938)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3673)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3284)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookupDialog.open(DimensionLookupDialog.java:761)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.spoon.Spoon.editStep(Spoon.java:3099)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.spoon.SpoonGraph.editStep(SpoonGraph.java:2063)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.spoon.SpoonGraph.access$100(SpoonGraph.java:96)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.spoon.SpoonGraph$4.mouseDoubleClick(SpoonGraph.java:246)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:182)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:938)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3673)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3284)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.spoon.Spoon.readAndDispatch(Spoon.java:964)
    2007/10/24 14:00:38 - test_SCD - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.spoon.Spoon.main(Spoon.java:7325)
    2007/10/24 14:01:37 - Table input.0 - linenr 10000
    2007/10/24 14:02:01 - Dimension lookup/update.0 - linenr 10000
    2007/10/24 14:03:43 - Table input.0 - linenr 15000
    2007/10/24 14:04:13 - Dimension lookup/update.0 - linenr 15000
    2007/10/24 14:06:32 - Table input.0 - linenr 20000
    2007/10/24 14:07:10 - Dimension lookup/update.0 - linenr 20000


    Also when i click on the SQL button to create an index on the natural key a message saying "an error occured: unable to determine if indexes exists on table [test_scd.cacciatoredim1] incorrect table name 'test_scd.cacciatoredim1' " so what's the problem ? it seems that pan cannot see my table! but at the same time the data is inserted in it, that's strange!!!

    Thanks in advance!
    hamma

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

    Default

    Did you really name your table "test_scd.cacciatoreDim1" or did you mean to call it table "cacciatoreDim1" in schema "test_scd"?

  6. #6

    Default

    my table name is just "cacciatoreDim1" i never used schemaName.TableName for table names

    Thanks in advance!

  7. #7

    Default

    Ah ok, I think i found the problem, I shouldn't fill the target schema text field in the SCD output step properties, because i don't use composed names. I think that's the pb isn't it?

    Thanks!
    Last edited by hammamr; 10-24-2007 at 11:23 AM.

  8. #8

    Default

    I created the index on the natural key and now it takes just 5.08 minutes to finish the transformation which represents 1/6 of the previous execution time (important results!!!)

    Note: modifying the commit size to 1000 doesn't speed up the performances (at least in my case: the execution time remains the same)

    Thanks alot!
    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.