The DeepSee Shell Best Practices Series - Example of using the MDX subquery flag in the DeepSee Shell

We have different flags for DeepSee advisors to look into the execution details and the methods DeepSee is using to do calculations.

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

flag cmbr on|off – Show current member processing.

flag compound on|off – Show rewrite of compound queries.

flag crossjoin on|off – Show crossjoin processing.

flag relations on|off – Show relationship processing.

flag rewrite on|off – Show rewrite of query.

flag subquery on|off – Show subquery processing.

The DeepSee Shell Best Practices Series - Get your MDX query plan, MDX Query Trace and more from the DeepSee Shell

To process a query, DeepSee performs the following steps:

1. Preparation, which occurs in process (that is, this step is not launched as a background process). In this phase:

• DeepSee parses the query and converts it to an object representation, the parse tree. In the parse tree, each axis of the query is represented separately. One axis represents the overall filtering of the query.
• DeepSee converts the parse tree to a normalized version of the query text.

The DeepSee Shell best Practices Series - Execute an MDX query in the DeepSee Shell with/without  results cache

This cache is different from cache reset. Cache reset clears everything in the namespace but “cache off” only clears the cache in ^DeepSee.Cache.Results and ^DeepSee.Cache.Axis global node for the corresponding cube. The difference is quite smaller in the case as below, but in some cases it can be a big difference.

^DeepSee.Cache.Results, which contains values for each query previously executed for a given cube.

The DeepSee Shell Best Practices Series - Reset your DeepSee cache in the DeepSee Shell

This method is provided as a convenience for developers for use on development or test systems. You should not use this method on production systems as it will have immediate effect on the performance of end user operations.

• Stops all DeepSee background agents
• Clears any pending tasks
• Clears the result cache for the current namespace.
• If you use “reset 1”, then the cell cache for the current namespace is also cleared.

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.

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?