More information

Utilities

Site Specific Notes

Data Sync Utility

The Data Sync Utility is a maintenance utility that should be run to ensure that data rows which use value-based (super) lookup table entries have the Value and Code information correctly in sync with the current data in the lookup table, or to update the Group field in Relationship data rows with the current data in the Relationship Type lookup table. The utility must be run following lookup maintenance that changes the Value that you want to associate with an existing Code in those tables, or an update to Relationship Type Groups. Making such a change in the lookup table does not automatically trigger the associated changes in the data rows which use that entry. Those changes must be made by running this utility.

In addition, the utility may be used to update the security settings for specific Report Groups (a specialized lookup table used to provide security and organization within reporting) in existing rows in the report_descripts table. When those rows are created for a set of report criteria or the html output of the run of a report, a Report Group is identified and the Maintenance and Access settings for that Report Group are also copied into the row. If you subsequently change the Maintenance or Access settings for the Report Group, those changes are not automatically reflected in the existing rows in report_descripts. The Data Sync utility will update the rows in report_descripts in accordance with the current settings in the Report Groups lookup table.

Background

Most data fields that are driven by a lookup table store the Code field from the lookup table entry (only). When that data row is displayed, the system retrieves the current Value from the lookup table to use for display, based on the Code that is in that data row. And when those lookup tables are used in data maintenance, all entries are shown in a pull-down list. This requires the system to cache the entries for the lookup tables.

Certain lookup tables often have an extremely large number of entries - to the degree that caching those tables has a negative impact on system performance. Millennium treats that set of lookup tables as 'value-based' lookup tables. When one of those lookup tables is used for a field in another data row, BOTH the Code and Value are stored in the data row. This minimizes the need for the system to cache and for the interface to attempt to display all of the entries in maintenance forms. However, when you make edits to the Values associated with these lookup table Codes, the system does not automatically make those changes in the data rows which are already using the entry.

Although not a value-based (super) lookup table, a similar situation occurs with the Relationship Type lookup table. The Relationship data row stores both the Code and the Group from the lookup table entry for the Relationship Type. If you edit the Group via lookup table maintenance, that change is not automatically made in the data rows.

The Data Sync utility addresses these situations.

Utility Behavior

This utility may be run for one of the value-based or other specified lookup tables at a time. The edits that the utility will perform vary slightly, based on the nature of the lookup table. The chart below shows the lookup tables that may be selected, the field that is used as a basis of comparison to the lookup table, and the data field that will be edited (if necessary) to match the current data in the lookup table. Note that the utility will not change records if those records were inserted after edits were made to the value based lookup tables.

Lookup table Table with Code Data => Lookup fields to be compared Data field to be changed Edit based on
Attribute Types attribute attrtype => table_code

attrtypenm

attrgroup

table_val

table_type

Constituent Types corebio coreconst => table_code corecfae table_cfae
Chart of Accounts gifts giftrest => chart_code

giftacctno

giftacctid

giftacctgp

giftacdprt

giftacctnt

giftacctgl

giftacctcf

giftacctdv

giftacctnm

chart_acct

chart_dept

chart_grp

chart_dprt

chart_ntfy

chart_goal

chart_cfae

chart_div

chart_val

Chart of Accounts dues duesrest=>chart_code

duesacctno

duesacctid

duesacctgp

duesacdprt

duesacctnt

duesacctgl

duesacctcf

duesacctdv

duesacctnm

chart_acct

chart_dept

chart_grp

chart_dprt

chart_ntfy

chart_goal

chart_cfae

chart_div

chart_val

Chart of Accounts corebio corerest => chart_code coreacctnm chart_val
Chart of Accounts plannedgifts planrest => chart_code planacctnm chart_val
Chart of Accounts proposal proprest => chart_code propacctnm chart_val
Countries address addrcntry => table_code

addrdstrct

addrterrit

addrregion

addrzone

table_dist

table_terr

table_regn

table_zone

Institutions school schlinstit => table_code schlinstnm table_val
Relationship Type relation

relisa => table_code

relwhose => table_code

relisagrp

relwhogrp

table_type

table_type

Report Groups report_descripts report_groups => table_code

report_mgp1

report_mgp2

report_agp1

report_agp2

table_mgp1

table_mgp2

table_agp1

table_agp2

