Page History

...

TABLE OF CONTENTS

Circ Snapshot Script for LDP

The first step in the process is to create the circsnapshot4.sql table in the LDP, which is where data updates will be appended and stored. Run the create_circsnapshot4.sql query to create the circsnapshot4 table in the local_core schema on the LDP. This query (run just once) finds all records in the loans_items table where the user_id is not null, and gets the demographics associated with that user via the user_users table and the users_departmnts_unpacked table. This makes the a starting table that is updated by the update_circsnapshot4 query daily automatically.

...

Here is a screenshot of the local_core.circsnapshot4 table in the LDP. 

Automation for LDP script

The update_circ_snapshot4 script for LDP is scheduled to run daily at 8am

Testing LDP script Results

Use this query to make sure the circ_snapshot4 table in the local_core schema is getting updated daily at 8am.

select 
sm.extract_date::timestamp,
count (sm.loan_id) as count_of_loans

from local_core.circ_snapshot4 as sm
group by sm.extract_date::timestamp
order by sm.extract_date::timestamp desc

Circ Snapshot Script for Metadb

The circsnapshot for Metadb uses the table that was originally created in LDP to store additional updates to demographics information needed for certain circulation queries. This table was exported from the LDP and imported into the local_shared schema in Metadb. In the data import process, column data types were set to match the data types in the equivalent LDP table. 

...

The daily_update_circ_demographics_metadb task is set up to execute the update_circsnapshot4.sql query daily at 7am, 11am, 2pm, 5pm, and 9pm. The update_circ_snapshot4_metadb.sql query (in the LOCAL_AUTOMATED folder in DBeaver) runs the following code on Metadb using the z_slm5 user account, which uses the INSERT function to get the new checkouts and add them to the sm_local_shared.circsnapshot4 table daily automatically. The sm_local_shared.circsnapshot4 table is used in circulation queries to generate reports that need the PII data that is removed everyday from circulation data in the FOLIO system. . The automation must be set up with a reporting team member account permissions to have write access to the local_shared schema, and specifically to update the sm_local_shared.circsnapshot4 table.

The metadb version of the query was revised to NOT use derived tables. This will ensure the retrieval of all changes to records when the update query appends new data to the local_shared sm_circ_snapshot4 table.

...

Here is a screenshot of the local_shared.sm_circs_snapshot4 table in Metadb. 

Permissions for Circ Snapshot Metadb Script

Reporting team member user accounts have read-write access to the sm_circsnapshot4 table. The z_metadbuser_cornell user account has read-only access to the sm_circsnapshot4 table.

For instructions on setting permissions, see Setting Permissions for Schema Tables in Metadb. 

Automation for Circ Snapshot Metadb Script

The update_circ_snapshot4_metadb.sql query is automated to run daily at the following times:

• 7am
• 11am
• 2pm
• 5pm
• 9pm

Testing

...

Results for Circ Snapshot Metadb Script

To test that the sm_circs_snapshot4 file is getting updated, run the query below. You should see daily updates to the file in the extract_date column, and a loan count total in the count_of_loans column.

...