collection-analysis documentation & resources

NOTE: This document and tool are both works-in-progress. Data in the various databases provided, and information found in this documentation is subject to change.

The interface and included data can be found here:

• https://collection-analysis.cincy.pl/

Source for these docs as well as the export / import scripts and more can be found here:

• https://github.com/cincinnatilibrary/collection-analysis

General Purpose of this Resource

This resource is provided to document the snapshot process for the Cincinnati & Hamilton County Public Library (CHPL) “current_collection” data set and to provide other resources related to the use of this tool.

Using the Data Set

The software used to power this data set is called “Datasette” (https://datasette.io/) and is currently written and maintained by Simon Willison. Datasette documentation can be found here: https://docs.datasette.io/en/latest/

To explore the data, it’s possible to use this tool to run SQL queries directly on the tables in each of the databases to explore various states of the CHPL physical collection.

For example: To find the number of total titles that have at least a single item associated with that title, you can use the following query.

select
   count(distinct b.bib_record_num)
from
   bib as b
   join item as i on i.bib_record_num = b.bib_record_num

Click to Run this query on the “current_collection” snapshot database

Below are static SQL queries for reports and analysis

Static SQL Queries & Reports

Static SQL Queries & Reports: Holds

More examples, general use-cases and miscellaneous information can be found below.

Examples and Uses

Miscellaneous Links & Information

What is Included in the Data Set?

• Snapshots of the CHPL physical collection’s metadata is done three times weekly (Tuesday, Thursday, Saturday)

There are two primary tables in each data snapshot (additional tables are also included to supplement these tables which can be found in the links below):

1. bib: Bibliographic metadata associated with a resource

2. item: Item-level metadata (such as item location, barcode, etc.)

More detail about what is included in each of the database snapshots can be found below.

Database Tables, Columns, and Definitions

Uh, OK. But, Like …Why?

Data is amazing! The ability to examine, aggregate, and transform data can give incredible and powerful insights into the large physical collection that CHPL maintains for the public.

Reports, search tools and other really interesting and useful things can be generated from this data

For Example This:

Top Circulating Titles With Subject Heading ‘dystopias’ in the CHPL Current Collection

---

• Search Page

• Database Tables, Columns, and Definitions
  • bib Table, item Table Relationship
  • bib Table
  • item Table
  • bib_record Table
  • item_status_property_myuser Table
  • itype_property_myuser Table
  • physical_format_myuser Table
  • country_property_myuser Table
  • language_property Table
  • record_metadata Table
  • bib_record_item_record_link Table
  • location Table, location_name Table, branch Table, branch_name Table Relationship
  • location Table
  • location_name Table
  • branch Table
  • branch_name Table
  • phrase_entry Table
• Static SQL Queries & Reports
  • Collection Value
  • Available Items & Circulation Information By Location at Branch
  • Lucky Day Leased Books and Leased DVDs Analysis
  • New Books List
  • Items with 0 Circulation by branch_name (including pagination)
  • Item Data Consistency Report – Excluded Titles
  • Titles with 100% items (active) checked out (by popularity–total items checkouts)
  • Percent Total Items Checked Out by Location at Branch (given branch code)
• Static SQL Queries & Reports: Holds
  • Defining “active holds”
  • Defining “active items”
  • “active holds” by title (sorted by count “active holds”)
• Miscellaneous Links & Information
  • Useful Links
• Examples and Uses
  • bib and item Tables
  • Full Text Search
  • Limit to “active” Items
  • Aggregate Counts