PDA

View Full Version : dimension update/lookup



MattCasters
04-14-2006, 11:49 AM
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

MattCasters
04-14-2006, 11:51 AM
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

MattCasters
04-14-2006, 11:52 AM
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

kettle_anonymous
04-14-2006, 11:53 AM
It does exist and the transform points to the right table & database. I can do sqlplus and describe the table

MattCasters
04-14-2006, 12:06 PM
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

kettle_anonymous
04-14-2006, 12:21 PM
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!!!

kettle_anonymous
04-14-2006, 12:27 PM
That raises an interesting question, what should I be using to create a SCD v/s a very basic one

MattCasters
04-14-2006, 01:19 PM
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

kettle_anonymous
04-17-2006, 11:39 AM
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

MattCasters
04-18-2006, 12:28 AM
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

kettle_anonymous
04-18-2006, 07:37 AM
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 ?

MattCasters
04-18-2006, 07:40 AM
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.

kettle_anonymous
04-18-2006, 07:53 AM
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>

kettle_anonymous
04-18-2006, 07:54 AM
I do have the technical key filed name in there.

MattCasters
04-18-2006, 08:04 AM
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

kettle_anonymous
04-18-2006, 08:06 AM
what do I do with other databases (mysql or sql server) then ?

MattCasters
04-18-2006, 08:07 AM
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

MattCasters
04-18-2006, 08:10 AM
see below

kettle_anonymous
04-18-2006, 08:29 AM
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 ?