Incorrect results with design-time artifacts

[rindia]

Hi,

Problem description:

After creating design-time artifacts, Analytic view with Anlaytic privilege having assign restriction as repository procedure is not returning desired results.

HANA AWS revision 70.

Problem Recreation:

Below I am giving the process for problem recreation.

1. EDW.hdbschema:

schema_name="EDW";

2. EDW_DD.hdbdd:

namespace excent.P1;

@Schema : 'EDW'

context EDW_DD {

type TT_LV {

LowValue : String(18);

};

entity USER {

@Catalog.tableType: #COLUMN

key User              : String(10);

key InfoObject        : String(10);

key Option            : String(2);

key LowValue          : String(18);

HighValue             : String(18);

ChangedOn             : LocalDate;

ChangedBy             : String(8);

};

};"

3. Created Analytic view (AN_EDW_SALES.analyticview) based on VBAP table with output as VBELN, MATNR, NETWR. Apply privileges is left empty.

4. Created repository procedure (LowValue_IN.procedure)

CREATE PROCEDURE LowValue_IN (OUT RES "EDW"."excent.P1::EDW_DD.TT_LV" )

       LANGUAGE SQLSCRIPT

       SQL SECURITY DEFINER

       DEFAULT SCHEMA _SYS_BIC

       READS SQL DATA AS

v_cnt INT;

BEGIN

RES = SELECT "LowValue" FROM  "EDW"."excent.P1::EDW_DD.USER"

             WHERE "User"            = SESSION_USER

               AND "InfoObject"       = 'MATNR'

               AND "Option"           = 'EQ'

             GROUP BY "LowValue",  "User"

;

SELECT COUNT(*) INTO v_cnt FROM :RES;

IF :v_cnt = 0

THEN RES = SELECT 'EMPTY' AS "LowValue" FROM DUMMY;

END IF ;

END;

5. Created Analytic privilege(AP_EDW_SALES.analytic privilege) with assign restriction on Repository procedure (LowValue_IN.procedure) with operator "IN" and Privilege Validity with operator as ">=", From date as yesterday (2014-03-24)

6. Created Role (EDW_SALES.hdbrole) and assigned the below privileges:

system privilege: CATALOG READ;

catalog sql object "SYS"."REPOSITORY_REST": EXECUTE;

catalog schema "_SYS_BI": SELECT;

catalog schema "_SYS_BIC": SELECT;

analytic privilege: excent.P1:AP_EDW_SALES.analyticprivilege;

package excent.P1: REPO.READ;

7. Created user from Systems view (EDWUSER) and assigned the role (EDW_SALES.hdbrole).

8. In SQLConsole, executed the query to insert 1 record, which has in VBAP:

insert into "EDW"."excent.P1::EDW_DD.USER" values('EDWSALES','MATNR','EQ','M-08','','','');

Now when I do the data preview, I can see all the records instead of only 1 record with MATNR "M-08".

Few Observations:

1. When SELECT statement of procedure when executed in SQLconsole for EDWUSER(with extra privilege SELECT on EDW schema) it returns only 1 row but not the Analytic view.

2. Also the repository procedure can be used as catalog procedure in Analytic privilege. This also behaving the same.

3. When I change the "Apply Privileges" option to "Analytic Privileges" in Analytic view, I am getting below error when doing data preview.

I could not understand where I am doing wrong. Is it a product bug or mistake from my end?

Regards

Raj

[former_member182302]

Hi Raj,

What happened with this approach? Did this work or you went with the normal way?

Regards,

Krishna Tangudu

[rindia]

Hi Krishna,

This POC was kept aside. This was more of product bug. I not checked yet in SPS 8.

Regards

Raj

[former_member182302]

Thanks Raj for the update.  We are also going with repository proceedure created from content folder. As we are on Rev 74.

Regards

Krishna Tangudu

[rindia]

Hi Thomas Jung,

Request your inputs on this.

Regards

Raj

[former_member182302]

Hi Raj,

I have already used a Repository procedure ( from content ) in a analytic privilege and it works fine for me as expected.

Yet to test with .procedure though. Can you share the syntax of EDW_DD.USER?

Regards,

Krishna Tangudu

[rindia]

Krishna,

I created the User from System view under Security -> Users -> New user.

It is not an .hdbuser

Regards

Raj

[former_member182302]

But you mentioned as "EDW"."excent.P1::EDW_DD.USER" in your procedure script?

Regards,

Krishna Tangudu

[rindia]

It is table declared in  EDW_DD.hdbdd.

Declaration syntax already given.

[former_member182302]

Ohh! Yes.. Got it now.

Well I tried to recreate your scenario: ( Tested on Rev 70)

1) Am sure you might have used the correct insert statement:

insert into "EDW"."MarchBatch.tst::EDW_DD.USER" values('EDWUSER','MATNR','EQ','1400-310','','','');

The user name must be EDWUSER not EDWSALES.

2) Have created Analytic Privilege with Validity date and others as mentioned by you. Only difference is am using a Repository procedure created under Content folder using Modeller Perspective (PROC_TEST) as shown in the below screenshot.

3) The data preview for EDWUSER as shown below is showing expected results i.e MTNR = 1400-310 is only showing up.

4) When I used the .procedure (LowValue_IN) , I also hit the same error same as you mentioned below:

With only difference being the way the procedure is created, the issue must be in the way .procedure is used. May be a bug I guess

Regards,

Krishna Tangudu

[rindia]

Thanks Krishna for your time.

Actually I created user "EDWSALES" (not "EDWUSER" but I mentioned it incorrect in point 7 - that's a good catch though) and had the correct insert into table. Added the system with that user and doing the data preview.

Have you tried  Analytic view with "Apply Privileges" blank and repository procedure(.procedure) in Analytic privilege? It will give results but not correct. Is it the same with you? I hope so as we are using same code.

I need to go for .procedure/.hdbprocedure (Repository).

Regards

Raj

[former_member182302]

Hi Raj,

If I set the Analytic Privilege to BLANK. Am getting the results with out any restriction. The procedure is not even getting invoked.

I tried with .procedure and .hdbprocedure both are giving the same error "connection reset".

This is the trace message i saw:

  message: a timeout occured while reading from the channel
  info:    timeout occured
  param:   erroneous channel 1022 from 56674 to 127.0.0.1:30005

Only the procedure created from modeller perspective is working as expected.

Will be waiting along with you now for Thomas's reply.

Regards,

Krishna Tangudu