Upgrading the WID can be time consuming, but is pretty straightforward if your database is well-maintained. It can be intimidating if you’ve never done it so here are some steps for a person new to the process. Please upgrade to WID 2.8 by July 15, 2020.
Identify your needs
Determine which tables beyond the core tables you need. If you have non-standard content that you need to preserve, have a plan in place to build that into the new version. Many states store data within tables that may not be in national versions of lookup tables. State analysis regions, for example, data types that come from the UI system, or data used for websites are all possible examples. Particularly if you’re using the available Access database (which comes with lookup tables populated), you will need to be aware of which tables have more or different data than the standard lookup tables so you can bring them over.
Build a new shell
A “shell” is an empty database with the correct table structure and constraints but no data. How you do this will depend on what database management software you use.
Software |
Structure |
Notes |
| Access | 2.7 accdb, with lookup tables populated | This version does not have foreign and primary keys. Access can’t handle the multi-key constraints that the WID has very well. |
| MS SQL Server | 2.8 scripts 2.7 scripts | You need elevated permissions on the database to run create table scripts. Use caution. |
| Move 2.7 data to 2.8 structure | This moves only basic WID content and depends on WID2.7 being a standard format. Any state-specific tables, views, or processes will need to be moved separately. |
If you use Oracle or MySQL, other strategies would be to import from the Access database, to modify the SQL Server scripts, or to script out your existing database and manually alter the tables that have changed.
It is recommended that you not maintain empty tables to prevent confusion. If you’re not using non-core tables, remove them from the Access database or scripts before you move data over to avoid accidentally deleting tables you need.
Move the data
In most cases, you should be able to insert your existing content directly into the new shell with only minor manual transformations or changes to field mapping. The trickiest part is ensuring that you get the data in without violating foreign keys – Appendix A of the Structure Document has a recommended load order. If you follow that order (leaving out any non-core tables you don’t populate) you shouldn’t run into problems.
If you need additional lookup table content, you can find it here.
Change references to the database
Many states have jobs or processes that pull data from the WID. If your database name has changed, you will need to change those connection strings or references. In addition to websites or export processes, you may have people using the data through other software – Excel, Access, ProjectionsSuite, Tableau, and most statistical software can connect directly to the WID, so those users should be identified and have their settings updated.
Clean up
Cleaning up your old database is also a critical step – leaving the old one could result in the wrong database being updated or used and inaccurate data getting out. You may not want to delete it right away in case something was moved over incorrectly, but changing the name is a way to test if all the references to the database have been changed from the old to the new and can be easily reversed if something went wrong.
