Upcoming changes to journal-related columns in publication tables in v6.4

On occasion, it is necessary to improve the structure of the Reporting Database in such a way that existing SQL queries crafted by clients will require a small change to continue functioning. This article provides advance notice of a planned alteration to the way journal information is represented in the Reporting Database for publications.

For a long time, Elements has maintained automatically-calculated correspondences between publication and journal objects, using metadata found in publication and journal records. This work makes a few changes to how data relating to these correspondences is exposed in the Reporting Database.

As with all other correspondences between data entities in Elements, correspondences between publications and journals are, and always have been, maintained at the publication level. This work reinforces that approach by removing columns describing additional competing references to journals from the various source-specific metadata records within a publication, simplifying the product.

This has been prompted by the need to make some under the bonnet refactoring of the way journals are used in the system easier to implement.

The following Elements-calculated columns will be removed from the [Publication Record] table:

• [Journal ID] - the ID of the Elements-determined journal object relating to the metadata record

• [Canonical Journal Title] - the title of the journal with the above ID

• [Journal Data Source] - the journal data source whose title variant was copied into the [Canonical Journal Title] column

• [Sherpa Romeo Data] - the SHERPA/RoMEO JSON data relating to the above journal

Please note that the [journal] and [issn] source metadata columns will not be removed. These will continue to reflect the name of the journal and the ISSN of the related journal, as provided by the relevant publication data source.

The following Elements-calculated columns will be removed from the [Publication] and [Publication (Display Names)] tables:

• [Journal Data Source] - the journal data source whose title variant was copied into the [Canonical Journal Title] column

• [Sherpa Romeo Data] - the SHERPA/RoMEO JSON data relating to the above journal

Please note that the [Journal ID] and [Canonical Journal Title] calculated columns will not be removed from the [Publication] table.

The [Sherpa Romeo Data] column value will be moved to a new column of the same name in the [Journal] table. You can therefore still find the SHERPA/RoMEO data of your publication by looking for this data on the related journal object. You can do this by joining to the [Journal] table's ID column using the [Publication].[Journal ID] value.

You may wish to prepare for the change by reviewing any custom SQL your institution is using against the Reporting Database for usage of the affected columns, and plan accordingly. Depending on your level of engagement with the reporting platforms in Elements, this may include custom SQL-based dashboards, custom SSRS reports and custom integrations with downstream systems based on the Reporting Database.

Alternatively, you can simply wait until release of v6.4 and then test your custom dashboards, SSRS reports and downstream integrations work as expected, detecting and resolving any issues at that time as a part of your upgrade project. As usual, we will release a technical upgrade guide providing step-by-step instructions for how to alter custom SQL to work with the new database structure at the time of release of v6.4. 

Please note: the referenced v6.4 Reporting Database technical upgrade guide has now been released.