Skip to content

[MM-W20] Query Time and Rows

Shows the query time and row count of each Web Intelligence data provider, and the database tables and columns its SQL uses.

Report document

Web Intelligence document [MM-W20] Webi IA - Query Time, Row & DB Object Analysis from the MetaMiner standard report pack. It reads the MetaMiner Database (MMDB) through the MetaMiner Standard universe. See Reports and Data.

What it answers

  • Which data providers are slow or return the most rows?
  • Did query time or row counts change after a database, universe or platform change?
  • Which database schemas, tables and columns each document's SQL reads.

Who uses it: BI platform administrators and database administrators tracking query performance and database dependencies.

Report tabs

Tab Shows
Query Time & Row Count (SQL Listed) Query time and rows per data provider, with the SQL shown.
Query Time & Row Count (SQL Selectable) Variant of the SQL Listed tab.
DBTables Used Database schemas, tables and columns found in each data provider's SQL.
Query Time & Row Count Query time and rows per data provider, without SQL.

Image of MM-W20 refreshed for one environment, Query Time & Row Count (SQL Listed) tab

../images/reports/mm-w20-layout.png

Legacy image for reference: images/mmsr_layout_MM-W20.png

Columns and measures

Group Columns
Document Environment, Document, Document is Deleted?, Webi Doc Recorded Date, Folder Type, Folder Name, Folder Path Complete
Data provider Dataprovider, Dataprovider Query Identifier, Dataprovider Query SQL, Dataprovider Query SQL is Custom?, Dataprovider Query SQL is Parseable?
Statistics Dataprovider Duration, Dataprovider Row Count, Dataprovider Refresh Date
Database objects Schema, Table, Column

Prompts

Prompt Required Answer What it does
Environments No Pick one or more Environments to include, listed by CMSID with their description. Leave empty for all. The prompts below list only values from the environments you pick.
FolderTypes No Pick one or more Folder types to include.
FolderPath Yes Pick one Folder path pattern, matched with LIKE. Enter % for all folders.
Folders No Pick one or more Folders to include. The list depends on the prompts above.
Documents No Pick one or more Documents to include.

Image of the MM-W20 Prompts dialog with Environments answered

../images/reports/mm-w20-prompts.png

Legacy image for reference: images/mmsr_prompt_MM-W20.png

Input controls

Display controls change how the report shows its data:

Input control What it does
Prompt Selection Shows or hides the block under the header that lists your prompt answers. Spaced (default) shows it with a space between list items, Show shows it without spacing, Hide hides it.
Environment / CMSID Shows environments in the report body by display name or by CMSID. Filters and prompts always list CMSIDs.
Folder / Complete Shows the folder name or the complete folder path.
Number Format Switches large numbers to a short format, for example K for 1,000s.
View/Refresh/etc* Picks the action the activity measures count: View, Refresh, Prompt, Create, Delete, Modify, Save, Search, Edit, Run, Deliver or Retrieve.

Filter controls keep only the rows that match the values you pick: Environment, Folder, Document, Dataprovider.

Image of the MM-W20 Input Controls panel

../images/reports/mm-w20-input-controls.png

Link Opens
Document name Opens the document in BI launch pad.
Document drill Opens [MM-W12] Object Use Detail for that document.

A drill link opens the target report through BI launch pad OpenDocument and passes the environment and the selected item as prompt answers.

Run the report

  1. In BI launch pad, open [MM-W20] Webi IA - Query Time, Row & DB Object Analysis from the folder where you imported the standard report pack.
  2. Refresh the document and answer the prompts. Keep FolderPath at % to include all folders.
  3. Use the input controls to change the display or narrow the results.

Data it needs: Web Intelligence document processing (data providers, queries, report elements and variables of each document). See Document Processing.

  • [MM-W17] Data Provider SQL - lists the SQL of each Web Intelligence data provider, with row counts and refresh times, and finds documents whose SQL contains a search term.
  • [MM-W12] Object Use Detail - shows every place a universe object is used inside Web Intelligence documents: report tab, element, variable and expression.
  • [MM-D01] Activity and Data Source - lists documents of every type with their data providers, universes or connections, and when and by whom they were last touched.
  • Reports and Data - all standard reports, shared prompts and delivery