cancel
Showing results for 
Search instead for 
Did you mean: 

HANA Analytic Privileges and Procedures in logical OR condition

andrea_davoli83
Participant
0 Kudos

Hi to all,

I've a simple requirement for my HANA "pure" (HANA Platform) DWH:

Using a simple "Or" condition between dimension of a Analytic or Calculation view.

A simple Example: in my calculation view I've company (Company_cod) and Sales organization (org_cod) that are in a 1:n relationship (one company - more organization).

I want to build a simple analytic privilege with this rule:

All data for company 2000 (all sales organization for company 2000) or only Sales Organization 3010 and 3020 (for company 3000).

I want to build this but is not easy with analytic privilege. Let me explain:

1) if a build an analytic privilege with company_cod filter to 2000 and org cod filter to 3010 and 3020 results is empty list because this is an AND logical relationship between filters;

-------------------------------------------------------------------------------------------------------------------------------------------

2) If I use two different analytic privilege (one for COMPANY_COD and one for ORG_COD) I can see only data for company 2000 and nothting for ORG_COD 3010 and 3020

Someone can help me to understand how it work and how to achieve this requirement?


Thanks thousand

Accepted Solutions (1)

Accepted Solutions (1)

anindya_bose
Active Contributor
0 Kudos

For the second Analytic Privilege ( on ORG_CODE) add another characteristic Company_Code .

So your second privilege would be like .

--Company Code 3000  and Org Code 3010 , 3020 .

If that does not work, make two privileges with both the characteristics.

Privilege1 :   Company Code 2000   and Org_Code  2010 - 2090  ( use between operator or something like that )

Privilege2 : Company Code 3000 and Org Code  3010 - 3020  .

Please let me know result.

Cheers

Anindya

Answers (2)

Answers (2)

impraveen0192
Participant
0 Kudos

Dear Andrea Davoli,


if i have understood your requirement correctly then you need


          1) to display all the data of 2000(all sales organization under 2000 )


          2) only Sales Organization 3010 and 3020 (for company 3000).

for this you can create two analytical privilege

1) one analytical privilege With sales company 2000

2) 2nd analytical privilege with sales company 3000 and Sales organization 3010 and 3020.

explanation for this is

Within one Analytic Privilege, all Dimension restrictions are combined with

a logical “AND”.

If two Analytic Privileges (or more) are defined to apply to the same view,

SAP HANA combines the corresponding conditions with a logical “OR”.

Regards

Praveen Kumar

andrea_davoli83
Participant
0 Kudos

Thanks Praveen,

thanks thousand.

Your post is clear but I don't know how can achieve this result using dynamic procedure that read this data from a simple table with 3 columns:

USER

COMPANY

ORGANIZATION

Can you help me?

Andrea

andrea_davoli83
Participant
0 Kudos

Another Question: do you think is possibile to use a single Procedure with two variable out for using in a unic analytic privlege for two dimension?

Like this (but doesn't work):

Loed
Active Contributor
0 Kudos

Hi Andrea,

Were you able to make your requirement work?

How did you do it?

Thanks.

Loed

Former Member
0 Kudos

Hello Andrea,

Have you tried utilizing SQL Analytic Privilege for this use case?

Have a look at http://scn.sap.com/docs/DOC-65734

Although the example shows a procedure, you can achieve simply by using and/or combinations in the SQL Editor option of the SQL Analytic Privilege. Refer SAP HANA Academy - Modeling: SQL Analytic Privileges [SPS 10] - YouTube

Official help link Create SQL Analytic Privileges - SAP HANA Developer Guide for SAP HANA Studio - SAP Library

Hope that helps you..

Regards,

Nihal

andrea_davoli83
Participant
0 Kudos

Hi Nihal,

thanks for your reply.

I tried to use procedure but the results is still the same (unfortunately).

I've build two procedure to read a simple table builded as below image:

Unfortunately I don't know how manage "all entries" when in the table procedure the select return "null" values (case row 2, company = 2000 and org_cod = null, all organization);

Rows 3 and 4 can be done if COMPANY_COD is 3000.

Could you help me?

Here the simple code of my procedure

l_cnt INT;

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

BEGIN

var1 = select "COMPANY_COD" from "AUTHORIZATION_MATRIX" where "UT_WIND" = Session_User;

select count(*) into l_cnt from :var1 WHERE "COMPANY_COD" is not NULL;

if l_cnt = 0   

then

else

varout = select "COMPANY_COD" from "AUTHORIZATION_MATRIX" where "UT_WIND" = Session_User;

end if;

END;

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



Thanks thousands

Andrea

Former Member
0 Kudos

Hi Andrea,

I feel you can achieve it simply it using the SQL Editor radio button in SQL Analytic Privilege and entering.

COMPANY_COD = 2000 OR ORG_COD = 3010 OR ORG_COD = 3020

The condition would match only for this serving your use case. I would suggest you to go through the links I've provided above.

Of course, this can be achieved even using a procedure.

Regards,

Nihal

andrea_davoli83
Participant
0 Kudos

Hi Nihal,

unfortunately I think the version of HANA Db or HANA Studio (both .85) can not support SQL Analytic privilege (in the youtbe video that you posted hana revision is 10.xx);

I've no choices of Aalytic privilege type when I create it.

Next week is in plan an upgrade to revision .97, do you know if it's available as in releease 10.xx as in release .97?

Thanks

Andrea

Former Member
0 Kudos

Hi Andrea,

This is clearly a new feature in SP10. I would suggest you to upgrade to it. If I am not wrong, this would be Revision 100 onwards.

Regards,

Nihal

andrea_davoli83
Participant
0 Kudos

Thanks Nihal,

do you think is possible to supply with procedure?

How can I use a procedure with two variable out (one for COMPANY one for ORG_COD) into an anlytic privilege?

Unfortunately is impossible to upgrade system in the next year.

Thanks

Andrea