ARC Newsletter

Analyst Resource Center Serving the workforce data community

                                                                Summer 2019


Inside this issue

O*NET Web Services

O*Net Logo

O*NET Web Services
(services. provides easy access to occupational data and career exploration tools for over 900 occupations, direct from one of the nation’s primary sources of occupational information. O*NET Web Services is sponsored by the U.S. Department of Labor, Employment  & Training Administration, and developed by the National Center for O*NET Development.

What is O*NET?

The Occupational Information Network covers the entire U.S. economy with its SOC-based taxonomy (see: The O*NET-SOC Taxonomy). It contains a comprehensive framework describing the world of work, covering both worker and occupational requirements (see: The O*NET Content Model). The O*NET Program collects and publishes approximately 500 standardized ratings plus occupation-specific information for each of 900+ occupations.

The O*NET Database is updated quarterly with information collected from job incumbents, employer job postings, expert research, and other sources (see: O*NET Occupation Update Summary).

O*NET websites cover multiple audiences, from students (My Next Move) to veterans (My Next Move for Veterans) to Spanish speakers (Mi Próximo Paso) to workforce professionals (O*NET OnLine).

O*NET Web Services provides access to the full O*NET Database, along with the most popular features and tools from each of our websites.

To learn more about the O*NET Program visit: Contact O*NET Customer Service ( for questions, comments, or feedback.

What Is the API?

Data from the O*NET Database including:

  • Knowledge, education, and skills needed
  • Worker abilities and work environment
  • Occupation-specific tasks and job titles
  • Technical skills used on the job

Tools from O*NET websites including:

  • Keyword search for career exploration and intelligent occupation lookup
  • Military transition search for all branches
  • Interest Profiler assessment to help students and job seekers find a career they'll love

Easy to Implement API

  • REST API with full documentation and interactive demo before you sign up
  • HTTPS for privacy and integrity of requests
  • Choice of JSON or XML output
    • Straightforward account login
    • Server-side: industry-standard Basic authentication
      Client-side: query string identifier plus CORS domain registration

Full working examples and connection libraries in several languages (see:

Before You Sign Up

All O*NET data and Web Services access is free, but attribution is required in your applications. See our license at:

The API is rate limited; contact us if you expect more traffic. Customers requiring extremely high volume or guaranteed uptime should consider using the downloadable O*NET Database as a local source of information, or caching common requests for performance.  See all terms of service at:

Phil Lewis
Technical Officer
National Center for
O*Net Development

Wid 2.8 Changes

As labor market and other economic data changes and evolves, the databases that track this data must also.  It is to this end that the Structure Committee of the Analyst Resource Center has released WID version 2.8.  This release was official on May 15, 2019.

The major change in WID 2.8 is the restructuring of the license tables.  Both the licauth and license tables have new fields, to give an analyst more information to understand licensing in their state and how it affects jobs and job seekers.  As a result of these new fields, ten lookup tables have been added, to standardize the values of these new attributes.

The title fields for the various codes have been expanded and standardized.  The regular or long title is now length 200, and “short” titles are 75.  This should allow for the current titles and for future titles.

The structure of the demographics table seems to have been problematic since its inception.  As a first step to making this table more usable, the mean error fields have all been removed, as it seems that no one was using them.  We in the Structure Committee would welcome feedback from DBAs and analysts on how they use this table, and any feedback on improvements that could be made.  For now, this table has those categories available from the Census Bureau.

Some indicator fields have been added, while a few redundant fields have been removed.  An ETPL approval flag was added to the programs table, and inddir now has an ownership field.  In the other direction, the fields siccode, naicscode and axnaicscode have been removed from stfirms.  Industry is known using the usual indcodetype/indcode combination of fields.

The final structural change was the addition of mocxocc, a new generalized crosswalk between military occupation codes and any of the standard occupation codes used.

Several tables have been deprecated in this edition of the WID.  Many of these tables were for code systems that no longer exist.  Some, such as the MLS tables, are for programs that don’t exist, or for data that’s no longer available.  Keep in mind that our policy is to keep these deprecated tables in the WID for three versions, and that they will be supported by the ARC during this time.

Administrators should be aware that the process for acquiring the Employer database (table empdb) will likely become the responsibility of the Federal government.  Once this happens, the structure may change, and the WID will respond with an updated version 2.8.1.

The dictionary document itself saw a few changes in version2.8.  Based on comments from users, we’ve added an explanation of the field values section of the data dictionary.  Core tables are now flagged in the table descriptions section with their own icon, and the Contact information has all been moved to the end of the document, rather than scattered throughout.

As structure changes go, the move to v2.8 is an incremental one.  Future changes may be driven by technology changes, especially APIs, as much as changes in data.  And the WID will change to adapt.

Dana Plazeck
Structure Chair
Analyst Resource Center

New WID version 2.8 - What You Need to Know

WID version 2.8 is out! Implementation of WID 2.8 needs to be completed within 14 months, by summer of 2020.

WID Structure Document:

Navigating the Structure Document

The WID structure document is intended to be a comprehensive description of all standard WID tables. While Core tables are required for all states, the structure document contains many tables that are not Core and may not be in use in every state. It also contains deprecated tables, which remain standard for three versions after they’ve been identified for deprecation. While states are discouraged from using those tables, sometimes the transition away from them may take time, or legacy data needs to be stored for business reasons. Because states have very different data needs and the WID attempts to accommodate all of them, the document can be overwhelming at first. Here are some tips for navigating the structure document.

Core tables
The tables all states are required to maintain are called “Core” tables. They’re tables for BLS programs data that all states produce, license tables, and the employer database (EMPDB), as well as the necessary lookup and administrative tables to support that content. A complete list of those tables is in Appendix E of the Structure document, on the very last page.
The bulk of the structure document describes table structure in detail with field names, types, content and keys. In that part of the document, core tables are designated with an apple core icon to make them easy to identify.

Table types
Within the database, tables have different functions. There are data tables that contain the data, lookup tables that contain the descriptive information associated with the codes in the data tables, crosswalk tables that relate one type of data (usually occupation or industry codes) to another, and administrative tables which are necessary for the functioning of the database itself. In the structure document tables are grouped into each of those four types and then arranged alphabetically. This means that functional groups of content – all the tables necessary for the CES program data, for example – do not appear together in the structure document. Some will be in the data tables section, some in the lookup table section, and some in the crosswalk table section.

To understand the dependencies it’s best to find the appropriate data table in the document (CES, LABFORCE, INDUSTRY, IOWAGE, IOMATRIX) and look at the end of the table description for the foreign keys. The necessary lookup tables will all be listed there, since you can’t have data without descriptions for the types included in the fields.

Data tables don’t usually have dependencies on crosswalk tables, since that’s usually related to the coding structure and is applied when using the data. They’re not typically part of functional groups.

Field Values
There are a number of standard values for the lookup tables that are described in the Structure Document starting on page 107. While most of the lookup tables allow for state-specific values, using the standard values wherever possible is necessary to make content truly comparable across states. These values are organized alphabetically by field name, which is often the same or very similar to table name. To download all standard values for a given table in an import-friendly format, go here:

Deprecated Tables
Appendix C of the structure document lists all deprecated tables, which version they were deprecated in, the reason for deprecation, and the replacement table (if applicable). This can be very helpful when cleaning up the database – certain types of codes may no longer have any related data and your state may be maintaining crosswalks that are no longer needed. While not definitive (some states maintain legacy data for good reasons), this appendix is a starting point for understanding what content should still be maintained.

Load Order
One of the most frustrating things for new DBAs can be understanding the foreign key violation failure. Data can’t be inserted if the codes don’t have descriptions in the related lookup tables. During regular data loads these errors usually indicate a data format problem – dropped leading zeroes, numeric instead of text formats, etc. At the start of a calendar year it can indicate a change in the data itself – a new industry code in CES or a new area code in the CPI. It can be a mechanical problem – tables are dependent on the period table and if the insert process doesn’t add a new period problems can crop up, as well. When rebuilding or upgrading the database, though, all of the contents in the lookup tables need to be added. Since those tables have a lot of dependencies, figuring out the correct order to load that content can be complicated – and just going through in the order of the Structure document isn’t going to cut it. Fortunately, Appendix A has all tables listed in the correct load order to take the mystery out of the process.

Database update

Fortunately, we’ve already got some resources available to streamline the database upgrade process. If you’re new to this task, there’s an overview available here:

Even if you’re not new, there are some tools that you many find useful.

Database Creation Scripts
These were developed for a Microsoft SQL Server environment, but could be modified for Oracle. Deprecated table structures are included but commented out – review the scripts to remove non-core tables that you don’t need and add any non-standard or deprecated tables you do use.

Database Migration Scripts
These were created assuming you’ve got a standard WID 2.7 in a database called WID27 and have already created the WID 2.8 shell and called it WID28. These should be carefully reviewed for their application to your specific database structure. They’re provided for convenience only – it’s up to you to ensure their use is appropriate for your database.

Lookup Table Content
The standard values can be obtained at the link above and loaded into the database. Many of our lookup tables have stfips as part of the key to accommodate state-specific content. When that’s the case, the lookup table values would need to be inserted for each stfips that the state has content for – usually ‘00’ (national) and the state itself, but sometimes neighboring states, too. The files on the server have records with the ‘00’ stfips. To load for your own state, you’ll want to either replace the ‘00’ stfips with your own state FIPS code or duplicate records as necessary.

Data sources and documentation

While the structure document goes into a lot of detail about how data should be stored, there are content-related topics that are helpful to understand and that may change in between versions. For a selected list of subject areas, that kind of information is on our website in the documentation feature, which you can access by selecting WID>Content Index and Documentation or by the direct link:

Along the right side of this feature are specific table names and content areas that may be of interest. Each page talks about the potential sources of data for that table, describes any downloadable content that the ARC provides, and links to the format and related tables.

If you have questions or are wondering about a topic that’s not already included in that documentation, please contact

Changes to non-standard folder

Beyond the standard tables described in the Structure document, states often want to store other useful related data in a WID-compatible format. When that comes from an ARC member state or another state shares something that’s broadly useful with us we create documentation and content and make it available for anyone to try out. This often serves as a trial period before new tables are added to the Structure document, but some content is too niche to ever make that transition. As a result, our file server had developed a glut of outdated files and multiple versions of similar content – understanding what non-standard content was available and current was a challenge. In an effort to change that, the file server ( has been cleaned up and some of this content is in the documentation feature of the website, tagged (and linked to under “Categories”) as “Non-Standard Tables”. While sample downloadable content for these tables is on the server, some of them may not be the most recent version available. Look to the source if you’d like to put the information to active use. If you have questions about this content, suggestions for additional topics, or would like to see an update or change, contact


Part of the ARC’s mission is to find efficiencies for states to meet their data related needs and fulfil the requirements of the WID. Recently state feedback has started to indicate that states want to use APIs from federal agencies rather than maintain their own copy in the WID of the same content. For web development there are already good resources for states, but for content that does need to reside in state WIDs (data for non-BLS areas like state-defined economic development regions or State-specific LAUS areas, state data that’s published before the BLS release, or custom state-funded variables) the state WID is still the definitive source of the data. To that end, we’ve got a project to set a standard for building APIs from the WID. Not only does this have the potential to save states a lot of effort in documentation and identifying approaches and best practices if they decide to create their own web services, it also has the potential to make it easier for states to share data with each other, since the API calls would be standard across states. This is still in a preliminary phase and there are no plans to make this mandatory for states at this time. For details on the project, check here:

Another use case for APIs is for analysts doing research. Those same federal agency APIs that can power websites can be connected to by software, such as R or SAS. Tableau can also connect directly to APIs, but the output format has to be defined in what’s called a web data connector. ARC member states have made some of those and they’re available for other states to use. For more information, check here:

Amanda Rohrer
Analyst Resource Center

In the Spotlight

Gary Sincick photo

How long have you been involved in the world of LMI?

As of this February, 30 years. I started out in 1989 as a CES Analyst. From there, I became a Regional Economist for the Eugene and Roseburg area, and then moved on to be the economist for the Portland metro area. Several automation-related side projects led me to shift into full-time application and web-development work. I was on the team that created OLMIS, Oregon’s first LMI web site, and I’ve been heavily involved in the development of each iteration of our LMI web presence ever since.

What is your current job title?

My classification is Information Systems Analyst 8. My functional title is a little fluid…I usually say ‘Application Developer’, ‘Web Developer’, or ‘Lead Developer’ depending on the circumstances. I get involved in everything from the user interface to the database to the application logic, depending on what we are working on and what’s needed.


      Gary Sincick, Oregon

Are you originally from Oregon?

Yes, I grew up in Beaverton, which is a town just outside of Portland. That area was largely rural when I grew up, and had a population of about 10,000. Now the city has nearly 100,000 residents, and is host to companies such as Nike and Intel. Both my parents grew up in the area as well. Being a native Oregonian is increasingly rare these days!

What is your educational background?

I have a B.A. degree in Economics from the University of Oregon. I also took quite a few math and computer science classes from Portland State University after I graduated and was working as an economist in Portland.

What is the most rewarding aspect of your job?

There’s a lot that I like about my job. One thing that I really enjoy is learning new things, and trying to come up with a better way of doing things. It’s really gratifying when something you’ve done makes someone’s job easier, or provides them critical information that they need to make decisions.

My job offers a nice mixture of working independently and working with other people. I’m really fortunate to have great people in Oregon to work with. It’s a smart, creative bunch, and it also helps that everyone has a good sense of humor!

I’ve also really enjoyed being involved with the ARC the last few years. It’s always really interesting to find out how things are being done in other states, and it’s been a lot of fun getting to know this group.

What is the most frustrating or challenging aspect of your job?

Occasionally when working with technology, you end up fighting with the tools that are supposed to make things easier for you. If you find that you are locked in to a framework or toolset that isn’t a good fit for what you’re doing, or has a lot of bugs, it can be pretty frustrating.

What is the most interesting or awe-inspiring place you have been to?

I’ve been fortunate to have been able to travel to some really amazing places. Two years ago I was in southern Thailand, which is an amazingly beautiful place. My brother lives in Bangkok, so after visiting him there, I spent some time on Koh Lanta, which is probably the most relaxing place I’ve ever been.

Probably the most awe-inspiring place I’ve been is Nepal. In 1988 I did a 125-mile trek around the Annapurna range, which was truly spectacular.

What are your interests outside of work?

I still do quite a lot of hiking, and try to get out backpacking and camping when I can. One great thing about living the Pacific Northwest is that you have access to some really amazing natural areas. The Columbia Gorge is a spectacular area, and is only about an hour away. Likewise, Mt Hood and the Oregon Coast are both easily accessible and offer fantastic scenery.

As for sedentary activities, I like reading, listening to podcasts (mostly philosophy and politics), watching the Trail Blazers, playing poker, and seeking out the latest IPAs.

Read any good books lately (personal or work related) that you would recommend to others?

The book I’m reading currently is ‘Build APIs You Won’t Hate’ by Phil Sturgeon. I’m trying to glean as much wisdom as I can in the service of helping design a good API for the WID database. Although this book is a bit dated (2015), it still contains a lot of good information about APIs.

As for non-work-related reading, I recently read ‘Astoria’ by Peter Stark. This is a fascinating story about the founding of Astoria, a town on the coast of Oregon that was the first colony on the West Coast of North America. The town was established by John Jacob Astor as an outpost of his global fur-trading empire. Today, Astoria is probably better known for being the town that ‘The Goonies’ was filmed in, as well as for producing really good beer.

WID DBA Training

training image

The Analyst Resource Center has begun planning for DBA training that we plan to deliver in the spring of 2020. 

To help us plan for the level and type of training states
need, we invite you to complete a short survey.  SURVEY





Structure changes
There have been changes to the structure of the license tables. While the core of the data remains the same, we added new indicator fields to help search and filter the data. These are coded fields (char(1)) that describe what might be in the description (licdesc) field. For example – Education allows values 0 (No Education), 1 (Specific Course), or 2 (Degree). All indicator fields allow undetermined (value 9), as well. Because of database integrity needs, that means that we’ve also added tables with the values and their descriptions for each of those indicator fields.

Another change is to the licauth table, where we replaced the name1, name2, name3 fields with department, division, and board. This will improve consistency between states in how they use those fields and to help with web display – it has been difficult to determine which field to pull from when some states start with the most detailed and others with the most general, and others just always use name1.

We lengthened the title field and added a “LicenseUpdated” field to serve as an indicator of how current the data is and when it last changed.

Source data
To help states adapt to the new table structure, some of the data is now being obtained or cleaned centrally. That content and the most current version of each state’s license data is available here: File names lead with state FIPS codes.

Indicator fields: Preliminary values for the new indicator fields have been obtained from alternate data sources or existing license description fields, where available. In the case of using alternate data sources, licenses were matched on occupational code which means that states that have a lot of provisional or temporary licenses with lesser requirements may have more error than those with a more direct match.

Geocodes: Most states don’t submit XY coordinates for licensing authorities, but they do use the address fields for those offices. Batch processing the submitted data through the Census Bureau’s geocoder ( Because so many state offices use P.O. Boxes or use a building name as an address, only about half of licensing authorities were successfully geocoded, but we may look at ways to improve this for map displays.

Occupational coding: Historically, each state has had its own approach to assigning occupation codes. They may limit themselves to a one-to-one relationship between occupations and licenses or may have many matches. They might use SOC or ONET. Certain types of licenses don’t match well with occupations, and the assigned code might be unintuitive. When using the combined data set, though, those inconsistencies of method have made it very difficult to make cross-state comparisons. If users search for Mechanical Engineering licenses in all states and only a handful come up because some use SOC codes and some code all engineers to “Engineers, all other”, then the data appears more incomplete than it is. As a result, we’re now coding occupations centrally so that the same rules are applied consistently. While the overall effect is an improvement, a small minority of licenses may be miscoded because there are so many of them and there is just not the same detailed knowledge here as in the states. We don’t ask that our codes be incorporated into state applications, but if you’d like to review them the licxocc table is included in the download file.

Re-ordering of name fields: We’ve reassigned the name1, name2, and name3 fields into the department, division, board system.

Added licensing authorities: In the course of the data review, some apparent gaps in coverage were observed. To combat that, we did some web scraping of agencies that are referred to as licensing boards or authorities, then compared the output to our existing list of licensing boards, and created entries for anything that appeared to be a genuine board but was not included in the data. They are stored in their current form in the database and may be used as part of the web display or downloads, but it’s strongly recommended that states review these additions and ensure that if they’re genuine all associated licenses are included and if they’re not that information be passed on to us. These boards are in the download file and are identifiable by licensing authority IDs that start with ‘z’.

Added licenses: Similarly, even after reassigning occupational codes, some states didn’t have an entry for a license that an alternate data source indicated they had licensure for. Records for these probable licenses were also created and added to the file for review – they are indicated with a licenseid that starts with ‘r’. Many don’t have an associated licauthid and therefore can’t be loaded into a database without further investigation.

Additionally, we’ve created a file upload page on the website in hopes of addressing some of the challenges of sharing license files via email. You can access that page here: using password L!c3nse. Please name your files something that indicates the state and send an email to to alert us that new files are out there. There should be no restrictions on file types or sizes, but this process is still new. If you have had success with another file transfer method (such as email or dropbox) and would prefer to stick with that it’s fine as well – but remember – we will always confirm receipt of your files. If you don’t receive that confirmation, there’s a good chance that your attachments have not made it through.

Amanda Rohrer
Analyst Resource Center