Factory Calendar consumption in SAP HANA

[dmitry_kuznetsov1]

I needed to do some working-days based calculation in a standalone HANA system and struggled quite a bit. Having achieved what I was aiming for, decided to share my solution. Hope it may help somebody in the future, too.

A pre-requisite for any of this is:

1. l Table TFACS loaded/replicated from a SAP system
2. l Your custom table DATA_TRANSFORM (explained below)
3. l Generated Time-data in HANA (optional)

The essence of problem

Factory calendar in SAP is stored in a binary format which requires some creativity to consume. There is extra column per month in a year; each column contains 0-s and 1-s where each position means a day number within month and 0 means a day off and 1 means a working day.

Would be much more usable in calculations to have it like this:

After producing SQLScript Calculated view using formulas WORKDAYS_BETWEEN and ADD_WORKDAYS I still did not manage to consume this data in a graphical view, so the second attempt is below where I transpose months and days in two steps:

The solution XML is attached, so you may try plug and play, but a bit of logic explanation is here.

(1) Transpose months from columns to rows. I used a nice & simple idea brought in this blog for months: http://scn.sap.com/docs/DOC-51791 brought in by abanip and martin.donadio.

Which in the case with months looks like this:

Where a projection of every month has a constant for the filed MONTH (from 01 to 12).

They are later put together in a union and output looks like this:

Better? :smile: I think so. But we need to cut the field into pieces (calc. fields 001…031) and go on…

Now we could do the same transposing action again, this time with days (anybody wants 31 projections? :smile: I don’t).

(2) Transpose days: I use Matrix transpose idea by abanip and tony.cheung.

Step by step:

1. Produce the matrix (or download from here: http://stuff.bi-consulting.eu/Blog3%20-%20Factory%20Calendar%20in%20HANA%20_worksheet_.xlsx) and load it to HANA as table DATA_TRANSFORM
2. Add a calculated field with a nice tiny “if” statement, based on Matrix to fill the field DAYNUM:

if("1"=1,'01',

if("2"=1,'02',

if("3"=1,'03',

if("4"=1,'04',

if("5"=1,'05',

if("5"=1,'05',

if("6"=1,'06',

if("7"=1,'07',

if("8"=1,'08',

if("9"=1,'09',

if("10"=1,'10',

if("11"=1,'11',

if("12"=1,'12',

if("13"=1,'13',

if("14"=1,'14',

if("15"=1,'15',

if("16"=1,'16',

if("17"=1,'17',

if("18"=1,'18',

if("19"=1,'19',

if("20"=1,'20',

if("21"=1,'21',

if("22"=1,'22',

if("23"=1,'23',

if("24"=1,'24',

if("25"=1,'25',

if("26"=1,'26',

if("27"=1,'27',

if("28"=1,'28',

if("29"=1,'29',

if("30"=1,'30',

if("31"=1,'31',''))))))))))))))))))))))))))))))))

1. And one calculated field to calculate if we are dealing with a workday or not:

"001"*"1"+"002"*"2"+"003"*"3"+"004"*"4"+"005"*"5"+"006"*"6"+"007"*"7"+"008"*"8"+"009"*"9"+"010"*"10"+"011"*"11"+"012"*"12"+"013"*"13"+"014"*"14"+"015"*"15"+"016"*"16"+"017"*"17"+"018"*"18"+"019"*"19"+"020"*"20"+"021"*"21"+"022"*"22"+"023"*"23"+"024"*"24"+"025"*"25"+"026"*"26"+"027"*"27"+"028"*"28"+"029"*"29"+"030"*"30"+"031"*"31"

The end result can be polished by adding some Time Dimension data to get the YearMonth, date in SQL format, etc. Output looks like the screenshot in the beginning of a document.

Have fun!