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:

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

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

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

  • 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)

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

  • 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…)

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

  • 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:

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

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

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

  • 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:

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

  • 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.

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

  • 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.

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

  • 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’

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

  • You should end up with the below view:

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

  • 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:

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

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):

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

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)

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

  • 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:

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

  • For WORKDAY use the following settings and formula:

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

  • 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:

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

  • 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)

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

  • 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.

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

 

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:

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

  • Here is what the join details will look like:

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

  • 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

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

  • 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’

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

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

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

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

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

  • 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.

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

 

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 !!!

New NetWeaver Information at SAP.com

Very Helpfull

User Rating: Be the first one !