The attached documentation describes the organisation, structure and layout of the Symplectic Elements Reporting Database. Use this document as a data reference when designing your reports.

For an overview of the Elements reporting framework and guidance on how to create and maintain your reporting database, please see the Introduction to custom integrations support article as well as the Reporting database administration guide. We also provide the following handy guides:

• Configuring CV's

• Create SSRS reports

• Introduction to dashboards in Elements 5.19.0 and above

As always, we recommend you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

New tables and views were added in 6.10.0 belonging to the standard dbo schema. These new tables support the introduction of Annotations, a new category of Elements object which allows the collection of contextual information about a user’s relationship with an object.

• Annotation

• Annotation Field

• Annotation Source

• Annotation Type

• Duplicate Annotation Suggestion

• User Grant Annotations

• Annotation History

• Annotation Label

• User Professional Activity Annotations

• User Publication Annotations

• Annotation Record

• User Teaching Activity Annotations

Two columns were deleted from the [Assessment Exercise] table:

• [Current Stage User Status]

• [Last Marked As Ready By User When]

The following column was deleted from the [Assessment Exercise Definition Stage] table:

• [Researcher Can Mark As Done]

A new [User Has Engaged Since Stage Change] column was added to the [Assessment Exercise] table.

A new [Format As Type ID] column was added to the [Publication Type] table.

A new [ID] column was added to the [Login Log] table.

New [File First Uploaded Date] and [Reuse Licence] columns were added to the [Publication Record File] table.

A new [Button Label] column was added to the [Assessment Exercise Definition Stage Transition] table.

As always, we recommend you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

New tables were added in 6.9.0 belonging to the standard dbo schema.

• [Assessment Section]

• [Assessment Section Definition]

• [Assessment Section Attachment]

• [Assessment Section Review]

• [Assessment Section Definition Unit Override]

The [Can Hold Items] column was removed from the following table:

• [Assessment List Definition]

Alongside the new columns associated with the new tables added, a number of new columns have been added to existing tables in the dbo schema.

The [Journal Record] table was updated with the addition of the following columns:

• [apc-amount]

• [apc-info-url]

• [has-doaj-seal]

• [licence-terms-url]

• [oa-for-doaj]

• [oa-statement-url]

• [review-process]

The [Publication Record] table was updated with the addition of the following column:

• [open-access-status]

As always, we recommend you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

A number of new tables was added to the dbo schema:

• Assessment Section

• Assessment Section Definition

• Assessment Section Attachment

• Assessment Section Review

• Assessment Section Definition Unit Override

The [Can Hold Items] column was removed from the [Assessment List Definition table]

Alongside the new columns associated with the new tables added, a number of new columns have been added to the following existing tables in the dbo schema:

• [Journal Record]

• [Publication Record]

As always, we recommend you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

• A new column [Where Clause] was added to the [Group] table.

As always, we recommend you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

A new table was added to the dbo schema:

• [Publication Record Person Author Type]

New views were added to the rfs schema:

• [Grant Research Funding Organisation Relationship]

• [Grant Research Funding Proposal Relationship]

Alongside the new columns associated with the new tables added, a number of new columns have been added to the following existing tables in the dbo and rfs schemas:

• [* Degree]

• [* Postgraduate Training]

• [Grant Record]

• [Professional Activity Record]

• [Research Funding Proposal Record]

• [Teaching Activity Record]

The [ID] and [Resolved User ID] columns were removed from a number of tables.

As always, we recommend you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

A new group of child tables was added.

• [* Thumbnail]

A small number of columns were deleted from the following tables belonging to both the rfs schema and dbo schema.

• [* Degree]

• [* Postgraduate Training]

• [Impact Record]

• [Professional Activity Record]

• [Teaching Activity Record]

• [Research Funding Review Record]

Alongside the new columns associated with the new tables added, a number of new columns have been added to the following existing tables in the dbo and rfs schemas.

• [* Degree]

• [* Postgraduate Training]

• [Impact Record]

• [Professional Activity Record]

• [Teaching Activity Record]

• [Research Funding Proposal Record]

• [Research Funding Review Record]

The following sections had text updated to better detail the restricted nature of some data.

• 4.6 Security and sensitive data (name changed to ‘Security and restricted data’)

• 2.3 Research Funding Solution data

• 3.1.11 Assessment Supporting Information

For the full change history please see section 7.1 of the v6.6.0 guide

As always, we recommend you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

