Beam

Front 1

[DE]

Back 1

Event and Fact Table Types
Discrete Event.
Point in time or short duration (completed) transaction.

Front 2

[EE]

Back 2

Event and Fact Table Types
Evolving Event.
(multi-verb) process that takes time to complete.

Front 3

[RE]

Back 3

Event and Fact Table Types
Recurring Event.
Measurements taken at predictable regular intervals.

Front 4

[TF]

Back 4

Event and Fact Table Types
Transaction Fact table.
Physical equivalent of DE. Typically maintained by insert only.

Front 5

[AS]

Back 5

Event and Fact Table Types
Accumulating Snapshot.
Physical equivalent of EE. Maintained by insert and update. Typically contains multiple milestone date/time dimensions and duration facts.

Front 6

[PS]

Back 6

Event and Fact Table Types
Periodic Snapshot.
Physical equivalent of RE. Typically contains semi-additive facts.

Front 7

[AG]

Back 7

Event and Fact Table Types
Aggregate.
Fact table that pre-summarizes an existing detailed fact table.

Front 8

[DF]

Back 8

Event and Fact Table Types
Derived Fact table.
Fact table constructed by merging, slicing, or pivoting existing fact tables.

Front 9

[CV]

Back 9

Dimension Types
Current Value.
Contains current value only dimensional attributes. Type 1 SCD.

Front 10

[HV]

Back 10

Dimension Types
Historic Value.
Contains at least one historical value dimensional attribute. Type 2 SCD.

Front 11

[RP]

Back 11

Dimension Types
Role-Playing.
Used to play multiple roles.

Front 12

[RU]

Back 12

Dimension Types
Roll-Up.
Derived from a more granular dimension.

Front 13

[SD]

Back 13

Dimension Types
Swappable Dimension.
Part of a set of dimensions with a common surrogate key that can be used in place of each other.

Front 14

[ML]

Back 14

Dimension Types
Multi-Level.
Dimension containing additional members representing higher levels in the dimension’s hierarchy.

Front 15

[HM]

Back 15

Dimension Types
Hierarchy Map.
Table used to resolve a recursive relationship. Represents a variable depth hierarchy.

Front 16

[MV]

Back 16

Dimension Types
Multi-Valued.
Bridge table used to resolve a many-to-many relationship between a fact table and a multi-valued dimension.

Front 17

[PD]

Back 17

Dimension Types
Pivoted Dimension.
Contains column flags built from the row values of another dimension.

Front 18

MD

Back 18

General Column Types
Mandatory.
Value is present under normal conditions. Can be nullable to handle errors.

Front 19

NN

Back 19

General Column Types
Not Null.
Column does not allow nulls. All SK and FK columns are NN by default.

Front 20

ND
NDn

Back 20

General Column Types
No Duplicates.
Numbered to define combinations of column values that must be unique. PK columns are ND by default.

Front 21

Xn

Back 21

General Column Types
Exclusive.
Column is not valid in combination with other X columns. Numbered to identify mutually exclusive groups and identify the specific DC which controls validity.

Front 22

DC
DCn,n

Back 22

General Column Types
Defining Characteristic.
Column value dictates which X columns are valid. E.g., Product Type DC defines which exclusive product dimension attributes are valid. Number list relates multiple defining characteristics in the same table.

Front 23

[W type]
[dimension]

Back 23

General Column Types
Dimension type or name.
The W type (who, what, when, where, why, how) of an event detail or the dimension name when a detail is a role.

Front 24

DD

Back 24

Event and Fact Table Column Types
Degenerate Dimension.
Dimensional attribute stored in a fact table. Typically used for transaction IDs (how details).

Front 25

GD
GDn

Back 25

Event and Fact Table Column Types
Granularity Dimension.
Dimension combination that defines the granularity of a fact table. Numbered when alternative combinations exist.

Front 26

MV

Back 26

Event and Fact Table Column Types
Multi-Valued.
Event detail contains multiple values that must be resolved using a bridge table. Fact table FK that references a multi value bridge table.

Front 27

ML

Back 27

Event and Fact Table Column Types
Multi-Level.
Event detail can represent various levels in a hierarchy; e.g., individual employee or teams/branches. Fact table FK that points to a multi-level dimension and makes use of the additional levels.

Front 28

FA

Back 28

Fact Types
Fully Additive.
Fact that produces a correct total when summed across any combination of its dimensions. For a fact to be (fully) additive, it must be expressed in a single unit of measure. Percentages and unit prices are not additive.

Front 29

SA
SAn

Back 29

Fact Types
Semi-Additive.
Fact that can be correctly totaled by some dimensions but not by at least one non-additive (NA) dimension. SA facts are often averaged over their NA dimension. SA is always used in conjunction with at least one NA dimension to relate the semi-additive fact to its non-additive dimension(s). Numbering relates multiple SAn facts in the same table.

