Introduction to the Other Business Intelligence Tools

Accessing the MDX Shell

To access the MDX shell, start the Terminal and do the following:

1. Switch to the namespace in which you defined the cube or subject area.

2. Enter the following command:

    Do ##class(%DeepSee.Utils).%Shell()

   

Now you can enter MDX queries like the following:

SELECT MEASURES.[%COUNT] ON 0, birthd.decade.MEMBERS ON 1 FROM patients

When you do so, the shell executes the query, displays its results to the console, and redisplays the shell prompt, as follows:

                             Patient Count
 1 1910s                                 71
 2 1920s                                223
 3 1930s                                572
 4 1940s                                683
 5 1950s                              1,030
 6 1960s                              1,500
 7 1970s                              1,520
 8 1980s                              1,400
 9 1990s                              1,413
10 2000s                              1,433
11 2010s                                155
---------------------------------------------------------------------------
Elapsed time:       .014128s

In the shell:

• To display a list of cubes and subject areas, enter cube

• To see the contents of a cube or subject area, enter cube name_of_cube_or_subject_area

  Note:

  This command does not display calculated members and named sets, although you can use these elements in the shell and elsewhere.

  For a subject area, this command lists all elements, even if those are specified as hidden in the subject area.

• To exit the shell, enter q

• To enable query caching, enter cache on. If you set cache off and then run an MDX query in the shell, the MDX shell purges all cached queries for that specific cube system-wide. This could lead to slower performance for other users.

• To enable the asynchronous mode, enter async on

• To build a cube, enter build cubename

• To reset the query cache, enter reset

• For a list of additional shell options, enter ?

Viewing the Indexes Used by a Query

The Business Intelligence shell provides a quick way to see the indexes that a query uses:

1. Issue the following shell command:

   stats on
   

   

2. Enter the query, preceded by %SHOWPLAN. For example:

   %SHOWPLAN SELECT aged.[age group].members ON 0, allerd.H1.MEMBERS ON 1 FROM patients WHERE colord.red
    
                              0 to 29           30 to 59               60 +
    1 additive/colorin              27                 19                 14
    2 animal dander                 15                 25                  8
    3 ant bites                     15                 19                 11
    4 bee stings                    24                 27                  7
    5 dairy products                25                 25                  4
    6 dust mites                    28                 23                 10
    7 eggs                          19                 21                 13
    8 fish                          26                 17                 11
    9 mold                          23                 23                  6
   10 nil known allerg              80                 82                 21
   11 No Data Availabl             216                194                 92
   12 peanuts                       26                 15                  8
   13 pollen                        29                 22                 11
   14 shellfish                     29                 23                 14
   15 soy                           25                 25                  6
   16 tree nuts                     22                 18                  8
   17 wheat                         16                 17                  8
   -------------- Query Plan ---------------------
   **%SHOWPLAN SELECT [AGED].[AGE GROUP].MEMBERS ON 0,[ALLERD].[H1].MEMBERS ON 1 FROM [PATIENTS] WHERE [COLORD].[RED]**
   **DIMENSION QUERY (%FindMemberByName): SELECT TOP 1 %ID,Dx327553094 MKEY,Dx327553094 FROM 
   Cubes_StudyPatients.Star327553094 WHERE Dx327553094=? ORDER BY Dx327553094**
   **EXECUTE PARALLEL: 1x1 task(s) **
   **CONSOLIDATE**
   -------------- End of Plan -----------------
   

   

   Line breaks were added here for readability.

The system captures all the indexes used by the query and reports them. Note that the query results are not necessarily correct because the query is only partially run; the purpose of %SHOWPLAN is to enable you to see the indexes, not to get the query results.