Skip to main content

Introduction to the Other Business Intelligence Tools

This page introduces the other tools for working with InterSystems IRIS Business Intelligence.

BI Samples

Most of the samples in this documentation are part of the Samples-BI sample (https://github.com/intersystems/Samples-BIOpens in a new tab) or the Samples-Aviation sample (https://github.com/intersystems/Samples-AviationOpens in a new tab).

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.

MDX Shell

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:

  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.

Utility Methods

  • The class %SYSTEM.DeepSeeOpens in a new tab includes the most commonly used utility methods. These include:

    • BuildCube()

    • KillCube()

    • ListCubes()

    • Reset()

    • Shell()

    • SynchronizeCube()

    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")
  • 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:

    • %AddFavorite()

    • %DeleteFolderContents()

    • %DeleteFolderItem()

    • %Export()

    • %GetFolderList()

    • %ImportContainer()

Data Connector

The data connector class (%DeepSee.DataConnectorOpens in a new tab) enables you to make arbitrary SQL queries available for use in cubes and listings. See Implementing InterSystems Business Intelligence.

Result Set API

The class %DeepSee.ResultSetOpens in a new tab enables you to execute MDX queries programmatically and access the results.

For information, see Implementing InterSystems Business Intelligence.

JavaScript and REST APIs

The Business Intelligence JavaScript API is provided by the file DeepSee.js, which is in the install-dir/CSP/broker directory. This JavaScript library enables you to interact with Business Intelligence from a client that is based on JavaScript. The functions in this library are a wrapper for a REST-based API for Business Intelligence. You can also use the REST API directly.

For information, see Client-Side APIs for InterSystems Business Intelligence.

FeedbackOpens in a new tab