Business Intelligence and MDX
This article explains how InterSystems IRIS® Business Intelligence supports MDX (MultiDimensional eXpressions), which is a query language implemented by many vendors.
Be sure to consult the online InterSystems Supported Platforms document for this release for information on system requirements for Business Intelligence.
Purpose of Business Intelligence
The purpose of Business Intelligence is to enable you to embed business intelligence (BI) into your applications so that your users can ask and answer sophisticated questions of their data. Your application can include dashboards, which can include pivot tables.
A pivot table is an interactive, drillable display of data, designed for specific user roles or for specific areas of your user interface.
Each pivot table has an underlying MDX query that is executed at runtime. Instead of directly querying your transactional tables, the system queries its cubes, which are synchronized with the transactional tables. (For information on defining cubes, see Defining Models for InterSystems Business Intelligence.)
Introduction to Pivot Tables
Pivot tables are central to Business Intelligence; they select and aggregate data and display it in an interactive format.
The following figure shows an example pivot table. It shows the number of patients and the average allergy count per patient, grouped by age and gender.
Because the concepts are interrelated, making it difficult to discuss each concept without reference to the others, it is useful for us to start with preliminary definitions:
A level enables you to group records. A level has members. Each member, in turn, corresponds to a specific group of records in the source data.
For example, the Age Group level has the members 0 to 29, 30 to 59, and 60+. The Age Bucket level has the members 0-9, 10-19, 20 to 29, and so on. The Gender level has the members Female and Male.
A measure is a value displayed in the body of the pivot table; it is based on values in the source data, for selected records. For a given context, a measure aggregates the values for all applicable source records and represents them with a single value.
For example, the measure Patient Count is the number of patients, and the measure Avg Allergy Count is the average number of allergies per patient.
Introduction to MDX
MDX is a standard query language for OLAP (online analytical processing) databases. The MDX language provides syntax for referring to cube elements. Most of the statements and functions in the language enable you to execute queries against a cube. The returned data is a result set, which can be displayed as a pivot table.
MDX also provides the capability of extending a cube definition. In particular, you can define new elements based on existing elements, and then use those new elements in MDX queries.
Business Intelligence supports MDX as follows:
When you create a pivot table in the Analyzer, the system generates and uses an MDX query, which you can view directly.
The Analyzer provides an option for directly running MDX queries.
You can run MDX queries in the MDX shell and see their results.
The system provides an API that you can use to run MDX queries.
Within a Business Intelligence model, you use MDX expressions and queries to define certain elements, as discussed in the following subsection.
Note that some MDX queries are too complex to create within the current user interface. You can execute such queries in the shell or via the API, but you cannot create them via drag and drop actions in the Analyzer.
For further information, see the following sources:
For information on the Analyzer, see Using the Analyzer.
For information on the MDX shell, see Introduction to InterSystems Business Intelligence.
For information on the MDX API, see the Implementing InterSystems Business Intelligence.
The system provides an implementation of MDX. Results may differ from other implementations.
MDX in Business Intelligence Models
In Business Intelligence models, you can use MDX expressions and queries in the following places:
Within a cube definition:
You use an MDX member expression to define calculated members.
You use an MDX set expression to define named sets.
You use an MDX set expression to filter the cube.
These are all optional.
Within a subject area definition, you use an MDX set expression to filter the subject area. This is optional; a subject area does not have to include a filter.
Within a KPI (key performance indicator) definition, you can use an MDX query to define the KPI. This is optional; you can use an SQL query instead.
For information, see Defining Models for InterSystems Business Intelligence and the Implementing InterSystems Business Intelligence.