Example of using the MDX subquery flag in the DeepSee Shell
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