Key Columns
Most fact tables in the data warehouse use the same key column structure, which consists of two types of internal identifiers.
The first identifier is referred to as a WID value. The WID on a fact table is a foreign key reference to a dimension table’s ROW_WID
column. For example, a PROD_WID
column in a sales table is referring to the ROW_WID
on W_PRODUCT_D
(the product dimension table). Joining the WIDs on a fact and a dimension will allow you
to look up user-facing descriptors for the dimensions, such as the product number.
The second identifier is known as SCD1_WID
and refers to slowly changing dimensions, which is a common
data warehousing concept. The IDs on the SCD1_WID
columns are carried forward through reclassifications and
other dimensional changes, allowing you to locate a single product throughout history, even if it has numerous records in
the parent dimension table. For example, joining PROD_SCD1_WID
from a sales table with SCD1_WID
on W_PRODUCT_D
will receive all instances of that product’s data throughout history, even if the product
has several different ROW_WID
entries due to reclassifications, which insert new records to the dimension
for the same item.
The other core structure to understand is Date WIDs (key column DT_WID
). These also join with the ROW_WID
of the parent dimension (W_MCAL_DAY_D
usually), but the format of the WID allows you to
extract the date value directly if needed, without table joins. The standard DT_WID
value used is a combination
of 1
+ date in YYYYMMDD + 000
. For example, 120210815000
is the DT_WID
value for “August 15, 2021”.