Introduction to the Other Business Intelligence Tools
This chapter introduces the other tools for working with InterSystems IRIS Business Intelligence.
Most of the samples in this book are part of the Samples-BI sample (https://github.com/intersystems/Samples-BIOpens in a new window) or the Samples-Aviation sample (https://github.com/intersystems/Samples-AviationOpens in a new window).
InterSystems recommends that you create a dedicated namespace called SAMPLES (for example) and load samples into that namespace. For the general process, see Downloading Samples for Use with InterSystems IRIS®.
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:
Do ##class(%DeepSee.Utils).%Shell()Copy code to clipboard
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_areaNote:
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 Indices Used by a Query
The Business Intelligence shell provides a quick way to see the indices that a query uses:
Issue the following shell command:
stats onCopy code to clipboard
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 -----------------Copy code to clipboard
Line breaks were added here for readability.
The system captures all the indices 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 indices, not to get the query results.
The class %SYSTEM.DeepSeeOpens in a new window 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:
Do $system.DeepSee.BuildCube("MyCube")Copy code to clipboard
The class %DeepSee.UtilsOpens in a new window 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 window
%GetResultsetFromPivot() — returns an instance of %DeepSee.ResultSetOpens in a new window that holds the MDX query defined by a pivot table and optionally runs that query
The class %DeepSee.UserLibrary.UtilsOpens in a new window includes methods that you can use to programmatically perform the tasks supported in the Folder Manager. These methods include:
The data connector class (%DeepSee.DataConnectorOpens in a new window) enables you to make arbitrary SQL queries available for use in cubes and listings. See the Implementing InterSystems Business Intelligence.
Result Set API
The class %DeepSee.ResultSetOpens in a new window enables you to execute MDX queries programmatically and access the results.
For information, see the Implementing InterSystems Business Intelligence.
For information, see Client-Side APIs for InterSystems Business Intelligence.