Calculation View: Calculation is wrong in some cases

[fabian_krger]

Hey guys,

a few days ago I noticed that some of the results I get from my OData Service are wrong.

The definition of my views is rather simple:

I have an Attribute View based on a table with one left outer and a few text joins.

On top of this attribute view there is a calculation ("CV_RESPONSETIME_BUSINESS_CASE_AFTER") view with two parameters. The calcview consists of a projection with a filter based on these parameters and the default aggregation node:

Now what I noticed is that when calling the calcview via odata with parameters "min_resptime" = 40000 and "max_resptime" = 0 I got a result of 74 for the sum of the "Value" column, although the result must have been much higher. This was confirmed by a SQL query via HANA Studio - with the same paramters I got a Sum("Value") of 7688!

Trying to find the difference, I enabled developer mode and appended "&profile=html" to the OData URL giving me the SQL Statement:

select sum("ResponseTimeBusinessCaseAfterType2"."Value") "Value" ,
  sum("ResponseTimeBusinessCaseAfterType2"."ResponseTime_avg") "ResponseTime_avg" ,
  sum("ResponseTimeBusinessCaseAfterType2"."Acceleration_Potential") "Acceleration_Potential"
from (select TO_BIGINT(?) "min_resptime",TO_BIGINT(?) "max_resptime" from sys.dummy) "Param2",
  "_SYS_BIC"."ibisness.com.rbe.solution.hanaInnovation.data/CV_RESPONSETIME_BUSINESS_CASE_AFTER"(PLACEHOLDER."$$min_resptime$$" => $1,PLACEHOLDER."$$max_resptime$$" => $2) as "ResponseTimeBusinessCaseAfterType2"
where 1=1 and (("ResponseTimeBusinessCaseAfterType2"."UserType")=?)

Executing the same query in HANA Studio with same parameters still gave me the wrong result:

Next I removed the parameters and instead directly put in the values: [QUERY1]

select sum("ResponseTimeBusinessCaseAfterType2"."Value") "Value" ,
sum("ResponseTimeBusinessCaseAfterType2"."ResponseTime_avg") "ResponseTime_avg" ,
sum("ResponseTimeBusinessCaseAfterType2"."Acceleration_Potential") "Acceleration_Potential"
from (select TO_BIGINT(40000) "min_resptime",TO_BIGINT(0) "max_resptime" from sys.dummy) "Param2",
  "_SYS_BIC"."ibisness.com.rbe.solution.hanaInnovation.data/CV_RESPONSETIME_BUSINESS_CASE_AFTER"(PLACEHOLDER."$$min_resptime$$" => 40000,PLACEHOLDER."$$max_resptime$$" => 0) as "ResponseTimeBusinessCaseAfterType2"
where 1=1 and (("ResponseTimeBusinessCaseAfterType2"."UserType")='A');

Still, wrong values:

Next, I removed the strange "Param2"-Part, which seems only to be needed to get the parameters into the prepared statement (?): [QUERY2]

select sum("ResponseTimeBusinessCaseAfterType2"."Value") "Value" ,
  sum("ResponseTimeBusinessCaseAfterType2"."ResponseTime_avg") "ResponseTime_avg" ,
  sum("ResponseTimeBusinessCaseAfterType2"."Acceleration_Potential") "Acceleration_Potential"
from --(select TO_BIGINT(40000) "min_resptime",TO_BIGINT(0) "max_resptime" from sys.dummy) "Param2",
  "_SYS_BIC"."ibisness.com.rbe.solution.hanaInnovation.data/CV_RESPONSETIME_BUSINESS_CASE_AFTER"(PLACEHOLDER."$$min_resptime$$" => 40000,PLACEHOLDER."$$max_resptime$$" => 0) as "ResponseTimeBusinessCaseAfterType2"
where 1=1 and (("ResponseTimeBusinessCaseAfterType2"."UserType")='A');

And viola - now I get the correct values:

