Calculation views design: Maximum number of materials used for each Material Type at a given date

[darshansomaiah1]

Dear SCN Members,lars.breddemann,

In finding the “Maximum number of materials used for each Material Type at a given date” (On three Material Types).

Projection 1:

Material Type (Filter = Material Type A)

Material Start Date Time

Flag = 1

Projection 2:

Material Type (Filter = Material Type A)

Material Start Date Time

Flag = -1

Then I have made a union of Projection 1 and Projection 2 as Calculation View 1

Then I have taken a Running Total based on Date Time i.e., Time stamp as follows using windows function as Calculation View 2

var_out =

select "Material Type","ACTIVITY_DATE_TIME",

sum("FLAG") over (order by "ACTIVITY_DATE_TIME" rows unbounded preceding) as "RUNNING_TOTAL"

from "Calculation View 1";

Apologies: I will use TF in place of Calculation Scripted Views.

Then I have taken a Max of Running total based on a given date, by extracting a date from the above Calculation View 2 and it is called as Calculation View 3

Semantics of Calculation View 3 are as follows

Material Type Activity Date Time Hidden Running Total Max Activity Date

Additional Requirement: To have an entry for 1 week additional to Activity Date, with Null as Running Total, performed the following as Calculation View 4:

DECLARE var_1 DATE;

DECLARE var_2 DATE;

SELECT ADD_DAYS (TO_DATE(MAX("ACTIVITY_DATE"), 'YYYY-MM-DD'),1) INTO var_1

FROM Calculation View 3;

SELECT ADD_DAYS (TO_DATE(MAX("ACTIVITY_DATE"), 'YYYY-MM-DD'),8) INTO var_2

FROM Calculation View 3;

temp_1 = SELECT "GENERATED_PERIOD_START",

"GENERATED_PERIOD_END",

"ELEMENT_NUMBER"

FROM SERIES_GENERATE_DATE(

'INTERVAL 1 DAY', :var_1, :var_2);

var_out= SELECT "Material Type","RUNNING_TOTAL","ACTIVITY_DATE" "GENERATED_PERIOD_START",'' "GENERATED_PERIOD_END"

FROM Calculation View 3

UNION ALL

SELECT

'Material Type A',null"RUNNING_TOTAL","GENERATED_PERIOD_START","GENERATED_PERIOD_END"

FROM :temp_1;

Similar approach I have taken for Material Type B and Material Type C, hence there are 12 views in total which is made union at the end.

Here, I am in a dilemma, even though the requirement is achieved from the above process but it increased the calculation for each material type i.e., for three material types I needed (3*4)+1 = 13 views which is not a good design since Material Type is dynamic and will be added very frequently which needs changes regularly by adding additional 4 views.

Please suggest if I have missed anything in the above design and it can be improvised in a better fashion since there is no input parameter for this requirement as the consumer of the final view is Business Objects Data Services.

Thanks in advance!

Best Regards,

Nachappa S

[lbreddemann]

Please provide an example of your input data and the expected output.

Make sure that the data is sufficient to actually run queries/models on it and to produce the output.

Ideally provide the CREATE TABLE and INSERT statements for your example.

[darshansomaiah1]

Dear lars.breddemann,

Apologize as I could not reply on time as I did not receive email notification from this thread even after following it (Earlier I used to before migration of SCN). Hence, it is delayed.

Please find the example data.

Source data are as follows (Image 1):

Projection 1: Assign + 1 to all the Material Start Date Time to the above source i.e., Image 1

Projection 2: Assign -1 to all Material End Date Time to the above data source i.e., Image 1

Union of Projection 1 with Projection 2 with respect to Material Type (GROUP BY) as shown below image.

Running total of Flag with respect to Material Type (Group By) Order By Time stamp.

To take the maximum of Materials produced on a given Material type and a date by taking a Max of Flag (Running total) with respect to Material Type and Date.

There was a small new requirement i.e., to have a Maximum of Date + 7 days generated from HANA to pass it to R server for generating forecasting data for 7 days. Hence used a Time series function to generate the same. I have found another approach which is again Scripted View/ TF. I have attached the details to this message.

Final output is as follows:

Info: In the above image, NULL is nothing but '?' in HANA preview, but I have mentioned NULL in excel for explanation.

Second Approach taken as follows:

Calculation View 2: Scripted View for now, I will convert it to TF 🙂

/********* Begin Procedure Script ************/ 
 BEGIN 
 
DECLARE temp_1 DATE;
DECLARE temp_2 DATE; 


var_1 = select "MATERIAL_TYPE","ACTIVITY_DATE_TIME","DATE_MATERIAL",
    sum("FLAG") over (partition by "MATERIAL_TYPE" order by "ACTIVITY_DATE_TIME" rows unbounded preceding) as "RUNNING_TOTAL"
from "CALCULATION_VIEW_1";


