Combining Date and Factory Calendar Data in SAP Datasphere

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.

Understanding Factory Calendars in SAP S/4HANA

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. 

Data Sources and Setup

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).

• Prerequisite: Replicate this CDS view into Datasphere via a replication flow.

A table with columns like FactoryCalendar, Year, Month01WorkingDaysString (e.g., "1111100...") etc.

Building the Unified Calendar View

Objective

Combine the two datasets to create a calendar with the following columns:

1. Date (from M_TIME_DIMENSION)

2. Year

3. Day Name (e.g., Monday)

4. Month Name (e.g., January)

5. Working Days in Year (total working days per year)

6. Working Day (1 or 0 indicating if the date is a working day)

Watch the recording of our webinar! 

Step 1: Extract Basic Date Information

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.

Step 2: Extract Working Day Flags

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.

Step 3: Combine Data with a Join

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]).

Date and Factory Calendar Data - Our Conclusion

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!