cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Analytic Privilege equivalent of Classic Analytic Privilege using Procedure with Table result?

MattHarding
Active Contributor
0 Kudos

Hi All,

I've been tasked with creating an HR Organisational Analytic Privilege for various HR Calculation Views (running HANA SP10).

Firstly, correct me if I'm wrong, but it looks like SQL Analytic Privileges are the current approach and I assume Classic Analytic Privileges are being phased out.

With that in mind, looking at HANA Live HCMRenewal content there is the existing procedure "GETORGUNITFORCURRENTUSER" that for the given user, gets their relevant organisational units and returns it (concatenated with the ERP client number) as a table result.

Now this is very useful if using Classic Analytical Privileges as you can just follow the instructions like shown here:

However, if I look at how to do something similar with SQL Analytic Privileges, I need to look at using the "SQL Editor" or the "Dynamic options" for dynamic privileges.

The dynamic option could be straightforward except it is expecting a where clause output from the procedure, which while I could construct this; for someone at the top like the CEO, I'm probably going to need to return a where clause constructed programmatically that could be extremely long to cater for all organisational units that a CEO is master chief of (and it's ugly string coding).

The SQL Editor option looks promising as it gives me the ability to do a subquery in it (e.g. OrgUnit in (select...)); however when I tried to use a Table Function to implement the SQLScript; (I believe) I found a limitation that the where clause can only be based on the results from selection from real tables.  e.g. Select from the Table Function fails with this error but if I replace with a  native table in the select it works as expected.

[2991] table config for index HARDINGM:_SYS_SS_CE_578889_139811508402176:2_RET is not valid

BTW - For reference, the Table Function works as expected when called directly via SQL.

So do I go back to Classic for this scenario, am I doing something wrong here or is there a better way (apart from designing the security in the initial calculation view with a reusable script Calculation View which I would do because filtering at the end feels like I'm abusing the power of HANA)?

Thanks,

Matt

Accepted Solutions (1)

Accepted Solutions (1)

MattHarding
Active Contributor
0 Kudos

Here's a solution to it (not the answer I was hoping for but will let me meet the requirements). I'm guessing using a Table user defined function directly is not working due to some kind of bug or the feature is not available...

If I use the Table Function in a Calculation View; then use the Calc View in the in clause like this:

(ORGUNIT in (select ORGUNIT from "_SYS_BIC"."MyCalcView") )

then this works as expected...

Cheers,

Matt

Answers (1)

Answers (1)

MattHarding
Active Contributor
0 Kudos

Okay - There may also be something funky with my Calculation View (which is not a Cube and very basic with a projection of a table with a couple of fields) as using the Procedure approach also gives me the same error...

And interestingly, apparently analytical privileges will optimise themselves in the query itself as per this post () - Clever stuff!