Versions Compared

Old Version 26

changes.mady.by.user user-815f5

Saved on

compared with

New Version 27

changes.mady.by.user user-815f5

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

__________________________________________________________________________________________

Anchor
intro
intro
A. Introduction

Library Technical Services tracks and compiles production statistics at the department, unit, and individual levels. This information is important for individual employee performance evaluation and management, and for short and long term planning at both the unit and department levels. Information from 948 fields is harvested and processed to determine what type of work was done to a record, on what day, and by whom. An Access database was built to query this data and create reports for individuals, units, and the entire department.

...

Additional specialized reports have been written in a separate database to gather information regarding batch processing production in LTS. These statistics are compiled by the Batch Processing Supervisor for each fiscal year.

Anchor
connect
connect
B. Connecting to the Technical Services Statistics Database

Note: If you need help with the following set-up, please send a message to CUL Desktop Support at cul-dshelp-l@cornell.edu.

  1. Download and install the MySQL ODBC 5.1 driver. The installer is available on the LTS share, in "netadmins\Software\MySQL." Also, either create a desktop shortcut to the database, or map drive to it. The database is found at \\files.cornell.edu\lib\ds-deptshares\LTS\tsstats\1New_culv2-2012-2013.accdb.
  2. Ask Amy Blumenthal or Chris Manly for the database password.
  3. Configure the driver, using the 32-bit ODBC Administrator, here: c:\windows\syswow64\odbcad32.exe. (Note: Do not use the ODBC Administrator in Control Panel/Administrative Tools.)

Anchor
basic
basic
C. A Basic Guide to the Database

  1. Go to \\files.cornell.edu\LIB\ds-deptshares\tsstats (Probably mapped on your computer as L:\tsstats)

  2. Open the file named 1New_culv2-2012-2013.accdb

  3. You should see the following sidebar on the left side of the Access window:

...

vii. The program has saved your report as a .pdf file in your chosen folder and has opened it in Adobe Acrobat for you to review. If you wish to print it at this time, click the "print" button on the toolbar. Now that you have saved the report as a .pdf, you may retrieve and print it at any time. 

Anchor
desc
desc
D. Brief Description of Each Report

  • "Enter OPID" is a sub-function of these other macros. You will never need to select this; it's built into any staff member report you run.
  • "Enter Start and End Dates" is also a sub-function of these other macros. You will never need to select this; it's built into every report you run.
  • Run “Dept Summary Report” if you want a summary of all work done in LTS for the desired date range.
  • Run “Individual Report” if you want to extract a summary of all work done by a specific staff member during the desired date range.
  • Run “Staff/Bib ID Report” if you want to extract a list of all bib IDs worked on by a specific staff member during the desired date range.
  • Run “Law TS Summary Report” if you want a summary of all work done in LAW TS for the desired date range.
  • Run “BATCH Summary Report” if you want a summary of all work done via batch processing for the desired date range.
  • Run “NON-BATCH Summary Report” if you want a summary of all work done by non-batch processes for the desired date range.
  • Run "Maintenance Summary Report" if you want a summary of all maintenance processes for the desired date range.
  • Run "Transfers Summary Report" if ????????
  • Run "Unit Reports" if ???????

Anchor
unit
unit
E. Using the “Unit” reports in the TS Stats Database

  1. Go to L:\tsstats and select "1New_culv-2012-2013.accdb." Make sure that the "Macros" menu is showing. If it does not say "Macros," click on the little circled arrow and select "Macros." Click on the "Unit Reports" macro.

  2. Enter the desired date range in the boxes and click "Ok."
  3. This dialog box will most likely appear (below). If it does, you must click YES to clear the data from the last time someone ran this report for their unit.
  4. Next the macro will ask you to enter the name of your unit. (See below.) You may enter it in full, as rendered in the Unit ID/ Unit Name table shown below, or type the first three letters of the name as shown in that same table (case sensitive); the script will then automatically choose the corresponding unit name. After entering the name, click “ok” to run the query and return a report.

         

...

5. The query will now run. It may take a minute, since it’s parsing so much data. When it completes, it will return two reports: The “Unit Summary Report” and the “Unit Detail Report.” The Unit Summary Report returns a communal tally of all the work done by the unit, while the Unit Detail Report breaks out the same information by staff member. In the Unit Detail Report, each staff member’s statistics for the date range are arranged on a separate page, with the very last page showing the total number of records processed by the unit as a whole.

Anchor
batch1
batch1
F. Using the Batch Stats Database

Scope: The purpose of this Batch Stats Access database is to account for work done by LTS staff using Gary Strawn’s Location Changer and Record Reloader programs. Records altered using these programs are not given 948 field by which we can tally them in the TS Stats database. Appropriate staff members have been given individual “batch” log-ins to use when running Strawn software; these ID’s show up in the “Operator_ID” field in the Voyager tables. This database queries and reports this work, using these ID’s and other parts of the records. Please note that there is a query and report in the TS Stats database for those batch jobs which are processed using other tools and therefore contain 948 fields by which to count them. Together, the information from the two reports account for work done in batch form by LTS staff.

  1. Go to \\files.cornell.edu\LIB\ds-deptshares\tsstats and open the .mdb file called “3_BATCHstats.mdb.”
  2. At the right side of the screen, double-click on the macro called “1 – Batch BIB Summary Report.” (see below)

  3. The following window will appear (below). Enter the starting and ending dates for the time period for which you wish to collect data.

  4. If you haven’t already been in an Access database for Voyager during your computing session, Access will ask you for the VGR7 username and password (dbread, dbread). (see below)

  5. The script will start to run a series of queries. When it is done, it will open the report containing the data for the date range you entered. (See next page for screenshot.) NOTE: If you want to save a copy of your report, you’ll need to right click on it and Export it as a PDF. If you or someone else runs the query before you’ve done that, your report will be overwritten and you will have to run it again (and export it immediately) in order to save the information it generated. Here's a sample report (below). See also the annotated version, after this one.

