on 01-14-2016 2:57 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.