This page introduces the other tools for working with InterSystems IRIS Business Intelligence.
The system provides a shell in which you can issue MDX queries to explore your cubes and subject areas. This section introduces this shell and lists the supported MDX options and functions.
For an introduction to MDX queries, see Using InterSystems MDX, which contains many examples.
Also see the InterSystems MDX Reference.
Accessing the MDX Shell
To access the MDX shell, start the Terminal and do the following:
Switch to the namespace in which you defined the cube or subject area.
Enter the following command:
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:
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
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:
Issue the following shell command:
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) **
-------------- 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.
The class %SYSTEM.DeepSeeOpens in a new tab includes the most commonly used utility methods. These include:
This class is available via the special variable $SYSTEM, as are all classes in the %SYSTEM package. For example, to build a cube, you can use the following:
The class %DeepSee.UtilsOpens in a new tab includes a large set of utility methods, including:
%ExportExcelToFile() — exports a Business Intelligence query or KPI to a file in Microsoft Excel format
%ExportPDFToFile() — exports a Business Intelligence query or KPI to a file in PDF format
%GetAgentCount() — gets the current agent count
%GetBaseCube() — gets the name of cube on which a subject area is based
%GetCubeFactClass() — gets the name of fact table class associated with a cube
%GetCubeLevels() — gets the levels, measures, and relationships defined in a cube
%GetDimensionMembers() — gets the list of members of a dimension
%GetMetricList() — gets all production business metrics visible to current user
%GetSQLTableName() — gets SQL table name for a given class
%ProcessFact() — updates a single fact for a cube
%GetMDXFromPivot() — returns the MDX query defined by a pivot table
%ExecutePivot() — runs the MDX query defined by a pivot table and optionally returns an instance of %DeepSee.ResultSetOpens in a new tab
%GetResultsetFromPivot() — returns an instance of %DeepSee.ResultSetOpens in a new tab that holds the MDX query defined by a pivot table and optionally runs that query
The class %DeepSee.UserLibrary.UtilsOpens in a new tab includes methods that you can use to programmatically perform the tasks supported in the Folder Manager. These methods include:
For information, see Client-Side APIs for InterSystems Business Intelligence.