Sierra API
Visualized Data Analysis

2017 SCIUG Conference at Chapman University
(Oct. 10)

Presented by Seong Heon Lee, Systems & Technology Librarian Hugh & Hazel Darling Law Library, Chapman University

Final Product Preview

Circulation Transaction Report

Why (1)?

Sierra has Web Management Reports

  • How many of you are using WMR?
  • What is your user exprience?
  • Decision Center

Why (2)?

Who uses what?

  • Compare resource usages among libraries
  • Compare by other criteria:
    • locations
    • patron types
    • item types
    • transaction times
    • etc...

Why (3)?

Why not?

  • Plenty of data visualization tools
  • Sirera offers APIs (Direct SQL & REST API)
  • Customize a data report as you wish

Exploring data


  1. Plotly (Online & Offline)
  2. Sierra SQL API
  3. Some Examples (Sierra & Plotly)
  4. Jupyter & Data Analysis

What is Plotly?

  • A data analytics and visualization tool
  • Charts & Dashboard
  • Online and Offline
  • Open Source
  • D3.js(SVG) and (WebGL) for web graphics
  • Great API Documentation & Examples

Plotly Online

Plotly Offline (1)

  • Support multiple languages (Python, R, MATLAB, JavaScript, etc)
  • Python Library
  • Plotly Offline Setup
  • Make the "first" offline chart (as a html file)
  • Interactive: display modeBar
  • How to embed charts in a html?

Plotly Offline (2)

How to embed charts in a html?

  • offline library: output_type option (file, div)
  • a template engine (Jinja2)
    • Output Div => Template
  • a template html
  • an embed example

Plotly Offline (3)

Plotly: Summary

  • Creating different types of charts
  • Interactive
  • Data, Plot, Attributes
  • Data from file, url, db connector
  • Online & Offline (output_type, offline.plot())
  • Template engine (Jinja2) and a template html

Sierra SQL API

  • PostgreSQL relational database
  • Sierra_view schema & 349 tables (read only)
  • Special permisson to "Sierra SQL Access" required
  • Access to:
    • bibliogrphical data (bib, item, holding)
    • transactional data (circ_trans, fine, patron)
    • system parameters (location, custom codes, loanrule, properties)
  • By default, 5 concurrent connections per user

PGAdmin, SQL Client

  • Version 3 or 4
  • Connect to database
  • Run SQL queries
  • Show results in tables
  • Creating/testing SQL queries

Setup PGAdmin

Know Sierra Database

  • SierraDNA
  • Learn the structure of Sierra database
  • Category links and search box
  • Detailed table view
  • ERD (Entity Relationship Diagram) view
  • Example: Transactions > Circulation > checkout

Find Checkouts

(PGAdmin Demo)

					-- Search for checkedout
					SELECT pv.home_library_code, pv.ptype_code, c.checkout_gmt, c.renewal_count, pv.barcode, c.item_record_id 
					FROM sierra_view.checkout AS c
					JOIN sierra_view.patron_view AS pv ON = c.patron_record_id
					WHERE NOW() - c.checkout_gmt <= interval '1 hours'
					AND pv.home_library_code = 'lhome'

Plotly & Sierra

How to use Sierra data in Plotly?

  • Re-use Plotly offline script
  • But, with Sierra data
  • Three things:
    • get Sierra data (connection and query)
    • transform Sierra data to plotly data
    • generate a Plotly chart

SQL Output

Plotly X Y Axes

Embed Sierra Charts

Can we embed multiple charts in one HTML page?

  • Use the same SQL output from circ_trans
  • Run data transformations on each column
  • Pltoly option: output_type = div
  • Pass the chart outputs to the template engine
  • Prepare a template html that presents multiple charts

Plotly Offline (3)

Interactive Program

Can we input specific options?

  • Python sys.argv - read user inputs from terminal
  • User Inputs:
    • days (10 => 5)
    • transaction types(o i f r = > o i)
    • ouput filename
python 5 oi sierra_interactive.html

Sierra SQL API & Plotly: Summary

  • PGAdmin to build SQL commands
  • SierraDNA to learn Sierra Database
  • Plotly's Python offline with Sierra data
  • Database adapter to connect (psycopg2)
  • Data transformation & template
  • Interactive program

What is Jupyter?

  • Web application with its own host
  • Open Sources Project of Ipython
  • Ipython: Interactive Python Shell
  • Data science and scientific computing
  • Interactive data visualization
  • Share code, data, plots, and explanation
  • Publish in pdf, html, slide, and more
  • Documentation

Try Jupyter

How to Use Jupyter on Your PC

  • Install Anaconda 3.6 (Link)
  • Open the anaconda terminal
  • Type "jupyter notebook" and Enter

Plotly Offline & Jupyter (1)

  • Re-use the offline Plotly codes
  • Three tweaks:
    • Plotly.offline.iplot
    • init_notebook_mode(connected=True)
    • No output filename
  • More Info

Plotly Offline & Jupyter (2)

Three ways to use Plotly on Jupyter
  • Write a script on a cell and run
  • Import a script as a module
  • Use a magic function %run with user inputs

Plotly, Sierra SQL, and Jupyter: Summary

  • Plotly: View
  • Sierra SQL: Data
  • Jupyter: Communication

What kinds

business and collection analytics
do we need?

Library Business & Data Analytics

  • Possible to create many analytics tools
  • Re-run or auto-run
  • Communicate on a Jupyter
  • Examples:
    • Expired Patrons with Checkouts
    • InterLibrary Loan Map:
      • ILL lendings in the last 100 days
      • ILL partners


  • More data from Sierra:
    • WebPac usages (search, download)
  • Non-Sierra data:
    • EzProxy
    • LibGuides
    • Library building usage (gate counter)
    • Digital repository system
    • And so on...

What is the next?

[ Library Data Group ]

Seong Heon Lee,

Thank You.