Dimension Tables

Basics

When you compile a cube definition class, the system also generates a table for each level, other than the age- and time-type levels. These tables are in the same package as the fact table. The system also generates foreign key definitions in the dimension tables that declare links to the other dimension table IDs within the model schema.

The system updates the dimension tables (also called star tables) whenever you build the cube or update it incrementally.

The dimension table for a level contains one row for each member of that level. The dimension tables are created dynamically as the system processes records in the base table. For a given level, each time a new unique value is discovered, that value is added as a new row to the appropriate dimension table. This means that the system automatically adds rows to the dimension tables when needed; no intervention is required.

Name of Dimension Table

If the cube definition specifies the factName attribute for the corresponding level, the applicable dimension table uses that value as its name.

Otherwise, the name of the dimension table has the following form:

Stargenerated_name

Where generated_name is the corresponding field name in the fact table, without the Dx at the start. For example, suppose that in the fact table, the field name for the Home City level is DxPostalCodeViaHomeCity. In this case, the corresponding dimension table is named StarPostalCodeViaHomeCity.

Note that two levels using the same source property or source expression must have the same factName.

Columns in a Dimension Table

The columns in this row are as follows:

• ID — ID of this row, assigned when the row is created.

• One column to contain the key for this member. The field name is the same as the column name field in the fact table that corresponds to this level; see the previous section.

• One column for each property of this level, to contain the actual property value for this member.

  The field name starts with Dx and is based either on the source property name or is generated as a unique number, as described earlier.

  If you use linkClass and linkProperty to define both the property and the level to which it belongs, the field names for the property and the level would be identical. In this scenario, the system appends _Link to the end of the field name for the property.

• One column for the parent level of this level, to contain the ID of the parent of this member.

  The field name is the same as the column name in the fact table that corresponds to the parent level.

Depending on how the level is defined, you can find the member names as follows:

• By default, the key is used as the name, and the name is not stored separately.

• If the level includes a property that is defined with isName="true", then the member names are stored in the column that contains that property (with one exception). The exception is when the property is also defined with isReference="true"; in this case, the field is computed at runtime.