A number of new tables were added in 6.5.0 belonging to the rfs schema:

• [Research Funding Opportunity Record Label]

• [Research Funding Proposal Record Label]

• [Research Funding Review Record Custom Value]

• [Research Funding Review Record Form]

• [Research Funding Review Record Form Data]

The rfs schema [Research Funding Proposal Record File] table has been removed from the database.

Alongside the new columns associated with the new tables and the rfs schema, a number of new columns have been added to existing tables in the dbo and rfs schemas.

In the rfs schema, there are several new [labels] columns that indicate labels attached to the object or record. Additionally, there are some new [labels hash] columns, used by Elements for change detection during Reporting Database synchronisation (downstream systems may cache this and also use it for change detection). A new [Has CV] column has been added to the [Research Funding Proposal Record Person] table. In the dbo schema, [contact-url] has been added to the following tables:

• [Equipment]

• [Equipment Record]

Alongside the columns associated with the deleted rfs schema [Research Funding Proposal Record File] table, some columns were removed from the [Research Funding Proposal Record Person] rfs schema table. The following columns were deleted:

• [CV GUID]

• [CV URL]

As always, we recommend you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

A number of new tables were added in 6.4.0 belonging to both the rfs and standard dbo schema.

Standard dbo schema;

• [Privacy Resource Types]

• [* Ancestor]

rfs schema;

• [Duplicate Research Funding Review Suggestion]

• [Research Funding Lookup Proposal Outcome]

• [Research Funding Lookup Review Outcome]

• [Research Funding Lookup Review Status]

• [Research Funding Lookup Review Type]

• [Research Funding Proposal Research Funding Review Relationship]

• [Research Funding Review]

• [Research Funding Review Field]

• [Research Funding Review Source]

• [Research Funding Review Type]

• [Research Funding Review User Relationship]

• [Research Funding Review Record Address]

• [Research Funding Proposal Record File]

• [Research Funding Review History]

• [Research Funding Review Record Identifier]

• [Research Funding Review Label]

• [Research Funding Review Record]

• [User Research Funding Review Preferences]

• [Research Funding Review Record Role]

‘Research Funding Review’ represents a new category added to the rfs schema. This data category includes many standard child tables. See the table descriptions for details.

In this release some columns were removed from the [Publication], [Publication Record] and [Publication (Display Names)] tables. The [Sherpa Romeo Data] column value has been moved to a new column of the same name in the [Journal] table. More information about this change and how to account for it can be found in this support article.

Alongside the new columns associated with the new tables and the rfs schema, a number of new columns have been added to existing tables in the dbo schema. The following tables had new columns added;

• [Category]

• [Data Source]

• [User]

• [{Category} Field]

For the full change history please see section 7.1 of the v6.4.0 guide

As always, we recommended you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

There were two new tables added to the rfs schema.

A number of fields were removed from the following tables;

• Research Funding Opportunity Record

• Research Funding Organisation Record

• Research Funding Proposal Record

For the full change history please see section 7.1 of the v6.3.0 guide

As always, we recommended you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

Elements 6.2.0 introduces the ability to set a privacy level directly on an object (e.g. a publication, grant, teaching activity, etc.) Previously, privacy levels could only be set on a user's relationship with an object, or on their own HR or profile data. This new functionality means that an object itself can in be marked as Public, Internal or Private. This is reflected in the reporting database by the addition of a Privacy Level column to each [{Category}] table.

Note that data in the Reporting Database is not filtered by privacy level. Anyone with direct access to a table or view will see all data at all privacy levels. Is it the responsibility of each data consumer to choose the appropriate privacy level for their purpose and filter their results accordingly. It is the responsibility of your institution's database administrator/owner to ensure that only appropriate people are given access to the raw data, and that they fully understand their responsibilities with respect to privacy and data protection.

For more information, see sections 2 "Privacy and Data Protection in the Reporting Database" and 5.4 "Privacy Level columns" within the guide, and the support article Introducing Object Privacy (v6.2 Onwards).

Elements 6.2.0 introduces the Research Funding Solution (RFS), which supports institutions in finding funding opportunities, developing proposals, monitoring funded projects, and other funding-related activities. The potentially sensitive nature of this data and the incorporation of existing technologies requires a approach to RFS data that differs in some ways from other Reporting Database data.

