Purpose

A "Date" dimension table typically provides a key that is a simplified string version of a date (e.g., "20180723") along with many different string descriptors of that date, making it easy to filter a Fact table based on different types of date and calendar representations (e.g., day, month, quarter). These tables do not typically take hour into account unless the desired analyses require time of day. Accordingly, the DateDim view does not include a calendar representation shorter than a single day. Part of the Core View Collection.

SQL Object Name

analytics.DateDim

Usage Notes

School Calendar concepts can be important for the overall analytics data model, but these properties are school-specific dimensions that do not belong in the Date view.

Data Source

The range of dates to include is generated from the edfi.CalendarDateCalendarEvent table, removing duplicates due to SchoolId being unnecessary. The example values below are based on July 19, 2018.

Structure

Name

Data Type

Description

Example

DateKeystringDate without time component20180719
DateDateTimeRaw date at midnight2018-07-19 00:00:00.000
DaytinyintDay number within a month19
MonthtinyintNumeric month number7
MonthNamestringEnglish nameJuly
CalendarQuartertinyintBased on 1: Jan-Mar, 2: Apr-Jun, 3: Jul-Sep, 4: Oct-Dec3
CalendarQuarterNamestringOrdinal nameThird

CalendarYear

smallintFull four digit year2018

Contents

  • No labels