SmartUpdate

Open the SmartUpdate window in the Tools menu.

SmartUpdate will enable you to copy data from any source to a local target table. To do this several steps must be taken, depending on your situation.

There are several options that can be configured in this window.

• You can create the SmartUpdate table and procedure. This can be done in any database, so you can choose which database will be your data warehouse.
• You can create a local view on a remote table in a different database.
• You can add table copy and compare configurations to the SmartUpdate table.
• You can create the SmartUpdate command to execute the SmartUpdate procedure
• You can schedule the SmartUpdate command for automatic execution.

These steps will all be explained in the following sections.

SmartUpdate requires a table to store its configuration. This table can be created in any database that you have a connection to. Select the proper connection and click on “Create SmartUpdate Table” to create the table.

Next click on “Create SmartUpdate Procedure” to create the procedure on the same connection.

If the table or procedure already exists an error will occur. The existing objects will not be replaced. If you need to update these objects, remove them and create new ones.

Select the source for your data. This is the tricky part of the setup. You can only select a table or view from the database where your connection points to. To include data from a different database, you need to create a view to that other database first. If this other database is on a different database server, you need to create a Linked Server connection to that server first. This can be done with the Linked Server Configurator in Sequenchel.

Creating a view is relatively easy. 

• If your database is on a different server, select the correct Linked Server.
• If your linked server includes a target database name, do not enter the name of that database in SmartView. If the name is not included enter the name at “Source Database”.
• If the database is on the local server do not enter a Linked Server but only enter the name of the database
• Enter the name of the source schema for the table and the source table name.
• If you do not enter a target schema name, the name of the source schema will be used for the target.
• If you do not enter a target view name, the name will be the source table (or view) name with “vw_” appended at the beginning.
• Select the database source system: MS SQL Server; Oracle; Progress (SQL92) or MySQL. If you wish to connect to a different system select the one that uses the same method for selecting the “TOP n rows” from a table. If you don’t know which one, simply try any option until there is no error and the bottom screen is populated.

After entering the required data click on “Create Local View”.

If the creation of the local view is succesful, the name of the local view will be entered in “Source Table or View” inputbox. The name of the original source table will be used for “Target Table” and the table structures of both will be automatically imported.

After you have selected a source table or view, you can select a target table. You cannot use a view for this because the target needs to store the data from the source and a view usually doesn’t store data.

If the target table does not yet exist, SmartUpdate can create it for you. If you want the table to be automatically created, simply enter the desired name in the Target Table textbox.

After entering the Source and Target table names, click “import Table Structure(s) to display the result of your selections.

Creating a Target Table

If you have typed a name for the Target table, only the source table structure is retrieved. SmartUpdate does not know which columns you wish to copy or compare, so all copy and compare checkboxes are unchecked.

The checkmark(s) in the PK (Primary Key) column indicate this field or these fields will be used for identifiing which record of data we are dealing with. Every row or record in a database table must be identified so it can be matched and compared to a row or record in the target table. The Primary Key is what uniquely identifies every record. Without a primary key, data from source and target cannot be compared. SmartUpdate needs a Primary Key unless you use the “ClearTargetTable” switch. If you do not specify a Primary Key, SmartUpdate will try to find the Primary Key from the source and/or target table.

The checkmarks in the “Copy” column indicate that that column needs to be copied from source to target. When selected, data form this column will always be copied from source to target, even if the datatypes do not match. If the datatypes do not match SmartUpdate will try to convert the data.

The checkmarks in the “Com” (Compare) column indicate that SmartUpdate will check for differences in this field. If the contents of this field has changed, all copy fields will be updated. The old data will be logged in the audit table and the new data will be entered into the target table. If there is no checkmark for a field, the data for that field will not be monitored and if only the data for non-monitored fields changes, no action is taken and the datawarehouse is not updated. This can be useful if you get the same data every day, except for a timestamp field that always changes. You may not want to monitor all these timestamp changes.

If you use a target table that does not exist and import columns, you will see that the checkmark “Create Target Table if not exist” on the right side of the window has activated. You need this checkmark to be activated if the target table does not yet exist.

Selecting the Wrong Source or Target Table

If you select a source and target table, but they do not match very well, you will see the following result.

