Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
50 Cards in this Set
- Front
- Back
[DE]
|
Event and Fact Table Types
Discrete Event. Point in time or short duration (completed) transaction. |
|
[EE]
|
Event and Fact Table Types
Evolving Event. (multi-verb) process that takes time to complete. |
|
[RE]
|
Event and Fact Table Types
Recurring Event. Measurements taken at predictable regular intervals. |
|
[TF]
|
Event and Fact Table Types
Transaction Fact table. Physical equivalent of DE. Typically maintained by insert only. |
|
[AS]
|
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. |
|
[PS]
|
Event and Fact Table Types
Periodic Snapshot. Physical equivalent of RE. Typically contains semi-additive facts. |
|
[AG]
|
Event and Fact Table Types
Aggregate. Fact table that pre-summarizes an existing detailed fact table. |
|
[DF]
|
Event and Fact Table Types
Derived Fact table. Fact table constructed by merging, slicing, or pivoting existing fact tables. |
|
[CV]
|
Dimension Types
Current Value. Contains current value only dimensional attributes. Type 1 SCD. |
|
[HV]
|
Dimension Types
Historic Value. Contains at least one historical value dimensional attribute. Type 2 SCD. |
|
[RP]
|
Dimension Types
Role-Playing. Used to play multiple roles. |
|
[RU]
|
Dimension Types
Roll-Up. Derived from a more granular dimension. |
|
[SD]
|
Dimension Types
Swappable Dimension. Part of a set of dimensions with a common surrogate key that can be used in place of each other. |
|
[ML]
|
Dimension Types
Multi-Level. Dimension containing additional members representing higher levels in the dimension’s hierarchy. |
|
[HM]
|
Dimension Types
Hierarchy Map. Table used to resolve a recursive relationship. Represents a variable depth hierarchy. |
|
[MV]
|
Dimension Types
Multi-Valued. Bridge table used to resolve a many-to-many relationship between a fact table and a multi-valued dimension. |
|
[PD]
|
Dimension Types
Pivoted Dimension. Contains column flags built from the row values of another dimension. |
|
MD
|
General Column Types
Mandatory. Value is present under normal conditions. Can be nullable to handle errors. |
|
NN
|
General Column Types
Not Null. Column does not allow nulls. All SK and FK columns are NN by default. |
|
ND
NDn |
General Column Types
No Duplicates. Numbered to define combinations of column values that must be unique. PK columns are ND by default. |
|
Xn
|
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. |
|
DC
DCn,n |
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. |
|
[W type]
[dimension] |
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. |
|
DD
|
Event and Fact Table Column Types
Degenerate Dimension. Dimensional attribute stored in a fact table. Typically used for transaction IDs (how details). |
|
GD
GDn |
Event and Fact Table Column Types
Granularity Dimension. Dimension combination that defines the granularity of a fact table. Numbered when alternative combinations exist. |
|
MV
|
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. |
|
ML
|
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. |
|
FA
|
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. |
|
SA
SAn |
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. |
|
NA
NAn |
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. |
|
DF
DF= formulae |
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. |
|
[UoM]
[U1, U2… ] |
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. |
|
CV
CVn |
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. |
|
HV
HVn |
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. |
|
FV
|
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. |
|
PV
PVn |
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. |
|
PK
|
Key Types
Primary Key. A column or group of columns that uniquely identifies each row in a table. |
|
FK
|
Key Types
Foreign Key. A column that references the primary key of another table. |
|
SK
|
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. |
|
BK
|
Key Types
Business Key. Source system key. |
|
NK
|
Key Types
Natural Key. Key used in the real world. |
|
RK
|
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. |
|
Cn
|
Data Types
Character. Number defines the maximum length, overriding any default length. |
|
DTn
|
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]. |
|
Dn
|
Data Types
Date. Number is used in duration formulas for derived facts. Number can denote default order of milestones within an [EE]. |
|
Nn.n
|
Data Types
Numeric. Number defines precision, overriding the default precision. |
|
Tn
|
Data Types
Text. Long character data used to hold free format text. Number defines the maximum length, overriding any default length. |
|
B
|
Data Types
Blob. Binary long object used to hold documents, images, sound, objects, etc. |
|
{Source}
|
Data Profile Annotation
Data source. system, table, column or file, field source name. / delimited choices. |
|
Unavailable MD
|
Data Profile Annotation
Unavailable or incorrect. Data source for table or column is unavailable or does not comply with the column type code. |