Factory Calendar transpose in SAP HANA Studio step-by-step

Firstly I would like to reference a few blogs where some of the information to help with the below was sourced and would like to thank the bloggers for their invaluable contributions.

1. https://blogs.sap.com/2016/06/27/factory-calendar-consumption-in-sap-hana/ (by Dmitry Kuznetsov
2. Dmitry references blogs by Abani Pattanayak https://archive.sap.com/documents/docs/DOC-50541 & https://archive.sap.com/documents/docs/DOC-51791

_____________________________________________________________________________

INTRODUCTION & OBJECTIVE

My experience was that I felt that some of the information provided was not detailed or comprehensive enough for me to complete the proposed approach and attain the desired outcome. Or it might have just been over my head ????

I decided to go the long route by firstly creating a transpose of Months (1-12) and secondly a transpose of Days (1-31), Below is a step-by-step, comprehensive, approach.

What we want to achieve is a final view which looks like this:

Additionally you could also add the DATE_SQL or any other fields you require.

1. FIRST TRANSPOSE (MONTHS)

You would need:

1. The TFACS & M_TIME_DIMENSION tables to be virtualised or copied across to a HANA schema
2. Create a Calculation View in a repository e.g. CV_FACTORY_CALENDAR

• Once you opened the created view, create a projection, call it e.g. FACTORY_CALENDAR and drag the TFACS table into it

• To ensure that you are not pulling calendars of countries you don’t need, you can filter the projection to a country(ies) of your choice e.g. Equal RU or Equal ZA or List of Values ZA,RU.
• To do that filter on the IDENT column in the Output window (right click on it and select Apply Filter)

• You can also deselect columns FENUM, WENUM, LOAD from the first projection, as you will probably not use them
• Next step is to create the first transpose being the Month Transpose
• This will require you to create 12 projections (one per month)
• First create the 1st projection and call it JANUARY.
• Drag the FACTORY_CALENDAR projection into it and deselect all the columns except for IDENT, JAHR and MON01 (it would be MON02 for FEBRUARY, MON03 for MARCH etc…)

• Next step is to create two calculated columns. One to reflect the CALENDAR_MONTH and the other to reflect the column reflecting the DAYS in the boolean format
• For CALENDAR_MONTH calculated column for the JANUARY projection use the following settings and formula:

• For DAYS calculated column use the following settings and formula:

• Now copy the JANUARY projection and paste it 11 times
• Rename the other 11 projections into the remaining months’ names i.e. FEBRUARY, MARCH etc…
• Select the correct ‘MONxx’ column. For FEBRUARY it would be MON02… and for DECEMBER it would be MON12
• Update the CALENDAR_MONTH and DAYS calculated columns’ formulae
• CALENDAR_MONTH formula should reflect the number of the respective month. For FEBRUARY the CALENDAR MONTH formula would reflect ’02’, for MARCH it would reflect ’03’ and so on
• Similarly for DAYS, for each of the month projections update the calculated column formula to reflect the next workday column i.e. “MON02” for FEBRUARY, “MON03 for MARCH
• See the FEBRUARY example below:

• Follow the process until all the 12 month projection names, detail, calculated columns have been updated
• Next, add a UNION to the calculated view call it e.g. TRANSPOSE_MONTHS and drag and drop every month from JANUARY to DECEMBER projections into the UNION
• Select the UNION called TRANSPOSE_MONTHS and in the details expand the first month being JANUARY and add the following columns to target one by one (IDENT, JAHR, CALENDAR_MON, DAYS). Do not add the ‘MONxx’ columns to the Target view.

• Then select the Auto Map By Name button as per below and then all the months’ columns should map to the target automatically. If they don’t you need to map all of them manually to each other by dragging and matching each source column to the target.

• The MONxx mappings will automatically appear on the target side and map source to target for each of the ‘MONxx’ columns. Simply right click on each MON01 to MON12 and select ‘Remove Target Column’

• You should end up with the below view:

• It would now be a good step to test that you are on the right track. To do that activate the Calculation View and perform a data preview on the TRANSPOSE_MONTHS UNION. The preview should reflect the below:

P.S. to activate the view you need to find it in the navigation pane under repositories, right click on it and select ‘Activate’ (or use the Ctrl+F3 shortcut):

2. SECOND TRANSPOSE (DAYS)

