Skip to content

Cookbook: ERM

Jump to bottom Edit New page
Stefan Dombek edited this page Nov 23, 2023 · 66 revisions

ERM Reporting

ERM reporting includes all reports relating to electronic media. In this cookbook, some connections and specific SQL statements should be explained.

Table of Contents

1. Agreements

A Folio Agreement represents an agreement between the library and a supplier to:

  • Give access to a set of resources
  • Under a specific license
  • For a period of time. Although there is some flexibility in how an institution chooses to represent agreements and group together their resources within an agreement.

[top]

1.1. Linking Agreements with resources

A knowledge base is an extensive database maintained by a knowledge base supplier that contains information about electronic resources such as title lists and coverage dates etc. Knowledge bases typically organize the resources provided by a content provider into collections or databases that reflect specific content provider offerings, for example packages of e-journals, e-books, or other materials. Libraries utilizing FOLIO can employ an external knowledge base or use FOLIO’s internal knowledge base. [Source: https://docs.folio.org/docs/erm/agreements/]

Resources are linked to agreements via an "agreement line" which can either represent a single title (serial or monograph) or a group of resources that come together (a package). There are three types of agreement line, each type allowing a different mechanism for describing the resource covered by that agreement line:

  • Internal: Internal agreement lines point to resources described within the Agreement's local knowledge base which, in turn, is populated with resource records from external sources
  • External: External agreement lines point to resources described elsewhere - currently this means a link to the eHoldings application, although the design of these means that they could point to other remote sources
  • Detached: Detached agreement lines don't link to a resource record but just have a free text description field which can be used to describe the resource. These can be used independently of any resource metadata you have access to

At the moment there are 4 ways (as far as we know) that libraries are linking agreements with resources:

  1. Using the integration with the Global Open Knowledgebase (GOKb) to populate Agreements internal knowledge base and using Internal agreement lines to link these resources into Agreements
  2. Populating the Agreements internal knowledge base using manual package upload from KBART files and using Internal agreement lines to link these resources into Agreements
  3. Using eHoldings with EBSCO HLM / EKB and using External agreement lines to link these resources into Agreements
  4. Using Detached agreement lines to enter a text description of a resource

Some of these may be used in combination. Particularly option 4 can be combined with any of the other options, and options 1 & 2 are sometimes used together.

[top]

1.1.2. Querying data from resources

Each agreement line (entitlement) can have a type property stored in ent_type.

  • NULL = The default value is for ent_type is NULL. This means the agreement line points at a resource in the Agreements local KB or even though the type isn't explicitly set.
  • detached = This means the agreement line does not have a linked resource.
  • external = This means that the agreement line is linked to a resource described in an external system / knowledge base.

[top]

1.1.2.1. Internal Knowledge Base

Please note: The ent_type has to be NULL.

The link between an agreement line (entitlement) and their linked resources can be etablished via the resource UUID that is stored in ent_resource_fk. It is a UUID for an ERM-Resource in the local KB.

[top]

1.1.2.1.1. Agreements Count Cluster

This report counts of e-resources by agreement. You can find the report in the repository under the section for report_queries in the category erm. The name of the directory is agreement_count. Inside this directory you find an explanation to the report and a sql file with the sql statement. The report uses sql statements prepared by the community. Make sure these "derived tables" exist in your database.

[top]

1.1.2.1.2. Package content items

The community created a sql statement to get a list of package content items that either are linked directly to an entitlement or have a package linked that is linked to an entitlement. You can find this sql statement in the repository under the section for derived_tables. The name of the sql file is agreements_package_content_item.sql. Please note: This is a sql statement to create a "derived table". If you would like only to query the data, you have to copy only the section of the selection statement.

[top]

1.1.3. External Knowledge Base (eHoldings)

Please note: The ent_type has to be external.

The link between an agreement line (entitlement) and their linked resources can be etablished via IDs. You can find these IDs in the attributes ent_authority and ent_reference. To know how to lookup the resource you need to check ent_authority and ent_reference to get what is essentially a service and ID combintation.

  • ent_authority The value tells you where the ent_reference is a valid ID, and should contain the description of the resource. Currently two authorities are supported (both for eHoldings):
    • EKB-PACKAGE
    • EKB-TITLE
  • ent_reference The ent_reference is the EKB (EBSCO Knowledgebase) identifier in both cases, but it differentiate because it allows you to manipulate the data slightly more easily. EKB IDs are between 1 and 3 numbers joined with a hyphen. The first number identifies the provider of the resource, the second the package and the third the title. Schema: provider-package-title. So in terms of what is stored:
    • EKB-PACKAGE IDs consist of two numbers joined by a hyphen. E.g. 19-1054 -> PsycBOOKS (EBSCO). The 19 tells us it's provided by EBSCO
    • EKB-TITLE IDs consist of three numbers joined by a hyphen. E.g. 19-1054-14702850 -> 125 years of the American Psychological Association, a book in the PsycBOOKS (EBSCO) package

You can link agreement lines and records from the external KB eHoldings via the ent_reference IDs. To do this you have to call the API from EBSCO. There are 2 APIs you can call (see below).

API of the FOLIO app eHoldings

Examples:

ent_authority == EKB-PACKAGE

<okapi URL>/eholdings/packages/19-1054

ent_authority == EKB-TITLE

<okapi URL>/eholdings/resources/19-1054-14702850

HoldingsIQ API of EBSCO's Knowledge Base

For performance reasons, you should use the HoldingsIQ API for larger queries. The description for this API can be found at: https://developer.ebsco.com/knowledge-services/holdingsiq.

[top]

1.1.4. Detached

Please note: The ent_type has to be detached.

There is no link to a resource record but just the ent_description field which must be present for any entitlements with ent_type == detached

  • ent_description is a piece of text describing what the entitlement represents.

Since the entitlement can still be linked to a purchase order line then it could still be valid to report on detached entitlements using either the description, or information from the linked purchase order line (and inventory if the purchase order line is linked to an inventory instance)

[top]

1.2. Agreements and orders

An agreement can be linked via an agreement line (entitlement) to an purchase order line to manage their costs.

Example:

SELECT 
    *
FROM 
    folio_agreements.subscription_agreement
    LEFT JOIN folio_agreements.entitlement ON entitlement.ent_owner_fk = subscription_agreement.sa_id
    LEFT JOIN folio_agreements.order_line ON order_line.pol_owner_fk = entitlement.ent_id
    LEFT JOIN folio_orders.po_line__t ON po_line__t.id = order_line.pol_orders_fk

In the Orders module, a link can be create to invoices.

[top]

1.2.1. Agreements Cost Cluster

This report is to provide a dataset of invoice lines to summarize costs by agreements. Filters were designed to customize this queries on local needs. You can find the report in the repository under the section for report_queries in the category erm. The name of the directory is agreement_cost. Inside this directory you find an explanation to the report and a sql file with the sql statement. The report uses sql statements prepared by the community. Make sure these "derived tables" exist in your database.

[top]

1.3. Custom Properties

You can create cutom properties for an agreement, that means the properties can be defined by the user and linked inside a record for an agreement. To query the linked custom properties you have to know, that each agreement has a ID custom_properties_id. This ID is linked to a container. A container can linked to several properties.

Example:

SELECT 
    *
FROM 
    folio_agreements.subscription_agreement
    LEFT JOIN folio_agreements.custom_property_container ON custom_property_container.id = subscription_agreement.custom_properties_id
    LEFT JOIN folio_agreements.custom_property ON custom_property.parent_id = custom_property_container.id 
    LEFT JOIN folio_agreements.custom_property_definition ON custom_property_definition.pd_id = custom_property.definition_id
    LEFT JOIN folio_agreements.custom_property_integer ON custom_property_integer.id = custom_property.id
    LEFT JOIN folio_agreements.custom_property_text ON custom_property_text.id = custom_property.id

There are more connections for custom properties.

[top]

1.4. Agreements and Licenses

There is a FOLIO app Licenses. You have the option to link agreements to records in the app licenses.

Example:

SELECT 
    *
FROM 
    folio_agreements.subscription_agreement
    LEFT JOIN folio_agreements.remote_license_link ON remote_license_link.rll_owner = subscription_agreement.sa_id
    LEFT JOIN folio_licenses.license ON license.lic_id = remote_license_link.rol_remote_id

[top]

1.5. Refdata values

Within agreements, values ​​can be stored in the settings. These values ​​can be used in certain fields when creating records. They behave like variables whose value can be freely determined.

When reporting the data records for agreements, these values ​​can be integrated via a separate table. Foreign keys from the agreements record to the table with the values ​​are used as connections. Since there are several values ​​to be connected, the table with the values ​​must be connected multiple times.

Example:

SELECT
    *
FROM
    folio_agreements.subscription_agreement
    LEFT JOIN folio_agreements.refdata_value AS sa_renewal_priority ON sa_renewal_priority.rdv_id = subscription_agreement.sa_renewal_priority
    LEFT JOIN folio_agreements.refdata_value AS sa_is_perpetual ON sa_is_perpetual.rdv_id = subscription_agreement.sa_is_perpetual
    LEFT JOIN folio_agreements.refdata_value AS sa_agreement_status ON sa_agreement_status.rdv_id = subscription_agreement.sa_agreement_status
    LEFT JOIN folio_agreements.refdata_value AS sa_reason_for_closure ON sa_reason_for_closure.rdv_id = subscription_agreement.sa_reason_for_closure

[top]

1.6. Organizations

Organizations are any institution with which your library interacts. For example, you may want to add a vendor or consortium associated with the agreement. Multiple organizations can be added to a record, but the organizations must first be created in the Organizations app. [Source: https://docs.folio.org/docs/erm/agreements/]

Example:

SELECT 
    *
FROM 
    folio_agreements.subscription_agreement
    LEFT JOIN folio_agreements.subscription_agreement_org ON subscription_agreement_org.sao_owner_fk = subscription_agreement.sa_id
    LEFT JOIN folio_agreements.org ON org.org_id = subscription_agreement_org.sao_org_fk
    LEFT JOIN folio_agreements.subscription_agreement_org_role ON subscription_agreement_org_role.saor_owner_fk = subscription_agreement_org.sao_id
    LEFT JOIN folio_agreements.refdata_value ON refdata_value.rdv_id = subscription_agreement_org_role.saor_role_fk

There is a Foreign Key in the table folio_agreements.org to the app Organizations.

[top]

2. Licenses

The Licenses app allows you to create and manage your library’s licenses. The licenses you create here can link to agreements in the Agreements app. A license explains what you can do with the content your library can access. The license is the contract or Terms of Use. [Source: https://docs.folio.org/docs/erm/licenses/]

[top]

2.1. Licenses and Agreements

As described in Chapter 1.4, an Agreement can refer to a Record in Licenses. Several licenses can be linked to one agreement. For this reason, it is good practice to LEFT JOIN the licenses.

Example:

SELECT 
    *
FROM 
    folio_agreements.subscription_agreement
    LEFT JOIN folio_agreements.remote_license_link ON remote_license_link.rll_owner = subscription_agreement.sa_id
    LEFT JOIN folio_licenses.license ON license.lic_id = remote_license_link.rol_remote_id

[top]

2.2. Custom Properties

You can create cutom properties for a license, that means the properties can be defined by the user and linked inside a record for a license. To query the linked custom properties you have to know, that each license has a ID custom_properties_id. This ID is linked to a container. A container can linked to several properties.

Example:

SELECT 
    *
FROM 
    folio_licenses.license
    LEFT JOIN folio_licenses.custom_property_container ON custom_property_container.id = license.custom_properties_id 
    LEFT JOIN folio_licenses.custom_property ON custom_property.parent_id = custom_property_container.id 
    LEFT JOIN folio_licenses.custom_property_definition ON custom_property_definition.pd_id = custom_property.definition_id
    LEFT JOIN folio_licenses.custom_property_text ON custom_property_text.id = custom_property.id
    LEFT JOIN folio_licenses.custom_property_integer ON custom_property_integer.id = custom_property.id
    LEFT JOIN folio_licenses.custom_property_decimal ON custom_property_decimal.id = custom_property.id

[top]

2.3. Refdata values

Within Licenses, values ​​can be stored in the settings. These values ​​can be used in certain fields when creating records. They behave like variables whose value can be freely determined.

When reporting the data records for licenses, these values ​​can be integrated via a separate table. Foreign keys from the license record to the table with the values ​​are used as connections. Since there are several values ​​to be connected, the table with the values ​​must be connected multiple times.

Example:

SELECT 
    *
FROM 
    folio_licenses.license
    LEFT JOIN folio_licenses.refdata_value AS lic_status ON lic_status.rdv_id = license.lic_status_rdv_fk
    LEFT JOIN folio_licenses.refdata_value AS lic_type ON lic_type.rdv_id = license.lic_type_rdv_fk
    LEFT JOIN folio_licenses.refdata_value AS lic_end_date_semantics ON lic_end_date_semantics.rdv_id = license.lic_end_date_semantics_fk

[top]

2.4. Organizations

Any institution with which your library interacts (this may or may not be an institution from which you purchase materials). The organizations associated with licenses are generally the licensors or a consortium. [Source: https://docs.folio.org/docs/erm/licenses/]

Example:

SELECT 
    *
FROM 
    folio_licenses.license
    LEFT JOIN folio_licenses.license_org ON license_org.sao_owner_fk = license.lic_id
    LEFT JOIN folio_licenses.org ON org.org_id = license_org.sao_org_fk
    LEFT JOIN folio_licenses.license_org_role ON license_org_role.lior_owner_fk = license_org.sao_id
    LEFT JOIN folio_licenses.refdata_value ON refdata_value.rdv_id = license_org_role.lior_role_fk

There is a Foreign Key in the table folio_licenses.org to the app Organizations.

[top]

3. Electronic Resources from inventory

Electronic resources can be managed through licensed contracts in Agreements. But there are also electronic resources that are managed via the FOLIO App Inventory. An example of this would be an e-book that was purchased. In order to create queries for these kind of resources, separately from the agreements, the Reporting SIG offers 2 reports.

[top]

3.1. ERM Inventory Costs Report

This report is to provide a dataset of invoice lines to summarize certain costs on the predefined filter for electronic resorces in the inventory. Filters were designed to customize this queries on local needs. Furthermore costs on invoice line level are exemplary divided by instance subjects and formats. Take in account that this might duplicate invoice lines and needed to be adjusted if summing up totals.

The costs relies on the amounts out of the transactions table in system currency. This data will only appear if an invoice status is 'Approved' or 'Paid'. The preset on the invoice status filter therefore is 'Paid'.

You can find the report in the repository under the section for report_queries in the category erm. The name of the directory is erm_inventory_cost. Inside this directory you find an explanation to the report and a sql file with the sql statement. The report uses sql statements prepared by the community. Make sure these "derived tables" exist in your database.

[top]

3.2. ERM Inventory Count Report

The aim is to provide title counts for electronic resources cataloged in the Inventory, by various filters.

You can find the report in the repository under the section for report_queries in the category erm. The name of the directory is erm_inventory_count. Inside this directory you find an explanation to the report and a sql file with the sql statement. The report uses sql statements prepared by the community. Make sure these "derived tables" exist in your database.

[top]

4. Open Access

The FOLIO App Open Access offers the possibility to manage publication requests that should published or has been published as Open Access. Additional data on a publication request can be recorded within the app, e.g. costs, contact persons or journals.

[top]

4.1. Publication requests

The publication requests are the start point for the reporting in this area. The most data in the FOLIO App Open Access need the informations from the publication request record. There are also scenarios where you can use the other data separately. However, these data records only really make sense together with the management of the publication requests for Open Access.

Example:

SELECT
    *
FROM 
    folio_oa.publication_request
    LEFT JOIN folio_oa.refdata_value AS pr_status ON pr_status.rdv_id = publication_request.pr_request_status
    LEFT JOIN folio_oa.refdata_value AS pr_pub_type ON pr_pub_type.rdv_id = publication_request.pr_pub_type_fk
    LEFT JOIN folio_oa.refdata_value AS pr_subtype ON pr_subtype.rdv_id = publication_request.pr_subtype
    LEFT JOIN folio_oa.refdata_value AS pr_publisher ON pr_publisher.rdv_id = publication_request.pr_publisher
    LEFT JOIN folio_oa.refdata_value AS pr_license ON pr_license.rdv_id = publication_request.pr_license
    LEFT JOIN folio_oa.refdata_value AS pr_doaj_status ON pr_doaj_status.rdv_id  = publication_request.pr_work_indexed_in_doaj_fk
    LEFT JOIN folio_oa.refdata_value AS pr_oa_status ON pr_oa_status.rdv_id  = publication_request.pr_work_oa_status_fk
    LEFT JOIN folio_oa.refdata_value AS pr_corresponding_institution_level_1 ON pr_corresponding_institution_level_1.rdv_id = publication_request.pr_corresponding_institution_level_1_fk
    LEFT JOIN folio_oa.refdata_value AS pr_closure_reason ON pr_closure_reason.rdv_id = publication_request.pr_closure_reason_fk
    LEFT JOIN folio_oa.request_party AS pr_corresponding_author ON pr_corresponding_author.rp_id = publication_request.pr_corresponding_author_fk
    LEFT JOIN folio_oa.refdata_value AS rp_role_corresponding_author ON rp_role_corresponding_author.rdv_id = pr_corresponding_author.rp_role        
    LEFT JOIN folio_oa.request_party AS pr_request_contact ON pr_request_contact.rp_id = publication_request.pr_request_contact_fk
    LEFT JOIN folio_oa.refdata_value AS rp_role_request_contact ON rp_role_request_contact.rdv_id = pr_request_contact.rp_role
    LEFT JOIN folio_oa.party AS party_corresponding_author ON party_corresponding_author.p_id = pr_corresponding_author.rp_party_fk
    LEFT JOIN folio_oa.party AS party_request_contact ON party_request_contact.p_id = pr_request_contact.rp_party_fk 
ORDER BY 
    publication_request.pr_request_number

[top]

4.1.1. Publication requests and their processing statuses

A publication request can have different processing statuses over time. You can find these processing statuses with their dates in the "publication_status" table.

Example:

SELECT
    *
FROM
    folio_oa.publication_request
    LEFT JOIN folio_oa.publication_status ON publication_status.ps_owner_fk = publication_request.pr_id
    LEFT JOIN folio_oa.refdata_value AS publication_status_description ON publication_status_description.rdv_id = publication_status.ps_publication_status

[top]

4.2. Charges

[top]

4.2.1. Charges and payer

A charge can have different payers. For this reason payers have their own table in the database. The link between the tables "charge" and "payer" has to be an left join. There is no direct connection in the ER diagram, but you can use a FK in the table "payer" to link to table "charge".

Example:

SELECT
    *
FROM
    folio_oa.publication_request
    LEFT JOIN folio_oa.charge ON charge.ch_owner_fk = publication_request.pr_id    
    LEFT JOIN folio_oa.payer ON payer.cpy_owner_fk = charge.ch_id

[top]

4.3. Works

4.3.1. Works and publication requests

It appears that some data is duplicated in the "publication_request" and "works". However, that is not entirely true. What is the difference?

  • "publication_request" contains the data for a publication when the publication request was created in the system.
  • "works" contains the data for a "work" with the current data (The data needs to be refreshed to get the latest.). These may change over time and differ at the time the publication request was made.

Here is an example to quering the data for the works:

SELECT
    *
FROM
    folio_oa.work
    LEFT JOIN folio_oa.title_instance ON title_instance.ti_work_fk = work.w_id
    LEFT JOIN folio_oa.identifier_occurrence ON identifier_occurrence.io_ti_fk = title_instance.ti_id
    LEFT JOIN folio_oa.identifier ON identifier.id_id = identifier_occurrence.io_identifier_fk
    LEFT JOIN folio_oa.identifier_namespace ON identifier_namespace.idns_id = identifier.id_ns_fk
    LEFT JOIN folio_oa.refdata_value AS journal_doaj_status ON journal_doaj_status.rdv_id = work.w_indexed_in_doaj_fk
    LEFT JOIN folio_oa.refdata_value AS jounal_oa_status ON jounal_oa_status.rdv_id = work.w_oa_status_fk
    LEFT JOIN folio_oa.refdata_value AS ti_type ON ti_type.rdv_id = title_instance.ti_type_fk
    LEFT JOIN folio_oa.refdata_value AS ti_subtype ON ti_subtype.rdv_id = title_instance.ti_subtype_fk
    LEFT JOIN folio_oa.refdata_value AS ti_publication_type ON ti_publication_type.rdv_id = title_instance.ti_publication_type_fk
    LEFT JOIN folio_oa.refdata_value AS io_status ON io_status.rdv_id = identifier_occurrence.io_status_fk

Here is an example to link the publication request with the works:

SELECT
    *
FROM
    folio_oa.publication_request
    LEFT JOIN folio_oa.work ON work.w_id = publication_request.pr_work_fk

[top]

4.3.2. Works and title instances

A "work" can have several "title instances".

work title instance
A A
A B

The link between the tables "work" and "title_instance" has to be an left join. There is no direct connection in the ER diagram, but there is a FK in the table "title_instance" to the table "work".

Example:

SELECT
    *
FROM
    folio_oa.work
    LEFT JOIN folio_oa.title_instance ON title_instance.ti_work_fk = work.w_id

[top]

4.3.2. Title instances and identifiers

The link between the tables "title_instance" and "identifier" has to be an left join. There is no direct connection in the ER diagram, but you can use the FK "io_identifier_fk" in the table "identifier_occurrence" to link to table "identifier" with the attribute "id_id ".

Example:

SELECT
    *
FROM
    folio_oa.title_instance
    LEFT JOIN folio_oa.identifier_occurrence ON identifier_occurrence.io_ti_fk = title_instance.ti_id
    LEFT JOIN folio_oa.identifier ON identifier.id_id = identifier_occurrence.io_identifier_fk
    LEFT JOIN folio_oa.identifier_namespace ON identifier_namespace.idns_id = identifier.id_ns_fk

[top]

4.4. Party

[top]

4.5. Checklists

Checklists can be used to keep an eye on the processing steps of a publication request. The data for the checklists are available in various tables. You can join these data and link to the publication request. Then you have the checklist items for a publication request.

Example:

SELECT
    *
FROM 
    folio_oa.publication_request
    LEFT JOIN folio_oa.checklist_item ON checklist_item.cli_parent_fk = publication_request.pr_id
    LEFT JOIN folio_oa.checklist_item_definition ON checklist_item_definition.clid_id = checklist_item.cli_definition_fk
    LEFT JOIN folio_oa.refdata_value AS cli_outcome ON cli_outcome.rdv_id = checklist_item.cli_outcome_fk
    LEFT JOIN folio_oa.refdata_value AS cli_status ON cli_status.rdv_id = checklist_item.cli_status_fk

[top]

4.6. Refdata values

Within Open Access, values ​​can be stored in the settings. These values ​​can be used in certain fields when creating records. They behave like variables whose value can be freely determined.

When reporting the data records, these values ​​can be integrated via a separate table. Foreign keys from a record to the table with the values ​​are used as connections. Since there are several values ​​to be connected, the table with the values ​​must be connected multiple times.

Example:

SELECT
    *
FROM
    folio_oa.publication_request
    LEFT JOIN folio_oa.refdata_value AS pr_status ON pr_status.rdv_id = publication_request.pr_request_status
    LEFT JOIN folio_oa.refdata_value AS pr_pub_type ON pr_pub_type.rdv_id = publication_request.pr_pub_type_fk
    LEFT JOIN folio_oa.refdata_value AS pr_subtype ON pr_subtype.rdv_id = publication_request.pr_subtype
    LEFT JOIN folio_oa.refdata_value AS pr_publisher ON pr_publisher.rdv_id = publication_request.pr_publisher
    LEFT JOIN folio_oa.refdata_value AS pr_license ON pr_license.rdv_id = publication_request.pr_license
    LEFT JOIN folio_oa.refdata_value AS pr_doaj_status ON pr_doaj_status.rdv_id  = publication_request.pr_work_indexed_in_doaj_fk
    LEFT JOIN folio_oa.refdata_value AS pr_oa_status ON pr_oa_status.rdv_id  = publication_request.pr_work_oa_status_fk
    LEFT JOIN folio_oa.refdata_value AS pr_corresponding_institution_level_1 ON pr_corresponding_institution_level_1.rdv_id = publication_request.pr_corresponding_institution_level_1_fk
    LEFT JOIN folio_oa.refdata_value AS pr_closure_reason ON pr_closure_reason.rdv_id = publication_request.pr_closure_reason_fk
    LEFT JOIN folio_oa.request_party AS pr_corresponding_author ON pr_corresponding_author.rp_id = publication_request.pr_corresponding_author_fk
    LEFT JOIN folio_oa.refdata_value AS rp_role_corresponding_author ON rp_role_corresponding_author.rdv_id = pr_corresponding_author.rp_role

[top]

4.7. Publication requests and Agreements

You can link a publication request to an agreement. In the database you have a connection from the publication requests to the agreemets via a third table "folio_oa.publication_request_agreement".

Example:

SELECT
    *
FROM
    folio_oa.publication_request
    LEFT JOIN folio_oa.publication_request_agreement ON publication_request_agreement.rol_id = publication_request.pr_agreement_reference
    LEFT JOIN folio_agreements.subscription_agreement ON subscription_agreement.sa_id = publication_request_agreement.rol_remote_id

[top]

5. eUsage

The eUsage app allows you to manage usage data reports for electronic resources.

The app provides automatic harvesting of COUNTER standard usage reports via SUSHI and manual uploading of standard and non-standard statistics. Usage data providers managed in eUsage can be referred to from the Agreements app. [Source: https://docs.folio.org/docs/erm/eusage/]

The COUNTER reports are available in the FOLIO database as JSON objects. The JSON objects are very nested and very large. For this reason, the Reporting SIG created some reports that transform the data into a relational format grouped by the type of COUNTER report. Please note: You can also create reports inside the FOLIO app.

[top]

5.1. COUNTER Master Reports

We offer 4 kind of COUNTER Master Reports in this repository.

  • COUNTER Platform Master Report
  • COUNTER Database Master Report
  • COUNTER Title Master Report
  • COUNTER Item Master Report

These reports are based on the standards for COUNTER reports and contain additional information about the report files. You can find the reports in the repository under the section for report_queries in the category erm. The name of the directory is eusage_counter_reports. Inside this directory you find subdirectories for each kind of COUNTER report. Each subdirectoriy contain an explanation to the report and a sql file with the sql statement.

Why there are no derived tables for the COUNTER reports?

Derived tables can be used to simplify complex issues or improve query performance. Derived tables are similar to materialized views. These tables are updated at certain intervals. In the case of COUNTER reports, the creation of such a derived table takes a lot of time. That would slow down the database enormously. In order not to impair performance, there are no derived tables for the COUNTER reports.

[top]

Add a custom footer
Add a custom sidebar
Clone this wiki locally