Derived Table Review - Fall 2024

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:

https://github.com/folio-org/folio-analytics/wiki/Cookbook:-General#6-using-cte-to-prepare-data-to-use-them-in-queries

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