[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.

Report document

Web Intelligence document [MM-W17] Webi IA - Dataprovider SQL 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 documents query a given table, column or SQL fragment?
• What SQL does a data provider send to the database?
• How many rows did each data provider return at its last refresh, and how long did it take?

Who uses it: Report developers, database administrators and data teams planning database or schema changes.

Report tabs

Tab	Shows
Webi IA - Dataprovider SQL	One row per data provider, with its SQL.

Image of MM-W17 refreshed for one environment, Webi IA - Dataprovider SQL tab

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

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

Columns and measures

Group	Columns
Document	Environment, Document, Document CUID, Folder Type, Folder Name, Folder Path Complete
Data provider	Dataprovider, Dataprovider ID, DP Type, Dataprovider Universe
SQL and statistics	Dataprovider Query SQL, Dataprovider Row Count, Dataprovider Duration, Dataprovider Refresh Date

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.
Datasources	No	Pick one or more	Data sources (universes and other sources) to include.
DocumentCUIDs	No	Pick one or more	Document CUIDs, normally filled in by a drill link from another report. Leave empty when you refresh the report yourself.
Search Term	Yes	Type a value	Text to look for in the SQL. Enter % to list everything.

Image of the MM-W17 Prompts dialog with Environments answered

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

Legacy image for reference: images/mmsr_prompt_MM-W17.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.
Expand	Shows long multi-value fields (such as SQL text and lists) in full, or shortens them to one row.

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

Image of the MM-W17 Input Controls panel

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

Legacy image for reference: images/mmsr_ICs_W17.png

Drill links

Link	Opens
Document name	Opens the document in BI launch pad.

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-W17] Webi IA - Dataprovider SQL 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. Enter % in Search Term to list everything.
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.

Related reports

• [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.
• [MM-W12] Object Use Detail - shows every place a universe object is used inside Web Intelligence documents: report tab, element, variable and expression.
• [MM-C17] Command Text Use - lists the SQL command text of your Crystal Reports and finds reports whose command text contains a search term.
• [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