Dimensions, Hierarchies, and Levels

Levels and Members

A level consists of members, and a member is a set of records. For the City level, the Juniper member selects the patients whose home city is Juniper. Conversely, each record in the cube belongs to one or more members.

Most pivot tables simply display data for the members of one or more levels. For example, the Age Group level has the members 0 to 29, 30 to 59, and 60+. The following pivot table shows data for these members:

For another example, the following pivot table shows data for the Age Group and Gender levels (shown as the first and second columns respectively).

You can also drag and drop individual members for use as rows or columns. For example:

For more details on the options, see Using the Analyzer.

Member Names and Keys

Each member has both a name and an internal key. The compiler does not require either of these to be unique, even within the same level. Duplicate member names are legitimate in some cases, but you should check for and avoid duplicate member keys.

For an example of legitimate duplicate member names, consider that different people can have the same name, and you would not want to combine them into a single member. When a user drags and drops a member, the system uses the member key, rather than the name, in its generated query, so that the query always accesses the desired member.

Duplicate member keys, however, make it impossible to refer to each individual member. Given a member key, the system returns only the first member that has that key. You can and should ensure that your model does not result in duplicate member keys.

For information on the scenarios in which you might have duplicate member names and duplicate member keys, see Defining Member Keys and Names Appropriately.

Source Values

Each level is based on a source value, which is either a class property or an ObjectScript expression. For example, the Gender level is based on the Gender property of the patient. For another example, the Age Group level is based on an expression that converts the patient’s Age property to a string (0 to 29, 30 to 59, or 60+), depending on the age.

Hierarchies and Dimensions

In Business Intelligence, levels belong to hierarchies, which belong to dimensions. Hierarchies and dimensions provide additional features beyond those provided by levels.

Hierarchies are a natural and convenient way to organize data, particularly in space and time. For example, you can group cities into postal codes, and postal codes into countries.

There are three practical reasons to define hierarchies in Business Intelligence:

• The system has optimizations that make use of them. For example, if you are displaying periods (year plus month) as rows or columns, and you then filter to a specific year, the query runs more quickly if your model defines years as the parent of periods.

• You can use hierarchies within a pivot table as follows: If you double-click a member of a level, the system performs a drilldown to show the child members of that member, if any. For example, if you double-click a year, the system drills down to the periods within that year; for details, see Using the Analyzer.

• MDX provides functions that enable you to work with hierarchies. For example, you can query for the child postal codes of a given country, or query for the other postal codes in the same country.

  You can use these functions in handwritten queries; the Analyzer does not provide a way to create such queries via drag and drop.

A dimension contains one or more parent-child hierarchies that organize the records in a similar manner; for example, a single dimension might contain multiple hierarchies related to allergies. There is no formal relationship between two different hierarchies or between the levels of one hierarchy and the levels of another hierarchy. The practical purpose of a dimension is to define the default behavior of the levels that it contains — specifically the All level, which is discussed in the next subsection.

The All Level and the All Member

Each dimension can define a special, optional level, which appears in all the hierarchies of that dimension: the All level. If defined, this level contains one member, the All member, which corresponds to all records in the cube.

For example, the AgeD dimension includes one hierarchy with levels as follows:

For a given dimension, you specify whether the All member exists, as well as its logical name and its display name. Within this dimension, the All member is named All Patients.

List-Based Levels

In Business Intelligence, unlike many other BI tools, you can base a level upon a list value. For example, a patient can have multiple diagnoses. The Diagnoses level groups patients by diagnosis. For example:

For a list-based level, any given source record can belong to multiple members. The pivot table shown here includes some patients multiple times.

See Also

See Defining Dimensions, Hierarchies, and Levels.