TMS data augmentions

[RGShepherd]

This is a place to record augmentations which could be made in TMS that, once incorporated into the CIIM, would improve our interoperable data.

[RGShepherd]

1

Set up and record co-owners of paintings as locations in their own right, so that we can properly record changes of custody in Linked Art when paintings are with their other owners. (See #8)

Consider doing the same for transferred paintings (plus giving them 'owners').

Completed 29/12/2023 - data already exists in publish.vwNGLoanOutLocations and has now been added to publish.vwNGObjects.CurrentCustodianID, but is still to be added to CIIM extraction.

[RGShepherd]

2

Add external venues' IDs alongside the location supplement for loans out to the relevant view, so that they can be given proper IDs in Linked Art.

Completed 29/12/2023 - data already exists in publish.vwNGLoanOutLocations and has now been added to publish.vwNGObjects.CurrentCustodianID, which also allows for various 'don't know' locations and transferred objects, but is still to be added to CIIM extraction.

[jpadfield]

More long term perhaps, but having considered this a few times, it might be worth specifically considering what work would be needed to record internal painting locations as a position on a wall rather than just a room.

How do we currently manage a painting being moved within a room at the moment, is this recorded the same way as between rooms?

[RGShepherd]

3

Remove alternate number type 'acquisition reference' from TMS view (and others as necessary). (See #7 (comment) and #7 (comment)).

Completed 17/11/2023.

[RGShepherd]

More long term perhaps, but having considered this a few times, it might be worth specifically considering what work would be needed to record internal painting locations as a position on a wall rather than just a room.

This is a very low priority just now, and we will consider it when we decide we want to do it, rather than waste time on it now.

How do we currently manage a painting being moved within a room at the moment, is this recorded the same way as between rooms?

No, it's not recorded at all. Locations in TMS are recorded to room / rack level only, which are all that is needed to manage the collection.

[RGShepherd]

4

Review publication rules for dimensions, against publish.vwNGObjDimensionXrefs.Element = measurements.type.

Completed 17/11/2023 (#22 (comment)).

[RGShepherd]

5

Link Events to Places and Constituents.

[RGShepherd]

6

Include agent-agent relationships (e.g. former directors to NG): #20

Completed 28/12/2023 - data already exists in publish.vwNGConRelationships, but is still to be added to CIIM extraction; addition to $.agents and $.parents blocks listed in NG responses Doc.

[RGShepherd]

7

Find a way of connecting Schools to an authority / thesaurus, rather than relying on free text.

[RGShepherd]

8

Review and standardise entries in Object-related ObjConXref Prefix and Suffix, and make sure that all artists' names are stored as links to the relevant Constituent rather than free text.

Underway 29/12/2023 - for details see #13 (comment).

USE TMS

SELECT
	COUNT(*)
	, OCX.Prefix AS Qualifier
	, 'prefix' as Type

FROM publish.vwNGObjConXrefs OCX
	JOIN publish.vwNGObjects O ON (OCX.ObjectID = O.ObjectID AND O.PublicAccess = 1)

WHERE Role IN ('Artist', 'Lender''s Attribution', 'Previous Attribution')
	AND OCX.Prefix IS NOT NULL

GROUP BY OCX.Prefix

UNION

SELECT
	COUNT(*)
	, OCX.Suffix AS Qualifier
	, 'suffix' as Type

FROM publish.vwNGObjConXrefs OCX
	JOIN publish.vwNGObjects O ON (OCX.ObjectID = O.ObjectID AND O.PublicAccess = 1)
	AND OCX.Suffix IS NOT NULL

WHERE Role IN ('Artist', 'Lender''s Attribution', 'Previous Attribution')

GROUP BY OCX.Suffix

ORDER BY COUNT(*) DESC

And appending a conversation from Secret Slack:

Rupert Shepherd
So, we're trying to map TMS-derived data to Linked Art (it's going through our middleware along the way), and ... well, there's a nice flat patch on my forehead where I've been banging it on my desk. There are so many places where we need to map to an AAT URI, and there's nowhere to store it in TMS (and I wouldn't want to hard-code mappings from free-text to URI in our data transformations). Anyone else out there trying / tried to map to Linked Art; and how have you addressed the problem? We're primarily hitting this anywhere where we draw on an authority; and also (prompting this message) with attribution qualifiers held in Prefix and Suffix in ConXrefs.

Brenda Podemski
I definitely hear you on this, and have recommended to GS they include more ways to link to external vocabularies within the TMS data structure.

We're lucky to have some very clever folks here that are doing some of the easier mapping for us during the transform process, but for the most part we are treating many of these fields as linguistic objects and generating Getty URIs for them. Yuck 😞

We do have several data projects going to map terminology to vocabs via Term Attributes, which will help us do things like linking individual medium values. For TMS-authority tables that don't currently have places for link information I'm testing out a custom table that can serve as a TMS-wide VOCAB cross reference for authorities. For example, the TMS Constituent RoleID 1 = Artist. There is a corresponding entry in this table with ID = 1, TableID = 149 (ROLES), Source = AAT, and SourceID = 300025103. Still toying around with the other fields that might be helpful to include (e.g. PublicAccess flag, Primary flag, Remarks, etc.). It's not currently in use yet, but I want to start testing it out so I can determine what else would be needed.

And if in future GS starts including places for this information to live within TMS, we can always migrate the data at that time.

Brenda Podemski
For your Prefix/Suffix conundrum, that gets trickier because there isn't an authority for those fields. You could build your own authority table and match on the actual field values rather than ID, but you'd need to monitor data entry pretty regularly to ensure no errant terms get added.

Rupert Shepherd
Hmm. Hadn't though of custom tables; part of me is deeply resistant, because of data integrity issues and a fundamental reluctance to over-customise any application; on the other hand, I did bite the bullet and create one in order to massively simplify data-entry for logging light sensitivities. Do let me know how the testing goes. (Immediate thought: do you need a field identifier column in the table, too, if you're using it to manage multiple fields in the same table?)

