2023-06-15 Reporting SIG Meeting notes

Announcements /
Reminders

Upcoming meeting topics

• Monday, June 26: Onboarding Part 2 (tentative)

WOLFcon

• 73 sessions were submitted, including something like 7-10 from reporting
• virtual attendance is free! consider registering!
• August 22-24
• Information and registration: https://openlibraryfoundation.org/about/wolfcon/upcoming/

Any new members?

• Welcome/introductions

• Representative for the Documentation Working Group
• Query developers
• Members of the FOLIO Analytics Review Board
• Members and possibly a leader for monthly MM/RA/UM development subteam
• Co-convener

• FLO Implementation journey (2020 to now)
• Fenway Library Organization (FLO) has 10 libraries
• Simmons transitioned from Millennium to FOLIO first in 2020; New England College of Optometry just went this week; everyone else will be transitioning this fall
• The Voyager libraries have been using MS Access for reporting, are used to having a visual query builder, don't really have to know SQL
• Millennium also has a non-SQL reporting option, so really no one was using SQL
• Back in 2020, were looking for various options; tested DBeaver, Toad, MS Access; only DBeaver really worked
• DBeaver enterprise (paid version) has a visual query builder, there are more export options, and there are more granular permissions; but you do have a cost, there aren't that many differences from the free version, visual query builder isn't completely reliable and can't get too complex with it, also need IP authentication
• have since stopped paying for enterprise, just use the free version now (DBeaver community)
• even the community edition lets you use dynamic parameters; instead of just writing normal SQL, you can use a syntax that lets you clarify that a parameter belongs in a certain part of the query (:parameterName)
• Complaints about DBeaver
  • no "Save As" button
• Also now using CloudBeaver (see a demo version), which is a browser version of DBeaver; can send people a link, they don't have to install it themselves
• For every person using Desktop version of DBeaver, had to send in the IP address for each person so Index Data could allow access to that IP address; this way we can just set up accounts for them. Unfortunately, everyone sees everyone else's databases, because there is one Cloud Beaver for all FLO libraries.
• Downsides to CloudBeaver - the screen is a lot smaller, scripts panel is on the right instead of the left.
• For user management, decided to give each library an admin account and a staff account. Admin is full access, staff is read only. Staff can create a new query but can't save it in the cloud, would have to save it locally. For staff accounts, you can limit access to some of the databases. But admin accounts see everything.
• Before CloudBeaver, the main way to share SQL would be to email it to people. Here we can write queries for people and they just get access to it right away and can run it whenever they want. Each query also remembers which connection it is designed for.
• Otherwise, looks pretty much the same as DBeaver. There are a lot of export features, including Excel, even though this is the free version of CloudBeaver.
• Unfortunately can't use the dynamic parameters in CloudBeaver. Can change the name when you save, but still have issues getting it into the right folder, have to take an extra step for that.
• Simmons has been trying out CloudBeaver. As they bring in SQL queries from local machine, there isn't an easy way to do that, just creating new queries one at a time.
• Process for the 2020 migration: collect existing report needs in a spreadsheet, create a Google doc with the SQL of the reports organized by category
• For Simmons, couldn't get all of their data into FOLIO, so created a local table for legacy Millennium data, and documented how that was done; also exported data into a file that could be imported directly
• CloudBeaver also auto-saves; doesn't seem to be a version history for reverting
• For legacy data table, Simmons sent along a data file, and Eliana had to massage it into an "INSERT INTO" SQL file; since it's in local schema, it also shows up in the LDP app; do want to make sure there is a connection between the legacy data and FOLIO data
• Don't do any scheduling of reports right now, but probably will need that
  • Will CloudBeaver schedule things? probably not, but should check enterprise edition
  • Desktop DBeaver Enterprise can schedule using database tasks
• Likely to have FLO people writing queries, but also people can write their own queries