To keep RFS data clearly separated from other reporting database data, the reporting database has a new rfs schema. Using a separate schema allows permission to view RFS data to be controlled separately to the rest of the database. Any reporting database user who should not have access to RFS should be granted SELECT permission on the dbo schema only. To give a user access to the full set of data, including RFS data, grant them SELECT permission on the dbo and rfs schemas.

To aid the management of access to RFS data, a new role has been added to the database, elements-stock-rfs-reporting-consumer. Membership of this role grants access to the new rfs schema. For details, see section 2.3 "Research Funding Solution data" within the guide.

All new tables in 6.2.0 belong to the new rfs schema. There are three new categories of Research Funding object, each with a corresponding [{Category}] table:

• [Research Funding Opportunity]

• [Research Funding Organisation]

• [Research Funding Proposal]

Each of these has the usual [{Category} Record], [{Category} Label] and other child tables, [User {Category} Preferences], [Duplicate {Category} Suggestion], and [{Category} {Category} Relationship] tables that are associated with any [{Category}] table. These tables can be joined in the same way as similar tables associated with any other category, (e.g. Publications).

Two new classes of tables specific to RFS are the [Research Funding Lookup *] and [* Custom Value] tables. See the table descriptions in the guide for details.

The Dimensions for Universities data source is retired in Elements 6.2.0, as it has been replaced by the newer by Dimensions data source. Accordingly, the following columns have been removed from the [Publication] table:

• [Dimensions for Universities Citation Count]

• [Dimensions for Universities RCR]

There are, of course, many new columns in the new rfs schema. These are documented under the appropriate table desciptions in the guide.

In the dbo schema, there are several new [* Precision ID] columns as a result of some datetime columns changing to data type date. See the guide for details.

Some tables have a new [RoleAccess] column. This is only for internal use and should not be included in the results of any query. Its value should not be altered, as it governs access to potentially sensitive RFS data. Tables that have had this column added are:

• [Category]

• [Data Source]

• [Link]

• [Relationship Type]

The following three columns are also new in 6.2.0. See the relevant table documentation for their descriptions:

• [Assessment REF2 Output].[Media Of Output]

• [Category].[Reporting Table Name]

• [Link].[Grants View Access]

The [Publication Record] table has two new columns:

• preprint-for

• preprint-for hash

These support the addition of "Preprint" as a distinct publication type. preprint-for is a new underlying metadata field; where the data source has indicated that the publication is a preprint, this field may describe the subsequent publication that it is a preprint of.

The ID column has been removed from the [Address] table, as it served no purpose. When joining to the a [Address] table, instead use the [Address Hash] column.

All [Address Hash] and [Address Hash] columns are now of data type varchar(24), whereas they were previously nvarchar(24).

The [Category] table has a new set of columns supporting the new functionality allowing configurable display names for object categories. These columns show the values selected on the "Configure module display names" system admin page. Note that table and column names in the reporting database will still use the default names, regardless of what is set here.

• Object Display Name Lowercase Plural

• Object Display Name Lowercase Singular

• Object Display Name Sentence Case Plural

• Object Display Name Sentence Case Singular

The [Relationship Type] table has new [From Category ID] and [To Category ID] columns. These provide the integer IDs of the categories on each end of the relationship type. These can be used, for example, to join to the [Category] table to get the correct display name for the category, so that terminology used in reports matches what it used in the Elements interface.

All [{Category}] tables have a new [Reporting Dates Are Manually Managed] column. This is in support of the automatic reporting date setting functionality introduced in Elements 5.21.0. A value of 1 in this column indicates that the object's reporting date(s) are being manually managed, i.e. the system will not change them automatically.

All [{Category} Record] tables have a new [Suspected Deleted At Source] column. This flag indicates that Elements has evidence that metadata record has been deleted at the data source, for example, because an attempt to retrieve the record metadata from the source resulted in a 404 Not Found HTTP response. As with other columns in the [{Category} Records] tables, the value from the representative record bubbles up to the corresponding [{Category}] table.

The [Publication External User Account Item] table has a new [Identifier Account Scheme ID] column. This contains the internal integer ID of the identifier scheme in the [Identifier Account Scheme] column.

All [* Identifier] tables contain a new [Scheme ID] column. This contains the integer ID of the identifier scheme in the [Scheme] column.

The [Romeo Colour] table has been removed from the database in response to SHERPA's retirement of the RoMEO colours in 2020.

