cancel
Showing results for 
Search instead for 
Did you mean: 

Wrong aggregation of difference between 2 dates : Dates not key figure?

Former Member
0 Kudos

Dear All,

    I need to measure the delay between ordered date(EINDT) and actual fulfilment date(SAP_DATE). As date is not a measure so the difference between these wont be aggregated correctly( say at PO level when schedule lines have same dates ( delivery and order) only 1 delay is calculated, instead of at the schedule line level).

Please refer to the screenshot attached.

( I have used calculated measure which calculates only the delays using the day_between function)

Thanks in advance!

Vittali


Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Vittali,

I think using the days_between() function in a calculated measure is the right way to go about this requirement. Have you tried using the "Calculate Before Aggregation" option on the calculated measure?

Thanks,

Anooj

Former Member
0 Kudos

Hello Anooj,

    Thanks for your response!

I tried "Calculate Before Aggregation" with following formula

if(daysbetween(date("DATE_SAP"),date("EINDT")) < 0, daysbetween(date("DATE_SAP"),date("EINDT")), 0)

but didnot help me.. aggregation happen only for 1 unique combination of EINDT & DATE_SAP, rest are ignored.

Best Regards,

Vittali

henrique_pinto
Active Contributor
0 Kudos

Hi Vittali,

You're right in assuming that Hana won't aggregate measures calculated from attributes, if they come from attribute views. I suppose that's your case?

What you could do is to use the tables that contain those dates in the AN's data foundation (i.e. as fact tables). Performance won't be that good though. Alternatively, create the AN without this field and calculate the difference in a calc view and use a second graphical calc to join it with the AN.

Former Member
0 Kudos

Hi Henrique,

    Thanks for your reply!

Yes, you are right.

Infact Iam already using Factable EKBE(BUDAT for Delivered dates) and EKET(EINDT for PO delivery dates). Joined them in AN and using calculated measure.

I couldnot get your view, could you be please more specific for my scenario?

"create the AN without this field and calculate the difference in a calc view and use a second graphical calc to join it with the AN"

Best Regards, Vittali


henrique_pinto
Active Contributor
0 Kudos

Ok, so I did some thorough tests in order to come up with a proper answer for this, I'd like to share the results.

I created a simple model with 4 tables, sales_order, sales_order_item, delivery, delivery_item, with very simple data models:

sales_order:

company_id

order_id

customer_id

order_date

sales_order_item:

company_id

order_id

order_item_id

material_id

quantity

netvalue

delivery:

company_id

delivery_id

customer_id

delivery_date

delivery_item:

company_id

delivery_id

delivery_item_id

order_id

order_item_i

quantity

The basic idea was to join these tables into a model that I could use to finally calculate a measure called "delivery_days" as the difference in days between delivery_date & order_date, at the granularity of sales_order_item table. To simplify the work, I considered that the granularity of delivery was similar to sales_order and the granularity of delivery_item was similar to sales_order_item.

Then, I proceeded to create this model with several different approaches, testing whether I could get a SUM aggregation on the "delivery_days" measure. To test that, basically what I did was a SELECT with GROUP BY at order_id/delivery_id level (i.e. ignoring order_item_id, delivery_item_id & material_id), which would then aggregate the measures at order level.

Here are my findings:

a) two separate ANs + calc:

  • I created two different Analytical Views, one for sales order items and another for delivery items;
  • Then I joined them with a graphical calc view;
  • In the output step of the Calc View, I created a calculated measure "delivery_days" defined as: daysbetween("ORDER_DATE", "DELIVERY_DATE");
  • Result: calculated measure "delivery_days" DID NOT get aggregated;

b) one single AN, dates as attributes:

  • I created a single Analytical Views from the 4 tables, joining them at the Data Foundation layer as fact tables;
  • I added both dates as attributes;
  • In the AN, I created a calculated measure "delivery_days" defined as: daysbetween("ORDER_DATE", "DELIVERY_DATE");
  • Result: calculated measure "delivery_days" DID NOT get aggregated;

c) one single AN, dates as measures:

  • I created a single Analytical Views from the 4 tables, joining them at the Data Foundation layer as fact tables;
  • I added both dates as measures (in the hope that the OLAP engine would then aggregate them);
  • In the AN, I created a calculated measure "delivery_days" defined as: daysbetween("ORDER_DATE", "DELIVERY_DATE");
  • Result: I COULD NOT activate the AN, since OLAP Engine won't allow measures coming from different fact tables (you can only have more than one fact table of the other ones just add attributes - the measures can only come from a single fact table);

d) one calc:

  • I created a single graphical Calc Views from the 4 tables, joining them with Join steps, 2 at a time;
  • In the third Join step, I created a calculated column "delivery_days" defined as: daysbetween("ORDER_DATE", "DELIVERY_DATE");
  • In the output step, I added both dates as attributes and created a calculated measure "delivery_days2" defined as: daysbetween("ORDER_DATE", "DELIVERY_DATE");
  • Result: calculated measure "delivery_days2" DID NOT get aggregated, but calculated column "delivery_days" GOT AGGREGATED.

