SAS/MDDB Server Administrator's Guide

What Is an MDDB?

A multidimensional database, or MDDB, is a specialized storage facility that allows data to be pulled from a data warehouse or other data sources for storage in a matrix-like format. The process of building an MDDB summarizes the raw data; the data stored in the MDDB is thus said to be presummarized. The MDDB enables users to quickly retrieve multiple levels of presummarized data through a multidimensional view. An MDDB is not a SAS data file; instead of the traditional structure used for SAS tables, an MDDB stores its data as an NWAY cube and zero or more subcubes.

An NWAY cube is a multidimensional data model that specifies all the classification variables that can be used to define crossings. A crossing is a definition of one or more classification variables that exist in the data from which the MDDB is created. Each crossing represents a grouping on which summary statistics could be calculated.

While an MDDB can consist of an NWAY cube only, it is usually created with one or more subcubes. Subcubes are created using simple statements of hierarchy that define one or more additional specific crossings derived from the NWAY cube. Subcubes are built to enhance reporting speed and are projections of the most likely aggregates that business managers and other users will expect to see. If a subcube does not exist for a particular aggregate query, that is, if no subcube defines the exact crossings required to answer the query, the aggregate data will be derived from the smallest subcube that can provide the data. If no subcube can provide the data, it is derived from the NWAY cube.

By default, a minimum number of statistics are stored in the MDDB. During creation of the MDDB, you can specify up to 8 statistics for each analysis variable. Depending on which of the 8 statistics are stored, up to 13 additional statistics can be calculated by SAS/MDDB Server software at run time, allowing for up to 21 available statistics. For more information, see Stored and Derived Statistics.

Other characteristics common to all SAS MDDBs include the following:

MDDBs can be created interactively or in batch.
There is no practical limitation on the number of subcubes/crossings.
There is no practical limitation on the number of analysis variables.
MDDBs can be updated incrementally.

Understanding the MDDB Structure

The structure of an MDDB, including the NWAY cube and its subcubes, is best illustrated through an example.

The SAS table from which an MDDB is created is called the base table. For this example, assume that our base table contains the following fields:

classification variables: YEAR, MONTH, PRODUCT
analysis variables: SALES, RETURNS.

The base table might look like this:

Example Base Table
YEAR MONTH PRODUCT SALES RETURNS

1999 Jan Bib $10396 $1281

1999 Jan Blanket $11241 $1145

1999 Feb Bib $9823 $789

1999 Feb Blanket $14605 $1358

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

1998 Jan Bib $9846 $673

1998 Jan Blanket $10263 $1183

1998 Feb Bib $10793 $978

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

When you define your NWAY cube, you will include all the classification variables, thus defining a crossing for each combination of classification variable values for which input data is available. A multidimensional report based on this MDDB could allow users to research the total sales of a product in a given year, and then drill down to total sales of the product in a given month. Because these summary statistics are already stored in the NWAY cube, little processing at run time is required.

***Example Base Table***
YEAR	MONTH	PRODUCT	SALES	RETURNS
1999	Jan	Bib	$10396	$1281
1999	Jan	Blanket	$11241	$1145
1999	Feb	Bib	$9823	$789
1999	Feb	Blanket	$14605	$1358
. . .	. . .	. . .	. . .	. . .
1998	Jan	Bib	$9846	$673
1998	Jan	Blanket	$10263	$1183
1998	Feb	Bib	$10793	$978
. . .	. . .	. . .	. . .	. . .

Conceptually, your NWAY cube would look like this:

NWAY Cube

[IMAGE]

If you know of common queries that can be answered using a smaller set of crossings, you could create a subcube that specifies the exact crossing required. For example, assume that you know that the most commonly requested multidimensional report will be one that allows the user to research total sales in a given year for a given product. You could define a subcube that contains only the YEAR and PRODUCT variables.

NWAY Cube and Subcube

[IMAGE]

Of course, the crossing required to answer this query can be summarized from the NWAY cube, but because you know that the MONTH variable will not be needed, you would define the subcube to improve reporting performance. Achieving optimal performance from your MDDB depends in part on the subcubes you choose to define. Building and Updating MDDBs explains this and other performance considerations in detail.

Note: The preceding example is highly simplified. The real power of MDDBs lies in their ability to handle data in many dimensions, not just two or three. This example was used because it is easy to illustrate; it is difficult to imagine or draw an object with more than three dimensions. So, although an object with a YEAR dimension and a PRODUCT dimension does not really make a "cube" as in the previous diagram, the important thing to remember is that you can define a subcube on your MDDB with whatever dimensions that you want. [cautionend]

Chapter Contents
Previous
Next
Top of Page