Sample Other Dimension Table and Columns

FactX_Other_Dimension - Other
 dimension for FactX, which contains a collection of low-cardinality attributes that are applicable to FactX but are not appropriate for another dimension

Column  /  Alternative

Data Type  /  Comment

Description  /  Examples  /  Notes

FactX_Other_

Dimension_Key

FactXOtherDimensionKey

 SmallInt

Surrogate key arbitrary number that uniquely identifies a collection of low-cardinality attributes that are applicable to FactX.

1, 2, 3, ..., and 0 (for not applicable).

This key is also a column in the FactX table where it relates a fact to a collection of low-cardinality attributes.


  

Loaded_Date_Time

LoadedDateTime

 TimeStamp

Date and time, in the current time zone, a row was first loaded into the data warehouse staging area.

... 2011-05-20 23:59:59, 2011-05-21 00:00:00, ....

The date and time retrieved from this column are those that were in effect in the time zone of the retrieval when the statement that loaded the row began to execute, even if the row was loaded in a different time zone.

Modified_Date_Time

ModifiedDateTime

 TimeStamp

Date and time, in the current time zone, a row was last modified in the data warehouse staging area.

0000-00-00 00:00:00, ... 2011-05-20 23:59:59, 2011-05-21 00:00:00, ....

The date and time retrieved from this column are those that were in effect in the time zone of the retrieval when the statement that last modified the row began to execute, even if the row was modified in a different time zone.

  • It is likely that such a table would not be needed with Infobright. Since the columns are of low-cardinality, they would be massively compressed using Infobright's 'lookup' comment and could probably be added to the relevant fact table, speeding queries, while using very little additional storage space.
  • Some of the attributes in this dimension may be related to each other, in some sense, while other attributes may be completely unrelated.
  • This dimension is a type 1 slowly changing dimension, in that a history of changes to it is not maintained.
  • Only changes that are applicable to every fact to which an instance of this dimension is related may be made to that instance. Thus, few, if any, changes are made to instances of this dimension.
  • This type of dimension is sometimes called a "junk dimension" since it contains an assortment of things that are not appropriate for another holder, similar to a "junk drawer" in a kitchen cabinet. However, since this type of dimension contains valuable information, we prefer not to use the word "junk" to refer to it.
  • When this type of dimension applies to a fact table named Transaction it would be named Transaction_Other_Dimension.