Anchor
batch2
batch2
G. How to Compile Batch Processing Statistics for the Fiscal Year End

These statistics must be gathered from various Access reports, from multiple databases. Some statistics are required for the department-wide spreadsheet that is shared with Library Administration; some are for inter-departmental use.

  • The LTS Stats Database can be found at L://tsstats/1New_culv2-2012-2013.accdb. Queries to be run in this database:
    • Batch Activity Summary Report
    • Record Enhancement Report
  • An additional database, 3_BATCHstats.mdb can be found in the same folder. Queries to be run in this database:
    • Batch Activity by OP ID Report
  • A query in LS Tools, found at Administrative Procedures/TS Stats Summary, must be run for each month of the FY.
  • Spreadsheets used for this process:
    • 3_Batch_Stats_FY15_16.xlsx (found at L://tsstats)
    • LTS_Stats_2005-2016.xlsx (found at L://tsstats)
    • BATCHID_Stats_FY15_16.xlsx (found at L:\Batch_Processing\BatchIDStats)
    • VoyagerExtracts2015-16.xls (found at L:\Batch_Processing\LoadExtractLogs)
  1. Batch Processing Statistics Requested for Department Spreadsheet (LTS_Stats_2005-2016.xlsx):

    New records (print)

    New records (e-journals)

    New records (e-books)

    Records exported

    Records enhanced

    Headings maintenance

    Batch jobs

  2. How These Statistics Are Gathered:

    A. “New records (print)” – This information is found by running the “Batch Activity Summary Report” in the LTS Stats database. The total consists of “total Approvals input” + “total Books input” + “total NPAC input.”

    B. “New records (e-journals)” – This information is found in the “Batch Activity Summary Report” in the LTS Stats database. The “Catalog/Format/e-Resources (journals) total” is the desired total.

    C. “New records (e-books)” -- This information is found in the “Batch Activity Summary Report” in the LTS Stats database. The total is derived by adding “Catalog/Format/Computer files” total + “Catalog/Format/e-Resources (books)” total + “Catalog/Format/Sound recordings” total.

    D. “Records Exported” – The information for this total comes from the spreadsheet found at “L://Batch_Processing/LoadExtractLogs/VoyagerExtracts[FYdates].xls.” The total for the FY is derived by adding together the totals from the following sheets in the document: “Batchmatch (MARCadia)” + “OCLC Archives & Man. Records” + “OCLC Institutional Records” + “OCLC MARC Holdings” + “OCLC Daily Export.”

    E.“Records enhanced” – This information is found by running the Record Enhancements query in the database 1New_culv2-2012-2013.accdb. Includes info for WorldShare, Batchmatch, ebooksenhanced, and all “Update Backlog Records” (storage items) jobs. This query is run against the Batch_Stats table in the built by PH’s job in LS Tools, mentioned in H, below.To get the total number of records enhanced, run “Record enhancements” query and then run “Get Records Enhancements Grand Total” query. The resulting table will consist of one number: The total number of records enhanced in the FY. (Note: You will need to go into first query, "Record Enhancements" and edit to limit for specific FY.)

    F. “Headings maintenance” – Batch Processing staff have each been given an additional Voyager sign-on, to be used when they are running batch processes using Gary Strawn’s LocationChanger or RecordReloader. These sign-ons take the form of “batch[netid]” and will show up in the record histories of any records edited in this fashion. A separate database called “3_BATCHstats.mdb,” a set of queries, and a report called “Batch Activity by OP ID Report” have been created to harvest this information. For this specific line item in the department spreadsheet, information will be gathered by running the “Batch Activity by OP ID Report” and using the total of updated records for the operator id of “batchjm17,” “batchstr1” and “GLOBAL”.

    G. “Batch OP ID Stats” – This information will be gathered by running the “Batch Activity by OP ID Report”. (See also F, above.) Statistics for all batch operator id’s will be gathered for interdepartmental use and entered into the spreadsheet, “BATCHID_Stats_FY15_16.xlsx” found in L:\Batch_Processing\BatchIDStats.

    H. “Batch jobs” – LTS managers would like to determine how many batch processes are run each month. Additionally, Batch Processing unit managers would like to know how many records for each vendor/collection are processed. Not all of this information can be gathered via the existing LTS statistics database, so Pete Hoyt built a job in LS Tools to gather the data. (See query spec, “BatchStats_countXandH.docx”). The job is found under “Administrative Proc” in LS Tools, and is called “TS Stats Summary." The data from this job should be entered into the spreadsheet “3_Batch_Stats_FY15_16.xlsx” found in L:\tsstats\. (Get count from adding together “#of jobs run” from each page of 3_Batch_Stats_FY15_16.xlsx. This grand total will be entered into the “Batch jobs” line item in the LTS_Stats_2005-2016.xlsx spreadsheet.)  (Do we want to create a report for this data that presents it in some way other than monthly breakouts?)

    I. “Fastshelve” – In 1New_culv2-2012-2013.accdb there is a make table query called “Find fastshelve” that can be run to gather bib-level statistics on the number of records fast-shelved by LTS staff. Criteria for the date range and the name of the output table must be manually changed each year.