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.

CLEAR – Kill flag values

>>flag subquery

SUBQUERY is: off

>>flag subquery on

SUBQUERY is: on

>>g

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]

--> %Prepare: PATIENTS: 2016-02-11 15:31:50

--> %ExecuteParameters: PATIENTS

--> %InitializeResultsCache: PATIENTS

--> %InitializeResultsCache: PATIENTS: QUERY IS IN CACHE: en142672151

--> %PreProcessQuery: PATIENTS

--> %ExecuteAsynch: PATIENTS

--> %ExecuteAxes: PATIENTS: Status: 10

--> %InitializeAxisCache: PATIENTS: slicer: MEMBER [MEASURES].[MEDIAN TEST SCORE] AS '%KPI("%DeepSee.Median","MEDIAN",1,"%measure","Test Score","%CONTEXT")',FORMAT_STRING='#.##':[GEND].[H1].[GENDER].&[Female]:[GEND].[H1].[GENDER].&[Female]

--> %InitializeAxisCache: PATIENTS: REUSING AXIS: slicer: MEMBER [MEASURES].[MEDIAN TEST SCORE] AS '%KPI("%DeepSee.Median","MEDIAN",1,"%measure","Test Score","%CONTEXT")',FORMAT_STRING='#.##':[GEND].[H1].[GENDER].&[Female]:[GEND].[H1].[GENDER].&[Female]

--> %InitializeAxisCache: PATIENTS: axis: MEMBER [MEASURES].[MEDIAN TEST SCORE] AS '%KPI("%DeepSee.Median","MEDIAN",1,"%measure","Test Score","%CONTEXT")',FORMAT_STRING='#.##':[GEND].[H1].[GENDER].&[Female]:{[MEASURES].[%COUNT],[MEASURES].[ALLERGY COUNT],%KPI("%DeepSee.Median","MEDIAN",1,"%measure","Test Score","%CONTEXT")}

--> %InitializeAxisCache: PATIENTS: REUSING AXIS: axis: MEMBER [MEASURES].[MEDIAN TEST SCORE] AS '%KPI("%DeepSee.Median","MEDIAN",1,"%measure","Test Score","%CONTEXT")',FORMAT_STRING='#.##':[GEND].[H1].[GENDER].&[Female]:{[MEASURES].[%COUNT],[MEASURES].[ALLERGY COUNT],%KPI("%DeepSee.Median","MEDIAN",1,"%measure","Test Score","%CONTEXT")}

--> %InitializeAxisCache: PATIENTS: axis: MEMBER [MEASURES].[MEDIAN TEST SCORE] AS '%KPI("%DeepSee.Median","MEDIAN",1,"%measure","Test Score","%CONTEXT")',FORMAT_STRING='#.##':[GEND].[H1].[GENDER].&[Female]:NON EMPTY HEAD(NONEMPTYCROSSJOIN([AGED].[H1].[AGE GROUP].MEMBERS,[AGED].[H1].[AGE BUCKET].MEMBERS),2000)

--> %InitializeAxisCache: PATIENTS: CREATING AXIS: axis: MEMBER [MEASURES].[MEDIAN TEST SCORE] AS '%KPI("%DeepSee.Median","MEDIAN",1,"%measure","Test Score","%CONTEXT")',FORMAT_STRING='#.##':[GEND].[H1].[GENDER].&[Female]:NON EMPTY HEAD(NONEMPTYCROSSJOIN([AGED].[H1].[AGE GROUP].MEMBERS,[AGED].[H1].[AGE BUCKET].MEMBERS),2000)

INTERSECT s2: 4 => 1

INTERSECT s1 x s2: 2 x 4 => 1

INTERSECT s2: 8 => 1

INTERSECT s1 x s2: 2 x 8 => 1

INTERSECT s2: 2 => 1

INTERSECT s1 x s2: 2 x 2 => 1

INTERSECT s2: 5 => 1

INTERSECT s1 x s2: 3 x 5 => 1

INTERSECT s2: 3 => 1

INTERSECT s1 x s2: 3 x 3 => 1

INTERSECT s2: 7 => 1

INTERSECT s1 x s2: 3 x 7 => 1

INTERSECT s2: 1 => 1

INTERSECT s1 x s2: 1 x 1 => 1

INTERSECT s2: 6 => 1

INTERSECT s1 x s2: 1 x 6 => 1

INTERSECT s2: 9 => 1

INTERSECT s1 x s2: 1 x 9 => 1

--> %ExecuteAxes: PATIENTS: CURRENT AXES ARE VALID

WITH MEMBER [MEASURES].[MEDIAN TEST SCORE] AS '%KPI("%DeepSee.Median","MEDIAN",1,"%measure","Test Score","%CONTEXT")',FORMAT_STRING='#.##' 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] WHERE [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: .037432s

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