Prioritize Reports
Report prioritization involves members of the Reporting SIG and their institutions. Initial priorities were included by institutions in the Reporting SIG Master Spreadsheet. Since transferring all reports to JIRA, institutions have been maintain rankings in JIRA instead.
Select Report to Prototype
A list of high priority reports is created by considering institution rankings of reports, current FOLIO development and documentation, breadth of coverage of previously prototyped reports, and feedback from Reporting SIG members. The list of high priority reports is then reviewed by Report Prototype Working Group members, who volunteer to prototype individual reports.
Write Report Specification
Create Report Wiki Page
- review report details, including data elements and report requirements, using the Reporting SIG Master Spreadsheet and the corresponding JIRA issue for the selected report
- create a name for the report and one or more queries that will be required for the report (see Naming Conventions)
- create a wiki page for this report, using report name
- include a link to the JIRA issue for the report
- toward the beginning of the page, explain the purpose of this report
- include report name and names of all queries and describe any difference between queries
- add narrative text as needed, explaining the steps for the developers
Identify Data Elements
- review report details on the Reporting SIG Master Spreadsheet and the corresponding JIRA issue
- Note: if working on a report cluster, or if the report has variations listed, make sure to identify data elements required across all queries
- for any fields mentioned in the issue or present in a sample report, locate an appropriate match in the FOLIO source data
- go to https://dev.folio.org/reference/api/
- scan the modules to find the general area that is most likely to include the data element
- use only "-storage" modules
- when you find a likely module, click on "View 1" or "View 2" for the description in the API reference documentation
- In the View pages, you will see a list of paths to the endpoints underneath that module. For example, under the "loan-storage" module, you will see several endpoints: loan-storage/loans, loan-storage/loans/{loanId}, etc.
- For the endpoints that contain data we'll be using in the LDP, you should see two very similar paths. One has nothing at the end (e.g., "loan-storage/loans") while the second should have an identifier at the end of the path (e.g., "loan-storage/loans/{loanId}"). You want to focus on the second path, the one with the identifier in curly brackets at the end.
- Click on either the "GET" or the "PUT" link attached with that second endpoint path. If you use "GET", you will find the data element documentation under "Response." If you use "PUT", it will be under "Request."
- find the correct element, and note the full path to the data element
- if you have trouble understanding the data elements:
create a JIRA issue linked to FOLIO-1551 for each data element in the FOLIO source data that is missing documentation or needs additional documentation; see examples in the "relates to" section under this epic
David Crossley has created a guide for FOLIO Developers to use to write FOLIO attribute documentation at https://dev.folio.org/guides/describe-schema/
for information from POs on data elements, you may refer to : https://docs.google.com/spreadsheets/d/1B924D2JaFUc6dqgItyKoOFdhn36lxwT6jaYmKpyk2Ws/edit#gid=1697459676
- a note about the data elements in FOLIO
- the FOLIO documentation should include a "type" for each data element
- if the type is "array", that means that FOLIO is storing a list of items in this data element. When "type" is "array", you should also see "items", which will tell you what kind of element is being stored in the array.
- if the type is "object", that means that FOLIO is storing a JavaScript object, which is like a dictionary or a list of key-value pairs. When "type" is "object", you should also see "properties", which will list all of the keys that might be included in the object.
- See attached summary for more detail.
- Once you have matched up the data elements that are visible in the final report, do a final pass through the FOLIO documentation to make sure that you also include any data elements that will be required for joining tables together. These are likely identifier elements. Even if you want just the name to appear in the report, please locate and list all required identifiers as well.
On the report wiki page, create a table that lists all required data elements, their path in the FOLIO modules, and a description of how that field will be used (including whether or not the field should be displayed in the final report)
- Optional: for especially complicated reports, it might be nice to include a relationship diagram that helps query builders make the correct connections between tables
EXAMPLE TABLE
| Folio Attribute (Module, Path, Attribute) | Folio Data Element Description | Parameters/Query |
|---|---|---|
| (mod-users:) groups/id | A UUID identifying this (patron) group | sort and count results by (patron group) id |
| (mod-users:) groups /group | The unique name of this (patron) group | |
| (mod-users:) users/id | A globally unique (UUID) identifier for the user | |
| (mod-users:) users/patronGroup | A UUID corresponding to the group the user belongs to; this is a reference to (mod-users:) groups/id; this is a foreign key to the id attribute under groups | |
| (mod-circulation-storage:/loan-storage/loans)id | Unique ID (generated UUID) of the loan; represents the loan transaction; storage module; we get data from the storage module | count loan transactions |
| (mod-circulation-storage: /loan-storage/loans) userId | foreign key; ID of the patron the item was lent to. Required for open loans, not required for closed loans (for anonymization). | |
| (mod-circulation-storage: /loan-storage/loans) loanDate | Date time when the loan began | filter loan transaction ids by date range |
| (mod-circulation: /circulation/loans)id | Unique ID (generated UUID) of the loan; business logic module; called "loanid" in the reporting database, will be marked as a foreign key | |
| (mod-circulation: /circulation/loans)location | The effective location of the item; nested under "items" |
Include Sample of Report Output
- After mapping out the required data elements, include a sample of the desired report output
- The JIRA issue may have an attachment, or the master spreadsheet may have a link - reproduce one or both here
- Alternately, can create a table in the wiki page that simulates the output
- Note: if working on a report cluster or if report has variations listed, include separate samples for each query that needs to be developed
- Make sure it is clear if a filter or aggregation has been applied
Placeholder: review all included data elements to see if they have proper documentation in LDP data dictionary
Review Specification with Report Requesters, RPWG, Reporting SIG
- Make sure report requesters agree that the desired output listed is correct and that the purpose of the report has been understood accurately
- Have RPWG review the specification
- Have Reporting SIG review the specification
Communicate with Developers
- When all approve the specification and the wiki page is completed, let developers know if we need additional test data for these data elements, or if any needed tables are missing from the LDP database. Please use the FOLIO Issue Tracker for these purposes, by creating an issue in the "Library Data Platform (LDP)" project. Set the issue type to "New Feature", and fill in the summary and description fields. Please do not set any other fields in the issue.
Write Query
Create a Fork of the ldp-analytics Repository
- Your first time working on new queries that will go up on GitHub, you'll need to create a fork of the ldp-analytics repository.
- See Contributing report queries to ldp-analytics
Create an LDP Issue for Your Work on This Query
- Log into JIRA
- Click on Create to create a new issue
- For "Project" select LDP
- For "Issue Type" select "Task"
- In "Summary" note that this is work on the query(ies) for a specific report (include report name)
- Click Create
- Notify Nassib to assign the task to you
- Link the LDP issue (via the "defines" linkage) to the related REP or UXPROD JIRA issue. The LDP issue "defines" the REP/UXPROD issue.
Build SQL Query
- Create new folder in ldp-analytics/sql to store sql, using the name of the query specified in the wiki page
- If you have any trouble, contact one of the maintainers of ldp-analytics
- Create a file with the query name and the extension ".sql"
- Follow SQL conventions specified in LDP documentation
- Identify tables and data elements in the ldp from the ldp load specifications (mapping between Folio interfaces and ldp tables).
- Note that the ldp load specifications do not include all fields that are available in FOLIO, just those that are currently loaded into the LDP. As more fields get added, this document will be updated. Also, it does not list fields in the LDP that are JSON array data.
- Additional tips for SQL:
- Use all-caps for keywords (e.g., "SELECT", "FROM")
- Start new lines for new keywords, items in long lists
- Indent lines with 4 spaces
- Review previous queries for other style conventions
- Link to Angela's recorded presentation on building an SQL query (name of the file on the Google drive is GMT20190920-135741_RPWG--Quer_1760x900.mp4)
Test Query
- Confirm with developers that the LDP has all required test data needed for your query
- Request access to the test LDP (contact Nassib directly, he will need your fixed IP address and give you the login information)
- Log into the LDP using your reporting tool of choice
- Execute the query
- Review results and revise query as needed
- When results are correct, save sample output in query folder
Push Query Back to GitHub
- Periodically throughout your query development, you should commit and push your file edits to GitHub
- Once the query is complete, you can submit a "pull request" to have the new query merged into the ldp-analytics repository
- See Contributing report queries to ldp-analytics