Skip to content

Direct MMDB Access

Connect a SQL client or BI tool directly to the MetaMiner Database (MMDB) with a read-only login, so you can query MetaMiner metadata outside the MetaMiner Client (MMC).

Choose an access method

You do not always need a direct connection. MetaMiner has three built-in ways to run SQL against the MMDB:

Method Where Writes allowed Best for
Meta Query tab MMC No (statements that change data are refused) Ad-hoc queries, the SQL Script Catalog, results as a tree
Meta Query on the Web Browser No (read-only database roles) Users without the MMC, long-running queries, CSV/XLSX export
Database Console MetaMiner Administrator (MMA) on the server Optional read-only mode Administrators working on the server
Direct connection (this page) Any PostgreSQL client, ODBC/JDBC tool or BI tool Depends on the login you use Third-party tools, scheduled extracts, the MetaMiner Standard Universe

Before you begin

  • The MMDB runs on PostgreSQL only. Support for Oracle and Microsoft SQL Server as the MMDB has ended.
  • Find the MMDB host, port and database name in MMA (MetaMiner Database section of the main window). The embedded PostgreSQL defaults to port 49614 and database metaminer; an external PostgreSQL uses the values your DBA gave.
  • You need a PostgreSQL client on your workstation (for example pgAdmin 4, DBeaver, DataGrip or psql) and network access to the MMDB port. See Network Ports and Firewall.
  • To create a login you need the MMDB owner account or a PostgreSQL superuser.

Use a read-only login

Connect reporting tools with a dedicated read-only login, never with the MMDB owner account that the MetaMiner Server (MMS) uses. Writes to MetaMiner tables can corrupt collected metadata and are not supported unless Infolytik support asks you to run a specific script.

Steps

  1. Connect to the MMDB as the owner account or a superuser, from the server or with the Database Console.

  2. Create a login for your tool and give it the readonly role that MetaMiner maintains:

    Create a read-only login
    CREATE ROLE report_reader LOGIN PASSWORD '<password>';
GRANT readonly TO report_reader;

    MetaMiner grants the readonly role SELECT on every MetaMiner schema each time it starts, including tables added by upgrades, so the new login keeps working after updates.

  3. In your SQL client, create a PostgreSQL connection:

    Field Value
    Host MMDB host (the MetaMiner Server name for the embedded PostgreSQL)
    Port 49614 for the embedded PostgreSQL, or your external port
    Database metaminer (or your external database name)
    User / Password report_reader / <password>

  4. Connect and run a test query:

    Test query
    SELECT cmsid, count(*) AS users
FROM users
GROUP BY cmsid;

Image of a SQL client connected to the MMDB with the test query and its result here

../images/reports-data/mmdb-direct-access-test-query.png

Verify

  • The test query returns one row per BusinessObjects environment.
  • An UPDATE or CREATE TABLE statement fails with a permission error for report_reader.

Troubleshooting

Connection refused or timed out from another machine

Check that the MMDB port is open in the Windows firewall on the database host. For the embedded PostgreSQL, connections from other machines must use a password. See Embedded PostgreSQL.

permission denied for table ...

The login is not a member of readonly, or the table belongs to a schema created before the grant ran. Restart the MetaMiner service so MetaMiner re-applies its grants, then reconnect.

A new table is missing from the Meta Query tree in the MMC

The MMC Meta Query tree is built when the MetaMiner service starts. Query the table by name now, or restart the service to see it in the tree.

Next steps