Model on SAP HANA Views: Invalid SQL generated?

[Former Member]

Hi,

when creating a model on an HANA View (automated analytics), it turmed out when generating the automated model, there is a SQL error thrown.

Investigating further, it seems that the SQL error is due to that in the generated SQL to retrieve the data, the column names are all capital letters,

whereas in the used original HANA View, column names contain capital and small letters (e.g. executing the generated SQL statement in HANA Studio, also throws error).

We tried both with Predictive Analytics 2.1 and Predictive Analytics 2.2.

Making all column names CAPITAL letters in the HANA View is a lot of work and no nice workaround...

Has anybody experienced s.th. similar and a possible way to correct it?

Best Regards

Florian

[marc_daniau]

Hi Florian,

Can you share the definition of the view from HANA Studio as a bitmap ?

Want to check first if we are talking about a SQL view or a BIC view.

Want to see the naming not only of the view but also of the columns.

Thanks,

Marc

[Former Member]

Hi Marc,

will attach the screenshot as soon as possible.

Not sure if this is sufficient already to gain more insight:

The View in HANA is generated with a "manual written" SQL statement (join btw. two tables),

the orignal column names in the source tables and in the View still contain lower+uppercase letters, while the generated SQL in PA 2.1/2.2 contains only uppercase letters.

Best Regards

Florian

[Former Member]

Hi Marc,

here is the SQL from HANA Studio to create the View and the SQL generated by PA 2.2 later when creating a data manipulation/model based on that View:

(1) SQL for View definition (note that column names in Table and View are mixture of small and capital letters):

create view "SC"."SC_VIEW3" as

select T0."datetime", T1.*

from

"SC"."sc.sc::sc.DATA" T0 inner join "SC"."TEST_SC" T1

on

T0."CoolerPositionValveOutlet" = T1."CoolerPositionValveOutlet"

and T0."CoolerTempAirCooler" = T1."CoolerTempAirCooler"

and T0."DynRangesRetentionTimeMax" = T1."DynRangesRetentionTimeMax"

and ... (more join conditions)

(2) The SQL below is generated in PA 2.2. (Please note, here, only capital letters are generated for the columns and we get the SQL ERROR in PA 2.2. Moreover, when this SQL statement is executed in HANA Studio, we also get the error - the generated capital letter column names (e.g. "COOLERPOSITIONVALVEOUTLET") seem cant be matched with the original column names from the table/View (e.g. "CoolerPositionValveOutlet")...)

select

  SC_VIEW3.DATETIME as DATETIME,

  SC_VIEW3.COOLERPOSITIONVALVEOUTLET as COOLERPOSITIONVALVEOUTLET,

... (more fields, all capital letters now!!!)

  case

    when ((SC_VIEW3.COUNT_LINE < 100)) then 1

    else 0

  end as TARGET

from

  SC.SC_VIEW3 SC_VIEW3 ;

[marc_daniau]

Hi Florian,

I reproduced it when doing an Analyze Data Description.

The workaround is to use a description file rather than doing an Analyze.

For the description use the following quoting for each column :  """Days To Report"""

See the sample attached.

Doing that the scoring equation will be generated properly

CAST( (CASE

WHEN ( "Days To Report" IS NULL ) THEN -1.514364691659e-3

WHEN "Days To Report" <= 0.0e0 THEN -6.855889519408e-2

Let us know if it works in your case

equation.sql.zip

Desc_Claims_Hist_LC.txt.zip

[achab]

, see the useful information provided by Marc. Does it work for you?

Thanks & regards

Antoine

[Former Member]

Hi,

I am afraid there is no option to Add a Description in Data Manipulation, I also tried with HANA table instead of HANA View:

When we come to the Data Manipulation Screen.

1) It seems, here in the table, still the correct Column Names are Displayed (capital/small). There is no Option to use a description file.

2) When we switch to the SQL View, for the SQL, different column names are displayed (capital/small), leading to invalid SQL.

Please see the new attachments...

Best Regards

Florian

[achab]

Hi,

My initial understanding of this thread is that you were using the Modeler component (as per when creating a model on an HANA View (automated analytics), it turmed out when generating the automated model, there is a SQL error thrown.).

Is this thread purely about Data Manager as it now seems?

Thanks & regards

Antoine

[Former Member]

Hi,

it is like this:

1. I first create the data manipulation - there is no error thrown here, but you can Display the generated SQL.

2. I then create a "Regression/Classification" Model based on that data Manipulation - when I generate this, I get an error displayed.

I guess when generating the model, the data Manipulation's SQL ist executed and the error is thrown.

[marc_daniau]

I am reproducing it using  a data manipulation against a HANA SQL view with lower case column names. Even with loading a description file in the modeling data description step, the SQL error remains.

I would suggest you open a support case so we can keep track on this issue.

In the meantime I will try to find a workaround.

[achab]

Hi Florian, it is possible to open a support case with SAP support, as suggested by Marc?

Then I would kindly ask that you close this thread and that we manage future updates via the support ticket channel.

Thanks & regards

Antoine

[achab]

Hi Florian,

Any update on this? Can you please kindly flag the question as "Answered"?

Thanks & regards

Antoine