Hopefully there haven't been any issues, but if there are and our docs don't cover it please reach out.
The calendar dimension table is a crucial component in a data warehouse, as it provides a standardized and structured way to represent dates and time-related information. This table can be used to support various time-based analyses and reporting requirements.
The Streamlit app will guide you through a series of questions to help generate your personalized calendar dimension for your business or organization.
You can re-regenerate or create a duplicate as needed for different use cases.
The calendar will automatically self-update daily based upon your selected timezone to ensure all fields are correctly attributed to be enable consistent and timely measurements for your team and organization.
1. Pick your calendar type
2. Whether or not to include holidays
3. Calendar start / end dates
4. Select quarter 1 start month
5. Pick the first day of your week
6. (optional region for holiday to select)
7. Select account timezone
The calendar dimension table contains the following fields:
1. DATE_KEY: A unique identifier for each date, typically an integer value representing the date in YYYYMMDD format.
2. SQL_DATE: The date in the standard SQL date format (YYYY-MM-DD).
3. DATE_NAME: The date in the MM/DD/YYYY format.
4. SORT_DATE: The date in the YYYY/MM/DD format, designed for sorting and ordering purposes.
5. CAL_DAY_NUM: The sequential day number, starting from 1 on January 1st of the earliest year in the data.
6. CAL_MONTH_NUM: The sequential month number, starting from 1 for January.
7. CAL_QUARTER_NUM: The sequential quarter number, starting from 1 for the first quarter.
8. CAL_YEAR_NUM: The year number.
9. DAY_OF_WEEK_NUMBER: The day of the week number, where 1 represents Sunday and 7 represents Saturday.
10. DAY_OF_WEEK_NAME: The name of the day of the week.
11. DAY_OF_MONTH_NUMBER: The day of the month number, ranging from 1 to 31.
12. DAY_OF_MONTH_NAME: The name of the day of the month.
13. DAY_OF_YEAR_NUMBER: The sequential day of the year number, starting from 1 on January 1st.
14. DAY_OF_YEAR_NAME: The name of the day of the year.
15. DAY_NAME: The full name of the day of the week.
16. DAY_SHORT_NAME: The abbreviated name of the day of the week.
17. WEEKEND: A boolean flag indicating whether the day is a weekend (true) or a weekday (false).
18. HOLIDAY: A boolean flag indicating whether the day is a holiday (true) or not (false).
19. HOLIDAY_NAME: The name of the holiday, if the day is a holiday.
20. WEEK_OF_MONTH_NUMBER: The week of the month number, ranging from 1 to 5.
21. WEEK_OF_MONTH_NAME: The name of the week of the month.
22. WEEK_OF_YEAR_NUMBER: The week of the year number, ranging from 1 to 52 or 53.
23. WEEK_OF_YEAR_NAME: The name of the week of the year.
24. WEEK_START_DATE: The start date of the week.
25. WEEK_END_DATE: The end date of the week.
26. MONTH_OF_YEAR_NUMBER: The month of the year number, ranging from 1 to 12.
27. MONTH_OF_YEAR_NAME: The name of the month of the year.
28. MONTH_NAME: The full name of the month.
29. MONTH_SHORT_NAME: The abbreviated name of the month.
30. MONTH_START_DATE: The start date of the month.
31. MONTH_END_DATE: The end date of the month.
32. QUARTER_OF_YEAR_NUMBER: The quarter of the year number, ranging from 1 to 4.
33. QUARTER_OF_YEAR_NAME: The name of the quarter of the year.
34. QUARTER_START_DATE: The start date of the quarter.
35. QUARTER_END_DATE: The end date of the quarter.
36. YEAR_NUMBER: The year number.
37. YEAR_NAME: The name of the year.
38. YEAR_START_DATE: The start date of the year.
39. YEAR_END_DATE: The end date of the year.
The calendar dimension table can be used in various ways to support time-based analyses and reporting:
1. Filtering and Grouping: Use the date-related fields (e.g., DATE_KEY, SQL_DATE, CAL_YEAR_NUM, CAL_QUARTER_NUM, CAL_MONTH_NUM) to filter and group data based on time periods.
2. Trend Analysis: Analyze trends over time using the date-related fields and the associated metrics (e.g., sales, revenue, costs).
3. Seasonality Analysis: Identify seasonal patterns by examining the data at different granularities (e.g., day, week, month, quarter, year).
4. Holiday and Weekend Analysis: Analyze the impact of holidays and weekends on various business metrics using the WEEKEND and HOLIDAY flags.
5. Fiscal Calendar Support: The table includes fiscal calendar-related fields (e.g., FISCAL_YEAR_NUMBER, FISCAL_QUARTER_NUMBER, FISCAL_MONTH_NUMBER) to support reporting and analysis based on a fiscal calendar.
By using the calendar dimension table, you can easily perform sophisticated time-based analyses and create consistent, reliable reports across your data warehouse.
Please reach out to us with any questions or suggestions at support@dourodata.com or use the form above!