Database Guidelines

Database Guidelines

The Mercury Meta-database

Mercury stores meta-data -- report definitions, user/report permissions, etc -- in its own SQL Server database in Microsoft’s secure Azure environment.  Each Mercury installation gets its own Mercury meta-db – it’s not commingled with any other customer’s data, and of course it’s also completely separate from any of your production databases.  The meta-db keeps no production data.

All communication is encrypted:

  • The Mercury client talks to your Mercury Azure meta-database over a secure channel.

  • If your organization uses database-specific credentials to access production data (i.e. a database login-name and password rather than implicit Windows user credentials), those credentials are also encrypted.

Data Retrieval - Stored Procedures and Views

Mercury relies on queries (stored procedures, views, SQL statements, OData services, etc) to retrieve data.

If you choose to use Stored Procedures and/or Views to retrieve data for Mercury reports, these types of queries reside in one of your own databases.  

Dedicated query-database

We suggest creating a brand-new database on one of your servers to house them.  (For convenience we’ll call this a Mercury “query database” or “on-prem” database.  Even if it’s in a remote data center or in your cloud, “on-prem” emphasizes that it’s part of your network world – not Mercury’s.)

For speed and efficiency, this query database should be on the same network as your production databases.

User Permissions

Your Windows users would need permission to run stored procedures and views in this on-prem/query database.  And those procedures and views in turn need access to your production databases.  The details depend on your particular database platform(s) and how you wish to control user access.  Please contact us if you’re not sure how to proceed.

In most cases Mercury would then need to use only your query/on-prem database as the source of the views and stored procedures that retrieve data.  Those objects would in turn access the production data for a report, and all the security and access control resides with you.  The Mercury client application doesn’t have to know anything about the underlying servers and databases or tables.  It simply uses a view or stored procedure in your query/on-prem database.

Depending on your database platform, one way to go is to create a database “role” that has permission to the views and stored procedures, and make your users members (or if you use a database login credential, make that a role member).

Custom Data

There’s one more use for the on-prem/query database: using custom data to support your operation.  For example, if you have several companies with different accounting structures, you could create a custom account-number translation table to harmonize these structures and present a consistent set of reports to your audience.  This custom table could live in the query/on-prem database.

Mercury Is a Read-Only Application

Mercury is intended to be a read-only application. (Of course it updates the metadata in its own database, but production databases are intended to be read-only.)

Although Mercury has some ability to manipulate report data – for example, by creating custom calculated fields – no values are kept and nothing is cached. Mercury would keep only meta-data items such as calculation expressions and formatting.

However: if your organization uses stored procedures for data retrieval, Mercury has no knowledge of or control over the internal workings of those procedures.  Your IT department is responsible for ensuring that stored procedures used for Mercury data-retrieval don’t modify data.

    • Related Articles

    • Stored Procedure Guidelines

      Stored Procedure Guidelines for Data Retrieval This document contains information applicable to stored procedures in most database platforms.   It also includes some tips relating specifically to Microsoft SQL Server and Microsoft Dynamics GP and ...
    • Data Retrieval Guidelines

      Mercury reports (like any reports) rely on queries to retrieve data. Queries can take several forms. (Not all of these may be applicable to your database platform.) Stored Procedures Views Web Services (OData and similar) SQL Statements The query is ...
    • Database Queries for Reports and Lookups

      To obtain data for your reports (and lookups), Mercury must query your database(s). Depending on the Connection used for your report or lookup, there will be one or more of the following query-types available. (For more on Connections, see Admin > ...
    • Quick Start Guide for Administrators

      So you’ve just set up a trial, or purchased Mercury for your company. Thank you! Now what? Connect to Your Corporate Database(s) To use Mercury with your corporate data, you need to: Create one or more Connections to your corporate data sources. Each ...
    • Microsoft Dynamics GP Actions

      By using Mercury "Actions" in a report based on a Dynamics GP database, you can: Open an underlying GP form associated with the data (a drillback) Open an attached document. Each of these features is discussed in detail below. For a general overview ...