Displaying Factory Calendar data in HANA

[former_member184768]

There had been always a requirement to create a HANA Information model object to display the Factory Calendar. I just happened to receive some Information on Factory calendar related tables and could create a HANA script for displaying the Factory Calendar. This can be customized and used in a script based Calc view and can be then joined with the Transactional data.

Please note that the script is currently created to suit my own business requirements and can be enhanced to add more information as per your business needs.

Tables required for Factory Calendar HANA Information model:

• SAP Tables:
  • TFACD - Factory calendar definition
  • TFACT - Factory calendar texts
  • TFAIN - Calendar: Intervals for company holidays, special shifts
  • THOC - Public Holiday Calendar
  • THOL - Public Holidays
  • THOLT - Public holiday text

• HANA generated Table:
  • "_SYS_BI".M_TIME_DIMENSION

• User defined Table:
  • TC_DAY_CALENDAR

I am sure there is proper documentation on Factory Calendar related tables on SAP Help portal which can be helpful in understanding the table structures.

The HANA generated Table M_TIME_DIMENSION contains data as generated using “Generate Time Data” option in HANA. Please find a nice document on this forum explaining how to generate the data. (http://scn.sap.com/docs/DOC-25977).

The granularity of the data in the generated table is quite high at "Second" level leading to millions of records in the table and may not be required for the Factory calendar. Hence a user defined table (TC_DAY_CALENDAR) can be created as a subset of the data from M_TIME_DIMENSION.

*** This can also be a view on M_TIME_DIMENSION, but I chose to create a table for some other purpose.

The code for the User defined table and the factory Calendar script is as follows:

CREATE COLUMN TABLE TC_DAY_CALENDAR (

DATE_SQL          DATE,

DATE_SAP          VARCHAR(8),

YEAR              VARCHAR(4),

QUARTER           VARCHAR(2),

MONTH             VARCHAR(2),

WEEK              VARCHAR(2),

WEEK_YEAR         VARCHAR(4),

DAY_OF_WEEK       VARCHAR(2),

DAY               VARCHAR(2),

CALQUARTER        VARCHAR(5),

CALMONTH          VARCHAR(6),

CALWEEK           VARCHAR(6),

YEAR_INT          INTEGER,

QUARTER_INT       TINYINT,

MONTH_INT         TINYINT,

WEEK_INT          TINYINT,

WEEK_YEAR_INT     INTEGER,

DAY_OF_WEEK_INT   TINYINT,

DAY_INT           TINYINT,

MONTH_LAST_DAY    TINYINT

);

insert into TC_DAY_CALENDAR

  ( DATE_SQL, DATE_SAP, YEAR, QUARTER, MONTH, WEEK, WEEK_YEAR,

    DAY_OF_WEEK, DAY, CALQUARTER, CALMONTH, CALWEEK,

    YEAR_INT, QUARTER_INT, MONTH_INT, WEEK_INT, WEEK_YEAR_INT,

    DAY_OF_WEEK_INT, DAY_INT, MONTH_LAST_DAY )

select distinct

    DATE_SQL, DATE_SAP, YEAR, QUARTER, MONTH, WEEK, WEEK_YEAR,

    DAY_OF_WEEK, DAY, CALQUARTER, CALMONTH, CALWEEK,

    YEAR_INT, QUARTER_INT, MONTH_INT, WEEK_INT, WEEK_YEAR_INT,

    DAY_OF_WEEK_INT, DAY_INT, MONTH_LAST_DAY

from "_SYS_BI".M_TIME_DIMENSION

where HOUR = '12'; --- Required as for some of the dates there could be another Week number assigned, especially for Hour '00'.

Script for Factory Calendar:

SELECT

  tfacd.ident FACT_CAL_ID,

  tfact.ltext FACT_CAL_NAME,

  cal.DATE_SQL,

  cal.DATE_SAP,

  cal.YEAR,

  cal.QUARTER,

  cal.MONTH,

  cal.WEEK,

  cal.WEEK_YEAR,

  cal.DAY_OF_WEEK,

  cal.DAY,

  cal.CALQUARTER,

  cal.CALMONTH,

  cal.CALWEEK,

  cal.YEAR_INT,

  cal.QUARTER_INT,

  cal.MONTH_INT,

  cal.WEEK_INT,

  cal.WEEK_YEAR_INT,

  cal.DAY_OF_WEEK_INT,

  cal.DAY_INT,

  cal.MONTH_LAST_DAY,

  DAYNAME(cal.DATE_SQL) DAY_NAME,

  MONTHNAME(cal.DATE_SQL) MONTH_NAME,

  CASE

    WHEN tfain.wert = 0 THEN 0

    WHEN tfain.wert = 1 THEN 1

    WHEN phol.IS_HOLIDAY ='X' AND tfacd.fetag = 0 THEN 0

    WHEN cal.DAY_OF_WEEK_INT = 0 AND tfacd.motag = 0 THEN 0

    WHEN cal.DAY_OF_WEEK_INT = 1 AND tfacd.ditag = 0 THEN 0

    WHEN cal.DAY_OF_WEEK_INT = 2 AND tfacd.miwch = 0 THEN 0

    WHEN cal.DAY_OF_WEEK_INT = 3 AND tfacd.dotag = 0 THEN 0

    WHEN cal.DAY_OF_WEEK_INT = 4 AND tfacd.frtag = 0 THEN 0

    WHEN cal.DAY_OF_WEEK_INT = 5 AND tfacd.satag = 0 THEN 0

    WHEN cal.DAY_OF_WEEK_INT = 6 AND tfacd.sotag = 0 THEN 0

    ELSE 1

  END WORKDAY,

  phol.DATE_DESC_LONG HOLIDAY_DESC,

  --IFNULL(phol.DATE_DESC_LONG, DAYNAME(cal.DATE_SQL)) HOLIDAY_DESC,

  CASE

    WHEN cal.DATE_SQL

      = MAX(CASE WHEN tfain.wert = 0 THEN NULL

                 WHEN tfain.wert = 1 THEN NULL

                 WHEN phol.IS_HOLIDAY ='X' AND tfacd.fetag = 0 THEN NULL

                 WHEN cal.DAY_OF_WEEK_INT = 0 AND tfacd.motag = 0 THEN NULL

                 WHEN cal.DAY_OF_WEEK_INT = 1 AND tfacd.ditag = 0 THEN NULL

                 WHEN cal.DAY_OF_WEEK_INT = 2 AND tfacd.miwch = 0 THEN NULL

                 WHEN cal.DAY_OF_WEEK_INT = 3 AND tfacd.dotag = 0 THEN NULL

                 WHEN cal.DAY_OF_WEEK_INT = 4 AND tfacd.frtag = 0 THEN NULL

                 WHEN cal.DAY_OF_WEEK_INT = 5 AND tfacd.satag = 0 THEN NULL

                 WHEN cal.DAY_OF_WEEK_INT = 6 AND tfacd.sotag = 0 THEN NULL

                 ELSE cal.DATE_SQL

            END) over (partition BY cal.CALMONTH)

    THEN 1

    ELSE 0

  END last_workday_in_month

FROM TC_DAY_CALENDAR cal

INNER JOIN SAPUSER.TFACD tfacd ON cal.DATE_SAP BETWEEN (tfacd.vjahr|| '0101') AND (tfacd.bjahr|| '1231')

LEFT OUTER JOIN SAPUSER.TFACT tfact ON tfact.ident  = tfacd.ident AND tfact.spras = 'E'

LEFT OUTER JOIN SAPUSER.TFAIN tfain ON tfain.ident = tfacd.ident

                        AND cal.DATE_SAP BETWEEN (tfain.von) AND (tfain.bis)

LEFT OUTER JOIN V_PUBLIC_HOLIDAY phol ON phol.DATE = cal.DATE_SAP AND phol.IDENT = tfacd.hocid

--WHERE tfacd.ident = 'D1' -- Specify factory Calendar Id

--  AND cal.DATE_SAP BETWEEN '20130701' and '20130801'  -- Date Range

--ORDER BY cal.DATE_SAP

;

A subset of the above data can be seen in the below screenshot:

Hope this helps.