After trying some other statements I further found out that this "Param2" part is not the only case when HANA returns wrong values.

For example if I try [QUERY3]

SELECT SUM("Value") AS "Value_SUM"
FROM "_SYS_BIC"."ibisness.com.rbe.solution.hanaInnovation.data/CV_RESPONSETIME_BUSINESS_CASE_AFTER" ('PLACEHOLDER' = ('$$min_resptime$$', '40000'), 'PLACEHOLDER' = ('$$max_resptime$$', '0')) 
WHERE "UserType" = 'A';

It gives wrong values:

But if I use the same statement and just add the two other columns from above: [QUERY4]

SELECT SUM("Value") AS "Value_SUM", SUM("ResponseTime_avg"), SUM("Acceleration_Potential")
FROM "_SYS_BIC"."ibisness.com.rbe.solution.hanaInnovation.data/CV_RESPONSETIME_BUSINESS_CASE_AFTER" ('PLACEHOLDER' = ('$$min_resptime$$', '40000'), 'PLACEHOLDER' = ('$$max_resptime$$', '0')) 
WHERE "UserType" = 'A';

It is correct again:

Next I used the explain plan and visualize plan features and I can see that there are differences.

[QUERY1] (wrong):

Note: The plan goes down to the Attribute view

[QUERY2] (correct):

Note: The plan only goes down to the calculation view

[QUERY3] (wrong):

Note: The plan goes down to the Attribute view

[QUERY4] (correct):

Note: The plan only goes down to the calculation view

It seems to me that HANA is doing some "optimizations" which lead to a wrong result.

I already wrote a message via the HANA One Portal but got the response that they are only there for issues with the Portal, not with HANA itself. I have no other possibility to write a ticket since I don't have any other HANA License than HANA One. I think this is a serious issue because it could possibly happen anywhere. Could somebody of SAP HANA Team please look into this issue?

Thanks,

Fabian

[fabian_krger]

Bump! Why the f*ck am I paying $720 per month when there is no support at all? AWS Marketplace clearly states:

Support available through community forum

Since this is the community forum, you need to provide support here!

[Former Member]

Hi Fabian,

First, I can see why you would be frustrated. It is also unfortunate that the calculations what you wanted to get are not correct according to you. I am also happy that you said it is fixed in 102.

HANA One takes HANA and makes it available in AWS. It is PAY-AS-YOU-GO model. If for some reason, you are not satisfied, you can terminate the instance.

Traditional model is perpetual license and obviously at a price point many can not afford.

We understood that so, we only charge 99 cents per hour of license.

In that license, you get fully configured, Web UI console supported HANA server. It is one of the most easy to use front-end and much powerful to make your life easier. Obviously, even if we try hard, we fall short of that once in a while.

But back to the support. No, other than HANA One console support as a community support, we can not provide any HANA support here for the offering based on the offering. We still have provided best possible support. There are many such examples here.

We think that the cost point of $0.99/hr for HANA One is a very competitive cost to you even if you only have Community support.

Thanks

HANA One Team

[Former Member]

Hi Fabian,

One of our engineers have recently reached out to you but you did not respond. If you need help, please respond to him.

HANA One team

[fabian_krger]

Hello Mandar,

I'm happy to hear that the issue will be solved with Rev. 102.

However:

"It is PAY-AS-YOU-GO model."

I agree. But pay as you go means, I can run it whenever I need it and it will satisfy my needs. Thats like on AWS. If I need an instance, I can run it whenever I want and I always get the instance. There will be no "oh sorry but your instance is not working properly - but since it's on Demand you only pay when you use it - so you can terminate it at any time". Sorry but thats not the definition of pay as you go!

In my case

1) I have a reserved instance - I pay for the instance even if I would terminate it.

2) I have people using my HANA Apps. I can't tell them "sorry but I need to terminate the instance for the next 6 Months because it's not working properly"

I also agree that the HANA One Console makes it easy to use - but still I would be lost without expert knowledge.

For Example:

