2020-09-28 Reporting SIG Meeting notes

Duke University

Emerson

Please note that JIRA rankings for UXPROD and REP issues were converted by Holly to the new ranking system R1 (urgent) to R5 (not needed).

• As of 2020, the Reporting SIG is only ranking reporting cluster issues, such as "UXPROD-2029 RA-Requests Cluster." You can find report clusters by using the label  "report-cluster" in your JIRA search.
• Individual report requirements are captured in JIRA issues that start with "REP," such as "REP-275 Curbside Pickup Report." 

RM Working Group

• for latest updates, see RM Prototype and Query Development Status

• RM Derived Table demos
  • rows 11, 21 shown so far
  • demo of local.invoice_lines_fund_distributions, needs to be updated for the new way to extract json arrays
  • WITH allows you to create a subquery (intermediate table "funds_distr" is created); locally-scoped table that exists for 

External Statistics Working Group

• Recategorize External Statistics REP Issues

Guidelines for Writing Aliases

• How do we keep aliasing consistent across derived tables?
  • Argument is that appropriate aliasing makes sure the reports make sense to end users and that consistent aliasing across queries ensures consistent readability
  • Don't want to add a lot of overhead, but do want groups to be thoughtful about how derived tables will get pulled into other queries
• General rules
  • because derived tables take data fields out of their original context (original tables), better to be more descriptive where possible
  • always alias the ID field by adding (simple) table name
    (e.g., id → user_id)
  • if joining in a name from a secondary table (and keeping the id field), both id and name should have similar alias
    (e.g., electronic_access_relationship_id, electronic_access_relationship_name) 
  • if a field name occurs in multiple tables, always add the table name
    (e.g., permanent_location_id → item_permanent_location_id, statistical_code_id → instance_statistical_code_id) 
• ideas for a "registry"
  • we could keep track of "approved" aliases in some kind of spreadsheet, like this example
  • groups could use it to verify aliases in a new query, store new aliases as created
  • could be a bit too much work, but might be nice to have a good starting set of example aliases
• note: column name length does have restrictions, might want to agree on some abbreviations for both table and field names

Review and update Topics for Future Reporting SIG Meetings