The DeepSee Shell Best Practices Series - Run MDX queries in the DeepSee Shell

Introduction to DeepSee Shell

There is a basic introduction video about DeepSee shell on InterSystems video portal.

(http://video.intersystems.com/video/Video.Pages.VideoLibrary.cls?video=3449629690001&playlistid=3329216973001)

Key points in this video:

•Invoke DS shell command, generate MDX in Analyzer, copy in shell;

•Cube command, Select statement and Create statement

•Clauses: With, solve_order, Format_string

Why do you need DeepSee shell?

•If you are in terminal session, or if you don’t have access to management portal, you can run MDX query in Shell.

•Your query in Analyzer or dashboard can get timeout errors if it takes more than 30 seconds to return results. DeepSee Shell won’t time out and it can easily take the last run query in Analyzer (Run MDX queries in the DeepSee Shell)

•Get your MDX query performance stats

•Reset your DeepSee cache

•Execute an MDX query with/without using result cache

•Get your MDX query plan, MDX query trace and more

•Get subquery, available agent, task log that related to the query (Example of using the MDX subquery flag in the DeepSee Shell)

•Debugging measures 

•When you think you get incorrect data or table formatting in Analyzer. DeepSee Shell will give you result output in text format, so you can compare the results and separate the MDX issue from the other UI components.

Case 1: Get some hints from Help menu

You can always type “?” in the shell to get some help about the commands.

>>?

DeepSee Command Line Shell

To execute an MDX query, type it in and press ENTER.

Available commands:

"q[uit]" Exit the shell.

"g[o]" Run the most recently executed or loaded query.

"k[ill] *name*" Kill saved query.

"k[ill] *" Kill all saved queries.

"l[ist]" List saved queries.

"o[pen] *name*" Open a previously saved query.

"s[ave] *name*" Save the most recently run query using the given name.

"#" Run query most recently run by Analyzer Page.

"async [on|off]" Turn async mode on or off. Async is now: on

"build <cubename>" Rebuild the given cube.

"cache [on|off]" Turn result caching on or off. Caching is now: on

"cube" Display list of available cubes.

"cube <cubename>" Display elements of the given cube.

"flag [flagname] [on|off]" Turn a diagnostic on or off.

"func" Display list of supported MDX functions.

"plan" Show plan.

"reset [0|1]" Reset the query cache.

"set [varname] [value]" Set the value of a pivot variable.

"stats [on|off]" Display query statistics. Statistics is now: off

"trace [on|off]" Trace. Trace is now: off

Back to the top

Case 2: Run MDX query

# - Last query in the Analyzer Page

This command can help you retrieve the most recent query run in Analyzer or Dashboard. If we generate this query in Analyzer:

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]

>>#

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

Elapsed time: .036585s

Please note that you can also get the last query by looking at ^DeepSee.LastQuery global.

g - the most recent executed or loaded query in shell

>>g[o]

Save [query name] – save the most recently run query using the given name

>>save test

Query saved as: 'test'

Open [query name] – open and run the saved query using the query name

>>open test

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

Elapsed time: .037s

List – list all the saved queries

>>list

test:

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]

K[ill] [query name] – delete the saved query using the query name

K * - delete all of the saved queries

>>kill test

Query deleted: 'test'

>>open test

Query not found: 'test'

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