Understanding the Cube Structure

A SAS OLAP cube stores data in a method that enables fast retrieval of summarized data. Data summarization in this context means condensing large numbers of detail records into meaningful numbers such as counts, sums, averages, or other statistical measures. The structure of a cube is hierarchical in nature and is derived from the associations between the different columns and rows of data in a data source. SAS OLAP cubes consist of dimensions, levels, hierarchies, members, and member properties. This structure enables you to easily select data subsets and navigate the cube structure when querying the cube.

SAS OLAP cubes organize data in a hierarchical arrangement, according to dimensions and measures. Dimensions group the data along natural categories and consist of one or more levels. Each level represents a different grouping within the same dimension. For example, a time dimension can include levels such as years, months, and days. Or an organization dimension of a bank's customer service centers can include levels such as branches, states, and regions.

Levels are organized into one or more hierarchies, typically from a coarse-grained level (for example, Year) down to the most detailed one (for example, Day). The individual category values (for example, 2002 or 21Jan2002) are called members.

A dimension can also have multiple hierarchies to provide different sequences of groupings. For example, a "Time" dimension can have a "Fiscal Year" hierarchy and a "Calendar Year" hierarchy.

Each combination of values within a dimension is called a member. Some examples of members are shown here.

For each dimension, there is also the special member called the ALL member, which represents the total for all members (for example, [Time].[All Time]). Not all categorical data attributes need to become a member of a hierarchy level. Some grouping information is needed only as additional information for a member or for applying subsets to data. These attributes can be loaded into member properties. Member properties can be associated with any level in a hierarchy.

Measures are the cube data values that are summarized and analyzed. A measure is the combination of a numeric input column with a roll-up rule or statistic. Measures are loaded from the data source that you summarize from. One input column can load one or more measures. For example, you can create the measures "Sum of Amount" and "Maximum of Amount" from the input column "Amount."

Not all measures are directly derived from input columns. You can create calculated measures, which are formulas that are based on the values of other measures. Calculated measures are designed and stored with the cube.

An aggregation is a summary of detail data that is stored with or referred to by a cube. They are the basis for fast response to data queries in OLAP applications. An aggregation is possible at each intersection of a level of one or more dimensions. Any combination of dimension levels can become a stored aggregation, as long as it is appropriate within the defined hierarchies. One of the major factors that influences query response time is which aggregations you create and use to query your cube. The aggregations that are being stored with the cube affect cube build time, the absolute cube file size, SAS OLAP Server CPU usage, and query response times. As a result, determining and building your cube aggregations is a crucial component of good cube design.