This lead me to the following conclusions:

  • In a model, if you define some column as an attribute, be it in an AN or CA, any measures calculated out of them won't get aggregated;
  • I suppose that, once you define a column as attribute, the OLAP engine interprets it as "not to be aggregated";
  • On the other hand, if you calculate that column before defining the source columns as attributes, it will get properly aggregated.

I didn't get to create a SQLSCript Calc View since it already worked with a graphical one, but I'd expect a similar result as the graphical:

  • creating an output parameter of the SQLScript step and calculating its value within the SQLScript code, it should be properly aggregated;
  • creating a calculated measure from attributes in the Output step, it would probably not be aggregated.

So, for your request, my recommendation would be to go for a Calc View and create a calculated column in the Join step.

Let me know if that works for you.

henrique_pinto
Active Contributor
0 Kudos

One more comment: notice that I didn't test anything performance related.

I suppose that the performance of this Calc would be much worse than the performance of an AN, which uses the OLAP engine more efficiently.

It might be the case that it might be worth it to create an AN for the whole model but the delivery_days measure, then create a calc to calculate it and join it with the AN. I'm not sure it'd increase the performance much more, but would be worth a shot, if the performance of the calc alone would not be acceptable.

henrique_pinto
Active Contributor
0 Kudos

Ok so a major update on the findings above.

I don't know why, but I simply forgot to consider the possibility of creating a calculated column in the Join Step of the Calc view in the 1st test case (a) above.

So I did it, I redid scenario (a) above creating a Calculated Column "DELIVERY_DAYS" in the Join Step and a Calculated Measure "DELIVERY_DAYS2" in the Output Step. Results were as follows:

a) two separate ANs + calc:

  • I created two different Analytical Views, one for sales order items and another for delivery items;
  • Then I joined them with a graphical calc view;
  • In the Join Step of the Calc View, I created a Calculated Join "deliver_days" defined as: daysbetween("ORDER_DATE", "DELIVERY_DATE");
  • In the Output Step of the Calc View, I created a Calculated Measure "delivery_days2" defined as: daysbetween("ORDER_DATE", "DELIVERY_DATE");
  • Result: calculated measure "delivery_days2" DID NOT get aggregated, but calculated column "delivery_days" GOT AGGREGATED.

Given that this scenario properly uses the Analytical Views for quicker OLAP access to the data, I'd say it's the best case scenario and I'd recommend you go for it in your model.

However, digging a little bit more, I also found this SAP Note:

https://service.sap.com/sap/support/notes/1641272

Basically, it states that creating calculated measures out of attributes is not officially supported (since the calculation occurs before aggregation, and hence they don't get properly aggregated, as you've seen in practice). It doesn't say anything about Calculated Columns though... 😉

henrique_pinto
Active Contributor
0 Kudos

And the final test I wanted to make, I created a SQLScript calc view just for the sake of testing this last scenario.

Basically, I projected the analytic views, joined them and created a calculated column (with CE_CALC()) function for the DELIVERY_DAYS measure.

And it GOT AGGREGATED as expected (yay!).

Notice this measure was already included in my Ouput Parameters list of the Script_View step.

Also, SQLScript Calc Views doesn't allow the creation of Calculated Measures in the Output Step (not really necessary though, since it probably would not get properly aggregated).

The code was as below, for my simple model (you need to adapt it to your own):

/********* Begin Procedure Script ************/

BEGIN

           

            lt_order = CE_OLAP_VIEW("_SYS_BIC"."test/AN_SALES_ORDER_ITEM");

           

            lt_delivery = CE_OLAP_VIEW("_SYS_BIC"."test/AN_DELIVERY_ITEM");

           

            lt_ord = CE_PROJECTION(:lt_order, ["COMPANYID", "ORDERID", "ORDITEMID", "MATID", "CUSTOMER", "CITY", "STATE", "REGION_NAME" AS "REGION", "ORDERDATE" AS "SOLD_DATE", "QUANTITY" AS "SOLD_QUANTITY", "NETVALUE" AS "SOLD_VALUE"]);

           

            lt_del = CE_PROJECTION(:lt_delivery, ["COMPANYID", "DELID", "DELITEMID", "ORDERID", "ORDITEMID", "DELDATE" AS "DELIVERED_DATE", "QUANTITY" AS "DELIVERED_QUANTITY"]);

           

            lt_join = CE_JOIN(:lt_ord, :lt_del, ["COMPANYID", "ORDERID", "ORDITEMID"]);

           

            var_out = CE_PROJECTION(:lt_join, ["COMPANYID", "ORDERID", "ORDITEMID", "DELID", "DELITEMID", "MATID", "CUSTOMER", "CITY", "STATE", "REGION", "SOLD_DATE", "DELIVERED_DATE", "SOLD_QUANTITY", "SOLD_VALUE", "DELIVERED_QUANTITY", CE_CALC('daysbetween("SOLD_DATE", "DELIVERED_DATE")', INTEGER) AS "DELIVERY_DAYS"]);

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

In the end, you have the option to either go for a Graphical Calc View with Calculated Column in the Join Step or a SQLScript Calc View.

Performance wise, the graphical one should be at least as good as the SQLScript one, but if you have a huge amount of data on these tables, I'd make a couple of performance tests between these two models.

Former Member
0 Kudos

Hello Henrique,

    Thanks for your effort..

I will check them and soon let you know my results..

Best Regards

Vittali

Answers (0)