Every state is different and has different dependencies and software. This process is intended to outline the steps that should be followed in a general platform-independent sense, although specific examples may not be relevant to all states.
For questions, try the Forum at https://ulmita.org/forum or email us.
Planning
While the WID 3.0 is a framework that can be used in many settings, only core tables are mandatory. Additionally, states often add more content to support non-WID activities. Identifying the differences between the two can streamline the process and prevent the creation of empty tables that may confuse users.
- Review WID Structure Document/create scripts to see which tables are still needed.
Core tables are listed in Appendix B of the structure document and should all be included. Beyond that tables can be excluded at state discretion. Start with the data tables, then check the foreign keys to see which are referenced. Most foreign keys will reference core tables that are used for many things (e.g. Geographies, Periods), but sometimes there will be a lookup (e.g. IncomeTypes, IncomeSources) that is only related to a single table and can be removed along with that table. - Comment or delete unneeded tables and their unique dependencies from the create script or database structure.
If this is being done after the database exists, deletion needs to occur in the reverse of load order (data tables, then lookups) because most databases will not allow you to remove objects that have dependencies. - Check for custom modifications for your state.
Many states have added tables or fields to their WID that meet their unique needs. Those structures will continue to work with WID 3.0, but will have to be brought over and may need some modifications (AreaTypeVersion field, changing naming conventions) - Check for differences in key values as a result of structure changes.
Most values, particularly for current series data should remain the same. The addition of AreaTypeVersion to the area keys is the most likely to cause disruption because correct usage of MSAs now requires them to have the same AreaType and different AreaTypeVersions.
Age categories, used by the demographics table, were also restructured because there were many duplicated categories. While there aren’t many dependencies on that table in the most commonly used WID tables, state-specific content may require some care to adapt to those changes.
Many states have errors or deviations from the standard values in some of their lookup tables – this is also a good opportunity to correct those. - Identify ways the database is used.
To move the data with a minimum of disruption it’s helpful to have a clear idea of the connections. This may include apps, software connections like Tableau, users who may store queries or generate reports, and any sort of automation that loads or exports data.
Creation
A number of resources have been provided for state users. The majority of states either use a contractor to build and implement their WID structures or use Microsoft SQL Server. Some use Microsoft Access, MySQL, or Oracle. Resources for each are listed below:
| Resource | Location |
| WID Structure Document | Website |
| Load Order | Appendix A |
| Core Tables | Appendix B |
| Change Summary | Appendix C |
| Previous Table Names | Appendix D |
| Microsoft SQL Server | |
| Create Script | WID30Create.txt |
| Compatibility Views | WID30CompatibilityViews.txt |
| Application Views | WID30ApplicationViews.txt |
| Migration Script | WID30Migrate.txt |
Not every resource will be helpful for every user, but they’re available if needed.
1. Create WID 3.0. Run the Create Script after unnecessary tables have been removed.
2. Add state-specific structures (tables, views, stored procedures) as needed.
These may be upgraded or copied over as is. The main thing is to change them to reference the new lookup tables rather than creating duplicated content.
3. Add application and compatibility views as desired.
These are in separate scripts, linked above. If they’re needed they can be added to the database at any point.
At this point, you have an empty shell with all keys and structures functioning. If other people will need to adapt their processes, they can be given access and work on changing their connections before the data is fully migrated or being maintained in the new database.
Migration
Because the 3.0 structure changes field names and the key structure of some structural tables moving the data from WID 2.8 to WID 3.0 is more complex than in previous updates. Migration scripts provided will help with the field mapping if you’re using a clean, fully standard WID 2.8, but any deviation will still have to be tested.
Map Key Values
Some structural changes in the common concepts of the database were made and as a result major tables and their corresponding referencing fields in many tables may not directly match what was in WID 2.8. All the information can be brought over, but there may be a need to recode historic values or set default values for new fields.
| Concept | Key Fields | Tables Impacted | Notes |
| Time interval | PeriodYear, PeriodType, Period | PeriodYears, Periods, PeriodTypes | The change to these tables was in splitting out PeriodYears from the PeriodTypes table and adding a description field. There is no impact on data tables, only the lookups. |
| Region | StateFips, Areatype, AreaTypeVersion, Area | Most tables | The change requires an additional AreaTypeVersion field in every table with an area. In many cases, an AreaType has only one version so mapping will be a static value. In two cases (MSAs and CPI areas), versions will have to be defined and may be complicated if there is a long history stored. |
| Industries | StateFips, IndCodeType, IndCode | NAICSCodes | The structure is largely unchanged. There’s a type added to the NAICSCode table, but that’s more a parallel structure than a dependent structure. |
| Occupations | StateFips, OccCodeType, OccCode | SOC matches NAICS above, but that’s an established pattern. | |
| Ages | StFips, AgeGroup, AgeGroupType | AgeGroups, AgeGroupTypes | This is a change to the lookup tables only, data tables are unaffected. Because each source has its own age categories the numbering of this table became unwieldy. There are few tables in the core structure that reference this, but if states have non-standard content they may have to create new AgeGroupTypes to accommodate it. |
| Ethnicity | StateFips, Ethnicity | UIClaims | Race and Ethnicity were previously a single very complicated table because of historic Census data. These were split out and mapping that difference will depend on state-defined UI Claims categories. |
| Race | StateFips, RaceCode | UIClaims |
Populate Lookup Tables
Lookup tables contain titles or descriptive content for codes. The structure of the database has foreign keys that define those relationships to prevent the inclusion of orphaned data with unknown descriptions. The lookup content therefore has to be populated before other data to avoid a foreign key violation error.
Every state will have some content unique to their own needs. It may be custom state regions, or codes for a data source that is only available in the state. There are also differences in what states may need to include. CIP Codes, for example, are not a part of the core tables but many states rely on the schools and supply tables for analysis about graduates or to direct job seekers to resources. Those states will need to include CIP Codes in the OccupationCodes table, but other states may not need to. As a result, there’s not a single, all-purpose import for any of the lookup tables.
The best approach would be to bring over content from the state’s WID 2.8, but common values are available as a series of download files and are described in the Data Values section of the Structure Document.
Migrate State Data
Move the data from WID 2.8. To avoid foreign key violations, this needs to happen in order so that tables that are referenced are populated before tables that reference them. To facilitate that, the recommended load order is provided. That load order includes all tables in the database, even those that are not Core (required). The list of Core tables includes all required Lookup and Structure tables – if a state is implementing a very limited WID structure that is only the Core tables, it may help to reference that list before going through the entire load order.
Outside the inclusion of keys like AreaTypeVersion and CodeTypeVersion, the data tables themselves are very similar to their previous versions, with changes to field names but usually not type or content.
Dependencies
Every state uses their WID in different ways. Some have analysts connecting other software (Tableau, SAS, Excel, R) to their database. Some have applications that use the tables. Some have automation that generates reports or loads data into the database. To streamline this process, a number of resources have been provided:
Compatibility Views – These are views that map the new table structure to the previous field names and table structure. They begin with the prefix “c_”. While they change the field names, new key values are used.
Application Views – These are views designed for applications, with lookup values for titles already in the structure. While many states will already have a specific application view defined, these may be useful for users of other software, such as Tableau. They begin with the prefix “a_”.