Similarly, the [Sherpa Romeo Colour] column has also been removed from the [Publication] and [Publication Record] tables. The [Sherpa Romeo Data] column remains but, if populated, will now contain JSON data from the version two SHERPA API, rather than the version one XML-formatted data.

Unusually, some changes to the Reporting Database were made after the initial release of Elements 5.20.0.

The new [Assessment Exercise Definition Unit Manager] table details which users have been assigned the Manager role for the various Assessment Exercise Definition Units. See the table description for details.

The [Assessment REF2 Output] table contains a new column:

• English Abstract Word Count

See the table description for details.

To improve performance, the [Assessment Exercise Definition Unit REF2 Output Count Required] table now excludes default units. See the table description for details.

New [{Category} Manual Join Split History] tables were added to allow inspection of the join and split operations made to objects by users of the system. The instance of this table for the Publication category provides data to the "REF2 Modified Publication Report" stock report for REF Assessment managers at UK institutions. See the table description for details.

The [Assessment REF2 Output] table contains two new columns:

• Created When

• Language Within Unit Remit

See the table description for details.

There are four previous releases of v5.20.0 that do NOT contain the above changes:

• 5.20.0.2670

• 5.20.0.2661

• 5.20.0.2641

• 5.20.0.2632

As always, we recommended you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

No tables or columns are removed from the Reporting Database in version 5.20.0. Some columns have been renamed for clarity, and many columns that previously stored dates as data type int have had their data type changed to date.

In order to make it easier for reporting and analytics tools to analyse dates in the reporting database, Elements has migrated all columns representing dates using the SQL Server int column type (such as the [Reporting Date 1] and [Reporting Date 2] columns of the [Publication] table) to columns of the same name that represent dates using the SQL Server date column type.

There is more detail about this change in Changes to dates in the reporting database in v5.20, including the reasoning behind such a wide range change, and examples for writing code that will work with both the new date columns and the pre-5.20.0 int columns.

As part of the changes to the representation of dates across the reporting database, the primary key of the [Date] table has changed from being an int column called ID to a date column called Date. As with other date representations, the former integer representation has been replaced with the equivalent date value, e.g. the integer 20200518 is replaced with the date '2020-05-18'.

In the [Assessment Exercise Definition Unit REF2 Output Count Required] table view, a number of columns have been renamed to better express their purpose:

• Number Of REF1 Researchers With Zero Attributions Expected is renamed Number Of Included REF1 Researchers With Zero Attributions Expected

• Required Number Of REF2 Research Outputs (raw calculation) is renamed Initial Calculated Number Of Required REF2 Research Outputs

• Required Number Of REF2 Research Outputs (rounded) is renamed Initial Calculated Number Of Required REF2 Research Outputs (rounded)

• Submitted Category A FTE is renamed Submitted Category A FTE Of Included REF1 Researchers

• Assessment Exercise Definition REF Output Submission Status

• Assessment Exercise Definition Unit Excluded Score Override

• Embeddable Media Type

• HERDC Nomination Checklist Item

• HERDC Return Nomination Checklist Item

• User Record Embeddable Media

See the table descriptions in the guide for details.

The Assessment Exercise Definition table has a new REF2 Open Access Manager Role Enabled column, indicating whether the REF2 Open Access Manager Role is enabled for the exercise.

The Assessment REF1 Researcher table has three new columns:

• Default REF1 Contribution Research Group ID

• Initials

• Surname

The Assessment REF2 Output table has 26 new columns. See the guide for details.

The Assessment REF2 Output REF1 Contribution table has a new Contribution Statement Word Count column, giving the word count of the 'Author contribution' statement.

The Assessment Exercise Definition Unit table has a new REF Edit Rights Restricted column, indicating whether the Unit of Assessment has been locked for editing by Unit Managers.

In the [Assessment Exercise Definition Unit REF Research Group] table, the Name has been extended from nvarchar(64) to nvarchar(128) to allow for longer Research Group names.

As always, we recommended you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

No tables or columns are removed from the Reporting Database in version 5.19.0.

The [Publication OA Policy Exclusion] table introduced in version 5.15.0 had a mis-named Value column which references [OA Policy].ID. This has been renamed OA Policy ID to follow the convention elsewhere in the database. The Policy ID column of the [Publication OA Policy] table has also been renamed OA Policy ID to match. See Changes from v5.14.1 to v5.15.0 notes for details and examples.

There are nine new tables. The new [Publication External User Account Item] table reports details of publications that have been synchronised to an external user account such as a user's ORCID profile.

