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.
• ALLWORKFACT
• CAMPAIGNFACT
• PROJECTFACT
• PROOFFACT
• TASKFACT
• TIMEENTRYFACT
• WORKREQUESTFACT
Dimension Tables: contain entity attributes and are effective for a range of time defined by the Effective Start Date and Effective End Date.
• CAMPAIGNCUSTOMFIELDS
• CAMPAIGNDIMENSION
• DATEDIMENSION
• PROJECTCUSTOMFIELDS
• PROJECTDIMENSION
• PROOFDIMENSION
• TASKDIMENSION
• USERDIMENSION
• WORKREQUESTDIMENSION
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.