1) Once I made suse updates via "zypper up" making the instance unaccessible.

2) Twice my instance was unaccessible because of insufficient space on the root volume. You can search for the thread here in the forum. One reason is that old client/studio downloads are not getting deleted properly if you upgrade by skipping one revision in between. Thats because the script is not deleting ALL files in /srv/www/htdocs/downloads/client/ and /srv/www/htdocs/downloads/studio/ BEFORE copying the new ones, instead it is only deleting the files from the previous update. If you skip an update, the old files will be kept since...

__ removing old download files...

rm: cannot remove `/srv/www/htdocs/downloads/client/Rev091ClientWin86_64.zip': No such file or directory

rm: cannot remove `/srv/www/htdocs/downloads/client/Rev091ClientWin86_32.zip': No such file or directory

rm: cannot remove `/srv/www/htdocs/downloads/client/Rev091ClientLinux86_64.zip': No such file or directory

rm: cannot remove `/srv/www/htdocs/downloads/client/Rev091ClientLinux86_32.zip': No such file or directory

rm: cannot remove `/srv/www/htdocs/downloads/studio/Rev091StudioWin86_64.zip': No such file or directory

rm: cannot remove `/srv/www/htdocs/downloads/studio/Rev091StudioWin86_32.zip': No such file or directory

rm: cannot remove `/srv/www/htdocs/downloads/studio/Rev091StudioLinux86_64.zip': No such file or directory

__ done removing old download files.

... other old revisions are not getting deleted and Rev091 which is previous to the installed upgrade isn't even existing.

The other reason is that the root volume runs out of inodes because of the high number of files in /var/spool/postfix/maildrop.

--> due to these facts we may have a "most easy to use front-end" but still there are some issues where you need aws and linux skills (which are being ignored too!)

Back to support: I think if you need to wait 5 months (!!!) for a response - which is not even a solution YET (will probably be 6 months until solution is available), I would not call this support at all. Support would be to make a response within a week that you are aware of the issue and request further information if needed. 5 Months without an answer is 5 Months NO SUPPORT. I don't care if there are other examples where you provided support as long as you keep ignoring my issues...

Regarding to the response to the engineer - he contacted me 30 minutes before you said "but you did not respond". That sounds like it's my fault, but I probably live in a different timezone (Europe) which makes it impossible to me to respond when I'm not at work... and since I will be on holiday the next two weeks I will reach out to him when I'm back.

Still it is nice to see that you care about the problems now 🙂

[Former Member]

Hi Fabian,

Once you are back, we can go over the issues you have.

"I also agree that the HANA One Console makes it easy to use - but still I would be lost without expert knowledge."

We do our best. There are always situations where deep and expert knowledge will be needed. We want to make that situation as infrequent as possible.

But I will point out few things:

We don't have any HANA specific suggestion for you on the calculations. I only repeated what you suggested that102 will solve the issue as we don't know that for sure. We still don't have any specific answer to your calculation issue. Please copy that question to HANA specific forum.

This forum is for HANA One Admin Console ONLY.

Very specific HANA questions are for different forum. That is why we did not respond.

Your Console questions are always answered as soon as we get to it.

As to disk volume being full: I have to say that, customers should use AWS specific tools to address those issue. We just did not want to duplicate the efforts. We still show the disk as 100% free/full on the status page. We cant replace GOOD Linux sysadmin.

YOU are RIGHT that a good sysadmin for Linux is a need.

You have pointed out an issue with NOT cleaning the client folder-

"Thats because the script is not deleting ALL files in /srv/www/htdocs/downloads/client/ and /srv/www/htdocs/downloads/studio/ BEFORE copying the new ones, instead it is only deleting the files from the previous update"

We will address this as soon as possible. THANKS for pointing this out. We can make a better product by your constructive feedback.

Have a nice vacation! After you come back, let us get in touch.

HANA One Team

[fabian_krger]

Update: in Rev. 100 calculation is wrong in all queries above!