0

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

Feb 05, 2017 at 04:20 AM

117

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.

Best Regards,

Nachappa S

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.

Lars Breddemann

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.

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.

Nachappa S

data-source.png (9.7 kB)
projection-1.png (8.3 kB)
projection-2.png (8.3 kB)
union.png (19.6 kB)
running-total.png (19.4 kB)
max-of-date.png (3.5 kB)
final-output.png (18.1 kB)
Lars Breddemann

Dear @lars.breddemann,

Request for suggestions. Thanks!!

Nachappa S

Lars Breddemann
Feb 27, 2017 at 04:08 AM
1

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.

Show 1 Share

Thank you!