-
Notifications
You must be signed in to change notification settings - Fork 30
Cookbook: DBS
Stefan Dombek edited this page Feb 22, 2024
·
55 revisions
This page is still under construction!
The German Library Statistics (DBS) contains all important data on public and academic libraries. It documents the work of libraries on a national level.
The German library statistics (DBS) follows the uniform definitions of the international library statistics (ISO standard 2789). They enable a comparison of performance based on statistical data in the areas of equipment, inventory, loans, expenses, finance and staff.
In this cookbook, we would like to explain the different statistical points for academic libraries and how they can be queried.
DBS 1 - 10 | General information
- DBS 1 - Library locations
- DBS 2 - Students
- DBS 3 - Scientific staff
- DBS 4 - Borrower
- DBS 5 - Registrations
- DBS 6 - Opening days per year
- DBS 7 - Opening hours during the week (regular opening hours)
- DBS 8 - Percentage of closed stacks
- DBS 9 - Exhibitions in the reporting year
- DBS 10 - Other cultural events
DBS 11 - 17 | Building, facility
DBS 18 - 34 | Books, dissertations, magazines and newspapers
- DBS 18 - Books (including dissertations), journals and newspapers counting by bookbinding unit, total - inventory
- DBS 19 - Books (including dissertations), journals and newspapers counting by bookbinding unit, total - receiving
- DBS 20 - Books (including dissertations), journals and newspapers counting by bookbinding unit, total - withdrawal
- DBS 21 - Books (including dissertations), journals and newspapers, total - expenses
- DBS 30 - Books in inventory: Incunabula
- DBS 34 - Books in inventory: Book rarity
DBS 38 - 58 | Other printed works
- DBS 38 - Other printed works, total - inventory
- DBS 39 - Other printed works, total - receiving
- DBS 40 - Other printed works, total - withdrawal
- DBS 41 - Other printed works, total - expenses
- DBS 42 - Other printed works: Maps and plans by sheets - inventory
- DBS 46 - Other printed works: Music notes by physical units - inventory
- DBS 50 - Other printed works: Single-sheet materials - inventory
- DBS 54 - Other printed works: Patents by physical units - inventory
- DBS 58 - Other printed works: Norms by physical units - inventory
DBS 62 - 74 | Other non-electronic materials
- DBS 62 - Other non-electronic materials, total - inventory
- DBS 63 - Other non-electronic materials, total - receiving
- DBS 64 - Other non-electronic materials, total - withdrawal
- DBS 65 - Other non-electronic materials, total - expenses
- DBS 66 - Other non-electronic materials: AV materials - inventory
- DBS 70 - Other non-electronic materials: Micromaterials according to individual pieces - inventory
- DBS 74 - Other non-electronic materials: Further materials by physical units - inventory
DBS 78 - 98 | Manuscripts and autographs
- DBS 78 - Manuscripts and autographs, manuscript fragments and certificates, total - inventory
- DBS 79 - Manuscripts and autographs, total - receiving
- DBS 80 - Manuscripts and autographs, total - withdrawal
- DBS 81 - Manuscripts and autographs, total - expenses
- DBS 82 - Manuscripts: Textual manuscripts and manuscript fragments - inventory
- DBS 90 - Manuscripts: Musical manuscripts and manuscript fragments - inventory
- DBS 98 - Manuscripts and autographs: Autographs - inventory
DBS 102 | Literary remains
DBS 110 - 121 | Digital Library Holdings
- DBS 110 - Digital Library Holdings, total - inventory (without electronic journals and newspapers)
- DBS 111 - Digital Library Holdings, total - withdrawal
- DBS 113 - Digital Library Holdings, total - expenses
- DBS 113.1 - Digital Library Holdings: Inventory of commercially distributed e-books (related to number 110)
- DBS 113.4 - Digital Library Holdings: Expenses of commercially distributed e-books (related to number 113)
- DBS 113.5 - Digital Library Holdings: Digitized works that are self-hosted (inventory by volume)
- DBS 114 - Digital Library Holdings: AV materials - inventory (related to number 110)
- DBS 118 - Digital Library Holdings: Databases and platforms - inventory (related to number 110)
- DBS 121 - Digital Library Holdings: Expenses for databases (related to number 110)
DBS 122 - 130 | Journals and newspapers in non-electronic form
- DBS 122 - Count of subscriptions in the reporting year of continuously holded non-electronic journals and newspapers
- DBS 123 - Expenses of continuously holded non-electronic journals and newspapers
- DBS 130 - Count of subscriptions in the reporting year of non-electronic paid journals and newspapers that are unsubscribed
DBS 131 - 138 | Journals and newspapers in electronic form
- DBS 131 - Count of titles in the reporting year of licensed and digitized electronic journals and newspapers
- DBS 132 - Count of titles in the reporting year of licensed electronic journals and newspapers (related to number 131): Count of titles in the reporting year of licensed paid electronic journals and newspapers
- DBS 133 - Count of titles in the reporting year of licensed electronic journals and newspapers (related to number 131): Count of titles in the reporting year of continuously holded paid electronic journals and newspapers
- DBS 134 - Expenses of continuously holded electronic journals and newspapers in the reporting year
- DBS 135 - Further expenses for electronic journals and newspapers
- DBS 138 - Count of titles in the reporting year continuously holded electronic journals and newspapers: new licensed paid electronic journals and newspapers
DBS 141 - 148 | Receiving (Accession)
- DBS 141 - Receiving of conventional media: purchase, exchange, gifts
- DBS 142 - Receiving of digital media: purchase/license, exchange, gifts
- DBS 143 - Receiving of conventional media: "Pflichtexemplar"
- DBS 144 - Receiving of digital media: "Pflichtexemplar"
- DBS 146 - Receiving of other non-paid digital media
- DBS 147 - Receiving of conventional media, total
- DBS 148 - Receiving of digital media, total
DBS 149 - 166 | Expenses, financing
- DBS 149 - Acquisition, total
- DBS 150 - Acquisition: Purchase (related to number 149)
- DBS 151 - Acquisition: Purchase, Expenses for digital/electronic media (related to number 150)
- DBS 151.1 - Acquisition: Purchase, Expenses for Open Access publications (related to number 149)
- DBS 152 - Acquisition: Compensation for "Pflichtexemplar" (related to number 149)
- DBS 152.1 - Acquisition: Compensation for digital/electronic "Pflichtexemplar" (related to number 152)
- DBS 153 - Acquisition: Book cover
- DBS 154 - Other material expenses, total
- DBS 157 - Other material expenses: Expenses for inventory preservation measures
- DBS 159 - Cost for staff
- DBS 162 - Total of expenses
- DBS 163 - Financing from the financier
- DBS 164 - Financing with third-party funds
- DBS 165 - Self-generated funds
- DBS 166 - Total of funds
DBS 167 - 184 | Offers and Use of Services
- DBS 167 - Loans by physical units, total
- DBS 170 - Loans: Renewals from user
- DBS 172 - Holds
- DBS 174 - Usage of items from the reference collection
- DBS 175 - Inquiries to the information desk
- DBS 176 - Number of visitors to the library
- DBS 176.1 - Number of virtual visitors (visits)
- DBS 177 - Courses for user (hours)
- DBS 178 - Number of participants in courses for user
- DBS 178.1 - Views of e-learning courses of the library
- DBS 178.2 - Accepted dissertations from the own college
- DBS 178.3 - Accepted dissertations from the own college: online dissertation
- DBS 178.4 - Open Access publications (green and gold) that are hosted on own repositories (Receiving in the reporting year)
- DBS 179 - Number of searches in the OPAC and the discovery system
- DBS 180 - Number of searches in databases and platforms
- DBS 181 - Total number of accesses to journal titles
- DBS 183 - Total number of full text views of articles in journals
- DBS 184 - Total number of full text views of individual documents
- DBS 184.1 - Total number of full text views of individual documents: Full text view of commercial distributed e-books
- DBS 184.2 - Total number of full text views of individual documents: Full text view of e-books from institutional repositories
DBS 185 - 208 | Interlibrary loan, document delivery
Types of orders
| Type of order (German) | Explanation (German) | Type of order (English) | Explanation (English) |
|---|---|---|---|
| Gebende Fernleihe (aktive Fernleihe, AFL) | Bücher aus der eigenen Bibliothek an fremde Bibliotheken ausleihen | Interlibrary loan (active interlibrary loan, AFL) | Borrow books from your own library to other libraries |
| Nehmende Fernleihe (passive Fernleihe, PFL) | Bücher aus fremden Bibliotheken für die eigenen Benutzer:innen bestellen und ausleihen | Interlibrary loan (passive interlibrary loan, PFL) | Order and borrow books from other libraries for your own users |
DBS statistics
- DBS 185 - Active ILL: Total number of orders
- DBS 186 - Passive ILL: Total number of orders
- DBS 189 - Active ILL: Received orders: Direct delivery service
- DBS 189.1 - Active ILL: Received orders: Direct delivery service, Campus delivery service
- DBS 190 - Passive ILL: Shipped orders: Direct delivery service
- DBS 191 - Active ILL: Positively completed orders
- DBS 192 - Passive ILL: Positively completed orders
- DBS 195 - Active ILL: Positively completed orders: Direct delivery service
- DBS 195.1 - Active ILL: Positively completed orders: Direct delivery service, Campus delivery service
- DBS 196 - Passive ILL: Positively completed orders: Direct delivery service
- DBS 197 - Active ILL: Total orders completed through hard copied
- DBS 198 - Passive ILL: Total orders completed through hard copied
- DBS 201 - Active ILL: Total orders completed through hard copied: Direct delivery service
- DBS 201.1 - Active ILL: Total orders completed through hard copied: Direct delivery service, Campus delivery service
- DBS 202 - Passive ILL: Total orders completed through hard copied: Direct delivery service
- DBS 203 - Active ILL: Total orders completed by electronic transmission
- DBS 204 - Passive ILL: Total orders completed by electronic transmission
- DBS 207 - Active ILL: Total orders completed by electronic transmission: Direct delivery service
- DBS 208 - Passive ILL: Total orders completed by electronic transmission: Direct delivery service
DBS 210 - 214 | Descriptive and subject cataloging
- DBS 210 - Descriptive cataloging: Number of newly recorded titles
- DBS 211 - Descriptive cataloging: Number of newly recorded titles, own created records
- DBS 212 - Descriptive cataloging: Number of retroconversions
- DBS 213 - Subject cataloging: Verbally indexed titles
- DBS 214 - Subject cataloging: Classified indexed titles
DBS 215 - 223 | Staff, education level and training
- DBS 215 - Number of library staff positions according to "VZÄ" and financed from the institution's financier (excluding student assistants)
- DBS 216 - Number of library staff positions according to "VZÄ" and financed from the institution's financier: Ordinary level and intermediate level civil servants
- DBS 217 - Number of library staff positions according to "VZÄ" and financed from the institution's financier: Higher intermediate level civil servants
- DBS 218 - Number of library staff positions according to "VZÄ" and financed from the institution's financier: Higher level civil servants
- DBS 219 - Number of staff positions according to "VZÄ", financed with third-party funds (excluding student assistants)
- DBS 221 - Number of student assistants (Financing does not matter)
- DBS 222 - Person-month from the apprentices and interns
- DBS 223 - Number of training days for all employees
DBS 224 - 234 | Further libraries in the library system
- DBS 224 - Number of all libraries from the college
- DBS 225 - Number of all libraries from the college: Libraries with an inventory of over 100,000 items
- DBS 226 - Number of all libraries from the college: Libraries with an inventory of 50,001 to 100,000 items
- DBS 227 - Number of all libraries from the college: Libraries with an inventory of 10,001 to 50,000 items
- DBS 228 - Number of all libraries from the college: Libraries with an inventory of to 10,000 items
DBS 235 - 409 | Subject Statistics
- 19.1. General
- 19.2. Philosophy
- 19.3. Psychology
- 19.4. Religion and theology
- 19.5. Pedagogy, education, teaching
- 19.6. Sociology, society, statistics
- 19.7. Politics, public administration, military
- 19.8. Economy, work, tourism industry
- 19.9. Law
- 19.10. Nature, natural science in general
- 19.11. Mathematics
- 19.12. Informatics, cybernetics
- 19.13. Physics, astronomy
- 19.14. Chemistry
- 19.15. Earth Sciences, Mining
- 19.16. Biology
- 19.17. Medicine, veterinary medicine
- 19.18. Technology, basics
- 19.19. Mechanical engineering including materials science, production engineering, transport technology, microtechnology, process engineering
- 19.20. Electrical engineering including electronics, communication technology, energy technology
- 19.21. Civil engineering, mining engineering
- 19.22. Agricultural and forestry science, household and nutritional science, food technology
- 19.23. Environmental protection, spatial planning, landscape design
- 19.24. Architecture, fine arts, photography
- 19.25. Music, theater, dance, film
- 19.26. Sport
- 19.27. General and comparative linguistics and literature
- 19.28. English language and literature
- 19.29. German Linguistics and Literature
- 19.30. Romance Linguistics and Literature
- 19.31. Classical Linguistics and Literature
- 19.32. Slavic and Baltic Linguistics and Literature
- 19.33. Linguistics and literature of other languages
- 19.34. Archaeology, history, including social and economic history
- 19.35. Geography, local and regional studies, travel, atlases, folk and ethnology
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
Incunabula (incunable, lat.) are printed matters (not manuscripts) printed in Europe before the year 1501, printed with movable types.
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
The expenses for open access publications. That includes all costs for Open Access (APCs, BPCs, Open Access memberships, pledging (e.g. Knowledge Unlatched)).
In FOLIO there are different ways to record the costs for open access. You can use the FOLIO app "Open Access" and use it to manage costs, or you can configure special budgets, funds or cost types for Open Access expenses in the FOLIO app "Finance". This cookbook describes the database query options for both variants. Additional costs must be added if necessary.
FOLIO app "Open Access"
Example:
FOLIO app "Finance"
You can set up budgets, funds or cost types specifically for Open Access in the FOLIO “Finance” app and query them via the database. Since you only need the actual expenses, you should start from the actual transactions made. This means that you look for all invoice transactions for the relevant period and filter out the corresponding budgets, funds or cost types for open access. Another advantage is that the transactions are in the system currency. This means that you receive calculable amounts through the transactions.
Example:
WITH parameters AS (
SELECT
'APC' :: VARCHAR AS expense_class_name,
'' :: VARCHAR AS expense_class_code,
'' :: VARCHAR AS fund_name,
'' :: VARCHAR AS budget_name,
'' :: VARCHAR AS ledger_name,
'' :: VARCHAR AS fiscal_year_name,
'' :: VARCHAR AS vendor_code,
'' :: VARCHAR AS vendor_name
),
finance_funds AS (
SELECT
fiscal_year.id AS fiscal_year_id,
jsonb_extract_path_text(fiscal_year.jsonb, 'code') AS fiscal_year,
jsonb_extract_path_text(budget.jsonb, 'name') AS budget_name,
fund.id AS fund_id,
jsonb_extract_path_text(fund.jsonb, 'code') AS fund_code,
ledger.id AS ledger_id,
jsonb_extract_path_text(ledger.jsonb, 'code') AS ledger_code
FROM
folio_finance.fiscal_year
LEFT JOIN folio_finance.budget ON budget.fiscalyearid = fiscal_year.id
LEFT JOIN folio_finance.fund ON fund.id = budget.fundid
LEFT JOIN folio_finance.fund_type ON fund_type.id = fund.fundtypeid
LEFT JOIN folio_finance.ledger ON ledger.id = fund.ledgerid
),
transactions AS (
SELECT
transaction.id AS transaction_id,
CASE
WHEN jsonb_extract_path_text(transaction.jsonb, 'transactionType') = 'Credit'
THEN
jsonb_extract_path_text(transaction.jsonb, 'amount') :: NUMERIC(19,2) * -1
ELSE
jsonb_extract_path_text(transaction.jsonb, 'amount') :: NUMERIC(19,2)
END AS transaction_amount,
jsonb_extract_path_text(transaction.jsonb, 'currency') AS transaction_currency,
jsonb_extract_path_text(transaction.jsonb, 'sourceInvoiceId')::UUID AS source_invoice_id,
jsonb_extract_path_text(transaction.jsonb, 'sourceInvoiceLineId')::UUID AS source_invoice_line_id,
jsonb_extract_path_text(transaction.jsonb, 'fromFundId')::UUID as from_fund_id,
jsonb_extract_path_text(transaction.jsonb, 'fiscalYearId')::UUID AS fiscal_year_id,
jsonb_extract_path_text(transaction.jsonb, 'transactionType') AS transaction_type,
transaction.expenseclassid AS transaction_expense_class_id,
jsonb_extract_path_text(expense_class.jsonb, 'name') AS transaction_expense_class_name,
jsonb_extract_path_text(expense_class.jsonb, 'code') AS transaction_expense_class_code,
CASE
WHEN jsonb_extract_path_text(transaction.jsonb, 'transactionType') = 'Credit'
THEN
transaction.tofundid
ELSE
transaction.fromfundid
END AS transaction_fund_id
FROM
folio_finance.transaction
LEFT JOIN folio_finance.expense_class ON expense_class.id = transaction.expenseclassid
WHERE
(
jsonb_extract_path_text(transaction.jsonb, 'transactionType') = 'Pending payment'
OR jsonb_extract_path_text(transaction.jsonb, 'transactionType') = 'Payment'
OR jsonb_extract_path_text(transaction.jsonb, 'transactionType') = 'Credit'
)
),
invoice_lines__fund_distributions AS (
SELECT
invoice_lines.id,
jsonb_extract_path_text(fundDistributions.jsonb, 'code') AS code,
jsonb_extract_path_text(fundDistributions.jsonb, 'value') :: NUMERIC(19,2) AS value,
jsonb_extract_path_text(fundDistributions.jsonb, 'fundId') :: UUID AS fund_id,
jsonb_extract_path_text(fundDistributions.jsonb, 'expenseClassId') :: UUID AS expense_class_id,
jsonb_extract_path_text(fundDistributions.jsonb, 'distributionType') AS distribution_type
FROM
folio_invoice.invoice_lines
CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(invoice_lines.jsonb, 'fundDistributions')) WITH ORDINALITY AS fundDistributions (jsonb)
),
all_transactions AS (
SELECT
*
FROM
transactions
LEFT JOIN finance_funds ON finance_funds.fund_id = transactions.transaction_fund_id
AND finance_funds.fiscal_year_id = transactions.fiscal_year_id
LEFT JOIN invoice_lines__fund_distributions ON invoice_lines__fund_distributions.id = transactions.source_invoice_line_id
AND invoice_lines__fund_distributions.expense_class_id = transactions.transaction_expense_class_id
LEFT JOIN folio_invoice.invoices ON invoices.id = invoice_lines__fund_distributions.id
LEFT JOIN folio_organizations.organizations ON organizations.id = jsonb_extract_path_text(invoices.jsonb, 'vendorId')::uuid
WHERE
invoice_lines__fund_distributions.expense_class_id IS NOT NULL
AND
((finance_funds.fiscal_year = (SELECT fiscal_year_name FROM parameters)) OR ((SELECT fiscal_year_name FROM parameters) = ''))
AND
((finance_funds.ledger_code = (SELECT ledger_name FROM parameters)) OR ((SELECT ledger_name FROM parameters) = ''))
AND
((finance_funds.budget_name = (SELECT budget_name FROM parameters)) OR ((SELECT budget_name FROM parameters) = ''))
AND
((finance_funds.fund_code = (SELECT fund_name FROM parameters)) OR ((SELECT fund_name FROM parameters) = ''))
AND
((transactions.transaction_expense_class_name = (SELECT expense_class_name FROM parameters)) OR ((SELECT expense_class_name FROM parameters) = ''))
AND
((transactions.transaction_expense_class_code = (SELECT expense_class_code FROM parameters)) OR ((SELECT expense_class_code FROM parameters) = ''))
AND
((jsonb_extract_path_text(organizations.jsonb, 'code') = (SELECT vendor_code FROM parameters)) OR ((SELECT vendor_code FROM parameters) = ''))
AND
((jsonb_extract_path_text(organizations.jsonb, 'name') = (SELECT vendor_name FROM parameters)) OR ((SELECT vendor_name FROM parameters) = ''))
--
UNION
--
SELECT
*
FROM
transactions
LEFT JOIN finance_funds ON finance_funds.fund_id = transactions.transaction_fund_id
AND finance_funds.fiscal_year_id = transactions.fiscal_year_id
LEFT JOIN invoice_lines__fund_distributions ON invoice_lines__fund_distributions.id = transactions.source_invoice_line_id
AND invoice_lines__fund_distributions.expense_class_id = transactions.transaction_expense_class_id
LEFT JOIN folio_invoice.invoices ON invoices.id = invoice_lines__fund_distributions.id
LEFT JOIN folio_organizations.organizations ON organizations.id = jsonb_extract_path_text(invoices.jsonb, 'vendorId')::uuid
WHERE
invoice_lines__fund_distributions.expense_class_id IS NULL
AND
((finance_funds.fiscal_year = (SELECT fiscal_year_name FROM parameters)) OR ((SELECT fiscal_year_name FROM parameters) = ''))
AND
((finance_funds.ledger_code = (SELECT ledger_name FROM parameters)) OR ((SELECT ledger_name FROM parameters) = ''))
AND
((finance_funds.budget_name = (SELECT budget_name FROM parameters)) OR ((SELECT budget_name FROM parameters) = ''))
AND
((finance_funds.fund_code = (SELECT fund_name FROM parameters)) OR ((SELECT fund_name FROM parameters) = ''))
AND
((transactions.transaction_expense_class_name = (SELECT expense_class_name FROM parameters)) OR ((SELECT expense_class_name FROM parameters) = ''))
AND
((transactions.transaction_expense_class_code = (SELECT expense_class_code FROM parameters)) OR ((SELECT expense_class_code FROM parameters) = ''))
AND
((jsonb_extract_path_text(organizations.jsonb, 'code') = (SELECT vendor_code FROM parameters)) OR ((SELECT vendor_code FROM parameters) = ''))
AND
((jsonb_extract_path_text(organizations.jsonb, 'name') = (SELECT vendor_name FROM parameters)) OR ((SELECT vendor_name FROM parameters) = ''))
)
SELECT
SUM(all_transactions.transaction_amount) AS expenses,
all_transactions.transaction_currency AS currency,
COUNT(all_transactions.transaction_id) AS count_transactions,
all_transactions.fiscal_year
FROM
all_transactions
GROUP BY
currency,
fiscal_year
ORDER BY
fiscal_year DESC
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
19.19. Mechanical engineering including materials science, production engineering, transport technology, microtechnology, process engineering
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]
[top]