Derived Table Review - Fall 2024
- Sharon Markus
The next planned major release of the Metadb software, version 1.4, will extract all the data fields from the data arrays into columns. Given this change, many of the derived tables the Reporting SIG currently maintains can be retired because they were designed to extract data from the data arrays. Some derived tables were created to achieve different goals, however, so they will be retained. The Reporting SIG is reviewing release 1.7.9 of derived tables for use with Metadb with the goal of reducing the number of tables overall. The focus will be on keeping tables that help report writers create queries that work well with the FOLIO software data model.
Reports that use derived tables can be rewritten to use CTEs (Common Table Expression), such as the CTE at the top of this user_permission_sets.sql query:
WITH permissions_users AS (
SELECT
jsonb_array_elements_text(jsonb->'permissions') AS user_permissions,
jsonb_extract_path_text(jsonb, 'userId')::uuid AS user_id
FROM
folio_permissions.permissions_users
)
See the Chapter on CTEs in the cookbook for more information:
The functionality of derived tables could also be replaced with FUNCTIONS.
Comments
CTEs not performative
need to understand impact of number of CTEs on performance
Can you build an index on a CTE?
Need to know more about FUNCTIONS
Derived tables enable building indexes
Lack of indexes could hamper performance of CTE
Can build CTE as a table and run an index on it, but more complex
Differences between derived and source table data can be confusing
How do we optimize our queries with CTEs? Put them closer to the end of the query?
Would it be possible to index the marct table?
It appears that marct is partitioned
Would be helpful to have some examples of FUNCTIONS
Need to see impacts with PowerBI, Tableau, etc. direct connections to the data.
Derived tables are being used at many institutions
Creating FUNCTIONS
-a script to convert derived tables to functions could run once a day or more frequently
-advantages - you only need to create one script
-in your query, you use the function when you need live data and you use the derived table when you do not
circulation query - use the function - get live data - use parameters to make it more performant than running the whole derived table
inventory query - use derived table - get yesterday’s data
Options
-functions available on folio-analytics repo; use these functions to create your own derived tables; write your sql query to point to the functions; you can tailor your institution’s functions to filter the data more to make the queries run faster
Indexes
-would be helpful to have indexes as FUNCTIONS
-for instance, we could index ISBNs or identifiers
1.7.9 Derived Table for Metadb | Keep or Retire? | Document in Wiki? | Notes |
|---|---|---|---|
Freeze | Yes | custom properties will be extracted into columns | |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
END |
|
|
|