SAP Datasphere offers a very simple way to manage data authorizations via Data Access Controls. This controls who can see which data content.
In addition to simple authorizations for values in a dimension, projects often also require authorizations for nodes in a hierarchy. Until now, this has not been possible directly in SAP Datasphere. In our blog post, we show a way of assigning data authorizations to hierarchy nodes.
Starting point: Our simple example data model
The diagram shows the very simple data model. There is a table with transaction data and an organizational unit dimension, which contains a hierarchy. The dimension is associated with the analytical dataset. Finally, an analytic model was created.
The analytic model contains key figures from the personnel area for each organizational unit. Personnel data is a very sensitive area where it is important that not all employees are allowed to see all data. Data authorizations are therefore of particular importance.
Of course, in an example as simple as this one, you could assign permissions for each individual organizational unit. If, for example, you are allowed to see all data of the IT department, you would have to manually authorize the IT department and all subordinate organizational units. However, this is not very practical.
On the one hand, there are usually much larger organizations with many more organizational units. Maintenance is simply too time-consuming here. On the other hand, organizations are subject to change. If, for example, new organizations are subordinated or even removed, this would have to be maintained manually. Hardly feasible.
A solution is therefore sought in which nodes in a hierarchy can be authorized.
Solution architecture
In our solution architecture, we use the standard function of the Data Access Controls and restrict the permissions to organizational units. The diagram shows the structure in our simple example:
The Authorization Values table contains the actual authorization values. However, we do not maintain individual organizational units in the table, but want all subordinate organizational units to be automatically authorized as well.
We achieve this via the SQL View Authorized OrgUnits (SQL Script). This determines all subordinate organizational units. We therefore search for all children of the hierarchy. This is made possible by the following coding:
return
WITH LT_HIER_DATA AS (
select *
from HIERARCHY_DESCENDANTS (
SOURCE HIERARCHY (
source (
select
"OrgUnit" as node_id,
"ParentOrgUnit" as parent_id
from "AuthDemoHierarchy"
)
)
)
)
SELECT C. "UserMailAdress", A.NODE_ID AS "OrgUnit"
FROM LT_HIER_DATA AS A
INNER JOIN LT_HIER_DATA AS B
ON a. "START_RANK" = B. "START_RANK"
INNER JOIN "AuthValues" AS C
ON B.NODE_ID = C. "OrgUnit"
AND B.HIERARCHY_DISTANCE = '0'
AND C.”Valid_From” <= current_Date
AND C. "Valid_To" >= current_Date;
The first SQL statement returns all "children" organizational units to an organizational unit of a hierarchy. The respective children can be assigned to an original parent node via Start rank. A standard HANA hierarchy function is therefore used. More about this here.
If we now restrict our authorization value to the IT department (number 14), all children are automatically returned and the data access controls are restricted:
In SAP Analytics Cloud, you only see the FTEs for the authorized organizational units.
Does not work - does not exist
SAP Datasphere gives customers more than just graphical views and SQL options. Thanks to the HANA Cloud basis, many SAP HANA functions can be used. In our example, we show that authorizations on hierarchy nodes can be easily created. During development, you should therefore always keep an eye on what SAP HANA does not already provide before trying to write your own procedures or SQL scripts. The motto here is often: Can't do - doesn't exist.
Have we aroused your interest?
Have we piqued your interest? As a selected SAP Gold Partner, we have already implemented SAP Datasphere (formerly SAP Data Warehouse Cloud) in customer projects.
You might also be interested in
Customer exit variables explained in Datasphere
With SAP Datasphere, SAP is consistently developing its data platform towards the cloud future. For many BW users ...
SAP Datasphere: Health Monitoring
WHY STANDARD MONITORING IS NOT ENOUGH FOR THE SAP DATASPEHERE AND INDIVIDUAL SOLUTIONS ARE A MUST. ...
SAP Business Data Cloud - revolution or just another upgrade?
With the announcement of the SAP Business Data Cloud (BDC), SAP is setting a new milestone in ...
Mapping hierarchies from SAP BW in Datasphere: This is how it works
How can complex hierarchies from SAP BW be mapped in SAP Datasphere? Since the introduction ...
How-To: How to successfully transfer data between SAP Datasphere and Python via hdbcli
The Python package hdbcli can be used to access data in the SAP Datasphere so that ...
SAP Datasphere - The end of SAP Business Warehouse?
Is SAP Datasphere the end of SAP Business Warehouse? In August 2023, SAP announced a ...
Christopher Kampmann
Head of Business Unit
Data & Analytics
christopher.kampmann@isr.de
+49 (0) 151 422 05 448
