Factory Calendar consumption in SAP HANA
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:
- l Table TFACS loaded/replicated from a SAP system
- l Your custom table DATA_TRANSFORM (explained below)
- 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 Abani Pattanayak 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? 🙂 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? 🙂 I don’t).
(2) Transpose days: I use Matrix transpose idea by Abani Pattanayak and Tony Cheung.
Step by step:
- 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
- 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’,”))))))))))))))))))))))))))))))))
- 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!
Nice one Dmitry. Thanks for sharing. I am still thinking on any other options we have apart from this multiple step complex flow.
Hi! I solved this task wiht midstr formula .
For example for string 0000110100111110011111001111100
midstr('0000110100111110011111001111100, Day in Month number, 1)
"Day In Month" - from M_TIME_DIMENSION table
Hi Dmitry Kuznetsov,
I have applied the logic which you have explained as above.
I created Transpose of months in a Hana Calculation view and transpose of days in a table.
How these 2 are inter linked with each other. I stuck here. i need further help to find out no. of days in a factory calendar in SAP HANA.
Kindly help me the same as soon as possible.
Awaiting your reply.
Thanks in advance.
Thanks and Regards,
Naresh.
Hi , naresh jupally!
I hope I can help u. You have to do
1 . Projection of Factory table TFACS with key YEAR
1.a Calculated column FactoryString = TFACS.MON01 + TFACS.MON02+ ….TFACS.MON12
2. Agregation of M_TIME_DIMENSION table
You have to create
2.1 Columns YEAR and DATE_SAP
2.2 Column DayNumInYear something ike daysbetween(date(“YEAR”+’0101′),date(“DATE_SAP”)) + 1
3. Join P1 with A2 (A2.YEAR = P1.YEAR)
4. Create calculate column FactoryFlag = midstr(FactoryString, DayNumInYear, 1)
ps you can use M_TIME_DIMENSION-DAY instead 2.2
Hi Former Member,
I got No of Days but i facing one issue with these Results. Count of days are correct but the table TFACS does not have neither MANDT, RCLNT nor MADANT (System Client) Field which i require in mapping with other Hana Calculation views.
I have created final Hana Calculation view and with union node i need to include No of days results. As there is no System Client i am unable to get correct results in my view.
Now My requirement is to include System Client columns and that login client data should be populated dynamically.
How can we achieve this?
In BW we can achieve this using Formula variable with system field SY-MANDT. How about HANA?
Thanks and Regards,
Naresh Kumar Jupally.
Naresh, just don;t join on SAP Client, it will work correctly.
Hi Dmitry Kuznetsov,
Its correct but my requirement does not full fill with the output achieved.
No of days is a small part of requirement.
Initially we are creating Hana Calculation Views, then on top of View again Composite Provider then BEx Query.
For my requirement i need to create a Union node in my calculation view and map respective attribute and measure in the union node.
Data is arriving correctly but MANDT is missing for my No of Days. So in BEx i am unable get No of days count for a specific period. rest are correct. If i can able to manage MANDT in the output of my final Calculation view then my requirement can be closed.
Kindly assist me with a solution thanks.
Thanks and Regards,
Naresh Kumar Jupally.
Well, since Factory Calendar is not SAP-Client-dependant, I see two options:
1. If you are certain, that it is and will be a single-client system (including copy-back of Prod to QAS, etc.), you can use a constant
2. if you want your solution bullet-proof, add an additional join to table T000 without joining on any field, you will have the same Factory Calender duplicated for every MANDT.
Then you can use it further up your flows.
Dmitry
How I can use WORKDAYS_BETWEEN in graphical HANA view ?
If not then if I got the data ready in 1 view like above. I need to calculate working/business days between two dates Start_date and End_Date. How I can join or relate to my main data set ?
Help is appreciated here....
Hey Guys, i was trying to find the attachment. But is it gone?