Column Structure of Dimension Views

The AW_DIMENSION_CREATE_ACCESS procedure generates a separate view for each dimension hierarchy. For example, an AW cube with the four dimensions shown in Table 9-1, would have six separate dimension views since two of the dimensions have two hierarchies.

Table 9-1  Sample Dimension Hierarchies

Dimensions	Hierarchies	Number of Views
geography	standard consolidated	2
product	standard	1
channel	standard	1
time	standard ytd	2

The dimension views are level-based, and they include the full lineage of every level value in every row. This type of dimension table is considered solved, because the fact table related to this dimension includes embedded totals for all level combinations.

Each dimension view contains the columns described in Table 9-2.

Table 9-2 Dimension View Columns

Column	Description
ET key	The embedded-total key column stores the value of the lowest populated level in the row.
Parent ET key	The parent embedded-total key column stores parent of the ET key column.
GID	The grouping ID column identifies the hierarchy level associated with each row, as described in "Grouping ID Column".
Parent GID	The parent grouping ID column stores the parent of the grouping ID column.
level columns	There is a column for each level of the dimension hierarchy.
level attribute columns	There is a column for each level attribute.

Sample Dimension View

For a standard geography hierarchy with levels for TOTAL_US, REGION, and STATE, the dimension view would contain columns like the ones shown below. Level attribute columns would also be included.

GID PARENT_GID  ET KEY     PARENT_ET_KEY TOTAL_US  REGION      STATE  
--- ----------  ------     ------------  --------  ------      -----
0   1           MA         Northeast     USA       Northeast   MA     
0   1           NY         Northeast     USA       Northeast   NY     
0   1           GA         Southeast     USA       Southeast   GA     
0   1           CA         Southwest     USA       Southwest   CA
0   1           AZ         Southwest     USA       Southwest   AZ
1   3           Northeast  USA           USA       Northeast
1   3           Southeast  USA           USA       Southeast
1   3           Southwest  USA           USA       Southwest
3   NA          USA        NA            USA

Grouping ID Column

The GID identifies the hierarchy level associated with each row by assigning a zero to each non-null value and a one to each null value in the level columns. The resulting binary number is the value of the GID.

For example, a GID of 1 is assigned to a row with the following three levels.

TOTAL_US  REGION      STATE  
--------  ------      -----
USA       Southwest   
0         0           1

A GID of 3 is assigned to a row with the following five levels.

TOTAL_GEOG COUNTRY REGION     STATE  CITY
---------- ------- -------    ------ -------
World      USA     Northeast
0          0       0           1      1