I recall there being something in the help text somewhere about being able to configure fields to look up from the Thesaurus and use it as a term picklist without making the actual thesaurus link (:face_palm:), which I will look into; then we could join on the term value (sub-optimal, but needs must).

But we're now looking quite seriously at TMS Collections, because the fundamental UX in non-Object module is potentially much better for our users in departments like Exhibitions. Which means that, given the investment, we are looking at other systems - and we'll certainly let GS know as much. One of the problems we'll them we have is a need for 'AltNums everywhere' - the ability to attach (ideally multiple) external identifiers to:

• every entity (i.e. the key entity in every module)
• every authority term

Pincer movement?

Rupert Shepherd
In other news, we're thinking about installing vocabulary management software to handle termlists / thesauri that need to be accessed by multiple systems across the Gallery. We could consider using this in place of a TMS custom table, and so handle all the mapping in the middleware.

[RGShepherd]

9

Start mapping TMS authority terms to external identifiers, in preparation for 'AltNums everywhere', probably using a temporary table, as identified in #13 (comment) - this could also resolve #13 (comment).

Underway 29/12/2023 - for details see #13 (comment).

[RGShepherd]

Summary

For monitoring progress

1. ✔ Set up and record co-owners of paintings as locations in their own right, so that we can properly record changes of custody in Linked Art when paintings are with their other owners. Consider doing the same for transferred paintings (plus giving them 'owners'). TMS data augmentions #13 (comment) - completed 29/12/2023 - data already exists in publish.vwNGLoanOutLocations and has now been added to publish.vwNGObjects.CurrentCustodianID, but is still to be added to CIIM extraction
2. ✔ Add external venues' IDs alongside the location supplement for loans out to the relevant view, so that they can be given proper IDs in Linked Art. TMS data augmentions #13 (comment) - completed 29/12/2023 - data already exists in publish.vwNGLoanOutLocations and has now been added to publish.vwNGObjects.CurrentCustodianID, which also allows for various 'don't know' locations and transferred objects, but is still to be added to CIIM extraction
3. ✔ Remove alternate number type 'acquisition reference' from TMS view (and others as necessary). TMS data augmentions #13 (comment) - completed 17/11/2023
4. ✔ Review publication rules for dimensions, against publish.vwNGObjDimensionXrefs.Element = measurements.type. TMS data augmentions #13 (comment) - completed 17/11/2023 (Questions for K-Int #22 (comment))
5. Link Events to Places and Constituents. TMS data augmentions #13 (comment)
6. ✔ Include agent-agent relationships (e.g. former directors to NG). TMS data augmentions #13 (comment) - completed 28/12/2023 - data already exists in publish.vwNGConRelationships, but is still to be added to CIIM extraction; addition to $.agents and $.parents blocks listed in NG responses Doc
7. Find a way of connecting Schools to an authority / thesaurus, rather than relying on free text. TMS data augmentions #13 (comment)
8. Review and standardise entries in Object-related ObjConXref Prefix and Suffix, and make sure that all artists' names are stored as links to the relevant Constituent rather than free text. TMS data augmentions #13 (comment) - update 12/12/2023:
   1. Standardise use of prefixes and suffixes in TMS: underway (seeking curatorial authorisation)
   2. ✔ Construct attribution strings in TMS for reuse on the website etc.: dbo.vwNGConstituentsWithHyperLinks, dbo.vwNGConstituentsQualified and dbo.vwNGAttributionStrings in test and live TMS
   3. ✔ Create custom table in TMS to hold external IDs for prefixes / suffixes: dbo.NGPrefixSuffixLODVocabs created in test and live TMS
   4. ✔ Add test data to TMS test: initial dataset uploaded to test and live TMS from spreadsheet prefix_suffix.xslx
   5. ✔ Update the CIIM publish.vwObjects view with (current) attribution strings: updated in test and live TMS
   6. ✔ Add LOD IDs to CIIM publish.vwNGObjConXrefs: updated in test and live TMS
   7. Incorporate new fields into CIIM indexes:
      • publish.vwNGObjConXrefs.AATQualifierTerm (n.b. multiple values are pipe delimited)
      • publish.vwNGObjConXrefs.AATQualifierID (n.b. multiple values are pipe delimited)
      • publish.vwNGObjects.Attribution
      • publish.vwNGObjects.AttributionFormatted
      • publish.vwNGObjects.AttributionIncDates
      • publish.vwNGObjects.AttributionIncDatesFormatted
   8. Review values in prefix and suffix, and related custom table, following final update of attributions (n.b. eliminate rows which combine a meaningful prefix and suffix on the same row)
