SAP Datasphere - Data Access Controls on hierarchy nodes

[christopher_kampmann2]

Intro

SAP Datasphere offers a very simple way to manage data permissions via Data Access Controls. This controls who can see which data content. In projects, in addition to simple authorizations on values of a dimension, authorizations on nodes of a hierarchy are often required. Until now, this has not been possible directly in SAP Datasphere. The blog post show a possibility how to assign data permissions on hierarchy nodes.

Starting point: a simple example data model

The diagram shows the very simple data model. There is a table with transaction data and an Organizational Unit dimension that contains a hierarchy. The dimension is associated with the Analytical Dataset. Finally, an Analytic Model has been created.

The Analytic Model contains key figures from the personnel area per organizational unit. Personnel data is a very sensitive area where it is important that not all employees are allowed to see all data. Therefore, the data authorizations have a special significance.

Of course, in a simple example like this, you could assign permissions for each individual organizational unit. For example, if you want to see all the 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. Here, maintenance is simply too time-consuming. On the other hand, organizations are subject to change. If, for example, new organizations are subordinated or even some are removed, this would have to be maintained manually. Hardly feasible.
Therefore, a solution is sought in which nodes of a hierarchy can be authorized.

The Solution

The solution architecture uses the standard Data Access Controls feature and restrict permissions to organizational units. The diagram shows the structure in the simple example:

The Authorization Values table contains the actual authorization values. However, users do not want to maintain individual organizational units in the table, but would like all subordinate organizational units to be automatically authorized as well.

This can be achieved via the SQL View Authorized OrgUnits (SQL Script). Here, all subordinate organizational units are determined. So you 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. Via Start rank the respective children can be assigned to an original parent node. So a standard HANA hierarchy function is used. More about this here: Link

Now if we restrict the permission value to the IT Department (number 14), all children are automatically returned and the Data Access Controls are restricted. In the picture you see the result of the SQL Code:

In SAP Analytics Cloud, you only see the FTE for the authorized organizational units.

Summary

With SAP Datasphere, customers get more than graphical views and simple SQL capabilities. Due to the basis of HANA Cloud, many functions of SAP HANA can be used. In the example of this Blog post I show that authorizations on hierarchy nodes can be easily created.