...
The functionality of derived tables could also be replaced with FUNCTIONS.
...
1.7.9 Derived Table for Metadb
...
Keep or Retire in Metadb v 1.4?
...
Document in Wiki?
...
Notes
...
agreements_custom_property.sql
...
Retire
...
Yes
...
custom properties will be extracted into columns
...
agreements_package_content_item.sql
...
agreements_subscription_agreement.sql
...
agreements_subscription_agreement_entitlement.sql
...
agreements_subscription_agreement_org_ext.sql
...
feesfines_accounts_actions.sql
...
...
...
finance_invoice_transactions.sql
...
finance_transaction_invoices.sql
...
finance_transaction_purchase_order.sql
...
holdings_administrative_notes.sql
...
holdings_electronic_access.sql
...
...
...
...
holdings_statements_indexes.sql
...
holdings_statements_supplements.sql
...
holdings_statistical_codes.sql
...
...
instance_administrative_notes.sql
...
instance_alternative_titles.sql
...
...
...
...
instance_electronic_access.sql
...
...
...
...
...
...
...
instance_physical_descriptions.sql
...
...
instance_relationships_ext.sql
...
...
instance_statistical_codes.sql
...
...
...
...
invoice_adjustments_in_addition_to.sql
...
...
invoice_lines_fund_distributions.sql
...
invoice_voucher_lines_fund_distributions.sql
...
...
...
item_effective_callno_components.sql
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
openaccess_publication_request.sql
...
...
...
...
po_line_fund_distribution_transactions.sql
...
po_line_vendor_reference_number.sql
...
...
po_lines_details_subscription.sql
...
...
...
po_lines_fund_distribution_transactions.sql
...
...
...
...
...
po_lines_vendor_reference_numbers.sql
...
...
...
...
...
...
...
users_departments_unpacked.sql
...
...
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 | ||||||||||||||||||||||||||||||||||||
STOP | ||||||||||||||||||||||||||||||||||||||