Fact Tables: contain aggregated entity measurements at a specific point in time. Aggregates are run daily. All Daily aggregates will be retained for over a rolling 30 day period. End of Month aggregates will be retained for the life of the data warehouse.
Dimension Tables: contain entity attributes and are effective for a range of time defined by the Effective Start Date and Effective End Date.
Relationships: Tables are joined on Key columns.Dimension Types: Dimensions are type 6 dimensions where changes are tracked for some columns and not others.
- Type 1 Column: Changes overwrite prior data with no tracking.
- Type 2 Column: Changes cause a new row to be added. Effective End Date is added to prior row and new row is added and designated as the current version.