Until now, the first transpose idea has been well explained by the bloggers mentioned earlier, although the example used was not specific to the TFACS table. Still, it would have been easy to follow and apply. The below is a step-by-step walk-through for the creation of the days transpose and a subsequent mapping of the output to the M_TIME_DIMENSION table. Although the matrix approach explained by Abani Pattanayak in his blog might potentially have slightly better performance, the advantage of this approach, as cumbersome as it may be, is that it ensures that all operations are performed in one Calculation View and there is no need to reference a separate matrix table. (it would also be advantageous if SAP does include/add the PIVOT/UNPIVOT functions to their SQL in later releases)

To begin the second part of the blog:

• Similar to the 12 Month projections (JANUARY, FEBRUARY etc…), you would need to create the 31 Day projections
• Create the first projection and call it DAY01
• Drag the earlier created UNION TRANSPOSE_MONTHS into the DAY01
• The DAY01 projection column selected should be all of the available ones (IDENT, JAHR, CALENDAR_MONTH, DAYS)

• Create two calculated fields in the projection called CALENDAR_DAY and WORKDAY. They would represent each day number and the workday Boolean (0,1) for that day, respectively
• For CALENDAR_DAY calculated column for the DAY01 projection use the following settings and formula:

• For WORKDAY use the following settings and formula:

• Now copy the DAY01 projection and paste it 30 times
• Rename the other 30 projections into the remaining days’ names i.e. DAY02, DAY03 etc…
• Update the CALENDAR_DAY and WORKDAY calculated columns’ formulae
• CALENDAR_DAY formula should reflect the number of the respective day. For DAY02 the CALENDAR_DAY formula would reflect ’02’, for DAY03 it would reflect ’03’…DAY31 it would reflect ’31’
• Similarly for WORKDAY, for each of the day projections, update the calculated column formula to reflect the next part of the Boolean string you wish to substring i.e. MidStr(“DAYS”,2,1) for DAY02, MidStr(“DAYS”,3,1) for DAY03…MidStr(“DAYS”,31,1) for DAY31
• See the DAY31 example below:

• For the next step you would need to UNION all the days
• Create a UNION called TRANS_DAYS and drag all the ‘DAYnn’ projections into it one by one (good luck with this process :))
• Select the UNION called TRANS_DAYS and in the details expand the first day being DAY01 and add all the reflected columns to target one by one (DAYS, CALENDAR_MONTH, JAHR, IDENT, CALENDAR_DAY, WORKDAY)

• Then select the Auto Map By Name button as per below and then all the days’ columns should map to the target automatically. If they don’t you need to map all of them manually to each other by dragging and matching each source column to the target.

3. JOIN OUTPUT TO M_TIME_DIMENSION

• One of the last steps is to join the M_TIME_DIMENSION to TRANS_DAYS to add the DATE_SAP & DATE_SQL columns and to ensure that only the valid dates pull through (e.g. FEBRUARY contains less than 30 days and the join to the time_dimension would ensure that only valid dates are mapped)
• Create a JOIN called TRANS_DAYS_TO_M_TIME_DIM and drag the M_TIME_DIMENSION table and TRANS_DAYS union into it
• INNER JOIN the tables on:
  • TRANS_DAYS.CALEDAR_DAY = M_TIME_DIMENSION.DAY
  • TRANS_DAYS.CALEDAR_MONTH = M_TIME_DIMENSION.MONTH
  • TRANS_DAYS.JAHR = M_TIME_DIMENSION.YEAR
• Select all the columns in TRANS_DAYS (DAYS, CALENDAR_MONTH, JAHR, IDENT, CALENDAR_DAY, WORKDAY) and date columns in M_TIME_DIMENSION (DATE_SQL, DATE_SAP
• The CARDINALITY of the joins should be 1..1 as per below:

• Here is what the join details will look like:

• Final step is to create an AGGREGATION and drag and drop the TRANS_DAYS_TO_M_TIME_DIM join into it
• In the aggregation you can select the final output columns you wish to propagate

• Remember to create at least one measure in the aggregation e.g. a CALENDAR_DAY count. You can do that by right clicking on the CALENDAR_DAY and selecting ‘Add As Aggregated Column’

• Your Semantics would look as per below (if you rename the Name and Label of CALENDAR_DAY measure to CALENDAR_DAY_COUNT :)):

• To seal the deal Save and Activate the Calculation View:

• Once you preview the data in the aggregation you will see the final view similar to below and this means that we have reached our objective.

You can use a SQL Command query similar to the below to test the Calculation View:

SELECT “WORKDAY”

FROM “Schema_Name”.”Calculation_View_Name ending with CV_FACTORY_CALENDAR”

WHERE “DATE_SAP” = 20170501

______________________________________________________________________________

I hope this was valuable and you have enjoyed my first blog.

I will attempt to post other similar step-by-step comprehensive guides.

Let me know if this helps you.

Enjoy & Good Luck !!!