9. Start mapping TMS authority terms to external identifiers, in preparation for 'AltNums everywhere', probably using a temporary table. TMS data augmentions #13 (comment) - update 29/12/2023:
   1. ✔ Identify initial data structure: completed
   2. ✔ Map initial terms: spreadsheet assembled for terms in AltNumDescriptions, Classifications, DimensionTypes, DimensionUnits, EventTypes, Roles (partial - not all contexts will be exported) - many terms lack AAT equivalents
   3. ✔ Query missing terms with Getty: first selection from Classifications, DimensionUnits and Roles added to AAT
   4. ✔ Create custom table in TMS to hold external IDs: dbo.NGLODVocabManager created in test and live TMS
   5. ✔ Add test data to TMS test: data uploaded from LOD_vocabulariies.xslx filtered for UploadDate IN (15/12/2023, 29/12/2023) into test and live TMS
   6. ✔ Add LOD IDs to the relevant CIIM views Updated in live and test TMS
   7. Incorporate new fields into CIIM indexes:
      • publish.vwNGObjects.AATClassificationTerm (n.b. multiple values are pipe delimited)
      • publish.vwNGObjects.AATClassificationID (n.b. multiple values are pipe delimited)
      • publish.vwNGObjAltNums.AATDescriptionTerm
      • publish.vwNGObjAltNums.AATDescriptionID
      • publish.vwNGDimensions.AATDimensionTypeTerm (n.b. multiple values are pipe delimited)
      • publish.vwNGDimensions.AATDimensionTypeID (n.b. multiple values are pipe delimited)
      • publish.vwNGDimensions.AATUnitTerm
      • publish.vwNGDimensions.AATUnitID
      • publish.vwNGObjConXrefs.AATRoleTerm
      • publish.vwNGObjConXrefs.AATRoleID
      • publish.vwNGConAltNums.AATDescriptionTerm
      • publish.vwNGConAltNums.AATDescriptionID
      • publish.vwNGBibConsResponsible.AATRoleTerm
      • publish.vwNGBibConsResponsible.AATRoleID
      • publish.vwNGBibAltNums.AATDescriptionTerm
      • publish.vwNGBibAltNums.AATDescriptionID
      • publish.vwNGEvents.AATEVentTerm
      • publish.vwNGEvents.AATEventID