Library Item Library Item

Dim_Library_Item holds details of items (books/CDs/ DVDs/ ebooks..) which can be loaned from the library.  It is based largely upon table UEA50.Z30 in Aleph.  Additional bibliographic information is taken from UEA50.Z13 in Aleph.

Because Aleph holds the date in format YYYYMMDD some dates on the dimension have been left in this format, whereas normally they would be displayed as a normal date.  The main difference is that on Aleph no date is shown as '0' whereas in the dimension it is shown s '-1'.


Dimension Fields

Column Name Data Type Description Derivation
item_sk integer Should not be used for reporting.  Purely for internal use. Automatically increments each time a record is added.
source_system varchar 'ALEPH' Hard-coded
item_record_key_src varchar Unique identifier on source for the item z30_rec_key


varchar Unique source identifier for the particular book/ other item Characters 1-9 of z30_rec_key
item_copy_no varchar Particular copy of the book/ other item Characters 10-15 of z30_rec_key
item_barcode varchar Internal barcode of item z30_barcode
item_sublibrary_src varchar Code of the sub-library that 'owns' the item z30_sub_library
item_sublibrary varchar Description of the sublibrary as above See DW_Library_Sublibrary in Warehouse Tables for details.
item_loantype_src varchar Code for the type of loan z30_item_status
item_loantype varchar Description of the type of loan See DW_Library_Loantype in Warehouse Tables for details.
item_create_date_sk integer Date copy was created on the system z30_open_date
item_update_date_sk integer Date copy was last updated on the system z30_update_date
item_updated_by varchar Username of person who updated record z30_cataloger
item_date_last_return_sk integer Date of last return transaction z30_date_last_return
item_hour_last_return_src varchar Hour and minute in format HHMM when last returned z30_hour_last_return
item_loan_number_src integer Number of times the item has been loaned z30_no_loans
item_collection_src varchar Collection code of the item z30_collection
item_collection varchar Description of the collection See DW_Library_Collection in Warehouse Tables for details
item_shelf_collection_src varchar Shelving location of the item z30_call_no
item_withdrawal_src varchar   z30_call_no_2
item_volume_info_src varchar Particular volume of the item z30_description
item_order_number_src varchar Order number of the acquisitions order record z30_order_number
item_invoice_src     z30_rec_key_3
item_availability_status_src varchar Code for process status of the item When null or blank then 'AV' (Means available), else z30_item_process_status
item_availability_status varchar Description for process status of item See DW_Library_Availability_Status in Warehouse Tables for details.
item_availability_status_update_date_sk integer Date availability status was last updated z30_process_status_date
item_classmark_src varchar Classmark of the item Leading characters, before numeric values, in z30_call_no
item_classmark_level varchar Number of letters matched against source record 1 if first letter matched, 2 if 2 matched, 3 if 3 matched.
item_classmark_level_1_code varchar Code for highest level, 1 letter, classmark First character of classmark_src, if alphabetic
item_classmark_level_1_desc varchar Description of highest level classmark See DW_Library_Classmark on Warehouse Tables for details.
item_classmark_level_2_code varchar Code for second level classmark, up to 2 letters First 2 characters of  classmark_src, if alphabetic
item_classmark_level_2_desc varchar Description of classmark with up to 2 letters As item_classmark_level_1_desc
item_classmark_level_3_code varchar Code for third level classmark with up to 3 letters First 3 characters of  classmark_src, if alphabetic
item_classmark_level_3_desc varchar Description of classmark with up to  3 letters As item_classmark_level_1_desc
bib_create_date_sk integer Date bibliographic item record was created z13.z13_open_date
bib_update_date_sk integer Date bibliographic item record was updated z13.z13_update_date
bib_classmark_src varchar Classmark record for bibliographic item z13.z13_call_no
bib_main_author varchar Main Author of book/ other item z13.z13_author
bib_title varchar Title of book/ other item z13.z13_title
bib_imprint varchar Information about publication. (Imprint is publishing term). z13.z13_imprint_code
bib_isbn_or_issn varchar Isbn (International Standard Book Number) or issn (International Standard Serial Number) z13.z13_isbn_issn
bib_publication_year_src varchar Field used to derive publication year. First 10 chars of z13.z13u_user_defined_2
bib_publication_year varchar Year the book/ other item was published. First 4 characters of bib_publication_year_src, if they all are numeric.
bib_last_return_date_sk integer Latest date bibliographic item was returned. Latest of item_date_last_return_sk where the bibliographic_id_src values are the same
item_withdrawal_year integer Year item was withdrawn Characters 3-6 from item_withdrawal_src


Technical Notes

Dim_Library_Item is a view based on an underlying table Dim_Library_Item_Base.