How do you run the Workforce Information Database on MS SQL Server?
The Workforce Information Database (formerly ALMIS) is compatible with most relational database products including SQL Server. MS SQL Server is currently the most popular database used to run the WID. About 55% of the states running the WID use MS SQL Server, the 45% use Oracle, Visual Foxpro, Access or open-source databases such as MySQL and Postgresql.
To implement the WID on any relational database management system, the database structure must first be created in that system and then populated with data. This is generally performed by a knowledgeable MS SQL Server, Oracle or other Database Administrator.
Data generally comes from two sources:
- Lookup tables, crosswalk table and national data such as the CPI can be obtained from the National Crosswalk Center, www.xwalkcenter.org.
- State specific data generally comes from the programs in your LMI unit, although some of it can be downloaded from the Bureau of Labor Statistics.
Income and population data can also be downloaded from NCSC or source agency.
What has been the toughest hurdle you have encountered in learning about the Workforce Information Database (WID)?
It depends on your background.
Most WID Administrators have an LMI (Labor Market Information) background and have to learn about relational databases. A few, on the other hand, have IT (Information Technology) backgrounds and must learn about LMI.
Even for someone who does know LMI, learning about all the different sources of data that go into the database can prove challenging.
The Anaylst Resource Center has developed a WIKI
for locating content for tables.
Many administrators find that the toughest hurdle to implementation of WID is getting access to the database from the IT department. And being able to update it in a timely fashion.
Which book(s) would you recommend for people about database administration?
This depends how much in depth and detail you would like to have about the topic. There are a wide variety of books and some are easier than others. Many vendors like Oracle and Microsoft provide a variety of books on working with their specific systems.
Here are a few books and links that may be useful:
- Learning SQL, Alan Beaulieu, O'Reilly
- SQL for Dummies, Allen G. Taylor
- Joe Celko's SQL for Smarties, Joe Celko, Morgan Kaufmann
- Introduction to Database Systems (8th Edition), C. J. Date, Addison Wesley
- Database in Depth, C. J. Date, O'Reilly 2005
(Chris Date was a colleague of relational database founder E. F. (Ted) Codd at IBM.)
- Practical Issues in Database Management: A Reference for the Thinking Practitioner, Fabian Pascal, Addison-Wesley Professional
- Refactoring Databases: Evolutionary Database Design, Scott Ambler, Addison-Wesley
- Fundamentals of Database Systems (5th Edition), Elmasri & Navathe, Addison Wesley
(Highly theoretical, not for the faint of heart, this is more of a reference for Computer Science students)
- www.orafaq.com (Oracle specific with some general information)
Give a load order of the occupations tables.
A complete load order for the Workforce Information Database is included in the structure document for Version 2.4 of the database
Do all fields need to be populated in all tables (e.g., benmark in labforce table)? We know the benchmark year back to 1990 but we have data prior to that and benmark is currently unpopulated. What does this affect?
There's no requirement to populate all fields in all tables in the Workforce Information Database. Rather, one of the beauties of the database is that states can pick and choose to populate only the tables most useful to them (other than the core tables). However, the primary key field(s) must be filled for the tables that are populated. Since the BENMARK field is part of the table's primary key, it must be populated. In the national LABFORCE table distributed by the National Crosswalk Service Center, the BENMARK field is filled with a current year default value taken from the national Current Employment Statistics (CES) files distributed by the Bureau of Labor Statistics. Output from state sources may include more specific values.
How can I have a "unique" license ID when there are many to many relationships between licenses and SOC occupation codes? How could the primary key be improved?
This situation most commonly manifests itself in a licensed occupation that is broader than individual SOC or O*NET occupations and may be related to a number of them. Common examples include engineers, physicians and teachers. The Analyst Resource Center and the ACINet licensing application
plan to address this issue by adding a license-to-occupation crosswalk file. Possible structures for these files can be found on the National Crosswalk Service Center's Non-Standard WID Database
How often do we have to update O*NET data in the Workforce Information Database(WID)?
Relatively little O*NET information is included in the WID(an ONETCODE lookup table and a few crosswalk tables). This was done by design. The O*NET database is updated on a regular basis. These changes frequently result in changes to that database's table structures and content. The Anaylyst Resource Center (ARC) has designed O*NET table structures that are compatible in format and content with the WID, so that the WID won't require changes when the O*NET database changes. For links to O*NET
databases that can be used with the WID, visit the National Crosswalk Service Center's Non-Standard WID
How often do we update Crosswalk data in the Workforce Information Database?
Revisions to crosswalk tables occur on an irregular and infrequent basis. The revisions are driven by changes in the respective classification systems included in the files. For example, crosswalks that include employment projections occupations and industries (MATXSOC and MATXNAICC are the currently active ones) are revised every two years when new projections become available. Revisions to O*NET databases frequently involve only changes to the descriptive information, rather than a change to the occupation codes or titles, so they frequently do not lead to crosswalk revisions.
Are all state's using the Workforce Information Database (WID)?
Yes. The Workforce Information Core Products and Services Grant from the U.S. Department of Labor, Employment and Training Administration (ETA) specifies the population of all tables designated as core tables in accordance with guidelines issued by the Anaylst Resource Center Consortium. The grant also specifies that grantees are required to deploy applications that allow electronic public access to the state WID and states are encouraged to develop systems that facilitate customer access to information across multiple states and for labor market areas that cross state boundaries. While each state may have their own delivery systems the underlying WID is essentially standard across the board. If you'd like to read more about what is specified as a core deliverable under the grant you can find the current program year grant on the ARC website
How can I get historical (last 10 years) CES (Current Employment Statistics) data based on NAICS (North American Industry Classification System) codes?
The information comes out of the CES ACES application. The information can be downloaded by year, downloading JOB E43. The information is only the employment, no hours or earnings. The format is not in the Workforce Information Database format, but it is a text file, with the column headings. The file has the following information in it: year, benchmark year, state, area, industry title, series and the employment for the months of January through December and the annual data.
I am having difficulty finding data for some of the tables. Is there any reference developed to assist in the hunt?
The Workforce Information Database Wiki
includes links to sources of content for many of the database's data tables. The site also has a feature that tracks changes in database table structures since version 2.1 of the database. To facilitate the sharing of information, the ARC has utilized Wiki software for this online source. Users are encouraged to help maintain and enhance the list of content sources. While anyone may browse the entire site, you need to create an account and login to be able to modify the information.
I am having difficulty obtaining data from within our own agency and from external sources. When they finally do give me data it is full of errors. How can I get them to give me what I need to do my job?
Unfortunately, this is a common problem for many Workforce Information Database Administrators. There are several approaches you could take. First of all, you need to ascertain what is causing their reluctance to share. If it's concerns about the disclosure of confidential data then you could work with them and either determine that only disclosable data will be loaded into the Workforce Information Database (WID) or you could explain how the suppress fields in the tables work in relationship to the application(s) that might be displaying the data. Also, explain the precautions that have been taken to ensure the protection and confidentiality of the data. If their reluctance is due to an attitude on their part that the WID is just not important and they are too busy then perhaps show them a copy of the latest State Workforce Information Core Products and Services Training and Guidance Letter (TEGL) from ETA that lists the WID and identifies several core tables as deliverables. Their reluctance may be because they do not have a grasp on what the WID is in relation to the applications that run off of it. Usually, a casual conversation explaining the database and what you do with the data in it is all that is needed.
Is there a central location where states are supposed to send their Workforce Information Database data?
No. All states are required to create and populate their own Workforce Information Database in whatever platform they choose (e.g. SQL Server, Oracle, etc.). However, the only exception at this point is licensed occupational data that is stored in the license, and licauth tables. This information is required to be sent to the National Crosswalk Service Center
as your data is updated (at least biennially). They, in turn, send quarterly updates to the folks at ACInet for inclusion on their site.
Is there a mandate to use a specific version of the ALMIS Database? Do I have to upgrade to Version 2.2?
The Workforce Information Core Products and Services Grant (PY 2002), Statement of Work Guidelines explains:The most current release of the ALMIS Database structure is Version 2.2. Depending on existing state applications for accessing the database, some states may need to continue maintaining an earlier version for a period of time. States are required to state in the plan which version of the database is currently being used and the projected date for upgrading to Version 2.2 if an earlier version is being used.
What does the National Crosswalk Service Center have for GEOG?
GEOG table with areatypes 00,01,04 included at this Internet location (ALMIS Database Version 2.2). An empty copy of the table structure for the SUBGEOG is available at this Internet location.
What is a trigger?
Triggers are used to automate the maintenance of selected administrative tables that are dependent on the content of other tables. The ALMIS Database Maintenance Consortium has developed sets of triggers for use with both Oracle and MS SQL Server databases. The following files relate to the use of triggers in the ALMIS Database:
·Addendum to Version 2.2
·Complete release package including database structure and trigger documents.
·Extract from the database documentation that describes the use of triggers.
· Text file containing triggers for use with Oracle.
Are there any ways that a system can be set up to make it easier to update/add to the database?
This depends on the type of relational database management system (Oracle, MS SQL Server, etc.) being used. Each has different load tools, Oracle loader, SQL Server DTS, etc. used to update the database. There was a free loader provided by the ARC Workgroup. There are also some open source tools such as Squirrel..
How do I use distinct to get all fields of a table?
SELECT DISTINCT tablename.*
Import question from Excel into a database: The formatted cells saved as .xls are not importing into databases.
Formatted cells (padded with zeros) WILL import into databases IF you change the
import field property to text (Tested in Access 2000).
I notice public economic development entities are now an authorized user of the ARC Employer Database. What is the definition of a “public economic development entity?
A public economic development entity is defined as an activity which is regularly supported, in whole or in part, with public fund.
Which State is entering into the contract with infoUSA and what is the length of the contract?
The State of Iowa and the Iowa Workforce Development have entered into this contract with infoUSA for a period of three (3) years with two possible one (1) year extensions.
When does the distribution under the new (Iowa) contract begin?
The first deliverable was sent on May 5, 2009 and was in the previous format you have received in the past. Each state should have received 3 sets of the CD/DVDs.
I heard that States will be able to order extra copies of the CD-ROMs for no cost. Is this true?
Yes, except we cannot exceed a maximum of 4,000 CD/DVDs nationwide for each 6 month delivery (See Attachment 2, Section 2.e of the Master Contract). If your state needs additional CD/DVDs please contact Jim Winner at 402-593-4583, or email, firstname.lastname@example.org If it appears we may exceed the 4,000 allotment, it may be necessary to adjust state quantities.
Can I order no-cost CD-ROMs anytime during the year?
No-cost CD/DVDs will be ordered a month before each deliverable and shipped free of charge in April and October. CD/DVDs ordered at any other time will be shipped at the licensee's expense. You do not need to do anything unless you need to adjust your state quantity.
What are the valid "uses" valid for the ARC Employer Database?
The valid "uses" for which the users may use the database include career exploration, job search, job development, employment support services, labor market research (including, but not limited to licensee's enhancement of existing employer name and address files) and related public workforce and public economic development system efforts. The database will be made available through a variety of means and media, including but not limited to standalone PC's, client-server systems and Internet/Intranet systems. The information in the database may be displayed, downloaded, printed, used to generate mailing lists, and used for other activities related to the purposes above.
Who can use the ARC Employer Database?
Users of the ARC Employer Database shall include: Individual job seekers, One Stop Career Centers, Workforce Development Boards, public economic development entities, State Workforce Agencies, public welfare agencies, State employment statistics agencies as defined under Section 309 of the Workforce Investment Act of 1998, all mandatory and option partners described under the Workforce Investment Act of 1998,Chapter 3, Section 121,(b) and any successor legislation; and the Employment and Training Administration of the United States Department of Labor and other partner agencies. The ARC Employer Database must be used by only approved "users" and for only those approved "uses". Usage must meet both criteria.
How often will updates be sent?
After the May 5, 2009 delivery, the database will be updated and delivered at six month intervals (October and April).
What ARC Employer Database products will I receive?
infoUSA will distribute a minimum of three copies of the employer database and associated software to each eligible licensee. infoUSA will also provide one copy of the uncompressed database to each licensee in the record layout accepted by ARC and in ASCII delimited format.
Who are the "licensees" under this contract?
The "licensees" shall mean each of the fifty states, the District of Columbia, the U.S. territories of Puerto Rico, Guam, the Virgin Islands, and the U.S. Department of Labor, Employment and Training Administration (ETA).
Can customers have access to the ARC Employer Database in libraries?
Yes, for the approved uses only. Persons using the ARC Employer Database in public, college, or university libraries will have End User access only through the Internet.
Who do I call if I have problems with the ARC Employer Database?
In the case of problems, infoUSA is responsible for providing a service contact telephone number, email address and assistance in the correction of any problem directly related to the product.
Can a "licensee" lose the privilege of using the ARC Employer Database?
Yes, by not complying with the requirements set forth in the License Agreement (Section 2, Grant of License) that each state and territory will sign. Non-compliance could also jeopardize the Master Contract.
Are there different levels of usage?
Yes, there are three specifically defined levels of customer access to the data. They are: Licensee, Intermediate User, and End User.
What are the limitations for the "end user" (an individual engaged in job search or, career exploration)?
End users may receive a display of partial records of which not more than 25 records can be downloaded, displayed or printed at one time. End users may also print the data elements up to twenty five (25) records at a time and email 25 records at a time (per single download request) to an email address. Such usage is for job search and career exploration.
What are the limitations for the "intermediate user" (ie, local One Stop, public economic development entity)?
After completing an initial search, Intermediate users may retrieve and display an unlimited number of partial records of which not more than 25 records can be viewed at one time. They can also print or download up to 100 records at a time per single print or download request. Usage is only for uses licensed by the contract and Intermediate Users must fill out a Sub-license agreement with the Licensee.
What data elements are included in the ARC Employer Database?
The ALMIS Employer Database contains over 40 data elements including:
Industry Code (NAICS & SIC)
Employment Size Range
Annual Sales Range
Do I need to install "captcha" if I am using the ARC Employer Database for viewing and printing only?
No, "captcha" is only required if you allow the download for web export of downloadable records from the database.
Will the Employer Database extract require any manipulation by a DBA to populate the EmpDB table in ARC Database?
The EMPDB table in the ARC Database will need to be changed to reflect the data elements that will be received under this new contract. Once this is done, the Employer Database extract format has been designed so that the extract can be used to populate the EMPDB table without requiring any additions, deletions or other modifications.
Can other private vendors link their websites to the ARC Employer Database?
No, a private vendor would not be an authorized user of the ARC Employer Database which is copyrighted licensed data.
Does infoUSA have to pre-approve a licensee's website application?
No, but infoUSA will review such an application if the licensee desires.
Do sub-contractors have to be approved?
infoUSA has preapproved Geographic Solutions, Inc., CIBER, Inc, Employment Security Commission of North Carolina, and Xpand Corporation. infoUSA shall have the right to request removal of another contractor for good cause.
Who are the contacts from the State of Iowa for contract administration?
Project Manager is:
Do I need to send a listing of "Intermediate Users" to infoUSA?
Yes, and it is required that the list be updated annually thereafter, with name and address.