The DeepSee Shell Best Practices Series - Get your MDX query performance stats from the DeepSee Shell

DeepSee Shell can show MDX query stats and result set stats.

>>stats on

Stats are: on

>>SELECT {[Measures].[%COUNT],[Measures].[Allergy Count],[MEASURES].[MEDIAN TEST SCORE]} ON 0,NON EMPTY HEAD(NONEMPTYCROSSJOIN([AgeD].[H1].[Age Group].Members,[AgeD].[H1].[Age Bucket].Members),2000) ON 1 FROM [PATIENTS] %FILTER [GenD].[H1].[Gender].&[Female]

Patient Count Allergy Count Median Test Scor

1 0 to 29->0 to 9 69 45 73.50

2 0 to 29->10 to 19 74 47 75.50

3 0 to 29->20 to 29 80 56 68.00

4 30 to 59->30 to 3 82 43 77.00

5 30 to 59->40 to 4 75 49 75.00

6 30 to 59->50 to 5 58 43 68.00

7 60+->60 to 69 43 24 68.00

8 60+->70 to 79 24 16 70.50

9 60+->80+ 20 9 78.00

============================================

Query Statistics:

Results Cache: 1

Query Tasks: 0

Computations: 0

Cache Hits: 0

Cells: 0

Slices: 1

Expressions: 0

Join Indices: 0

Prepare: 2.689 ms

Execute Axes: 16.048 ms

Rows: 14.568 ms

Crossjoin: 11.505 ms

Members: 5.491 ms

Execute Cells: 0.000 ms

Consolidate: 0.000 ms

Total Time: 18.737 ms

ResultSet Statistics:

Cells: 27

Parse: 7.189 ms

Display: 1.943 ms

Total Time: 9.132 ms

Elapsed time: .040249s

The values shown here are as follows:

Query Statistics — This group of statistics gives information about the query, which returned a result set. It does not include information on what was done to use that result set.

Results Cache is 1 if the results cache was used or is 0 otherwise.
Query Tasks counts the number of tasks into which this query was divided.
Computations indicates how much time was spent performing intermediate computations such as aggregating a measure according to its aggregation option. It does not include evaluating MDX expressions.
Cache Hits counts the number of times an intermediate cache was used.
Cells counts all the cells of the result set as well as any intermediate cells that were computed.
Slices counts the number of cube slices in the query. This count indicates the number of items on the WHERE clause.
Expressions indicates how much time was spent evaluating MDX expressions.

When the cache is used, Computations, Cache Hits, Cells, and Expressions are all zero.

Prepare, Execute Axes, Execute Cells, and Consolidate indicate how long different parts of the query processing took place. These parts are listed in order.
Total Time is the sum of those parts.

When the cache is used, Execute Cells and Consolidate are both zero, because those parts of the processing are not performed.

ResultSet Statistics — This group of statistics gives information about what was done to use the result set after it was returned by the result set. The values are as follows:

Cells counts the number of cells in the result set.
Parse indicates how long it took to parse the result set.
Display indicates how long it took to display it.
Total Time is the sum of those times.

Please note that if you don’t turn off the stats by using “stats off”, the stats will be showing for every query you run in the shell.

Send your MDXUtils report to WRC

If you need help from WRC to advise on the query performance, you can generate MDXUtils reports (click documentation here) for one specific query in your system. DeepSee provides a tool, the %DeepSee.Diagnostic.MDXUtils class, to enable you to gather query statistics and lower-level performance statistics at the same time. Here are the simplified steps.

1. Go to the right namespace (where the dashboard and cube are built)
2. Copy the MDX query from DeepSee Analyzer: Go to Analyzer, find the pivot table datasource -> open the pivot table -> MDX Query button

3. In terminal, go to the right namespace and run

Set pMDX=”<The MDX query to be analyzed >”
Set pBaseDir=”<The base directory for storing the output folder>”
Set pParms("pButtonsOn") = 1      
Set pParms("pButtonsProfile") = "2minrun"   (note that you can choose from all the available pButtons profiles according to how long your query takes to execute)
do ##class(%DeepSee.Diagnostic.MDXUtils).%Run(pMDX,pBaseDir,1,.pParms)

4. It takes a few minutes to generate the reports. After the files are created, please go to the output folder path, zip the folder and send it to us.

The %Run() method generates the following files:

MDXPerf_nnnnn_nnnnn.html — Main HTML report file. This contains query statistics, the query plan, and so on.
cubename.xml — Definition of the given cube.
Cached_MDXPerf_cubename_nnnnn_nnnnn.html — ^PERFMON timed collection report for running the query when using the result cache.
Uncached_MDXPerf_cubename_nnnnn_nnnnn.html — ^PERFMON timed collection report for running the query when not using the result cache.

Note that DeepSee creates a result cache only for a cube that uses more than 512,000 records (by default), so this report could have the same numbers as Cached_MDXPerf_cubename_nnnnn_nnnnn.html.

hostname_date_time.html — ^pButtons report.
Other files generated by ^pButtons. These vary by operating system.

The DeepSee Shell Best Practices Series

Run MDX queries in the DeepSee Shell

Get your MDX query performance stats from the DeepSee Shell

Reset your DeepSee cache in the DeepSee Shell

Execute an MDX query in the DeepSee Shell with/without results cache

Get your MDX query plan, MDX query trace and more from the DeepSee Shell

Example of using the MDX subquery flag in the DeepSee Shell

Debugging measures in the DeepSee Shell