Instant Data Warehouse Support

Each issue detected by any user of the IDW are logged below. I trust this will help evaluators of the IDW avoid problems.

Logged Problem Reports

PR0001. Data Transfer Utility not loading data correctly.

Symptom: When loading a 'self describing' text file using CDWU001 it appears that some key columns are not being loaded correctly into fact tables. The key column is inserted as null rather than as the correct value.

PR0002. When I put values into the input fact table that I knew were not available in the dimension table CDWAT01 issues a message and stops rather than return a zero key.

Symptom: As described in the above line.

Answers

A0001. Data Transfer Utility not loading data correctly.

Explanation: When building fact tables the IDW uses column names starting with dk_ (for detailed key) or for Sybase IWS xk_ for some 'special keys'. When these columns are then loaded using CDWU001, CDWU001 looks for dk_ and xk_ fields and changes them to pk_. However, it was looping through the work file internal structure using the number of columns in the target table rather than the number of columns in the source file. Just the wrong variable used to control the loop. So if the number of key columns on the input file was larger than the number of columns on the target table the column name was not adjusted and the column was not loaded. This problem is only likely to occur for Sybase IWS sites because the standard for non Sybase IWS databases is that the key columns precede the data columns on a table. So it would be very rare to have more key columns in the input file than there are columns on the target fact table when loading it.

Fix: The control variable on the loop has been changed to loop through every column on the input file. The fix has not yet been published. It will be published in the next version of the IDW utilities.

A0002. CDWAT01 stopping rather than returning a zero key.

Explanation. Well spotted. I hadn't tried failed lookups as all my test data was consistent. The problem is that the lookup function is supposed to return call returned ok if the row is not found but it returns call failed instead.

Fix: The code that checks the lookup for the dimension tables has been changed to check both the call returned ok flag and the SQL code to determine if the reason for call returned ok being set to false is just a not found record. The fix has not yet been published. It will be published in the next version of the IDW.