SmartUpdate tries to match the source and target table based on field(column)name and datatype. By default, both must match for SmartUpdate to be able to work with it. If the columns that match have no primary key, SmartUpdate will be unable to match the source to the target data. 

First, the columns that match for name and datatype are displayed. Next, the columns that match for name are displayed, but they are unselected for copy or comparison. Third, the remaining columns from the source are displayed followed by the remaining columns form the target. In any subsection, the Primary Key column is always displayed first, followed by the rest in alfabetical order.

If you select a column to be copied, but there is no column with the same name in the target, SmartUpdate produces an error and quits.

Selecting a Correct Existing Target

If you select an existing target table that is a good match for you source table, youwill be able to compare all columns or make your own selection. It is possible that your source or target does not have a primary key defined. You only need a primary key in one of them. If both do not have a primary key defined, you will have to define it yourself by putting in the correct checkmarks or selecting the option “Clear Target Table”.

You can choose to save the configuration for the source or the target table. This can be useful if you want to perform multiple steps on your data. If you have a source, staging and target table in your data warehouse and the data goes from source to staging and from staging to target, you only need to save the configuration for the staging table. SmartUpdate first checks to see if there is configuarion for the source tabel. if there is none, it will check for configuarion for the target table.The target table in step 1 of a 2-step process will be your staging table and that configuarion will be used.

If the target table does not yet exist, but you wish to save configuration for it anyway, configure all settings for the source table and select the option “Use Source Table Configuration for Target Table”.

If no configuarion is defined for source and target, SmartUpdate will use all columns that match for name and datatype for comparison. If no primary key is defined for the source or target, SmartUpdate will look for a primary key on the source table. If that does not exist, it will look for an identity field on the source tabel and if that does not exist it will look for a primary key on the target table. If no primary key can be found, SmartUpdate will quit, unless you spoecifi the option “Clear Target Table”

This means that if you provide no configuration whatsoever, SmartUpdate will use the exisintg primary key from the tables selected and copy and compare all columns that match for name and datatype. As long as a primary key exists, or the target table is cleared, SmartUpdate will work.
You can also save a primary key for source or target without specifying any columns for copy or compare.

The Start Date and End Date enable you to have different configurations for different time periods. If the company rules change (e.g.) per january 1, you can prepare yourself by adding an extra configuarion.

SmartUpdate Command

The SmartUpdate command is what needs to be executed to make it all work.To define this command you need to select the source and target and put the corect checkmarks.

• Create Target Table: If the table does not yet exist, it will be created. It will have the same columns as the source with an extra column SU_LogDate if “Use Auditing” is selected.
• Use Auditing: Changes will be logged to the audit table. The audit table has the same name and columns as the target table with 2 extra columns: SU_AuditLogDate and SU_AuditAction. Audit Action is whether the source data was new(insert), changed (update) or removed (delete).
• Create Audit Table: If the audit table does not yet exist, it will be created. If you enable auditing, but the table does not exist and this option is unselected, auditing will be disabled.
• Remove NON-source data: If data is removed from the source it will be removed from the target as well. This option should be unselected for incremental updates. If auditing is enabled all deletes are logged to the audit table so the data is not completely removed. It will be available in historical overviews.
• Use Target Collation: The source and target database may not have identical sorting and character sets. This will complicate comparisons between different sources. This option converts all character data to a single target collation so data from different sources can be compared. Use the most common source collation when creating your data warehouse database.
• Clear Target Table: This option removes ALL data from the target table before copying new (all) data from the source table. When using this option a Primary Key is not required because no data needs to be compared. Auditing is disabled because all data would be logged multiple times.
• Use All Columns: All columns from the configuration or the source table will be used for copying data, even if the data types do not match. SmartUpdate will try to convert all data to the target data type. All columns that match for name and datatype will be used for comparison. If a column in the source does not have a matching column in the target by name, an error will be produced and SmartUpdate fails.
• Equalize text data types: SmartUpdate will consider all text data types (char, nvarchar, etc) to be equal and will try to convert all text data to the target data type. 

After you have made all selections, the SmartUpdate command is displayed in the textbox on the bottom of the window. If you have created a SmartUpdate schedule using the Sequenchel Scheduler, you can add a jobstep to this schedule by pressing “Add to SmartUpdate Schedule”. You can copy this command and schedule it manually if you wish or you can execute it immediately against your current connection.