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 the DSpace database 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).
Note that all steps here assume that you have configured DSpace, the database and the reporting tool to use UTF-8.
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
(this may help: DSpace with Oracle DB test instance)
Enabling remote database connections
The trick is to edit pd_hda.conf to access from the smallest possible range of IP addresses:
You also need to make sure the Postgres daemon is listening on an IP address on the Postgres server that can be connected to from wherever you want to run your SQL client. Make sure that your "localhost" address is also included if you run postgres on the same machine as DSpace. The '*' value will make Postgres listen on all local IP addresses:
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
Most reporting systems use URL-like "connection strings" to connect to databases.
ODBC-based reporting tools work by running one or more SQL query against the underlying DSpace database. You can find some examples in - DS-1645Getting issue details... STATUS which illustrate the kinds of SQL joins that you'll be looking at using. Note the use of item.in_archive = 't' and item.withdrawn = 'f' to check whether the item has been deleted or withdrawn respectively. A diagram of the DSpace database schema is included in the Storage Layer section of the DSpace documentation. The SQL itself is stored in database-specific subdirectories of https://github.com/DSpace/DSpace/tree/master/dspace/etc
Note that some data (such as access stats) aren't held in the SQL database but in solr. Access details for these can be found at Solr#AccessingSolr.
Do you have some reports that you might be willing to share? if so please log into upload them.