SQL Statements in OBI

Enhancing Analytics Reports with SQL

2:00 – 3:00, April 13, 2022

Paul Ojennus

Outline

• Limit to a day/week/month/year
• Perform arithmetic on today’s date
• Tops and bottoms
• Challenge
• Resources

Limit to a day, etc.

• SQL lets you query the processor for the current day/time with functions like CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, and NOW
• Add a column, then in the drop-down “Edit formula”
• Clear the contents and click on the f(…) 🡪 Calendar/Date 🡪 Current_Date

Limit to a day, etc.

• So here’s a quick report of a patron’s loans, the loan date, and the due date
• Then I’ve added in the current date with the CURRENT_DATE function

Limit to a day, etc.

• SQL provides functions like DATE, DAY, HOUR, MINUTE, MONTH, YEAR that extract that part of a value with the data-type “date”
• Here we’ll use DAYOFWEEK on "Loan Date"."Loan Date"
• Add the column, edit formula
• Select "Loan Date"."Loan Date"

​

​

Limit to a day, etc.

• Then add a function using f(…) at the bottom left of the textbox 🡪 Calendar/Date 🡪DAYOFWEEK
• This returns an integer 1-7 so we can use it to order our results; add an ascending sort

Limit to a day, etc.

• Add another column of "Loan Date"."Loan Date"
• Edit the formula, add a function from Calendar/Date, but this time choose DAYNAME
• Add a the column "Loan"."Loans (Not in House) " to get some data
• Add a filter for the dates you want (say, last semester)

Limit to a day, etc.

• You can tidy up by adding custom column headings, hiding the DAYOFWEEK column and reordering the columns

Arithmetic on dates

• For many reports you run regularly, you want to know “what happened in the last 30 days?” “what will happen next week?”
• To answer those kinds of questions you can get the CURRENT_DATE and perform math functions on it

Arithmetic on dates

• Use case: circulation wants to call patrons before items get declared lost
• So we need a list of items that were due 24 days ago
• We grab some information about the loan
• Then filter to Loan Status = Active, Process status ≠ Lost

Arithmetic on dates

• To get today’s date minus 24 we use TIMESTAMPADD
• Syntax is:�TIMESTAMPADD(unit, interval, date_time expr)
• For “unit” OBI uses:
• SQL_TSI_DAY
• SQL_TSI_WEEK
• SQL_TSI_MONTH
• SQL_TSI_YEAR

​

Arithmetic on dates

• For “interval” any integer, here “-24” because we want 24 days in the past
• For “date_time expr” this can be a column with the date_time data type, but here we are querying today’s date using CURRENT_DATE
• Check the “Convert to SQL” box, then “OK”

Arithmetic on dates

• Note that OBI converted “equals or is in” to SQL “IN”
• But you can rewrite this with the comparison you need, maybe I’ll change this to >= and change my interval to 23, so if circ misses a day they’ll catch them all

Arithmetic on Dates

Tops and bottoms

• The functions TOPN and BOTTOMN return the highest and lowest counts of something
• For a simple example, “What items had the most circulation last month?”

Tops and bottoms

• Add some item information like Title, Author, Description
• I added "Item Location at time of loan"."Location Name" to exclude reserves

Tops and bottoms

• Now add "Loan"."Loans (In House + Not in House)"
• 🡪 Edit Formula
• Make sure your column is selected and 🡪 f(…)
• 🡪 Aggregate 🡪 TopN

Tops and bottoms

• OBI gives you back the SQL to complete:��TOPN("Loan"."Loans (In House + Not In House)", integer)
• Replace “integer” with the number of items you want returned, like “10”
• Add an ascending sort
• Hide this column

Tops and bottoms

• Add another "Loan"."Loans (In House + Not In House)" to return the number of loans
• We’ll add a SQL expression in a filter to limit to last month
• Get the column "Loan Date"."Loan Month Key"
• Add the SQL expression:�MONTH(CURRENT_DATE)-1
• Hide columns you don’t want to see, and…

Tops and bottoms

Challenge

• Problem: Change usage statistics from abstract numbers to human readable indicators
• Model application: For a group of title-by-title journal subscriptions, we want to show what quartile each is in, so we can expand holdings with journals like those in the first quartile, and consider cancellations in the fourth quartile

Challenge

• Step 1: get a numerical value indicating the position in a list of most-to-least used journals
• Solution: use the SQL function RANK:��RANK("Usage Data Details – Release 5”.“TR_J1 – Total Item Requests”)

Challenge

• Step 2: Find the total number of journals in the set
• Solution: use the SQL function SUM:��SUM("Usage Data Details – Release 5"."Title Identifier Count")

Challenge

• Step 3: Return human readable text depending on a comparison of the RANK and SUM calculations
• Solution: use SQL CASE(IF) with some mathematical operations
• Be careful with the logic here; you can only test on the conditions > and =
• I found OBI to be fussy about some math, e.g., couldn’t divide one column by another
• You may omit ELSE, then a NULL value will be returned if none of the conditions are met
• Try adding conditional formatting so “First Quartile” is blue, “Fourth Quartile” is red, etc.

CASE

WHEN RANK("Usage Data Details - Release 5"."TR_J1 - Total Item Requests") > (SUM("Usage Data Details - Release 5"."Title Identifier Count")*0.75)

THEN 'Fourth Quartile'

WHEN RANK("Usage Data Details - Release 5"."TR_J1 - Total Item Requests") > (SUM("Usage Data Details - Release 5"."Title Identifier Count")*0.50)

THEN 'Third Quartile'

WHEN RANK("Usage Data Details - Release 5"."TR_J1 - Total Item Requests") > (SUM("Usage Data Details - Release 5"."Title Identifier Count")*0.25)

THEN 'Second Quartile'

ELSE 'First Quartile'

END

Challenge

Resources

• Alliance Analytics Training Page: Analytics Training – Orbis Cascade Alliance
• MySQL Reference Manual: MySQL :: MySQL 8.0 Reference Manual
• How to construct statements, what are the allowed values for parameters, etc.
• MySQL syntax is sometimes different from other flavors of SQL
• Oracle Analytics Server documentation: Oracle Analytics Server – Guides
• Sometimes Oracle’s implementation of MySQL has additional limitations in syntax, functionality

Thank You!

• Thanks for listening!
• Feel free to contact me anytime: pojennus@whitworth.edu