1
CHAPTER
1
MDX Introduction and Overview
MDX Overview 1
Basic MDX and Cube Concepts 1
Dimensions 2
Hierarchies 2
Levels 2
Members and Measures 2
Additional MDX Concepts and Expressions - Tuples and Sets 2
Additional MDX Documentation 3
MDX Overview
Multidimensional Expressions (MDX) is a powerful syntax that enables you to query
multidimensional objects and provide commands that retrieve and manipulate
multidimensional data from those objects. MDX is designed to ease the process of
accessing data from multiple dimensions. It addresses the conceptual differences
between two-dimensional and multidimensional querying. MDX provides functionality
for creating and querying multidimensional structures called cubes with a full and
complete language of its own.
MDX is similar to the Structured Query Language (SQL), and MDX provides Data
Definition Language (DDL) syntax for managing data structures. However, its features
can be more complex and robust than SQLs features. The SAS 9.1 OLAP Server
technology uses MDX to create OLAP cubes and data queries. MDX is part of the
underlying foundation for the SAS 9.1 OLAP Server architecture, and it offers detailed
and efficient searches of multidimensional data.
With MDX, specific portions of data from a cube can be extracted and then further
manipulated for analysis. This allows for a thorough and flexible examination of SAS
OLAP cube data. Users of MDX can take advantage of such features as calculated
measures, numeric operations, and axis and slicer dimensions.
Basic MDX and Cube Concepts
To better understand the MDX language and the OLAP technology it supports, a
basic understanding of the OLAP cube components is required.
2 Dimensions 4 Chapter 1
Dimensions
Dimensions are the top or highest categories of a cube. They contain subcategories of
data known as levels and measures. A dimension can have multiple hierarchies and
can be used in multiple cubes. A cube can have up to 64 dimensions.
Hierarchies
A dimension might be categorized into different hierarchies. For example, a company
might categorize its profit dimension along the verticals of geography, sales territory, or
market.
Levels
Levels are categories of organization within a dimension. Levels are hierarchical, and
each level that is descended in a dimension is a component of the previous level. For
example, a time dimension could include the following levels: Year, Quarter, Month,
Week, and Day.
Members and Measures
An additional component of a dimension and a level is a member. A member is a
component of a level and is analogous to the value of a variable on an individual record
in a data set. It is the smallest level of data in an OLAP cube. In addition to creating
dimension members, a user can create calculated members and named sets that are
based on underlying members or on other calculated members and named sets. These
user-defined objects are based on evaluated query data from the cube.
Calculated members and named sets can be created in three different ways:
Query scope is only available during the query that defines it. It is created by
calculated using the WITH MEMBER/SET keyword.
member
Session scope is available for the user that defines the object for the duration of
calculated that session. It is created by using the CREATE SESSION
member MEMBER/SET keyword.
Global scope is available for anyone to use and is stored with the cube. It is
calculated created by using the CREATE GLOBAL MEMBER/SET keyword.
member Named sets have the same three scopes.
Calculated members can be created in the Measures dimension and can include any
combination of members. Calculated members can also be created in any other
dimension and are known as nonmeasure-based calculated members. Examples of
measures include sales counts, profit margins, and distribution costs.
Additional MDX Concepts and Expressions - Tuples and Sets
MDX extracts multidimensional views of data. A tuple is a slice of data from a cube.
It is a selection of members (or cells) across dimensions in a cube. It can also be viewed
as a cross-section or vector of member data in a cube. A tuple can be composed of
4 Additional MDX Documentation 3
member(s) from one or more dimensions. However, a tuple cannot be composed of more
than one member from the same dimension.
Sets are collections of tuples. The order of tuples in a set is important when querying
cube data and is known as dimensionality. It is important to note that the order of the
dimension members in every tuple must be the same. For example, if your first tuple is
(time_dimension_member, geography_dimension_member), then every other tuple in
that set must also have two members in it, the first from the time dimension and the
second from the geography dimension.
Additional MDX Documentation
In addition to the MDX usage examples, functions and related topics that are found
in this documentation, a supplementary text for the SAS OLAP Server is available. The
SAS OLAP Server: Concepts and Excerpts from MDX Solutions with Microsoft SQL
Server Analysis Services includes basic MDX information such as the MDX data model,
MDX construction, comments in MDX, and a complete MDX function and operator
reference. You can locate this text at support.sas.com/publishing.