The remaining eight are all specific to UK institutions using the Assessment module for REF2021 submissions:

• Assessment Exercise Definition REF Default Output Type Mapping

• Assessment Exercise Definition Unit REF Research Group

• Assessment Exercise Definition Unit REF2 Output Count Required

• Assessment REF1 Researcher

• Assessment REF1 Researcher Contract

• Assessment REF1 Researcher Contract No Research Connection Reason

• Assessment REF1 Researcher Contract Research Group

• Assessment REF2 Output Type

See the table descriptions in the guide for details.

All [{Category}] and [{Category} Record] tables have a new {Category} Source ID column, containing an integer ID referencing the ID column of the appropriate [{Category} Source] table. This is more stable than referencing via the Source Display Name, which may change over time, particularly for RT2 and custom sources. In the case of the [{Category}] tables, this refers to the data source of the representative record for the publication.

The [Publication] table has a new oapolicyexclusion hash column. This can be useful for downstream systems to detect when a publication's OA policy exclusions have changed by querying only the [Publication] table. Any change in the publication's OA policy exclusions will result in a change in oapolicyexclusion hash, which can be used to trigger further action.

The [Publication OA Policy] table has three new columns:

• Compliance Status Is Overridden

• Compliance Status Override Reason

• Compliance Status Without Override

See the table description in the guide for details.

The [User] table has a new Is Student column. This indicates whether or not the user is a student as set via the HR feed or manual user record.

The [User Identifier] table has a new Is Connection Write Capable column. For user identifiers such as ORCID that can be used to connect to an external account, this indicates whether the connection (if any) has permission to write to that account.

The [Group User Membership] table has a new Is Primary column, which indicates whether the membership was created using the User's Primary Group Descriptor.

The [Assessment Exercise Definition] table has a new REF1 Researcher Edit Rights Restricted which indicates whether all REF1 Researcher data in the exercise definition is read-only for particular roles.

The [Assessment Exercise Definition Unit] table has six new columns:

• REF Notes On Number Of Required REF2 Outputs

• REF1 Researchers Allowed Allocations To Multiple Research Groups

• REF1 Researchers Category A FTE Override

• REF2 Output Subprofile Categories Enabled

• REF2 Outputs Assignable To Research Groups

• REF6b Approved Reduction In Outputs

See the table description in the guide for details.

The [Assessment REF2 Output] table has seven new columns:

• Attribution Count

• Contribution to Each Attributed REF1s Attribution Count

• Output Allocation 1

• Output Allocation 2

• Output Allocation 3

• Potential Final Attribution Count

• Potential Final Contribution to an Affiliated REF1s Attribution Count

See the table description for details.

The [Assessment REF2 Output REF1 Contribution] table has a new Research Group ID column associating a REF1 Contribution to a Research Group.

As always, we recommended you use a quick database structure comparison tool such as DBComparer or

Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

No tables or columns are removed from the Reporting Database in version 5.18.0 (although see the note below about [Duplicate {Category} Suggestion] tables). There are a small number of new columns and some changes to the length of nvarchar columns containing identifier scheme names.

To improve system performance, the job that populates the [Duplicate {Category} Suggestion] tables is no longer run on an automatic schedule. Therefore, these tables will usually be empty, or may contain out-of-date data. If you have processes that depend on these tables please contact Symplectic to discuss how to generate the duplicate suggestions.

The [Assessment REF2 Output] table contains four new columns:

• Factual Information About Significance Of The Output

• Includes Significant Material Published Prior To 2014

• Information About Research Process Or Content

• Output Type Override

See the See the table description for details.

The [HERDC Return] table contains a new bit column Show Nomination Checklist , which indicates whether or not the Annual Collection (HERDC) Return has the Nomination Checklist functionality enabled.

The [Publication Record] table contains two new columns:

• formatted-abstract

• formatted-title

These may contain versions of the abstract and title using simple markup (e.g. using a simple subset of HTML), which can then be rendered in the user interface or downstream systems. Both columns are also surfaced in the [Publication], where they contain the data from the representative record (if any). If these fields are used on any publication types, they will also appear under their display names in the [Publication Record (Field Display Names)] and [Publication (Field Display Names)].

The Name column of the [Identifier Scheme] table and the Scheme column of the [* Identifier] tables have been standardised to nvarchar(50). Previously, these were nvarchar(100) and nvarchar(20) respectively, which caused truncation of identifier scheme names over 20 characters long.