Front 30

NA
NAn

Back 30

Fact Types
Non-Additive.
Fact that cannot be aggregated using sum; e.g., Temperature NA. Nonadditive facts can be aggregated using functions such as min, max, average. Non-additive dimension of a semi-additive fact. Numbering relates multiple non-additive dimensions in the same table to specific semi additive (SAn) facts.

Front 31

DF
DF= formulae

Back 31

Fact Types
Derived Fact.
Value can be derived from other columns within the same table. May be followed by a simple formula referencing other facts or date/time details by number; e.g., Unit Price DF=Revenue/Quantity.

Front 32

[UoM]
[U1, U2… ]

Back 32

Fact Types
Unit of Measure.
Unit of measure symbol or description; e.g., Order Revenue [$] or Delivery Delay [days]. List denotes that multiple units can be recorded for a quantity. They must be converted into a standard unit (U1) to produce an additive fact. Can also be use to document the list of conversion factors required at reporting time.

Front 33

CV
CVn

Back 33

Dimensional Attribute Types
Current Value.
Attribute records current values only. Changes overwrite previous values. Supports “as is” reporting. Also known as a type 1 slowly changing dimension (SCD). Combined with HV to define hybrid CV/HV attributes with default CV behavior listed first. Implemented as separate CV & HV attributes. Combined with PV to define hybrid CV/PV attributes or numbered to relate separate CVn attributes to matching PVn attributes.

Front 34

HV
HVn

Back 34

Dimensional Attribute Types
Historic Value.
Attribute records historical values. Changes cause new versions of dimension members to be created: preserving their historically correct values. Supports “as was” reporting. Also known as a type 2 SCD. Combined with CV to define hybrid HV/CV attributes with default HV behavior listed first. Implemented as separate HV & CV attributes. Numbering defines conditional HVn attributes groups: combinations of attributes that only act as HV when every member of their n group changes at the same time. Used in combination with CV to treat small changes or corrections as CV; e.g., Street CV, HV1 and Zip Code CV, HV1 will be treated as CV individually but as HV if both change at once.

Front 35

FV

Back 35

Dimensional Attribute Types
Fixed Value.
Attribute values do not change over time; e.g., Date of Birth FV. Corrections overwrite previous incorrect values: behaves like a CV attribute. Also know as a type 0 SCD.

Front 36

PV
PVn

Back 36

Dimensional Attribute Types
Previous Value.
Attribute records previous values. Supports “as previously” or “as at” reporting. Also known as a type 3 SCD. Combined with CV to define hybrid CV/PV attributes or numbered to relate separate PVn attributes to their matching CVn attributes; e.g., Previous Territory PV1 and Territory CV1. PV attributes can also hold initial or “as at date” values; e.g., Initial Territory PV1 or YE2010 Territory PV1.

Front 37

PK

Back 37

Key Types
Primary Key.
A column or group of columns that uniquely identifies each row in a table.

Front 38

FK

Back 38

Key Types
Foreign Key.
A column that references the primary key of another table.

Front 39

SK

Back 39

Key Types
Surrogate Key.
Anonymous integer assigned by the data warehouse as the primary key for a dimension table. Dimensional foreign key in fact tables. Denotes that example data will be replaced by integer keys.

Front 40

BK

Back 40

Key Types
Business Key.
Source system key.

Front 41

NK

Back 41

Key Types
Natural Key.
Key used in the real world.

Front 42

RK

Back 42

Key Types
Recursive Key.
Foreign key that references the primary key of its own table. Often used to represent variable-depth hierarchies. Used to build [HM] hierarchy maps.

Front 43

Cn

Back 43

Data Types
Character.
Number defines the maximum length, overriding any default length.

Front 44

DTn

Back 44

Data Types
Date/Time.
Number is used in duration formulas for derived facts; e.g., Delivery Delay DF=DT2-DT1. Number can denote default order of milestones within an [EE].

Front 45

Dn

Back 45

Data Types
Date.
Number is used in duration formulas for derived facts. Number can denote default order of milestones within an [EE].

Front 46

Nn.n

Back 46

Data Types
Numeric.
Number defines precision, overriding the default precision.

Front 47

Tn

Back 47

Data Types
Text.
Long character data used to hold free format text. Number defines the maximum length, overriding any default length.

Front 48

B

Back 48

Data Types
Blob.
Binary long object used to hold documents, images, sound, objects, etc.

Front 49

{Source}

Back 49

Data Profile Annotation
Data source.
system, table, column or file, field source name. / delimited choices.

Front 50

Unavailable MD

Back 50

Data Profile Annotation
Unavailable or incorrect.
Data source for table or column is unavailable or does not comply with the column type code.