Skip to Content
Technical Articles
Author's profile photo Mohamed EZZAT

SAP Analytics Cloud – Replicating BW Analysis Authorizations into Dimension Read/Write Property

Introduction

When it comes to import models, the first question that comes to anyone’s mind is whether they will need to redesign their authorization model again on SAP Analytics Cloud. In my previous blog post I showed a method to automate the authorization replication from SAP SuccessFactors for a certain use case.

In this blog post, I will be tackling a much more common use case, which is the BW Analysis Authorizations to be replicated into the SAP Analytics Cloud public dimenions’ read and write properties using a HANA calculation view.

Pre-requisites

  • At least SAP HANA 2.0 SPS 1.
  • User IDs for each user on SAP Analytics Cloud to be the same as their BW users.
    • If there is a prefix or a suffix included, it can be appended to the code, by concatenating either of them to the final UNAME.
    • In case the ids are not the same, a mapping containing the UNAME (BW USER ID), and the SAC USER ID can be loaded to either an attribute of an infoobject or to a new ADSO, the table containing the relevant data will have to be joined through UNAME from “AGR_HIER” and either the infoobject table or the ADSO table. SAC USER ID will replace all the UNAME callings in the presented code, after the selection of the “Main” table.
  • An import connection setup between the HANA 2.0 database and SAP Analytics Cloud.
  • Backend user to have “Read” right to the BW schema.

Solution Features

  • Works for BW4HANA as well as BW on HANA
  • Code is highly dynamic and can be deployed in any HANA 2.0 database, once it is executed, you will be prompted to enter:
    • The schema name
    • Name space
    • InfoObject name
  • No tables, functions, or any object other than the HANA calculation view need to be created, everything is handled temporarily during the execution through the usage of table variables as well as normal variables.
  • It works for the Equals, Between, Contains Pattern options specified within an Analysis Authorization Object.
  • No redundant or derived member ids are generated, all are retrieved from the InfoObject’s master data table.

Current Limitations

This version of the solution does not currently handle the hierarchy based authorizations.

Main Idea

Before I jump into the technical aspects of the code, I would like to shed some light on the basis of this idea.

Our main goal in the end is to get a list of specific members for a certain dimension in one column, and the user ids of everyone that has access to that member, whether it is through EQ, BT, or CP options in another column.

The authorization relevant infoobject values are maintained in an analysis authorization object that is maintained in tcode “RSECADMIN”, then a role is created having the authorization “S_RS_AUTH” for that specific object, “BIAUTH” must be set to the authorization object’s name that we created at first.

To be able to get the user id that is assigned to the role, and the value that is assigned to a certain object within the analysis authorization we need to go through 3 tables:

Main%20tables%20in%20scope

Main tables in scope

From the “AGR_HIER” table we will be getting the linkage between the user ids and role names (AGR_NAME) from the “AGR_1251” table, we will be getting the linkage between the role names and the analysis authorization object name, from “RSECVAL_STRING” we will be getting the values maintained for a certain infoobject for a specific authorization object.

The letters beside the table names represent their aliases that are used in the code that will be explained below, joins between the 3 tables are maintained as in the diagram, green fields represent the unprocessed expected final outputs, the other fields are not everything in scope, but the most important fields that we have.

Using dynamic SQL, I will be dynamically selecting the P table of a given infoobject, through the concatenation of schema “SCM”, name space “NMSPC”, P, infoobject Name “IOBJNM”. Ex: for the infoobject “ZE_PLANT”, the calculation view will be fetching the values from the “/BIC/PZE_PLANT” table. The main reason for choosing the P table in particular is that it is common and exists for all BW versions.

Step by Step Guide

Login to the HANA database, in a new or an existing package, right click, select New>Calculation View, specify a name then make sure you set the type to “SQL Script”

Calculation%20View%20-%20Initial%20Setup%20%281/3%29

Initial Setup

From the left panel, select “Script_View”, from the right window under the Output panel, click on (+) add. We need to create 2 attributes “MID” which will be an NVARCHAR(60), and “READ” which will be NVARCHAR(5000), 1 Measure INTEGER “DUMMY” which is dummy (as having a measure is mandatory in any calculation view).

Columns

 

Right click Parameters>New>we need to create 3 prompts, SCMA NVARCHAR(40), NMSPC NVARCHAR(10), IOBJNM NVARCHAR(15), all of them will be of Parameter Type “Direct”, all of them are mandatory.

