Run MDX queries in the DeepSee Shell
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.
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
•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)
•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
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