Using MDX with DeepSee
This chapter provides an overview of DeepSee and explains how
DeepSee supports MDX (MultiDimensional eXpressions), which is a query
language implemented by many vendors.
The purpose of InterSystems DeepSee 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,
DeepSee queries its cubes
, which are synchronized
with the transactional tables. (For information on defining cubes,
see Defining DeepSee Models
Introduction to Pivot Tables
Pivot tables are central to DeepSee; 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:
enables you to group
records. A level has members
. Each member, in
turn, corresponds to a specific group of records in the source data.
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.
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.
DeepSee supports MDX as follows:
When you create a pivot table in the Analyzer, DeepSee
generates and uses an MDX query, which you can view directly.
The Analyzer provides an option for directly running
You can run MDX queries in the MDX shell and see their
DeepSee provides an API that you can use to run MDX
Within a DeepSee model, you use MDX expressions and
queries to define certain elements, as discussed in the following
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:
DeepSee provides an implementation of MDX. Results may differ
from other implementations.
In DeepSee 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
You use an MDX set expression to define named sets.
You use an MDX set expression to filter the cube.
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.