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.
- Perform a database backup of the appropriate databases and verify your backup. DO NOT SKIP THIS STEP.
- 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.
- 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.
- Verify that utility pointer is indicating Step 1, Login. Click the Next button. You will be given a Login sub-window.
- 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.
- 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.
- 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.
-
Click an individual entry to highlight it and then click the Add
OR
Click the Add All button to move all displayed entries to the list on the right.
-
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.
- 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.
- Click Next. The utility will run. When the updates are complete, you will be given a message to that effect.
- If you turned Audit Logging off in Step 2, turn it back on.