Navigate to Tableau reports tagged as useful for Library.
This is an organised list of Library reports in Tableau as of March 2017, with brief notes on what they are for, and, where access is not ‘normal’, on who has access; more specific information on each report is in the Help sheet present for most workbooks or the ‘caption’ information below most sheets, or both.
Note that reports may be changed at any time if a need arises; the Caption information will normally contain the most up-to-date information. (Please note that, as of September 2017, there is a minor display problem with the last line of some captions, for which the solution is to include dummy text as the last lines, i.e. a row of x-es, and in some cases that row, or part of it, may be visible.)
All reports are originally based on Aleph data unless stated otherwise. All reports are based on Aleph data passed through ‘Data Warehouse’ unless stated otherwise; this means that they can be up to a week out-of-date, but also that they can where appropriate be linked with student information (e.g. school, nationality), though see the note below re. staff.
Access: ‘normal’ access is for a group consisting of management, Aleph module leaders and their ‘significant others’, and AELs. Membership of this group is controlled by Alan Exelby, Heather Wells, Catherine Breame and Ed Chamberlain, through SPOT.
Note: in reports created by AVE, little use was made before summer 2017 of the links with ‘registry’ type information (school etc.), and since then we have only two such reports,’Cash (Fines etc.)’ and ‘Stock Usage by School’. Please be aware that there are two difficulties in this area:
1. staff. Our data, e.g. loans, fits together quite well with details about students, but does not always fit with staff; before summer 2017, there was no link with staff information at all, and staff were generally lumped with other non-staff categories under ‘Other’, ‘Unknown’ or similar categories; although Liam Dolder has made major advances in solving this, some problems remain, either because some types of information are not available at all for staff or because others do not contain the right sort of information. Fuller notes are in the Word document ‘Tableau Desktop draft.docx’ referred to below.
2. People moving: when users move to a new unit (change School etc.) or a new level (UG to PG, PG to staff), any transactions move with them, because the links are to their current position at university level.
Some technical information is kept in a separate notes document in Word (‘Tableau Desktop draft.docx’) in LIB MS Docs Share, folder ‘data_warehouse’. Further information about the Data Warehouse element is in the Confluence wiki (https://ueacis.atlassian.net/wiki/display/LS/Library+data+warehouse+configuration+work), but access to this is limited to Ed Chamberlain, Ally Carroll and Alan Exelby with no ability by the Library to persuade IT staff to add more, so increasingly even the DW side is being documented in the Word document.
This is the default report for analysis of stock usage at the level of individual works/items. Its primary contents are separate sheets for title-level and copy-level.
In January 2017 a third sheet was added providing for filtering by collection code to enable specific review of pamphlets; and in March 2017 a fourth sheet was added for LaRC branch (now known as CSED Collection) to address classmark problems with this non-LC-classified branch (details in the Caption help for the sheet).
This report is intended to show materials with high recent usage. It has separate sheets for title-level and copy-level. The default setting is for 6 or more loans in the chosen calendar year, but this can be changed using the filter for ‘Loan Count’. Warning: the sheets can take a minute or more to load.
It is possible, with substantial alterations of the filters, to use this report to identify ‘most popular’ books, though only on the basis of calendar years.
This report has sheets for loans and items in stock broken down by classmark, not at the level of individual works (q.v. sub ‘Standard Stock Usage’). The Loans / Renewals sheet is self-explanatory; ‘Items that exist currently’ provides a stock count, for use on its own or in comparison with loans; the other three sheets offer various ways of looking at usage. There are two versions of ‘Items that have been loaned total...’ called ‘1994’ and ‘2002’, and these are based on different types of counting; see the Help sheet (dashboard) for a full explanation.
Intended to list books with multiple copies, as part of the major 2016/17 withdrawals work where it was of critical value, but with potential for continuing use. There are three elements: a classmark filter at the top, a list of multiple copies at the chosen classmark (which may be empty if the chosen classmark no longer has any multiple copies), and a linked ‘Detail’ section to show some extra information about a line selected in the list (this extra information cannot be included in the list because it is copy-specific, and putting it in the list would make every potential entry in the list a single copy and therefore stop it appearing in the list).
Warning: the Detail section has a column headed Item Bibliographic Id Src’; this is actually the Aleph ADM number, not bibliographic (BIB) number (in 1-2% of cases these numbers are different) and should be labelled accordingly; attempts to fix this in 2016 ran into a bug in Tableau Desktop and although this was after much effort ‘solved’, there remains a risk that attempting to fix it will break the report in Server, and the report is too important to risk breaking. As of July 2017, we are continuing to leave this as-is but will work on it if staff find the incorrect naming of the file to be a problem.
This is a new report developed October 2017 to analyse usage of Library stock by school or other category. The main sheet shows usage across schools for recent years, and is set with undergraduates as a default; other categories may be chosen using the filters. A second sheet provides a general idea of the usage of stock for a specific school (Mathematics is the default, but this can be changed using the filter) based on highest-level LC classmarks.
100% accuracy is not possible; see specific warnings in the Caption help for cases where changes in a person’s membership of UEA can ‘move’ their loan details. The figures were sample-checked against a similar report in Aleph (custom-03) in early November 2017 for the AY 2016/17, and taking the custom-03 report as ‘correct’ (not actually proven), Tableau figures were 91-100% accurate.
This was a draft two-sheet report created by David Astley in the early days of developing Tableau for the Library.
Owned by David Astley, not AVE, but a file copy has been saved in AVE’s Desktop for potential future development.
As of July 2017 there has been no interest shown in this type of reporting, but the report has been left showing in the front end as a valuable indicator of the sort of reporting that is feasible. Content has not been checked, and there are known formatting problems with numbers (digits not displayed). Since the report is not used, no action has been taken to update it in line with Liam Dolder’s revised set-up of ‘Loan Member’ to include staff.
All of these reports are based on SQL, not Data Warehouse; the SQL, though its product in Tableau is named ‘Library_Periodicals’, is intended for use in any Acq-type reporting, not just periodicals. As an SQL-based report, this cannot be integrated with ‘registry’ type data such as ‘Loan Member...’. The Help sheet (dashboard) lists the columns available for inclusion in reports; the full SQL is recorded in a separate notes document in LIB MS Docs Share, folder ‘data_warehouse’.
Provides information about current, past and projected future pricing of periodicals, both summary and detailed (title) level, with variants according to level of detail required. See the Captions for each sheet for the differences between them where this is not obvious; the ‘Full list title’ is the closest approximation to the pre-Tableau version of the PPR.
SQL, not DW (see ‘All of these reports’ note above).
Three general notes:
1. Negative amounts: almost all amounts in this report are debits, but, for creating a different report using the same SQL, it was essential to distinguish credits from debits, therefore all debits from the SQL are marked as ‘minus’; this causes ‘% Difference’ figures also to appear as minus when the amount paid has increased. In a % Difference column, read a minus sign in as indicating an increase in price by that percent; read an absence of a minus sign as indicating a decrease in price. See the Caption help for the sheet ‘Jane’s exportable’ for a fuller explanation.
2. No titles. If an entry in this report has no title shown, it is probably an ‘allocation’ transaction; initial and update allocations are normally credits, but may sometimes be debits, where an amount has been deducted from a budget. To confirm this, check against the Aleph GUI: ACQ module, Administration tab, Budgets node, find the relevant budget and look at its Transactions tab, specifically for transactions of type ILC and ALC.
3. Colour-coding: one sheet (‘Jane’s exportable’) contains colour-coding originally intended to highlight substantial increases; but the purpose of this sheet is to create data for the Head of Academic Liaison to put into a spreadsheet, and the colour-coding does not export to Excel (although this fact is not obvious due to UEA-wide settings, files exported from Tableau Server are .csv files, not Excel files, and .csv files cannot contain formatting), so the coding is effectively redundant. In addition, since the start of distinguishing credits from debits (point 1 above), the colour-coding is operating in reverse of logic (the reddest colour means the biggest decrease in price). Please ignore all colour coding.
Spending in different currencies broken down by vendor.
SQL, not DW (see ‘All of these reports’ note above).
This is a draft showing a summary of spending against ‘standing order’ budgets, originally produced January 2017; changes in budgeting summer 2017 will probably make this report pointless.
SQL, not DW (see ‘All of these reports’ note above).
This is a summary report of spending on ‘book’ budgets (not periodicals, not standing orders, not ‘recurrent’).
SQL, not DW (see ‘All of these reports’ note above).
This is still considered as ‘in progress’; an apparently simple addition of ‘initial’ allocations (to enable easy review of how much proportionally of a budget has spent ‘so far’) took so much effort that no further tweaks will be made until management has reviewed seriously how (if at all) it wishes this report to be used.
NB: this is a summary, and as of July 2017 it is not currently anticipated that detailed title-level reports will every be produced for ‘book’ budgets; there is no need for year-vs-year review (since books are one-off purchases, such a review would be pointless) and it is assumed that display of information within the Aleph GUI, and reports produced by the Aleph GUI, will take care of all needs in this area.
Currently only one report, with several sheets: two summaries of amounts, and a sample breakdown intended solely to show the type of further breakdown possible (though the breakdown given here, by domicile, was once the subject of a FOI request). This is intended primarily to help answer FOI requests about fines, because before this report, the Library did not have this sort of information to hand at all.
One report with several sheets:
From SQL, not DW. The full SQL is recorded in a separate notes document in LIB MS Docs Share, folder ‘data_warehouse’. Report was the first Library report in Tableau and was developed by BIU, and was originally ‘owned’ by David Astley, but ownership was transferred to AVE on 17.8.17. As of July 2017, this has not been updated to include Captions and a Help sheet (dashboard) to match Library practice developed since then (but a brief Description was added through Server).
Two summary sheets are for use by management in tracking expenditure on ILLs (only within Aleph); these are still used under the revised practices with ILL.
Two detail sheets are intended for use by AELs to monitor specific usage, to identify areas where they may need to step in e.g. to purchase stock. Due to ‘data protection’ concerns, individual readers are identified only by their Aleph Patron ID, which exists only in Aleph and can be used to identify a person only by Library staff with an Aleph password and appropriate permissions within Aleph; all users of this report must be aware of DPA issues when viewing it, and not share it with others. As of July 2017, it is not known if these two sheets are still used by AELs, and how valuable they are given that much ILL work now takes place outside Aleph.
Note: this was re-titled from its original ‘DWR39 Interlibrary loans’ (a name copied over from its previous incarnation in Discoverer; original URL = https://reports.uea.ac.uk/#/workbooks/118/views) but was re-titled by David Astley in July 2017; unfortunately, the process of doing so requires replacing, not simple re-naming, so the counts of previous ‘views’ were lost (the total views across all sheets recorded on 7.7.17 was 376).
A group generally thought of as primarily used by Heather Wells or Cath Breame or both.
Provides a count of swipe entries into the Library building through the main entrance gates. Separate reports show totals by day, week and month and also by Faculty and School.
Note: as of 12.7.17, on Heather Wells’ request, we have asked BIU to make this report accessible to all Tableau staff users, not just the special Library group, and as of 17.8.17 David Astley is conferring with HW about this. Action also required by DA as of 14.7.17 re. LD’s new ‘Loan Member...’ Dimension; AVE to DA 17.7.
Access limited to Cath Breame and possibly Heather Wells. Provides a list of manually created SPOT records (as opposed to these from SITS or ResourceLink).
The report can be filtered by department and by a range of expiry date parameters. A second tab provides all SPOT visitor records (both current and expired) which can only be filtered by department. This is useful when needing to view both live and expired records. The report is used for reporting purposes and almost exclusively looks at records under the LIB and EXT departments.
‘Owned’ by David Astley, not AVE.
NB: as of 12.7.17 this was broken (still so, 17.8.17), because Liam Dolder has removed the database; this is under investigation. Once this ‘broken’ aspect is fixed, action probably required by DA as of 14.7.17 re. LD’s new ‘Loan Member...’ Dimension; as of 17.8.17, anticipating Cath Breame will liaise with David Astley on this.
(Note: this is not officially a Library report at all, as it is intended for ITSD, but there is no ‘group’ into which this could be put except ‘Library’; most Library staff will not have permission to see it anyway – if you do see it, please ignore it. Ex inf. David Astley 17.8.17.)
(Note: this is not a Library report, and is not visible to the normal group of Library Tablea users, but is accessible by Rob Mitchell; however, as of May 2018, RM prefers to use an Excel route into the cubes because the current Tableau report is not useful for the Library, but that route may be withdrawn in the future, compelling use of the Tableau version. If you wish to view this report, please see Rob Mitchell.
‘Owned’ by David Astley, not AVE, and with access controlled by SPOT group CIS_DW_Group_Planning_Cube.)
Intended to produce files suitable (with some post-processing) for use in the Bibliotheca DLA (Digital Library Assistant) device used for shelf-reading.
As of July 2017, this is still in the early stages of development, as, although the report was first produced n September 2016, it has not been tested fully in use, so Caption help and a Helpsheet (dashboard) have not been created yet.
Intended to allow Library staff to assess the size of modules in connection with Talis work (such as identifying past modules that are no longer running, to enable deletion lists) and purchasing. Created by David Astley January 2017 but rarely used as of July 2017.
Problems were noted in January 2017 with small numbers of course appearing under completely inappropriate departments; as of July 2017, management considers that there are not enough of these to invalidate use of the report.
The Library does not use Tableau to duplicate what is already in Aleph, i.e. information that can be obtained through viewing parts of the Aleph GUI (e.g. ACQ module, Administration tab, Budgets node, Transactions tab for a selected specific budget), or that can be obtained through running Services in the various modules.
Occasionally, custom Services have been superseded by Tableau reports that can do more than Aleph, but note the custom service to count Items on Loan by Classmark (CIRC, Services, Custom Services, custom-15), which cannot be put in Tableau because it requires up-to-the-minute accurate information while Tableau can be up to a week out of date.