Data Services behaviour with SAP HANA as source

[afgmagalhaes]

Hi everyone,

Can someone help me explain this behaviour? I have the following dataflow, 3 flows with SAP HANA as source that are merged and converge in a single table:

If I check the Optimized SQL it gives the following code:

CREATE PROCEDURE "SAPABAP1"."DS_PROC_68745_CV_RDR" (OUT VAR_DS_TT_68745 "SAPABAP1"."DS_TT_68745")

LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW "SAPABAP1"."DS_VIEW_68745" AS

BEGIN

TBL01_001 = SELECT  "CSKB"."KOKRS"  "KTOPL", "CSKB"."KSTAR"  "SAKNR",'N/A' "TXT20",'N/A' "TXT50"

FROM "SAPABAP1"."CSKB" "CSKB"

where ( "CSKB"."KATYP"  = '42') and  not  { fn substring(ltrim( "CSKB"."KSTAR" , '0') , 1, 1)  }  IN ('0','1','2','3','4','5','6','7','8','9') and ( "CSKB"."MANDT"  = '010')

;

TBL01_002 = SELECT  "CSKU"."KTOPL"  "KTOPL", "CSKU"."KSTAR"  "SAKNR", "CSKU"."KTEXT"  "TXT20", "CSKU"."LTEXT"  "TXT50"

FROM "SAPABAP1"."CSKU" "CSKU"

where (( "CSKU"."KTOPL"  = 'ANA') or ( "CSKU"."KTOPL"  = 'ANAM')) and ((((((( "CSKU"."KSTAR"  like '009%') or ( "CSKU"."KSTAR"  like '00000097%')) or ( "CSKU"."KSTAR"  like '92%')) or ( "CSKU"."KSTAR"  like '%H')) or ( "CSKU"."KSTAR"  like '7281%')) or ( "CSKU"."KSTAR"  like '%X')) or ( "CSKU"."KSTAR"  like '%C')) and ( "CSKU"."MANDT"  = '010')

;

TBL01_003 = SELECT  "SKAT"."KTOPL"  "KTOPL", "SKAT"."SAKNR"  "SAKNR", "SKAT"."TXT20"  "TXT20", "SKAT"."TXT50"  "TXT50"

FROM "SAPABAP1"."SKAT" "SKAT"

where (( "SKAT"."KTOPL"  = 'ANA') or ( "SKAT"."KTOPL"  = 'ANAM')) and  not ( "SKAT"."SAKNR"  like '009%') and ( "SKAT"."MANDT"  = '010')

;

MRG01_004 = SELECT "KTOPL","SAKNR","TXT20","TXT50" FROM :TBL01_001 UNION ALL SELECT "KTOPL","SAKNR","TXT20","TXT50" FROM :TBL01_002 UNION ALL SELECT "KTOPL","SAKNR","TXT20","TXT50" FROM :TBL01_003;

VAR_DS_TT_68745 = SELECT * FROM :MRG01_004;

END;

Why is a procedure being called? Can I avoid this behaviour? This way I am unable to run this dataflow because it returns:

8384	7980	DBS-070404	2016-08-03 12:44:34	SQL submitted to ODBC data source <SAPHANA> resulted in error <[SAP AG][LIBODBCHDB DLL][HDBODBC] General error;258 insufficient
8384	7980	DBS-070404	2016-08-03 12:44:34	privilege: Not authorized>. The SQL submitted is <CREATE TYPE "SAPABAP1"."DS_TT_50E41" AS TABLE ( "KTOPL" VARCHAR (4) ,"SAKNR"
8384	7980	DBS-070404	2016-08-03 12:44:34	VARCHAR (10) ,"TXT20" VARCHAR (20) ,"TXT50" VARCHAR (50)  ) >.

The datasource user only has reading permissions, that's why it returns insufficient previlege. Can someone give some insight on this matter?

Thanks in advance.

[former_member192448 · Accepted solution]

Hi Andre

To avoid the error will mean disabling the pushdown functionality to HANA database.

Go to DSConfig.txt located at %DS_COMMON_DIR%\Conf

Then within [AL_Engine] enter HANA_CALCVIEW_OPTIMIZER=FALSE

restart the data services service and run the run job again.

Regards

Charles

[afgmagalhaes · Accepted solution]

Thank you very much. This seems to fix the issue, but is there any kind of permission to be given in SAP HANA to this user to be able to perform this kind of optimization? Since this is a production environment, CREATE permissions would not be recommended but as far as I know CREATE TYPE instruction does not take up any storage, right?

[former_member192448 · Accepted solution]

Hi Andre

Without the CREATE permission on the user pushdown to the database will not work.

No storage is consumed with CREATE TYPE thats correct

Charles

[former_member208402]

Hi Andre,

Your target is template table... you are trying to create this template table from bods..It means you dont have permission to create a table in the target datastore/database.

So the error.

Thanks,

Ravi kiran.

[afgmagalhaes]

The template table is writing to a Oracle database and not SAP HANA. That is not the issue here since the error is SAP HANA-related.