Skip to content

Meta Query

Write and run SQL against the MetaMiner Database (MMDB) and your BusinessObjects audit databases from inside the MetaMiner Client (MMC).

License and permission

The Meta Query tab appears when your license includes the BusinessObjects client features and your MetaMiner role grants Tab: Meta Query. This permission applies to all environment types, so it must be granted on every environment you load; one environment without it hides the tab. Querying a CMS audit database also needs Query Audit DB on that environment, and deleting scripts from the shared catalog needs Delete Meta Query scripts from catalog. See Permissions Reference and Editions and Licensing.

Tab layout

Image of the Meta Query tab: schema tree on the left, two SQL editor tabs, a result grid and the toolbar

../../images/client/meta-query-tab.png

Area What it shows
Schema tree (left) MetaMiner Database with its tables and columns, a Tableau branch when Tableau environments are loaded, and one Audit: node per BusinessObjects environment with an audit database configured.
SQL editor tabs One or more SQL sheets. Each sheet runs against the database you picked in the tree. Table and column names auto-complete.
Result grid Rows returned by the last run, with row count and total execution time.

Right-click a table or column in the tree for Show Table Values or Show Column Values. Right-click a result cell for Find in CMS Explorer, View XML (XML values) and Insert Env ID.

Run a query

  1. Open the Meta Query tab.
  2. Select the target database in the schema tree (MMDB or an audit database).
  3. Type the SQL in the editor, or open a saved script.
  4. Click Run or press F9. MetaMiner runs the first statement, or only the selected text if you selected part of the script.
  5. Read the results in the grid. Use F3 to move to the next row in the same column.

Editor toolbar:

Button What it does
Run Runs the first query or the selected part of the script (F9).
Format Reformats the SQL.
Clear Clears the SQL script.
Save to File Saves the script to a local .sql file.
Open from Disk Loads a script from a local file.
Show History Opens SQL History (Ctrl+Alt+H).
Export selector Exports the result grid to CSV or Excel. See Toolbar and Export.

Tip

Table and column reference for the MMDB: MetaMiner Database Schema. Ready-made queries: SQL Script Catalog.

Script Catalog

The Script Catalog stores SQL scripts on the MetaMiner Server (MMS) so other MMC users can reuse them.

Action What it does
Open Catalog Lists scripts with Script Name, Created By, Modified On, DB Connection, DB Type and Public. Search in SQL also searches script text. Open loads a script; Run loads and runs it.
Save to Catalog Saves the current script with a name, optional comma-separated Tags, the DB Type (MMDB or CMS audit database) and Public access (visible to all users) or private.
Import to Catalog Imports exported Meta Query script files into the catalog.
Set Visibility to Public / Private Changes who sees a catalog script.
Delete from Server Removes the selected script from the catalog. Needs Delete Meta Query scripts from catalog.

SQL History

Show History (Ctrl+Alt+H) lists the queries you ran from this client. Each entry keeps the SQL, the database, when it ran, the duration and a status.

Status Meaning
Succeeded The query completed and its results loaded.
Cancelled You stopped the query.
Timed out The query exceeded its time limit.
Result limit The result was too large to load (see below).
Results unavailable The query ran but its results could not be loaded.
Interrupted The run ended unexpectedly, for example on disconnect.

Type in Search to filter by SQL text or database name; press Enter to open the entry in a new tab. History is kept per MetaMiner Server and per MetaMiner user on this computer, up to 500 recent queries; identical SQL is stored once. History needs a MetaMiner login; without one it shows "History is unavailable".

Show results as a tree

Show as Tree turns query results into a CMS Explorer-style tree, so you can right-click the objects and use the normal Object Actions.

The result must contain:

  • a String column named CMSID (the environment), and
  • at least one column whose name ends with OBJECTID or CUID.
Example: unused Web Intelligence documents as a tree
SELECT d.cmsid, d.objectid, d.name
FROM documents d
WHERE d.kind = 'Webi'
LIMIT 100;

TODO: verify the example column names against the current MMDB schema

Source to check: documents table in the MetaMiner Database Schema page.

Large results

Before loading a result, MMC checks its size:

Result size Behaviour Client variable
Larger than 128 MB MMC warns "Result set is expected to be too large" and asks whether to continue or cancel. -Dmq.large.resultset (MB)
Larger than 576 MB, or larger than the client's maximum memory MMC refuses to load it: "Result set of this query is too large! Please adjust your SQL query." -Dmq.huge.resultset (MB)

Add a LIMIT or narrower WHERE clause, or raise the client memory. See Client System Variables.

Troubleshooting

The Meta Query tab is missing

Your role lacks Tab: Meta Query on at least one loaded environment, or your edition does not include it. Ask your MetaMiner administrator, or skip the environment without the permission in the Connection Manager.

An Audit node is missing

The environment has no audit database configured in the MetaMiner Administrator (MMA). See Audit and CMS Databases.

Show as Tree reports missing columns

Add a cmsid column and an ...objectid or ...cuid column to the SELECT list, and make sure all referenced environments are enabled and loaded.