Calendar Dimension
The Calendar Dimension is a specialized transformation in AnalyticsCreator used to generate a date table automatically. It provides a structured time reference that supports time-based analysis and reporting in data warehouse and star schema environments.
Function
The Calendar Dimension automatically creates a continuous range of dates based on the user-defined start and end dates. It calculates key date attributes such as year, month, week, and day, along with useful indicators for current and previous periods. This ensures consistency and simplifies time-based data modeling.
Access
The Calendar Dimension can be accessed and created from DWH → Transformations → Calendar Dimension in the main navigation panel.
Properties
| ID | Property | Description |
|---|---|---|
| 1 | Schema | Specifies the schema where the calendar table will be created (e.g., DWH). |
| 2 | Name | Defines the name of the calendar table (e.g., DIM_Calendar). |
| 3 | Date from | Defines the start date for the generated calendar (e.g., 01/01/1980). |
| 4 | Date to | Defines the end date for the generated calendar (e.g., 12/31/2040). |
| 5 | Date-to-ID function | Specifies the function used to generate a unique date key (e.g., Date2ID). |
| 6 | Stars | Lists the star schemas to which this calendar will be linked. |
| 7 | Add to Star | Adds the selected star schema to the calendar relationship. |
| 8 | Remove from Star | Removes the selected star schema from the relationship. |
**The dates format will follow the windows date
Screen Overview
The image below shows the New Calendar Transformation dialog with labeled fields for creating the calendar dimension:
Generated Columns
The Calendar Dimension automatically generates a table containing the following columns:
| Column | Description | Example |
|---|---|---|
| SATZ_ID | Unique identifier for each date (surrogate key). | 20250101 |
| VeryShortDate | Abbreviated date format (MM/DD). | 01/01 |
| ShortDate | Short numeric format (MM/DD/YY). | 01/01/25 |
| LongDate | Full date format (MM/DD/YYYY). | 01/01/2025 |
| Date | The actual calendar date. | 2025-01-01 |
| Year | The year extracted from the date. | 2025 |
| Month | The numeric month (1–12). | 1 |
| Day | The day of the month (1–31). | 1 |
| Week | Week number of the year. | 1 |
| Weekday | Day of the week (1 = Sunday, 7 = Saturday). | 3 |
| ISO_Week | ISO-compliant week number. | 1 |
| CurrentDate | Flag indicating if the date is the current system date (1 = Yes, 0 = No). | 0 |
| CurrentMonth | Flag indicating if the date is within the current month (1 = Yes, 0 = No). | 1 |
| PrevMonth | Flag indicating if the date belongs to the previous month (1 = Yes, 0 = No). | 0 |
| CurrentYear | Flag indicating if the date belongs to the current year (1 = Yes, 0 = No). | 1 |
Behavior
- The calendar table covers the full date range defined by the start and end dates.
- It automatically calculates time-related attributes such as year, month, and week.
- It generates useful flags for identifying current and previous periods.
- The dimension can be linked to one or more star schemas for analysis.
Notes
- Ensure the selected date range includes all relevant historical and future periods required for reporting.
- The Date2ID function creates unique date keys (e.g., 20250101).
- The generated flags (CurrentDate, CurrentMonth, PrevMonth, CurrentYear) simplify time-based filtering in reports.
- Multiple data marts can share the same calendar dimension for consistency.