Solicitations gifts giftsolic => sol_code giftsolnam sol_val
Solicitations dues duessolic => sol_code duessolnam sol_val
Solicitations plannedgifts plansol => sol_code plansolnam sol_val
Solicitations proposal propsol => sol_code propsolnam sol_val
Solicitations correspondence corrsolic => sol_code corrsolnm sol_val
Solicitations correspondence_name table_sol => sol_code table_soln sol_val
Solicitations membership_benefits table_sol => sol_code table_soln sol_val
Solicitations membership_publications table_sol => sol_code table_soln sol_val
Solicitations membership membsol => sol_code membsolnm sol_val

Once you choose a lookup table, you may select a specific set of entries from that table for the utility to use. This may be any number of entries (one or more). When you have chosen the entries to be used and the utility is run, it will examine all of the rows in the database which use that lookup table. When it finds a data row that contains the specified lookup data, it will update the corresponding data field in that data row, based on the information in the lookup table entry.

Running the Utility

Some of the standard Millennium utilities make use of certain Microsoft applications and associated files. While we ensure that the required applications are present and registered on the IIS machine as part of the installation process, Millennium's thin-client architecture does not install them on every workstation at your institution. Therefore, to ensure success, certain utilities must be launched from the IIS machine, using the system administrator's login and password. The Data Sync Utility is one such utility.

The Data Sync Utility is run outside of the interface you use to access Millennium, in the utility's own application window. It must be activated by locating the program file (data_sync.exe) in the Tools directory, using the Windows Explorer or other appropriate tool, and then double clicking the program name.

When you login, you must also use your Millennium User ID and Password. This ensures that the appropriate security views are invoked for the utility and that you are given access to lookup table functions in accordance with your Millennium User Group settings.

Before running the utility, MAKE AND VERIFY A BACKUP OF YOUR DATABASE. Note that if this utility is being used to update the Report Group security information, the updated information will be in the millreports database and therefore it should also be backed up. This utility should be run at a time when no other users are accessing the system.

If the utility encounters an error which it cannot resolve, the action will stop and an error message will be generated to the screen (of the machine from which the utility is being run). This error message will allow you to Retry, to Continue, or to Cancel the run. It is important that you understand that if the run is canceled, it is likely that some data will have been updated and some will not and therefore you would need to run the utility again at some time in order to update the remaining rows.

Utility Classification

This utility may be used to perform a mass update of existing data and therefore it is classified as an advanced User Utility.

Using the Data Sync Utility

The following instructions assume that you are familiar with the use of the Millennium Reporter. For more information about any procedure described in the following steps, use the hyperlinks to read more on that subject.

  1. Perform a database backup of the appropriate databases and verify your backup. DO NOT SKIP THIS STEP.
  2. If Audit Logging is turned on for the table or tables that you will be updating, you may want to turn it off while running this utility. Audit Logging can be turned off by a Millennium system administrator, on the System Options page.
  3. Using a suitable workstation, use the Windows Explorer to navigate to DataSync.exe. Double click to launch the utility. A sub-window will open with instructions for running the Millennium Data Sync Utility.
  4. Verify that utility pointer is indicating Step 1, Login. Click the Next button. You will be given a Login sub-window.
  5. Enter the name of the ODBC data source that you want to use to access the database. Tab or mouse click to the User Name text box and enter your Millennium login. Tab or mouse click to the Password text box and enter the Password associated with the User ID. Click OK. If the login was successful, you will be returned to the main utility window and the pointer will have advanced to the second step.
  6. Click the Next button. You will be given a Lookup Tables sub-window showing the names of the value-based lookup tables in Millennium and the Report Groups lookup table. Click the desired table to highlight it and click OK. You will be returned to the utility window. The name of the lookup table will be shown at step 2 and the indicator will be pointing to step 3.
  7. Click Next and you will be given a Table Entries window. Click either the Value or Code radio button to indicate your search preference. Type full or partial information into the Enter Criteria text box to identify a set of lookup table entries. Click the Search button. The box on the left will display all of the entries that match your search criteria. Note that this search is case sensitive.
  8. Click an individual entry to highlight it and then click the Add  button. That entry will appear in the listing to the right.

    OR

    Click the Add All button to move all displayed entries to the list on the right.

  9. If necessary, you may highlight individual entries from the list to the right and then click the Remove button to delete them.

    OR

    You may click the Remove All button to clear all entries from the list on the right.

  10. When the list of lookup table entries on the right is complete, click Next to continue. You will be returned to the main utility window. The Select Table Entries item will now show a pull-down list box containing the entries you selected and the indicator will be pointing to step 4, perform updates.
  11. Click Next. The utility will run. When the updates are complete, you will be given a message to that effect.
  12. If you turned Audit Logging off in Step 2, turn it back on.

Top of Page