Revise an SXV4 - SuperCHANNEL

The Revise function allows you to update selected fact table records in your existing SXV4 database.

For example, if your SXV4 contains details of your customers throughout Australia, but you have some updated customer data for all of your customers in the states of Victoria and New South Wales, then you can use the revise function to replace all the existing records for those customers with the new data. Alternatively, you might want to update the records for all your male customers with new data pertaining to those customers. You can use the revise function to do this.

Overview of the Revise Process

To initiate the Revise function, you select a column in the fact table that you want to update and click Revise. The column you select is used to determine which records to update in the target, so it is important to prepare your updated source data before running the procedure.

When you click Revise, SuperCHANNEL will:

1. Find all the values for the selected column in your updated source data.
2. Delete any records in the existing target database where the value of the selected column matches one of those values from the updated source.
3. Insert all of the records in the source data into the target database.

For example:

1. You have prepared updated source data containing only records for male customers:

   

2. You select the Gender column and click Revise:

   

3. SuperCHANNEL deletes all records in the existing SXV4 where the Gender is M:

   TEXT

   --Committing changes to target database
   --Started deleting from "F_Customer"...
   -- deleted rows successfully from "F_Customer"
   --      0hrs 0min 0sec

4. SuperCHANNEL inserts the new source data into the target database:

   TEXT

   -- channelled 235543 rows (out of 235543) successfully to "F_Customer"
   --      0hrs 0min 0sec
   --Commiting changes to target database
   --0hrs 0min 1sec
   0 cleansing action(s)
   
   Build complete

The deleted records are not physically deleted (they are only marked as deleted). It is possible for the revised target database file to be larger than the original one, even if it contains fewer records.

Preparing the Source Data for Revision

Because SuperCHANNEL uses the new source data to determine which records to delete and replace, it is important to prepare your source data before starting the revision.

Your new source data must contain only the set of records that you want to revise. You could do this by creating a new database or by using a view.

For example, if you are updating the records for your customers in New South Wales and Victoria, then you source needs to contain:

• All records relating to customers in New South Wales and Victoria that you want to be in the target database after the revision is complete; but
• Only records relating to customers in New South Wales and Victoria. If your source data included a single record for one of your customers in South Australia, then SuperCHANNEL would delete all the records in the target for your South Australian customers too, replacing these with the single record from your updated source data.

If the SXV4 you are revising was created prior to version 8.0 of SuperCHANNEL, and it contains non English/ASCII characters, then you may need to change the system locale in order to update it. See Unicode - SuperSTAR for more information. You may want to consider rebuilding the SXV4 from scratch instead as this will avoid the need to set the system locale when using it with SuperSERVER.

Using Revise and Update Together

Depending on your updated data, it may be necessary to run both Revise and Update to fully update your SXV4.

For example, suppose you are revising records for your customers based in New South Wales and Victoria, but your new source data includes a record for customer who previously lived in South Australia and now lives in Victoria.

During the initial stage of the revise process, SuperCHANNEL will delete the existing records for customers in New South Wales and Victoria, but will not delete the existing record for the customer who has moved (as this existing record will be for a South Australian customer).

When SuperCHANNEL inserts the new data, it will skip the record from the new source data that relates to the moved customer (assuming the record has a primary key identifier that is the same as the existing record for this customer).

After the revise process completes, you will need to run an update on the fact table to update the existing record for this customer to change the customer's address from South Australia to Victoria.

In some cases it may be safer to rebuild the SXV4 from scratch.

In addition to revising an SXV4 using the SuperCHANNEL GUI, you can also revise the database using SNU on the command line. See Command Line Options - SNU for more details.