SAP Datasphere: How to enable the "WORKDAYS" functions

[MKreitlein]

Dear readers and Datasphere learners,

this is my very first blog in the SAP Community, even if I've been contributing here since many years.

I myself have started learning SAP Datasphere in March 2023... but only recently I came across this topic, which will be explained in this blog.

I think, all of you know that you can use in-built functions in graphical view to create a calculated column, which can be used directly without any pre-requisites. Like string functions, numeric functions or date functions who probably the most of you have already been using.

What I found out - and is not described so far in any SAP help page or KBA is that two of them need some prerequisites:

• ADD_WORKDYS
• WORKDAYS_BETWEEN

Both of them need a least the factory calendar of an ERP or S4 system, stored in the SAP Datasphere space, in which they are intended to be executed.

The easiest way to fulfill this requirement is to use the Data Provisioning Agent (if you already have enabled it) to deploy the remote table TFACS, which stores all factory calendars,  from your source system:

Remote Table imported into SAP Datasphere (with identical table structure):

In most cases it should be sufficient to load a snapshot once, since in most systems I know, all dates until year 2050 are maintained. If not, then you would have to load it again, after additional years have been maintained.

Finally we can check the syntax of the function, here the example of WORKDAYS_BETWEEN:

WORKDAYS_BETWEEN Function (Datetime)
Computes the number of workdays between a specified start date and a specified end date.

Syntax
WORKDAYS_BETWEEN( <factory_calendar_id>, <start_date>, <end_date> [, <source_schema>])

Almost all parts of the syntax are obvious... except <source_schema>. The help text describes:

<source_schema>
Specifies the schema where the factory calendar table TFACS is located. This parameter can be omitted if the schema of the TFACS is the same as the current schema.

So, this means that the function works without any source schema if the table TFACS is located in the same space like the view:

You can see:

• if both dates exist, the difference is calculated, like here 0 or 22
• if not both dates exist, the result is NULL

To be honest, some questions are remaining here, for which I myself have not yet an answer... so please feel free to comment, and I can update the blog afterwards.

1) I tried to share my TFACS table (and also a graphical view on top of it, like this KBA proposes: 3396583) into a second space, to use it there, too. But when I trie to add, e.g. MYSPACE.TFACS, or my view: MYSPACE.V_TFACS as the last parameter, both tries result in an error message:

column store error: column store error: exception 334036: Unable to read information from table or view FHC_CONFIG - invalid schema name: V_TFACS: line 1 col 22 (at pos 21) at SQLFrontend/SQLPreprocessor/Rules/Impl/TableValidation.cpp:279; $$detail$=Statement: 'SELECT "CLIENT" FROM "V_TFACS"."FHC_CONFIG" WHERE KEY_FIELD = 'MIGRATION_COMPLETED' AND VALUE = 'X'', user: TEST_SME

--> What is the correct syntax for sharing the Table or the View into another space?

2) The above mentioned KBA links a SAP Help page which describes regarding the last parameter: Specifies the suffix appended to the standard Factory Calendar table name or the standard Factory And Holiday Calendar table names if using an alternative set of tables. For example, with suffix '_XYZ' the function accesses the alternative tables TFACS_XYZ or FHC_C_FCAL_XYZ instead of standard tables TFACS or FHC_C_FCAL. If omitted or empty, the standard tables are used.

It seems, there is a second table "FHC_C_FCAL" with which the functions could work as well. I found out this table exists from SAP S/4HANA release 2022 onwards ... but in my system the table is blank.

--> Does anybody know how the table "FHC_C_FCAL" can be filled? I looked for a SAP help page or another KBA, but I did not find anything, how to fill it.

3) The same help page describes 11 "FHC_C_*" tables in S4 HANA. 

--> Would all 11 tables be required in SAP Datasphere or is FHC_C_FCAL sufficient?

I hope you found the blog helpful anyways, and maybe this supports the one or the other development regarding the factory calender.

Best regards,

Martin