var_2 = select "MATERIAL_TYPE","DATE_MATERIAL",MAX("RUNNING_TOTAL") "MAX_MATERIAL"
          from :var_1 group by "MATERIAL_TYPE","DATE_MATERIAL";


 SELECT ADD_DAYS (TO_DATE(MAX("DATE_MATERIAL"), 'YYYY-MM-DD'),1) INTO temp_1 
 FROM "CALCULATION_VIEW_1"
 WHERE "MATERIAL_TYPE" = 'MT A';
 
 SELECT ADD_DAYS (TO_DATE(MAX("DATE_MATERIAL"), 'YYYY-MM-DD'),8) INTO temp_2 
 FROM "CALCULATION_VIEW_1"
 WHERE "MATERIAL_TYPE" = 'MT A';
 
 var_3 = SELECT "GENERATED_PERIOD_START" FROM SERIES_GENERATE_DATE('INTERVAL 1 DAY', :temp_1, :temp_2);


 SELECT ADD_DAYS (TO_DATE(MAX("DATE_MATERIAL"), 'YYYY-MM-DD'),1) INTO temp_1 
 FROM "CALCULATION_VIEW_1"
 WHERE "MATERIAL_TYPE" = 'MT B';
 
 SELECT ADD_DAYS (TO_DATE(MAX("DATE_MATERIAL"), 'YYYY-MM-DD'),8) INTO temp_2 
 FROM "CALCULATION_VIEW_1"
 WHERE "MATERIAL_TYPE" = 'MT B';
 
var_4 = SELECT "GENERATED_PERIOD_START" FROM SERIES_GENERATE_DATE('INTERVAL 1 DAY', :temp_1, :temp_2);


SELECT ADD_DAYS (TO_DATE(MAX("DATE_MATERIAL"), 'YYYY-MM-DD'),1) INTO temp_1 
 FROM "CALCULATION_VIEW_1"
 WHERE "MATERIAL_TYPE" = 'MT C';
 
 SELECT ADD_DAYS (TO_DATE(MAX("DATE_MATERIAL"), 'YYYY-MM-DD'),8) INTO temp_2 
 FROM "CALCULATION_VIEW_1"
 WHERE "MATERIAL_TYPE" = 'MT C';
 
var_5 = SELECT "GENERATED_PERIOD_START" FROM SERIES_GENERATE_DATE('INTERVAL 1 DAY', :temp_1, :temp_2);




var_out = SELECT "MATERIAL_TYPE","DATE_MATERIAL","MAX_MATERIAL" FROM :var_2
          UNION ALL
          SELECT 'MT A' "MATERIAL_TYPE","GENERATED_PERIOD_START" "DATE_MATERIAL",NULL "RTOTAL" FROM :var_3
          UNION ALL
          SELECT 'MT B' "MATERIAL_TYPE","GENERATED_PERIOD_START" "DATE_MATERIAL",NULL "RTOTAL" FROM :var_4
          UNION ALL
          SELECT 'MT C' "MATERIAL_TYPE","GENERATED_PERIOD_START" "DATE_MATERIAL",NULL "RTOTAL" FROM :var_5;


 
END /********* End Procedure Script ************/


<br>

Lars -

Here, I am in a dilemma, even though the requirement is achieved from the above first and second approach, In first approach it increased the calculation for each material type i.e., for three material types I needed (3*4)+1 = 13 views which is not a good design since Material Type is dynamic and will be added very frequently which needs changes regularly by adding additional 4 views.

In second approach, I have used Scripted View/ TF which is usually said SQL approach should be last option. Please suggest if I have missed anything in the above design and it can be improvised in a better fashion since there is no input parameter for this requirement as the consumer of the final view is Business Objects Data Services.

Thanks in advance!

Nachappa S

[darshansomaiah1]

Dear lars.breddemann,

Request for suggestions. Thanks!!

Nachappa S

[lbreddemann · Accepted solution]

Ok, I've been rather busy lately and you didn't bother putting in actual SQL *code* so that your example is not easy to reproduce.

If you want a quick answer, heck, if you want an answer at all, never ever make people type in your example data!

I had a quick go at it and I would do it in SQL like this:

 select "ACTIVITY_DATE_TIME",  "MATERIAL_TYPE", max("USAGE_COUNTER") as max_usage
 from (
 select "ACTIVITY_DATE_TIME",  "MATERIAL_TYPE",
         sum("FLAG") over 
                (partition by "MATERIAL_TYPE" 
                 order by "ACTIVITY_DATE_TIME" asc 
                 rows unbounded preceding) as "USAGE_COUNTER"
    from ( 
            /* checkout material */
        select material_type,  to_date(start_datetime) as activity_date_time, 1 as flag
    from mat_prod
    union all    
               /* checkin material */
    select material_type, to_date(end_datetime) as activity_date_time, -1 as flag
    from mat_prod
        union all
                /* neutral material entry for each day */
     select mats.material_type, t.date_sql as activity_date_time, 0 flag
    from 
        "_SYS_BI"."M_TIME_DIMENSION" t
     cross join
            (select distinct material_type from mat_prod) mats
       where date_sql between '2016-01-01' and  '2016-02-01'
   )
   ) 
group by  activity_date_time , material_type 
order by  activity_date_time asc, material_type asc ;

Using the time series generator is not bad or anything, but the M_TIME_DIMENSION table is doing pretty much the same thing here.

Currently, the cross join to produce the neutral entries for each material for each day only considered what is already in the table (e.g. only the materials that have been used at least once. If you want to make it work for all materials you need to look at the master data table here.

Other than that, this solution is independent of how many materials there are.

I didn't look into the new requirement you mentioned, but that looks straight forward to add.

[darshansomaiah1 · Accepted solution]

Thank you!