As always, we recommended you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

No tables or columns are removed from the Reporting Database in version 5.17.0. The majority of additions are related to functionality specific to the REF2021 Assessment Exercise for UK institutions, with a few general improvements around publication dates, author counts and user identifiers.

• Assessment REF2 Output Affiliated REF1 User

• Publication REF OA Policy

• Assessment REF2 Output REF1 Contribution

• Assessment Exercise Definition User

See the guide for details of these new tables.

The [Publication] table has six new columns:

• Author Count: provides a count of the total number of authors.

• Canonical Acceptance Date: The date of acceptance as displayed in the OA Monitor. The first non-null Acceptance Date from the publication's records in default precedence order. Null if there are none.

• Canonical Acceptance Date Precision ID

• Combined Publication Date: The date of publication as displayed in the OA Monitor. The first non-null Publication Date from the publication's records in default precedence order. Null if there are none.

• Combined Publication Date Precision ID

• REF Language: Used to determine a single Language value for publications in a REF context, sourced using the same logic as the UI. The first non-null Language from the publication's records in default precedence order.

The [User Identifier] table and its associated views [Claimed User Identifier], [Pending User Identifier], and [Declined User Identifier] each have four new columns relating to how the decision about the identifier was made:

• Status Modified When

• Status Set By Agent Type ID

• Status Set By Agent ID

• Status Set By User ID

See the guide for details.

The [Assessment REF2 Output] table contains 27 new columns. See the guide for details.

See the See the table description for details.

As always, we recommended you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

Version 5.16.0 sees the introduction of views to the Reporting Database. These gives data consumers the option to create more complex queries using larger tables that contain data across all categories of object (e.g. the new [Link] table), whilst maintaining backward-compatibility and ease of use by replicating category-specific tables in views (e.g. [Publication User Relationship]). This also reduces the physical size of the database and the server load involved in synchronising the Reporting Database.

The [User Identifier Association] table has been removed. It is replaced and extended by the new [User Identifier] table and its associated views. Queries using the [User Identifier Association] table will need to be rewritten to use the new table and/or its associated views. See the notes below and the table description in the guide for more details.

• Deposit Advice Deposit Advice Relationship

• Funding Body Funding Body Relationship

• Journal Journal Relationship

Since no link types exist for relationships that might populate these three Relationship tables, they were always empty. They have therefore been removed.

• User Identifier Association

This table has been replaced with the [User Identifier] table and its associated views. See the notes below and the table description for more details.

• Agent Type

• API Account

• Data Source

• Link

• Pending Grant

• Declined Grant

• User Identifier

• Claimed User Identifier

• Pending User Identifier

• Declined User Identifier

The new [Agent Type] table lists the different types of agent that can create links in Elements, e.g. user search, offline search, users, the API, etc.

The new [API Account] table lists the accounts registered for access to the Elements API.

The new [Data Source] table lists the data sources for all categories in a single table. The various [{Category} Source] tables are replaced with views of the new table. SELECT statements based on these views will function in exactly the same way as before; your queries should not need any modification.

The new [Link] table lists all links between all categories in a single table. This includes claimed, pending and declined links, for link types where approval status is applicable. The various [{Category} {Category} Relationship] tables are replaced with views on this table, as are the [Pending Publication] and [Declined Publication] tables. SELECT statements based on these views will function in exactly the same way as before; your queries should not need any modification.

With the introduction of Grant claiming in 5.15.0 it is necessary to report on the approval status of Grant-User links. The new [Pending Grant] and [Declined Grant] views provide this ability. Their function is analogous to that of [Pending Publication] and [Declined Publication] views for User-Publication links.

The new [User Identifier] table replaces the [User Identifier Assocation] table and significantly extends its functionality. Whereas the old table contained only claimed user identifiers (i.e. those set to auto-claim or auto-suggest), the new table contains all identifiers in the system, whether claimed, pending or declined (i.e. set to ignore or auto-reject). For ORCID identifiers, it is also possible to determine whether a connection to the corresponding ORCID account has been made and is currently active. Possible new uses include calculating the percentage of users who have an active Elements to ORCID connection, or reporting on identifiers that have been suggested but not acted upon by the user. The three views [Claimed User Identifier], [Pending User Identifier] and [Declined User Identifier] provide cut-down versions of this table containing only user identifiers with the corresponding approval status.

Several column types have had their maximum length increased to prevent truncation of data:

