SAS/MDDB Server Administrator's Guide

Analyzing Your Data

The key to creating an efficient MDDB is thorough analysis of both your data and its users. Armed with this analysis, you can determine

the best order for the classification variables in the NWAY cube
whether or not you should create subcubes
which classification variables should be included in the subcubes.

While it is possible to create an MDDB that consists of the NWAY cube only, this approach would require that the NWAY cube fulfill all requests made by OLAP clients. While this approach saves on storage space, you can improve query performance if you create subcubes based on anticipated client requests. In order to create an MDDB that requires the least storage space while providing users with the best response time, consider the following issues:

the warehouse data and how it will be used in the OLAP application
the business problem and user expectations
the need for an iterative approach.

By choosing and ordering classification variables for the NWAY cube and subcubes based on the needs of users, you will create an MDDB that meets the functional priorities dictated by your organization's business requirements.

Creating useful and efficient MDDBs is best accomplished as an iterative process that fits into the overall data warehouse and business intelligence strategies of your organization. You can analyze the usage patterns to determine whether your MDDB is defined correctly and make adjustments if necessary. For example, if a defined subcube is rarely or never accessed, it can be safely removed from the MDDB creation. Alternately, if there are subcubes that are not defined but are frequently requested, they can be added to the MDDB creation.

Using a Spiral Diagram to Order the Classification Variables

One way you can create an initial MDDB that will meet most users' requirements and performance expectations is to analyze your data by using a spiral diagram. By placing the classification variables on axes representing the hierarchies of the MDDB, you can develop an acceptable data model for the MDDB.

Imagine that you have a base table that contains retail sales information. The classification variables in this table can be grouped into four dimensions, or groups of variables with similar characteristics:

product (SECTOR, GRP, FAMILY, ARTICLE)
time (YEAR, MONTH, DAY)
geography (REGION, SHOP)
supplier (GRP_SUPP, SUPPLIER).

First, create an axis for each dimension. Then, place the classification variables on the appropriate axes (working from the outside to the center) in ascending order of cardinality (number of unique values), as shown in the following diagram. Each variable becomes a dimensional level for that dimension, with the outermost variable at the top dimensional level.

Axis Diagram

[IMAGE]

The placement of the axes in relation to each other can be significant. You might want to try several arrangements to find one that works best for you. The following are possible arrangements:

arrange axes in descending order of likelihood of use
arrange axes in descending order of cardinality at the top dimensional level
arrange axes in descending order of number of levels.

Now you can draw a spiral on the diagram that will indicate a general ordering scheme for the variables. Starting on the outside with the classification variable with the lowest cardinality that is also the one most likely to be of interest to users, draw a line from this variable to an adjacent variable on the diagram. Continue in this manner, spiraling in toward the center. As in this example, you might have to deviate slightly from this pattern when multiple variables near the center have high cardinality. Here, the spiral is drawn so that FAMILY is after SUPPLIER instead of DAY.

Spiral Diagram

[IMAGE]

You can then produce an ordered list of classification variables based on the diagram. For this example, the order would be

YEAR

SECTOR

REGION

GRP_SUPP

MONTH

GRP

SHOP

SUPPLIER

FAMILY

DAY

ARTICLE

From this list, you can develop an intelligent choice of crossings. Start with the whole list and successively drop the last item in a "stair-step" fashion to form a new crossing, as follows:

YEAR SECTOR REGION GRP_SUPP MONTH GRP SHOP SUPPLIER FAMILY DAY ARTICLE
YEAR SECTOR REGION GRP_SUPP MONTH GRP SHOP SUPPLIER FAMILY DAY
YEAR SECTOR REGION GRP_SUPP MONTH GRP SHOP SUPPLIER FAMILY
YEAR SECTOR REGION GRP_SUPP MONTH GRP SHOP SUPPLIER
YEAR SECTOR REGION GRP_SUPP MONTH GRP SHOP
YEAR SECTOR REGION GRP_SUPP MONTH GRP
YEAR SECTOR REGION GRP_SUPP MONTH
YEAR SECTOR REGION GRP_SUPP
YEAR SECTOR REGION
YEAR SECTOR
YEAR

From this list of crossings, you can define the NWAY cube and as many subcubes as you want.

Chapter Contents
Previous
Next
Top of Page

	YEAR
	SECTOR
	REGION
	GRP_SUPP
	MONTH
	GRP
	SHOP
	SUPPLIER
	FAMILY
	DAY
	ARTICLE