1 / 27

Workforce Information Database Fundamentals

Workforce Information Database Fundamentals. October 28, 2008 St. Louis, Missouri. Topics. WID Database History Table Layout How to read a table definition Table constraints and Triggers ETA Guidelines (TEGL) Core Tables Table Load Order Structure Changes. WID IS:. Workforce

erin-hudson
Download Presentation

Workforce Information Database Fundamentals

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Workforce Information Database Fundamentals October 28, 2008 St. Louis, Missouri

  2. Topics • WID Database History • Table Layout • How to read a table definition • Table constraints and Triggers • ETA Guidelines (TEGL) • Core Tables • Table Load Order • Structure Changes

  3. WID IS: Workforce Information Database

  4. The Analyst Resource Center: A Joint effort between the Employment and Training Administration of the U.S. Department of Labor and the States to: - conduct research, - develop products, and - deliver Information to job seekers, employers, economic developers, planners and other users.

  5. What is the WIDDatabase? In Computer Terminology: The WID Database is a Normalized, Relational Database Structure, Developed for the Storage and Maintenance of Labor Market, Economic, Demographic and Occupational Information.

  6. History of the WID Database WID (ALMIS) Database Version 1.0 Originated from Two Existing Databases _NOICC’s Occupational Labor Market Information Database or OLMID _North Carolina’s Human Resource Information System Database

  7. History continues … - August 1996 : Work Began on the ALMIS Database structure - April 1997: ALMIS Database Structure Version 1.0 Delivered to States - Spring 1997: Formation of the ALMIS Database Maintenance Consortium - Summer 1998: Definition and Release of “Core Tables” under the P/Y 1998 One Stop LMI Grant

  8. History Continues….. • December 1999: ALMIS Database Structure Version 2.0 Delivered to States • November 2001: ALMIS Database Structure Version 2.2 Delivered to States • August 2004: ALMIS Database Structure Version 2.3 Delivered to States • July 2007: WID Database Structure Version 2.4 Delivered to States

  9. History Continues……. • September 1998 - First training in Atlanta, Georgia • November 2003 – First National Seminar at the Emory Conference Center

  10. What Kinds of Information are Included in the WID Database? Some of the Types of Information Which Can be Found in an WID Database Include: - Population - Employment Projections - Tax Information - Mass Layoffs - Income - Labor Force Estimates - Employment - Sales Tax - Government Transfer Payments and - much more, such as…...

  11. WID Database Structure The table layout for the WID Database: • Lookup tables • Data tables • Crosswalk tables • Tables of standard field values • Administrative tables

  12. Common Relational Database Terms: • A Database is a structured collection of data, stored in tables • A Table is a collection of records related to one another • A Primary Key is a field or combination of fields that uniquely identifies each record in a table • A Foreign Key is when a value in one table matches the primary key of another table

  13. Common Relational Database Terms Continued: • A Data Table is a table consisting of a collection of data records • A Look Up Table is a foreign key table • A CrossWalk Table is a table that relates coding structure in the database • An Administrative Table is a table that contains information on the data and tables in the WID Database

  14. Load Order

  15. WID Database Structure ChangesVersion 1.1 to 2.2 • Number of Tables Added: 36 • Number of Tables Changed: 42 • Number of Tables Deleted: 11 • Number of Tables Unchanged: 72

  16. New Table Elements in 2.2 from 1.1 • For Data Tables That Have Changed: • CES: benchmark, suppression • ESDATA: stattype, esorders, newapp, referrals • INDPRJ: nchg, growcode • INDUSTRY: firms, m1emp, m2emp, topempav • LABFORCE: benchmark • LICHIST: licnumtyp • MLSCLAIM: claimtyp • MLSEVENT: dwvertype • *OESWAGE: (entire table) - new core table

  17. New Table Elements in 2.2 from 1.1**continued** For Data Tables That Have Changed: • OCCPRJ: nchg, growcode • PROGRAMS: compltyp, progtitle, url • SALES: salestyp • STFIRMS: lat, long, naicscode, axnaicscd • STINDPRJ: nchg, growcode • STOCCPRJ: nchg, growcode • UICLAIMS: claimtyp, indcode + occcode, agegroup, racethn, gender • *URL: (entire table)-new core table • WAGE: periodyr, periodtype, period, respondent, empcount

  18. Triggers A trigger is a fragment of code that you tell the server to run before or after a table is modified. A trigger has the power to • INSERT • UPDATE • DELETE

  19. Why have Triggers? Why have the Occcode & Indcode tables? • Can not have conditional foreign keys as was happening in the case of the multi-code fields in many of the WID DB tables. • Can not have a foreign key field length that is different than the primary key it is referencing.

  20. Trigger Example Action: Insert new soccode “959595” Soccode table Trigger fires! Action: Automatically inserts new soccode “959595” Occcode table

  21. Training and Employment Guidance Letter (TEGL) Statement of Work Deliverables Grantees are required to produce all six deliverables with PY 2008 funding. Except for deliverables one and two (WIDb maintenance and enhancements; and, Industry and Occupational Employment Projections), which are expected to be standard and comparable across states, states are allowed flexibility in developing the remaining required deliverables

  22. Continue….TEGL Funds shall be used to produce 6 required deliverables. • 1) Continue to populate the Workforce Information Database (WIDb) with state and local data. • 2) Produce and disseminate industry and occupational employment projections. • 3) Publish an annual economic analysis report for the governor and the SWIB.

  23. Continue…TEGL • 4) Post products, information, and reports on the Internet. • 5) Partner and consult on a continuing basis with workforce investment boards and key talent development partners and stakeholders. • 6) Conduct special studies and economic analyses.

  24. Core Data Tables Income Empdb CES Industry Labforce Populatn *Database Administrator may opt to populate one or both of these data tables Wage* Indprj Occprj Iomatrix Income License Oeswage*

  25. ALMIS Resource Center • Nebraska • Nevada • North Carolina • Oregon • South Carolina • Wisconsin • ETA • NCSC • Texas • Connecticut • Florida • Georgia • Illinois • Iowa • Massachusetts • Montana

  26. How to contact ARC: ALMIS Resource Center c/o Employment Security Commission Of North Carolina 700 Wade Avenue Raleigh, North Carolina 27611 Email: almis@ncmail.net WWW.workforceinfodb.org

More Related