Below is a the SCMA parameter setup and default value, setting it up based on the default value of your application database schema will save the effort of having to enter it each time the view is called.

Prompts – Schema name default value

From the left panel, click “Semantics”, specify MID, READ as attributes by selecting each of them then from the top left panel click on the blue rectangle, as for the DUMMY column, please set it to a measure by clicking the orange rectangle.

Column%20definitions

Semantics – Column definitions

This is how your output panel should look like right now:

Output%20panel

Output panel

Now let’s get to the code itself:

 /********* Begin Procedure Script ************/ 
BEGIN 
--Main table from which the user ids per dimension member will be captured
DECLARE MAIN TABLE (id nvarchar(60), tcthigh nvarchar(60), read nvarchar(5000), iobj nvarchar(15), option nvarchar(2), tctauth nvarchar(15));
--Final 'EQ' result table, straight forward
DECLARE EQ TABLE (mid nvarchar(60), read nvarchar(5000));
--Result set of filtering the Main table based on the option 'BT'
DECLARE BTRES TABLE (id nvarchar(60), tcthigh nvarchar(60), read nvarchar(5000), rnum integer);
--Initial loop result table, used as a temp area for supplying the final value in 'BT' table
DECLARE RES TABLE (mid nvarchar(60), read nvarchar(5000));
--Final 'BT' result table, containing all the members between each line of range
DECLARE BT TABLE (mid nvarchar(60), read nvarchar(5000));
--Count of result lines from the BT query result in BTRES
DECLARE LNBT INTEGER;
--Result set of filtering the Main table based on the option 'CP'
DECLARE CPRES TABLE (id nvarchar(60), tcthigh nvarchar(60), read nvarchar(5000), rnum integer);
--Final 'CP' result table, containing all the members matching a certain pattern
DECLARE CP TABLE (mid nvarchar(60), read nvarchar(5000));
--Count of result lines from the CP query result in CPRES
DECLARE LNCP INTEGER;
--'BT' and 'CP' Loop related variables
DECLARE i INTEGER DEFAULT 1;
DECLARE LOW NVARCHAR(60);
DECLARE HIGH NVARCHAR(60);
DECLARE RD nvarchar(5000);
DECLARE PTRN NVARCHAR(60);

EXEC 	'SELECT DISTINCT tctlow as id, tcthigh, UNAME as read, tctiobjnm as IOBJ, tctoption as option, tctauth
		FROM
		(
		Select 
		r.tctlow,
		r.tcthigh,
		u.UNAME,
		r.tctiobjnm,
		r.tctoption,
		r.tctauth,
		a.AGR_NAME,
		a.AUTH
		
		From "'||SCMA||'".AGR_USERS u
		Join "'||SCMA||'".AGR_1251 a
		on u.AGR_NAME = a.AGR_NAME
		join "'||SCMA||'".RSECVAL_STRING r
		on a.low = r.tctauth
		WHERE r.tctlow NOT LIKE''%:%''AND r.objvers =''A'' AND r.tctauth !=''0BI_ALL''AND r.tctiobjnm ='''||IOBJNM||''')' INTO MAIN;
		
--Handling the Equals 'EQ' Scenario
EQ = SELECT id as MID, read as READ FROM :MAIN WHERE option = 'EQ';

--Handle the Between 'BT' Scenario
BTRES = SELECT id, tcthigh, read, row_number() over(ORDER BY id asc) as rnum FROM :MAIN WHERE option = 'BT';
SELECT COUNT(*) INTO LNBT FROM :BTRES;

--Handle the Contains Pattern 'CP' Scenario
CPRES = SELECT id, tcthigh, read, row_number() over(ORDER BY id asc) as rnum FROM :MAIN WHERE option = 'CP';
SELECT COUNT(*) INTO LNCP FROM :CPRES;

--Case SAP Delivered InfoObjects having BI0 namespaces
IF NMSPC = 'BI0'

THEN
 
--Remove 0 from the infoobject name to perform the selections
SELECT REPLACE(IOBJNM,'0','') INTO IOBJNM FROM DUMMY;

