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:
SQL: Learning SQL, Alan Beaulieu, OReilly, 2005 (Readers on Amazon preferred this to SQL for Dummies, however examples are in MySQL.) SQL for Dummies, Allen G. Taylor, For Dummies, 2003 Joe Celko's SQL for Smarties, Joe Celko, Morgan Kaufmann, 2005 Databases: An Introduction to Database Systems (8th Edition), C. J. Date, Addison Wesley, 2004. Database in Depth, C. J. Date, OReilly 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, 2000 Refactoring Databases: Evolutionary Database Design, Scott Ambler, Addison-Wesley, 2006 Fundamentals of Database Systems (5th Edition), Elmasri & Navathe, Addison Wesley, 2005 (Highly theoretical, not for the faint of heart, this is more of a reference for Computer Science students.) Online Resources: www.dbazine.com www.databasejournal.com www.orafaq.com (Oracle specific with some general information) 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. 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. 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 80% of the states running the WID use MS SQL Server, the other 20% use either Oracle, Visual Foxpro 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: 1) Lookup tables, crosswalk table and national data such as the CPI can be obtained from the National Crosswalk Center, www.xwalkcenter.org. 2) 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, www.bls.gov. 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
How often do we update Crosswalk data in the ALMIS 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 MATXSIC are the currently active ones) are revised every two years when new projections become available. We expect to have some content for the MATXNAIC table when the new national projections are released next February. Other classification changes that will probably cause crosswalk file revisions include the release of the 2000 Classification of Instructional Programs (CIP) and 2000 Census occupations. 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.
How often do we have to update O*NET data in the ALMIS Database?
Relatively little O*NET information is included in the ALMIS Database (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 ALMIS Resource Consortium has designed O*NET table structures that are compatible in format and content with the ALMIS Database, so that the ALMIS
Database won't require changes when the O*NET database changes. For links to O*NET
databases that can be used with the ALMIS Database, visit the National Crosswalk Service Center's non-standard ALMIS Database page at
http://www.xwalkcenter.org/almisdb/nonstd.html
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 ALMIS Resource Center and the ACINet licensing application (http://www.acinet.org/acinet/lois_start.asp) 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 ALMIS Database page at http://www.xwalkcenter.org/almisdb/nonstd.html
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 ALMIS 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 '2002' a default value taken from the national Current Employment Statistics (CES) files distributed by the Bureau of Labor Statistics.
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.
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 ALMIS 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. The next issue of ALMIS Database News will discuss the steps necessary for transforming the historical CES data into an ALMIS Database format.
I am having difficulty finding data for some of the tables. Is there any reference developed to assist in the hunt?
An ALMIS Data Tables Suggested Sources document was developed by Iowa and can be viewed in both HTML or Word formats.
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 ALMIS DBAs. There are several approaches you could take. First of all, you need to ascertain what is causing their reluctance to share. If its 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 ALMIS Database 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 ALMIS Database is just not important and they are too busy then perhaps show them a copy of the PY 2002 State Workforce Information Core Products and Services Training and Guidance Letter (TEGL) from ETA that lists the ALMIS Database and identifies several core tables as deliverables. Their reluctance may be because they do not have a grasp on what the ALMIS Database 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. If you would like to make a more formal presentation to staff or customers remember the resources that are available to you on the ALMIS Database Web site: www.almis.dws.ut.us. There are several customizable downloads available for your use including two PowerPoint presentations and a brochure. If all attempts fail then you have two options left: the manager and/or food. Chocolate usually works best. Good luck.
I am trying to convert my database to Version 2.2, however, the lookup tables for NAICS codes do not accommodate supersectors. I have programmers waiting for a resolution so we can revise our data delivery system for aggregated NAICS NAICS-based employment data. What is the Consortium doing to fix this issue?
The structure committee has developed a resolution to this problem. An explanation of the resolution can be found in the ALMIS2-2_Addendum.zip file that contains structures for additional tables developed to include the Bureau of Labor Statistics supersectors and domains. [need to link to the ALMIS2-2 Addendum.zip]
In clsxproj table, the OES code field is still five characters. There are OESX crosswalks that need to be updated to SOC: oesxcens, oesxcids,oesxcip, and oesxjob. Have these lookups been built?
There are a number of issues here. The consortium has developed structures and some content for crosswalk files in which SOC occupations replaces the older five-digit OES occupations. The file structures and a Visual FoxPro database are available for downloading from the NCSCs Non-standard ALMIS Database Information page at http://www.xwalkcenter.org/almisdb/nonstd.html. The NCSC is updating the units of analysis files that are the basis for the CLSXPROJ, CLSXSUPL and ANALYSIS tables. The first stage involves updating occupations to the SOC and SOC-based projections and updating content for the ANALYSIS table. A subsequent project will update the training programs codes to the 2000 Classification of Instructional Programs.
In the propval table, a mill rate means per 1000, but the field in this table is
named rateper100 with mill rate as the description. (rateper100 number(6,4) mill rate) Is there a typo in this field name?
It is more of a field misname and is being addressed. The value entered in the table should be in thousands.
Are all state's using the ALMIS Database?
Yes. The Workforce Information Core Products and Services Grant (PY 2002) 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 ALMIS Database Maintenance Consortium. The grant also specifies that grantees are required to deploy applications that allow electronic public access to the state ALMIS Database 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 ALMIS Database is essentially standard across the board. If youd like to read more about what is specified as a core deliverable under the grant you can find the current program year 2002 grant online at http://wdr.doleta.gov/directives/ (select TEGL-Training & Employment Guidance Letter).
Is there a central location where states are supposed to send their ALMIS Database data?
No. All states are required to create and populate their own ALMIS Database in whatever platform they choose (e.g. SQL Server, Oracle, etc.). However, the only exception at this point is licensed and certified occupational data that is stored in the license, lichist, 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 at CareerOneStop.org.
What am I supposed to populate the URL table with and why is it a deliverable in the Workforce Information Grant from ETA?
That is a great question. If you look at the URL table in the ALMIS Database Data Dictionary it says :This table contains a unique listing of uniform resource locators [URLs]. Looking at the Data Sources document http://www.xwalkcenter.org/almisdb/adbsource/almissource.html on the National Crosswalk Service Center (NCSC) Website it says: States are encouraged to build meaningful URL tables. In the Program Year 2000 Workforce Information Grant states were asked to:...populate the URL table which identifies State web linkages having resources which may be of interest to our customers. Yes, the definition is a little vague but it allows for states to use the table in a manner that meets their individual needs. Possibilities for use include application development that uses the URL table as a source for extensive Web page links.
The ALMIS Resource Center is currently developing more in depth information to help guide you on populating this table, how it can be used, and why it is a deliverable. Look for that information in the next ALMIS Database News.
Additionally, the NCSC (starting with a file developed by Mike Blank of the Iowa Workforce Development) now maintains a URL file containing state Labor Market Information Web page addresses. The addresses are tested and updated monthly. You can download either url.dbf or url.txt from ftp:ftp.xwalkcenter.org/almis/national.
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.
In Version 2.2, the field value for aggregate in the Ownership table has been changed from 60 to 00, although there is no indication of the ownership lookup being changed in the change document. Is 00 a typo?
No, 00 is not a typo. This change was done in a consistency cleanup. For those of you whose states are using WIN202 for their Covered Employment and Wage program, the aggregate ownership data are 00 coming out of WIN202 so you need to use 00 instead of 60 in your lookup.
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..
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).
How do I use distinct to get all fields of a table?
SELECT DISTINCT tablename.*
Are States allowed to make manual changes to the ALMIS Employer Database when an error is discovered in the database, i.e. phone number, address, etc.?
We recommend that States do not make manual changes to the ALMIS Employer Database. Changing a data element within the ALMIS Employer Database leads to questions such as how the changed database stays current; does the changed record require infoUSA attribution (on the non-changed elements) and how does a user know the difference? We therefore recommend that States who discover errors in the ALMIS Employer Database report those to infoUSA on the following email address: employer.database@infousa.com infoUSA would then take the change and process the change through their data compilation procedures (including telephone verification). It is then very likely the change will be reflected in their next update of the ALMIS Employer Database.
A number of different versions of the ALMIS Database structure document refer to ALMIS ABI Empdb file in relation to the Employer Database and to ABI Address Types. Is an ABI code included in the database? And, is it the same as the EIN?
Although there are references to ABI-specific variables in different versions of the ALMIS Database structure document, we found no reference to an ABI code. ABI refers to American Business Information, a trade name used by InfoUSA, the vendor for the ALMIS employer database. At one point, the employer database was also informally known as the ABI employer database. Most likely, a reference to an ABI code refers to the table's UNIQUEID variable. The value of this variable is assigned by InfoUSA and is the table's primary key variable. Since the contents are developed by the vendor, they have no relationships with identifiers developed by other entities, such as the EIN.
Are there different levels of usage?
Yes, there are three specifically defined levels of customer access to the data. They are: primary recipient, intermediate user, and client/end user.
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 about April 1, 2004 and was in the previous format you have received in the past. Each state should have received 3 sets of the CD-ROMs.
I heard that States will be able to order extra copies of the CD-ROMs for no cost. Is this true?
Yes, except for the first deliverable in April 2004. For this first deliverable, only those States which have purchased additional sets previously from infoUSA will be receiving the free CD-ROMs. In October 2004, all States will then have the opportunity to order additional copies of the CD-ROMs at no cost. Instructions on ordering these additional CD-ROMs will be provided later this summer.
Can I order no-cost CD-ROMs anytime during the year?
No-cost CD-ROMs will be ordered a month before each deliverable and shipped free of charge in April and October. CD-ROMs ordered at any other time will be shipped at the licensee's expense.
What are the "uses" valid for the ALMIS 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 licensees enhancement of existing employer name and address files) and related public workforce 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 systems. The output can be used to generate mailing lists used for these purposes.
Who can use the ALMIS Employer Database?
Users of the AlMIS Employer Database shall include: Individual job seekers, One Stop Career Centers, Workforce Development Boards, the public job service, public welfare agencies, State employment statistics agencies as defined under Section 309 of the Workforce Investment Act of 1998 and any successor legislation, all mandatory and option partners described under the Workforce Investment Act of 1998, Chapter 3, Section 121, (c)(2)(b) and any successor legislation, and other partner agencies; and the Employment and Training Administration of the United States Department of Labor. The ALMIS 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 April 1, 2004 delivery, the database will be updated and delivered at six month intervals.
What ALMIS 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 ALMIS 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 ALMIS Employer Database in libraries?
Yes, for the approved uses only. Persons using the ALMIS Employer Database in public, college, or university libraries will have End User access only through the Internet.
Can a "licensee" lose the privilege of using the ALMIS Employer Database?
Yes, by not complying with the requirements set forth in the License Agreement (Section 3, Grant of License) that each state and territory will sign. Non-compliance could also jeopardize the Master Contract.
What are the limitations for the "end user" (an individual engaged in job search or, career exploration)?
End users may receive a display of single line records of which not more than 25 records can be viewed or printed at one time. End users may also print the data elements up to five records at a time and email one record at a time (per single print/email request). Such usage is for job search and career exploration.
What are the limitations for the "intermediate user" (local One Stop or other non-client user)?
After completing an initial search, Intermediate users may retrieve and display an unlimited number of single line records of which not more than 25 records can be viewed at one time. They can also print or download up to 50 records at a time per single print or download request. Usage is only for uses licensed by the contract.
What data elements are included in the ALMIS Employer Database?
The ALMIS Employer Database contains over 40 data elements including:
Employer Name Business Description
Mailing Address Employment Size Range
Telephone Number Private/Public Status
Contact Name Annual Sales Range
Industry Code (NAICS & SIC) Internet Address
Have I got time to prepare for the new CD-ROM and Flat file layout applications?
Yes, that is why the first deliverable (April 1) is under the old format. On July 1, 2004 each eligible licensee will receive an uncompressed (flat file) on DVD in the new format and containing the new data elements negotiated under the new Iowa contract. This uncompressed database is being provided to help licensees begin testing their applications using the new data format. In addition, the Iowa Project Manager will receive 5 (five) silver masters of the CD-ROM application for validating that the CD-ROM complies with the terms of the Iowa contract. ALMIS is authorized to make copies for each licensee for the licensees use in conducting training sessions in preparation for the October release of CD-ROMs in the new format.
Will the Employer Database extract require any manipulation by a DBA to populate the EmpDB table in ALMIS Database?
The EMPDB table in the ALMIS 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 I use the ALMIS Employer Database for a business marketing prospect list or economic development?
No, the license is not granted for these uses.
Can other private vendors link their websites to the ALMIS Employer Database?
No, a private vendor would not be an authorized user of the ALMIS 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., National Systems and Research Co., 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:
Daniel Brown
Phone: 515-281-5093
Fax: 515-281-8203
Email: daniel.brown@iwd.state.ia
Project Administrator is: Judy Erickson, LMI Bureau Chief Phone: 515-281-3439 Fax: 515-281-8203 Email: Judy.Erickson@iwd.state.ia Who do I call if I have problems with the ALMIS Employer Database?
In the case of problems, infoUSA has furnished the following Phone Number: 800-555-5211 and Email: employer.database@infousa.com
Ph: 800-555-5211, Email: employer.database@infousa.com
|
|---|
