Calendar datasets are foundational for many analytics and planning models, but they often require enhancements to unlock their full potential. In this article, we’ll walk through how to create a unified calendar view in SAP Datasphere by combining standard “date” data with factory calendar working days from SAP S/4HANA. The result will be a dataset that clearly identifies working and non-working days for specific regions, using SQL scripting.
A factory calendar in SAP S/4HANA defines working and non-working days for specific regions or business units, enabling accurate scheduling, production planning, and deadline calculations. These calendars store rules such as public holidays, weekends, and custom non-working days. Key CDS views like I_FACTORYCALENDAR (master data for calendar IDs and validity periods) and I_FACTORYCALENDARTEXT (language-specific descriptions) define calendar data, while I_PUBLICHOLIDAYCALENDAR and I_PUBLHOLIDAYCALHOLIDAYDATE provide public holiday rules and exact holiday dates.
For fiscal reporting, I_FISCALCALENDARDATE maps dates to fiscal periods.
In this article, we’ll use I_FACTORYCALWORKINGDAYSPERYR, which compresses monthly working days into encoded strings (e.g., “11100…”), to enrich standard date dimensions. To leverage these views, ensure they’re replicated into SAP Datasphere via replication flows using the CDS Views as a source.
1. SAP.TIME.M_TIME_DIMENSION
This default view in SAP Datasphere contains comprehensive date information, including:
DATE_SQL: Calendar dates
YEAR, MONTH, QUARTER: Hierarchical time divisions
Week and day identifiers
Prerequisite: Activate this view in your Datasphere space.
A table showing dates, years, months, and quarters.
2. I_FACTORYCALWORKINGDAYSPERYR (S/4HANA CDS View)
This predelivered CDS view stores factory calendar data, including:
Yearly working days per month, encoded as strings of 1 s (working days) and 0s (non-working days).
A FactoryCalendar column to differentiate regional calendars (e.g., Germany uses code 01).
A table with columns like FactoryCalendar, Year, Month01WorkingDaysString (e.g., "1111100...") etc.
Objective
Combine the two datasets to create a calendar with the following columns:
Date (from M_TIME_DIMENSION)
Year
Day Name (e.g., Monday)
Month Name (e.g., January)
Working Days in Year (total working days per year)
Working Day (1 or 0 indicating if the date is a working day)
Start by querying M_TIME_DIMENSION to retrieve the first four columns. Use HANA’s built-in functions DAYNAME, MONTHNAME and INITCAP to format day and month names:
Key HANA Functions Explained
DAYNAME(date): Extracts the name of the day (e.g., "MONDAY") from a date field. By default, it returns the name in uppercase.
MONTHNAME(date): Retrieves the name of the month (e.g., "JANUARY") from a date, also in uppercase.
INITCAP(text): Converts the first letter of a string to uppercase and the rest to lowercase. This is applied to DAYNAME and MONTHNAME results to format them as "Monday" or "January" for readability.
A table with formatted dates, years, day names, and month names.
The challenge lies in parsing the 1/0 strings from I_FACTORYCALWORKINGDAYSPERYR. For each date, extract the corresponding value from the relevant month column using SUBSTRING:
A table with the data format of the working days for each month.
Example:
For January 7, 2025: SUBSTRING("Month01WorkingDaysString", 7, 1)
For February 2, 2025: SUBSTRING("Month02WorkingDaysString", 2, 1)
Key Logic:
Dynamically identify the month column (e.g., Month01, Month02) based on the date.
Use the day of the month as the substring starting position.
A final query that dynamically combines date data with the month columns, based on the date.
Join the two tables on Year and filter for the German factory calendar (FactoryCalendar = '01'):
A unified calendar showing dates, working days, and totals
(e.g., 2025-01-01 = 0 [non-working], 2025-01-02 = 1 [working]).
By combining M_TIME_DIMENSION with factory calendar data, we’ve created a dataset that:
Enriches date dimensions with working/non-working day flags.
Simplifies reporting for region-specific business models (e.g., production planning in Germany).
Leverages SQL flexibility to parse compressed string patterns into usable data.
Looking ahead, this unified calendar view opens the door to further practical enhancements like tracking sales days in a quarter, in order to compare revenue on actual sales days rather than calendar days, or supplementing fiscal reporting with year-specific period logic.
Do you have questions about SAP Datasphere? Are you trying to build up the necessary know-how in your department or do you need support with a specific issue? Please do not hesitate to contact us. We look forward to exchanging ideas with you!