FOR i in 1..LNBT DO
SELECT id INTO LOW FROM :BTRES WHERE rnum = i;
SELECT tcthigh INTO HIGH FROM :BTRES WHERE rnum = i;
SELECT read INTO rd FROM :BTRES WHERE rnum = i;
EXEC 'SELECT "'||IOBJNM||'" as MID, '''||RD||'''as READ FROM "'||SCMA||'"."/'||NMSPC||'/P'||IOBJNM||'"'||' WHERE "'||IOBJNM||'"'||' BETWEEN '||''''||LOW||''''||' AND '||''''||HIGH||'''' INTO RES;
INSERT INTO :BT (SELECT * FROM :RES);
i = i+1;
END FOR;

FOR i in 1..LNCP DO
SELECT replace(id,'*','%') INTO PTRN FROM :CPRES WHERE rnum = i;
SELECT read INTO rd FROM :CPRES WHERE rnum = i;
EXEC 'SELECT "'||IOBJNM||'" as mid, '''||rd||'''as read FROM "'||SCMA||'"."/'||NMSPC||'/P'||IOBJNM||'"'||' WHERE "'||IOBJNM||'"'||' LIKE '||''''||PTRN||''''INTO RES;
INSERT INTO :CP (SELECT * FROM :RES);
END FOR;

ELSE

--Case custom InfoObjects having BIC or other namespaces
FOR i in 1..LNBT DO
SELECT id INTO LOW FROM :BTRES WHERE rnum = i;
SELECT tcthigh INTO HIGH FROM :BTRES WHERE rnum = i;
SELECT read INTO rd FROM :BTRES WHERE rnum = i;
EXEC 'SELECT "/'||NMSPC||'/'||IOBJNM||'" as MID, '''||RD||'''as READ FROM "'||SCMA||'"."/'||NMSPC||'/P'||IOBJNM||'"'||' WHERE "/'||NMSPC||'/'||IOBJNM||'"'||' BETWEEN '||''''||LOW||''''||' AND '||''''||HIGH||'''' INTO RES;
INSERT INTO :BT (SELECT * FROM :RES);
i = i+1;
END FOR;

FOR i in 1..LNCP DO
SELECT replace(id,'*','%') INTO PTRN FROM :CPRES WHERE rnum = i;
SELECT read INTO rd FROM :CPRES WHERE rnum = i;
EXEC 'SELECT "/'||NMSPC||'/'||IOBJNM||'" as mid, '''||rd||'''as read FROM "'||SCMA||'"."/'||NMSPC||'/P'||IOBJNM||'"'||' WHERE "/'||NMSPC||'/'||IOBJNM||'"'||' LIKE '||''''||PTRN||''''INTO RES;
INSERT INTO :CP (SELECT * FROM :RES);
END FOR;

END IF;

--Final Result
var_out = SELECT DISTINCT MID, STRING_AGG(READ,';') as READ, 1 as DUMMY from (select * from :EQ  union select * from :BT union select * from :CP) group by MID;
END /********* End Procedure Script ************/

What needs to be done is simply copying everything above inside the Script_View, activate the calculation view, then you are good to go, as simple as that.

Before we see it on SAP Analytics Cloud, let me explain what this code is expected to do, let’s assume 3 simple cases.

Let’s start by the dimension members in the infoobject “ZE_PLANT”

Case 1, USER1 has access to plant AA01 only

CV Result:

Case 2, USER2 has access to plants between AA01 and AA05 only

CV Result:

Case 3, USER3 has access to all plants that start with A


CV Result:

Finally, combined together if these analysis authorization objects are active and assigend to certain roles that are assigend to certain users, this would be the CV’s result:

This is what SAP Analytics Cloud exactly needs to supply the property of the “ZE_PLANT” with the appropriate read users.

Demo

Finally, I will be showing actual screenshots from the calculation view results and the import job on SAP Analytics Cloud.

We have 3 users:

  1. TEST_B1 has access to company code 011118 only.
  2. TEST_B2 has access to company codes between 000000 and 099999.
  3. AUTH_USR5 has access to all company codes.

Calculation%20View%20Result%20for%20Company%20Code%20IOBJ

Calculation View Result for Company Code IOBJ

Now let’s create an import job in the “Data Management” tab of the public dimension that I created on SAP Analytics Cloud, “ZCOMPCOD6”, once the calculation view is selected, the prompts will show up:

SAC%20-%20CV%20Prompts

SAP Analytics Cloud – HANA Import Job – CV Prompts

Map the dimension id and the property

SAP%20Analytics%20Cloud%20Mapping

SAP Analytics Cloud Mapping

Final%20Result

Final Result

And just like that, any changes in the analysis authorization that is done with respect to the company code infoobject will be reflected through the scheduled import job, this will also work for the write property.

 

Thank you for your time reading this, I hope you have found this useful and informative. Hopefully the solution provided can solve one of the use cases that you currently have.

Looking forward to hearing your thoughts!

Assigned Tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Etienne Vionnet
      Etienne Vionnet

      Hello Mohamed, nice article!

       

      Do you also have experience or solution in a way to replicate authorizations of S4HANA in SAC (in read/write property) for planning model for example?

      Thanks,

      Author's profile photo Mohamed EZZAT
      Mohamed EZZAT
      Blog Post Author

      Hi Etienne,

      Thank you for your feedback, and sorry for the delayed reply.

      Currently not. I will let you know through this comment, in case I get the change do work on a similar usecase.

       

      Best Regards,

      Ezzat

      Author's profile photo Tobias Arnold
      Tobias Arnold

      Hi Mohamed, very good article!

      When when we use the data preview functionality, we get following error:

      "Dynamic SQL together with input/output variable is not supported in current SQLScript configuration"

      Do you know this error?

      Thank you,

      Best Regards,
      Tobias

      Author's profile photo Mohamed EZZAT
      Mohamed EZZAT
      Blog Post Author

      Hi Tobias,

      Thank you! What is your current HANA version?

      Author's profile photo Tobias Arnold
      Tobias Arnold

      Hi Mohamed,

      our Version is currently HANA 2.0 SPS 5.

       

      best regards,

      Tobias

      Author's profile photo Mohamed EZZAT
      Mohamed EZZAT
      Blog Post Author

      Hi Tobias,

      Apologies for the very delayed reply. If I am not mistaken you should be able to use Dynamic SQL in HANA views on your version, can you please attach a screenshot of the exact error you are recieving?

      Regards,

      Ezzat

      Author's profile photo Paul Vatter
      Paul Vatter

      Hi Mohamed EZZAT

      thanks for your blog!

      Following question: Are you aware of a limit at the number of users per member ID and any option to get rid of this?

      We have a similar case with 61 users on a member and get an error "Exceed maximum number of users supported"... So far we did not find any documentation of this limit...

      Thanks and best regards
      Paul

      Author's profile photo Mohamed EZZAT
      Mohamed EZZAT
      Blog Post Author

      Hi Paul,

      You are welcome. Ineed there seems to be a limit of 30 users only per import for the Read property, I figuered this out a week ago while I was implementing this soltuion for another customer, I am currently following up on this with our development team. Meanwhile, there are some workarounds that I can propose, how many SAC users do you actually wish to grab the authorizations for?

      Author's profile photo Paul Vatter
      Paul Vatter

      hi Mohamed

      thanks for your quick reply!

      In this one case we have 61 users - but there might be more users in other cases (overall the customer has 125 licenses).

      FYI: We have also raised incident 180383 / 2022 today, because we are able to paste the concatenated string containing the 61 uses without any issues.

      Best regards Paul

       

      Author's profile photo Mohamed EZZAT
      Mohamed EZZAT
      Blog Post Author

      Hi Paul,

      Thanks for sharing the incident number, I will also update the blog post with any information I recieve.

      Regarding my question, right now are you using this for a reporting scenario or a planning scenario? I am asking about the actual number of users that are required to currently use the implemented models.

      Regards,

      Mohamed Ezzat

      Author's profile photo Paul Vatter
      Paul Vatter

      hi Mohamed

      the public dimensions are used in reporting and planning scenarios.

      The 61 users above are one example for the read-access on one member of one dimension, on the other members we have 25, 20,... users to be assigned, but this might change of course in future. The users will then have access to the reporting scenario accordingly. Related to write-access we have of course fewer users.

      Best regards

      Paul

      Author's profile photo Paul Vatter
      Paul Vatter

      hi Mohamed

      as information - the incident was closed with the request to raise this as request in the idea place.

      We did that and are happy about all votes (once the idea place is hopefully back soon :()

      "Extend number of characters for Import Jobs into Dimensions READ/WRITE Column "
      Request ID: 280650
      https://influence.sap.com/sap/ino/#/idea/280650

       

      Best regards

      Paul