• Full Address columns have had their maximum length increased from 500 characters to 1000 characters.

• Organisation columns in [* Address] tables have had their maximum length increased from 100 characters to 500 characters.

• Columns containing URLs have had their maximum length increased from either 200 or 400 characters to 500 characters.

As always, we recommended you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

Consumers of the Publication OA Policy table should be aware of breaking changes to this table in version 5.15.0. Please see the change log in the guide for more information and examples of how to modify affected queries.

New tables:

• Elements 5.15.0 adds the ability to 'tag' Assesment Exercise Definitions. The Assessment Exercise Definition Tag table reports on these tags.

• Assessment REF2 Output is added specifically to aid UK institutions preparing for their REF2021 submissions.

• HERDC Nomination Evidence File reports on Evidence Files attached to a HERDC nomination (applicable only to Australian institutions).

• Repository Item File reports on the files deposited to an institutional repository via a Repository Tools 1 (RT1) connection.

• Publication OA Policy Non-Compliance Reason reports the reason that the Open Access Monitor has calculated a publication as being non-compliant with an OA Policy for which it is eligible.

• Publication OA Policy Exclusion reports publications that have been excluded from an OA Policy or Policies.

As always, we recommended you use a quick database structure comparison tool such as DBComparer or Visual Studio's Database Schema Comparison to examine the full set of changes yourself during your upgrade procedures, should you be in any doubt about the structural changes.

In the User table, the columns [Is Public] and [Institutional Email Is Public] have been removed and replaced with the new columns, [Privacy Level] and [Institutional Email Privacy Level] respectively. This brings user privacy reporting into line with other objects and fields, and allows access to multi-level privacy options such as Public/Internal/Limited. Queries accessing user data should be rewritten to use these new privacy columns. See the guide for details and examples.

Elements 5.14.0 introduces three new fields to publication records:

• altmetric-attention-score

• associated-identifiers

• field-citation-ratio

Corresponding columns have been added to the [Publication] and [Publication Record] tables, as well as an [associated-identifiers hash] column to aid easy change detection.

The HR Log table has three new columns to report on HR feed issues caused by clashing or invalid User Public URL Path fragments:

• Duplicate Public URL Path Fragment Count

• Invalid Public URL Path Fragment Count

• Locally Covered Public URL Path Fragment Count

Fixes:

• Locking or unlocking a manual record will now trigger an update of the [Is Locked] column in the [Category] tables as well as the [Category] Record tables.

• In [* Field Setting Override] tables, the data type of the [Hidden] column has been corrected from int to bit. The value of this column will still be either 1 or 0, so queries are unlikely to need rewriting.

• In [* Unit] tables, the data type of the [Subdescription] column has been corrected from nvarchar(20) to nvarchar(100). This avoids possible truncation of data.

• In [* Unit] tables, the [IsDefaultUnit] column has been renamed [Is Default Unit], in line with the style of other Reporting Database columns. Queries using this column will need to be modified accordingly.

• Symplectic Elements v5.14.1 - Reporting Database Guide.pdf

• Symplectic Elements v5.15.0 - Reporting Database Guide.pdf

• Symplectic Elements v5.16.0 - Reporting Database Guide.pdf

• Symplectic Elements v5.17.0 - Reporting Database Guide.pdf

• Symplectic Elements v5.18.0 - Reporting Database Guide.pdf

• Symplectic Elements v5.19.0 - Reporting Database Guide.pdf

• Symplectic Elements v5.20.0 - Reporting Database Guide.pdf

• Symplectic Elements v5.21.0 - Reporting Database Guide.pdf

• Symplectic Elements v6.0.0 - Reporting Database Guide.pdf

• Symplectic Elements v6.1.0 - Reporting Database Guide.pdf

• Symplectic Elements v6.2.0 - Reporting Database Guide.pdf

• Symplectic Elements v6.3.0 - Reporting Database Guide.pdf

• Symplectic Elements v6.4.0 - Reporting Database Guide.pdf

• Symplectic Elements v6.5.0 - Reporting Database Guide.pdf

• Symplectic Elements v6.6.0 - Reporting Database Guide.pdf

• Symplectic Elements v6.7.0 - Reporting Database Guide.pdf

• Symplectic Elements v6.8.0 - Reporting Database Guide.pdf

• Symplectic Elements v6.9.0 - Reporting Database Guide.pdf

• Symplectic Elements v6.10.0 - Reporting Database Guide.pdf