Symplectic-hosted instances of Elements: This article is not relevant to customers subscribing to Symplectic hosting services for Elements, as the reporting database is installed by Symplectic in this situation.

The reporting database is regularly-synchronised standalone SQL Server reporting database containing research data in a documented format that is ready for direct consumption by your reporting tools as well as by various reports and dashboards within Elements.

The reporting database is continuously refreshed from the underlying operational database by the Reporting Synchroniser background job (reach via the Scheduled Jobs page or from the System Admin => Operations => Reporting Synchroniser menu option).

For any report data to be available an initial full reporting database synchronisation must have completed.

Resourcing: Installing the reporting database is a significant job that requires dedicated IT resources for initial configuration and troubleshooting.

For a description of the organisation, structure and layout of the Elements reporting database see the following support article:

• Reporting Database guide

The following articles provide information on configuring exportable reports such as CV's:

• Configuring CV's and set up and create SSRS reports

The reporting database can be hosted on the same server as the Elements operational database and/or the Elements applications, or a different one.

Note: if the reporting database is to be on the same machine as the operational database, then as per the Elements Prerequisites specification, they should be kept on different disks.

You will require:

• An SQL Server instance ("reporting database server") - please review the End of Support announcements for supported Server versions/editions.

• Access via Windows integrated security from your Windows account on the Symplectic Elements application server to an SQL Server login on the reporting database server that is a member of the "sysadmin" server role.

Elements provides you with a reporting database creation tool that creates a fresh reporting database whose structure (collection of tables and columns) reflects the current data field definitions you have configured in Elements. This structure remains fixed in the reporting database thereafter until you manually cause the reporting database to be rebuilt (see the Reporting Synchroniser administration page in the Elements user interface.

This means that any new fields you create and any existing fields that you rename in Elements will not be represented in the reporting database that you generated before you made your changes. You must rebuild the reporting database in order to see data held in newly created or recently renamed fields.

Until then, any data synchronisation between the main Elements database and your reporting database may not faithfully transfer data for any fields that you have created or renamed since the reporting database was last rebuilt.

The following sections take you through the steps necessary to install the reporting database.

1. Under the Windows account mentioned above, run the ReportingSetup.exe executable, found in the Reporting.Setup directory in the Symplectic Elements application folder.

2. Select 'Install a new reporting database'

3. Type into the "Server" box the SQL Server name of the SQL Server instance on which your reporting database should be created. This may look like "192.168.1.68\servername", or for a default instance of SQL Server may consist just of the network name of the machine on which the SQL Server instance is running. By default this will be populated with the same server on which the Elements database is installed.

4. Choose a name for the reporting database. By default, this will be populated with the name of the Elements database suffixed with “-reporting”.

5. Provide SQL Server connection credentials to be used by the installer to install the reporting database. You can check the Integrated Security checkbox, or specify credentials for an SQL login.

6. For Elements v5.19 and later, a subsequent installer page will ask for separate SQL Server login credentials for use by dashboards and data extracts when Elements is running. Integrated Security is not supported for these credentials. Enter the username and password of a SQL Server login (may exist at the present time, or which can be added later) on the same instance of SQL Server to which the reporting database will be installed. Make a secure note of the username and password you used at this step.

7. If any settings have changed from the defaults, or from the last reporting database installation, the Elements Configuration page will provide the option to have the ReportingSetup tool reconfigure Symplectic Elements to synchronise to the new reporting database. If “Configure Elements later” is selected, the required configuration updates will be described, to allow configuration at a later time.

8. For v5.19 of Elements and later, in order to ensure Elements dashboards and data extracts can correctly and securely connect to the new reporting database, contact your DBA and ensure they have configured the instance of SQL Server securely as follows:

   • The login configured in step 6 should be added to SQL Server if it does not already exist (SQL: CREATE LOGIN WITH PASSWORD). We will assume this login is named elements-reports-login for the remainder of these instructions. The login should be granted rights to log in to the SQL Server and must be configured to use native SQL Server authentication, not Integrated Security (Windows authentication). This may require the DBA to enable mixed-mode or SQL Server-based authentication mode in SQL Server.

   • The above login should be unique to this instance of Elements and have no rights to access any database except the new reporting database. In particular, the login should not be granted any rights to select data from the Elements operational database or from the reporting database of a different instance of Elements. Either would represent a security risk to the use of reports in Elements.

   • A database user connected to the elements-reports-login should be configured in the new reporting database for the above login (SQL: CREATE USER FOR LOGIN).

   • The user should be assigned to the pre-existing elements-stock-reporting-consumer role in the new reporting database (SQL: EXEC sp_addrolemember)

   • Finally, your DBA should maintain a script to reapply whichever parts of this this security configuration need to be reapplied whenever you might choose to drop and reinstall the reporting database, such as after upgrading Elements to a newer version.

9. For v6.2 of Elements and later, the Reporting Synchroniser service must connect to the reporting database with a db user which is a member of the db_owner role. The connection string used for this can be found in the file
   ..\Reporting.Synchronise\Reporting.Synchronise.exe.config, in the <dataSource database="reporting" .../>  entry.
   Note that the Reporting Synchroniser is managed by the Elements Scheduler service, and if you are using integrated security, the security context of the Scheduler (as set up during the original Elements installation) will be used when connecting to the Reporting Database.

Please see the following article for information about how Elements keeps data in the Reporting Database up to date:

• How is the Reporting Database kept up to date?

When accessing the reporting database directly, it is up to you to take any necessary precautions to ensure the data contained in the reporting database is appropriately protected. Note that many 3rd party reporting toolsets, such as Microsoft's SQL Server Reporting Services, offer comprehensive security settings within their report management tools that allow you to manage who has access to data, right down to the individual column level.

Review the data available through the reporting database thoroughly before making it available to report managers or downstream systems. Please see the following documentation for more information about the approach to data security in Elements and in the reporting database:

• Introduction to Data Privacy and Personal Data in Elements

• Reporting Database Guide