SAP Hana Analytical Privilege for Team Logic

You have a context calculation view and want to implement a manager user who can see content of his team .

For this purpose you opened an Authorization table with 2 columns (KEY_NAME KEY_VALUE)


Table AUTH_OPINT

KEY_NAME	KEY_VALUE
U1	U4,U5

According to this table U1 has a team consisting of U2 and U3 .

Your view has a column as approverid, which is the column for storing approverid.
By definition a manager user can see items he approved or items approved by his team.

We have entries at this table for defining our teams , so a query like below :
select KEY_VALUE from YOURSCHEMA.AUTH_OPINT where KEY_NAME = SESSION_USER

will bring "U4,U5"

instr is a function searching second argument in 1st, so
instr( "U4,U5","approverid" ) will return bigger than 1 if approverid is either U4 or U5



TOTAL SCRIPT

("approverid" = SESSION_USER ) or
(
instr( (select KEY_VALUE from YOURSCHEMA.AUTH_OPINT where KEY_NAME = SESSION_USER) ,"approverid" ) >= 1
)

SAP Hana Analyrical Privilege Implement Super User

You have a context calculation view and want to implement a super user who has access to all contents in cockpit.


For this purpose you opened an Authorization table with 2 columns (KEY_NAME KEY_VALUE)


Table AUTH_OPINT

KEY_NAME	KEY_VALUE
SUPERUSER	U1,U2,U3

According to this table U1, U2 and U3 are super users.
When you define below sql as dynamic sql

It will return 'SUPERUSER' if current SESSION_USER is a substring of KEY_VALUE ( "U1,U2,U3" ).



'SUPERUSER' =
(
SELECT KEY_NAME FROM YOURSCHEMA.AUTH_OPINT where KEY_NAME = 'SUPERUSER'
and KEY_VALUE like concat(concat('%', SESSION_USER ),'%')
)

SAP OPINT problem Failed to acquire scenario lock; either a background process is processing the scenario or another user is performing operations on the scenario:

when you try to generate a scenario on Hana Studio you get following error :

Failed to acquire scenario lock; either a background process is processing the scenario or another user is performing operations on the scenario:

You have to execute below Sql to delete locks by replacing .
Package Name is project name under contennt.
Scenario name is name of hprbusinessscenario file name.

If you are not sure about this name open table
"SYS_PROCESS_VISIBILITY"."sap.opi.pv::SPVD_SCENARIO_METADATA"
and search scenario_name and find full qualified name.


delete from "SYS_PROCESS_VISIBILITY" . "sap.opi.pv::SPVR_SCENARIO_DEFINITION_LOCK" where
"SCENARIO_DEFINITION_ID" in (
select "SCENARIO_DEF_ID" from "SYS_PROCESS_VISIBILITY" . "sap.opi.pv::SPVD_SCENARIO_METADATA"
t1 join "SYS_PROCESS_VISIBILITY" . "sap.opi.pv::SPVR_SCENARIO_DEFINITION_LOCK" t2 on
t1.SCENARIO_DEF_ID = t2.SCENARIO_DEFINITION_ID
where t1. "SCENARIO_NAME" = '' group by "SCENARIO_DEF_ID" )

SAP OPInt , Reporting Logic for Workflow

In classical programming we have database which is a row based representation of data.
Rows point each other and we have complete logic in code.

In BPM programming we have flow at BPM and data is either at db or BPM engine.

Classical reports are aggregation of columns. Aggregations are
sum,average,count .... which is a summary data for mass data.

But our jobs are not consisting of aggregates. Jobs consist of task. So classical reporting does not show
anything about smallest piece of our job.

SAP OPInt is a reporting tool on tasks.
It shows you which tasks are at critical,
which processes are overdue.

It show your task flow and can predict completion time of tasks.
It enables you to take preemptive action about your tasks.

SAP HANA privilege Problems

When you get an authorization error on SAP Hana, you can view exact error from trace file.

Open trace configuration tab as in picture.
Click new trace
Input user name
Select the "authorization" from Indexserver.

When you are finished ,repeat what you have done again.
Go to diagnosis tab, find your file(best is sort by date)

Download and check end of trace.
You will see a trace like :

User XXX is not authorized to access _SYS_BIC.XY_Business_Scenario/SPVR_YX_SCENARIO_CONTEXT because he is missing any structured privileges applicable to that object

Check your exception and do appropriate action.

SAP Open SQL Upper Lower Case

Querying HANA Calculation views from Java

Say you want to extract data from a calculation view.
You need to define a jdbc data source ( SYS_BICDB )on netweaver administration pages.

( Configuration -> Infrastructure -> Application Resources )

Then you will refer calculation view as follows :

String sql = "SELECT GROUP FROM \"_SYS_BIC\".\"MY_Business_Scenario/SPVR_MY_SCENARIO_CONTEXT\" where DEF = '" + rfxId + "' ";

MY_Business_Scenario : name of your scenario
SPVR_MY_SCENARIO_CONTEXT : name of your calculation view.

public String getProcGroup(String rfxId) throws Exception {

  Connection con = null;

  String sql = "SELECT GROUP  FROM \"_SYS_BIC\".\"MY_Business_Scenario/SPVR_MY_SCENARIO_CONTEXT\" where DEF =  '" + rfxId + "' ";

  try {
   InitialContext ctx = new InitialContext();
   DataSource dsource = null;

   dsource = (DataSource) ctx.lookup("jdbc/notx/SYS_BICDB");
   con = dsource.getConnection();

   PreparedStatement q = NativeSQLAccess.prepareNativeStatement(con, sql);

   ResultSet rs = q.executeQuery();
   
   
   String procGroup = "";
   while (rs.next()) {

    procGroup = (String) rs.getString(1);
    
   }

   con.close();
   return procGroup;
  } catch (SQLException e) {
   e.printStackTrace();
   logger.errorT("getProcGroupFail for " + rfxId + " : " + e.getMessage());
  } finally {
   if (con != null) {
    con.close();
   }

  }
  return null;
 }