collection-analysis documentation & resources

NOTE: This document and tool is very much a work 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:

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

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” ( and is currently written and maintained by Simon Willison. Datasette documentation can be found here:

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.

   count(distinct b.bib_record_num)
   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

Click to Run this query on the “collection-2021-01-04” 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 weekly

There are 3 “levels” of collection metadata snapshots provided as distinct databases:

  1. current_collection: Most-current snapshot of the metadata.

  2. collection_prev: Previous snapshot

  3. collection-YYYY-MM-DD: Start-of-year snapshot. (e.g., collection-2021-01-04 would represent the state of the collection from the first Monday of 2021)

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.



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