ODBC External Reporting

Many organisational DSpace users run reports on DSpace not from within DSpace itself, but from within an external reporting system (Business Intelligence, JasperReports, etc) which connect to DSpace via ODBC. Reports may be run on an external reporting system rather than DSpace to allow integrated reporting across a number of systems, to allow integration with other organisational systems, to exploit existing reporting skills, or for other reasons. Typically the connection is read-only, this allows both better security (since it limits the damage that an incompetent or malicious user can do) and better performance (since read-only accesses to tables typically require less locking or resource-use in most databases).

Setting up External Reporting

There are typically four steps to connecting DSpace to an external reporting system. Creating a database user with appropriate rights, allowing database connections from the remote system, opening firewall ports and creating the connection string.

Creating the user

Postgres

CREATE ROLE reporting LOGIN PASSWORD 'SECRET';
GRANT CONNECT ON DATABASE dspace to reporting;
GRANT USAGE ON SCHEMA public TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting

Oracle

TODO

Enabling remote database connections

Postgres

host    dspace            reporting        10.123.0.0/64                 md5

Oracle

TODO

Opening firewall ports

In a complex environment, there are likely to be firewalls between the machine hosting the database for DSpace and the machine(s) hosting the reporting software. These firewalls need to be opened on port 5432 (assuming the default postgres port).

Creating the connection string

TODO

Creating reports

This is based on on Unable to locate Jira server for this macro. It may be due to Application Link configuration. "support for connecting to institutional reporting tools"