Stories of the SAP Analytics Cloud offer a dynamic, flexible, and interactive canvas, allowing you to explore data to gain insights that drive informed business decisions. The SAP Analytics Cloud supports many sources of structured and unstructured data, including a live data connection to the SAP HANA Cloud. Especially for stories shared among a large audience, it becomes apparent that limiting access to the data is crucial. The envisioned scenario involves restricting User A, belonging to Region A and Business Unit A, to exclusively view data corresponding to Region A and Business Unit A when accessing the story in the SAP Analytics Cloud.
This blog post explains the implementation of a configuration where the Live Data Model, providing data to the stories, is built upon an SSO connection to the SAP HANA Cloud. The Live Data Model retrieves data from a Calculation View deployed within an HDI container on the SAP HANA Cloud. The Calculation View is secured with an Analytic Privilege, providing precise, row-level control over the data visibility to individual users. The Analytics Privilege leverages an SQL Expression, referencing to an Authorization table to identify the specific portion of data accessible to the user.
If you follow the steps of this blog post, you will understand how data in the SAP HANA Cloud backend is dynamically filtered, depending on the specific user accessing the SAP Analytics Cloud story.
0. Prerequisites
- Access to an SAP Business Technology Platform (SAP BTP) account with a Cloud Foundry environment.
- The subaccount is subscribed to the SAP Business Application Studio with the Developer role assigned to the user.
- An HDI-container is setup with tables containing data and a Calculation view with the Data Category Cube to make it visible to the reporting tools.
- Access to the SAP HANA Cockpit with a user having Admin role privileges.
- Access to an SAP Analytics Cloud tenant with the required roles to create a connection, a live data model and a story.
1. Create a live data connection from SAP Analytics Cloud to SAP HANA Cloud using Single Sign-On (SSO)
In the SAP Analytics Cloud, go to your Connections and create a new Connection. Expand the Connect to Live Data section and select SAP HANA. Give the new connection a name and copy the host name of the HDI container into the Host field. Under Authentication Method choose SAML Single Sign On and copy the Provider Name and download the certificate before you proceed with setting up the trust relationship between SAP HANA Cloud and SAP Analytics Cloud. Continue with the next steps before you select OK and finish creating this connection.
Follow these steps in the SAP HANA Cockpit:
- Go to the Certificate Store and import the downloaded certificate file.
- Go to SAML Identity Providers and click Add Identity Provider. Choose an Identity Provider Name and enter the SAML provider name that you copied from the connection dialog into the Entity ID field. Make sure that the certificate uploaded in the previous step is selected and click Add.
- You need to create a Certificate Collection by going to the Certificate Collections and click Add Collection. Type a name, click Add Certificate, and select the new certificate. Edit the purpose settings, ensuring SAML is selected, and associate it with the designated SAML provider. Save the changes to complete the process, solidifying trust and enabling a secure connection between the two systems.
To enable SAP Analytics Cloud users to access the SAP HANA Cloud system, it is necessary to establish a mapping to an SAP HANA Cloud user:
- Go to User Management in the SAP HANA Cockpit.
- Create a new user (DEMOUSER_SSO) and set Disable ODBC/JDBC Access to No.
- In the Authentication section, select SAML and click Add SAML Identity.
- Select the Identity Provider that was previously created. Switch off the Automatic Mapping by Provider, so that you can edit the External Identity field. Verify in the SAP Analytics Cloud SAML Single Sign-On (SSO) Configuration which user attribute is sent to the HANA Cloud and needs to be used for the mapping. Typically, it is the E-Mail (demo.user@example.com) or the User ID (U123456) that is entered into the External Identity field. The user attribute mapped to the identity provider is mentioned in the SAP Analytics Cloud, under System, then Administration, and there in the Security tab.
- Click on Assign Roles and select a role with read Object Privileges on the HDI container. It is necessary to assign this role to the new user to grant read access to the Calculation View. If you miss this step, the SAP Analytics Cloud user will not be able to see any Calculation Views through this connection.
- Save the user.
Return to the creation of the connection in the SAP Analytics Cloud and save it. For more details about this section, you may refer to the official documentation.
2. Confirm the newly created connection
At this point, it is recommended to confirm that the SSO connection is working. Access the SAP Analytics Cloud and launch the Modeler to initiate the creation of a new model from a live data connection. Choose SAP HANA as the System Type and select the connection created in Step 1. Choose the desired Calculation View in the Data Source field. Successful connection results in the appearance of Measures and Dimensions from the selected Calculation View in the Modeler. Save the model for future use.
3. Add the Analytic Privilege to the Calculation View
In the existing configuration, every user on the SAP Analytics Cloud mapped to SAP HANA Cloud has visibility to all data within the Calculation View. Next, we aim to modify the Calculation View by incorporating an Analytic Privilege, restricting data access exclusively to users with a designated role.
The upcoming steps lay the groundwork for implementing user-based data filtering:
- Open the project in the SAP Business Application Studio that includes the database connection to the HDI container housing the Calculation View utilized for the SAP Analytics Cloud Model.
- Create an Analytics Privilege using the Command Palette via the SAP HANA: Create SAP HANA Database Artifact Wizard. For now, only add the Calculation View to the list of Secured Models and do not further define the Analytics Privilege.
- Map the Analytic Privilege to the Calculation view by navigating to the Calculation view, and within the Semantics tab, modify the Apply Privileges field under View Properties to SQL Analytic Privileges.
- Create a role (.hdbrole) that includes object privileges on the Calculation View as well as the Analytic Privilege.
{
"role": {
"name": "name_of_role",
"object_privileges": [
{
"name": "name_of_calculation_view",
"type": "VIEW",
"privileges": [
"SELECT"
]
}
],
"schema_analytic_privileges": [
{
"schema_reference": "name_of_schema",
"privileges": [
"name_of_analytic_privilege"
]
}
]
}
}
- Add the new role to a Role Configuration File (.hdbroleconfig).
{
"name_of_role": {
"name_of_schema" : {
"schema": "name_of_schema"
}
}
}
- Deploy the src folder of the project including the modified Calculation View, the Analytic Privilege, and the role.
- Login to the SAP HANA Cockpit and assign the new role ("name_of_role") to the SAP HANA Cloud user (DEMOUSER_SSO) that is mapped to the SAP Analytics Cloud.
- The previously established SAP Analytics Cloud Model now retrieves data from the Calculation View, with the added application of the Analytics Privilege.
4. Implement User-Based Data Filtering for the Live Model
Utilizing the SSO-Connection, we can uniquely identify the SAP HANA Cloud user (DEMOUSER_SSO). In this section, we make use of this capability to establish conditions that precisely define the data visible to the user.
The Model comprises various Dimensions, including "Region" and "Business_Unit" among others. The data filter is intended to target these two columns of the Calculation View. To handle the data access for multiple users, it is recommended to create a dedicated table to determine which filters are applied to one user. Executing these steps leads to the creation of a new table, which is subsequently referenced in managing user-based data access:
- Open the project in the SAP Business Application Studio and create a Table file (.hdbtable). Upon deployment, this file generates a table as a database object. For the underlaying example, the table consists of three columns: USER_NAME, Region, and Business_Unit.
COLUMN TABLE "USER_ACCESS_FILTERS" (
"USER_NAME" NVARCHAR(50),
"Region" NVARCHAR(256),
"Business_Unit" NVARCHAR(256)
)
- Deploy the "USER_ACCESS_FILTERS" table.
- Launch the SAP HANA Database Explorer and connect the HDI container of this project. Open the table "USER_ACCESS_FILTERS " and specify the desired data access in there. For example, if user DEMOUSER_SSO is only allowed to view data for the Region APJ and Business Unit CSS, one row needs to be added to the table with these three values. It is crucial to emphasize that the value in the "USER_NAME" column must precisely match the name of the user created in the SAP HANA Cloud.
As a last step, an SQL Expression is added to the Analytic Privilege to utilize the "USER_ACCESS_FILTERS" table. The SQL SESSION_USER Function is leveraged to map the "USER_ACCESS_FILTERS" table to the Analytics Privilege. Here is an example of what the SQL Expression might look like:
("Region" IN (SELECT DISTINCT "Region" FROM "name_of_schema"."USER_ACCESS_FILTERS" WHERE "USER_NAME" = SESSION_USER))
AND
("Business_Unit" IN (SELECT DISTINCT "Business_Unit" FROM "name_of_schema"."USER_ACCESS_FILTERS" WHERE "USER_NAME" = SESSION_USER))
The SQL Expression defines the WHERE statement for the SELECT operation executed on the Calculation View. The Region and Business Unit records are filtered for the entries in the "USER_ACCESS_FILTERS" table that match the SESSION_USER (DEMOUSER_SSO). The SQL Expression can be modified to accommodate various scenarios, given that it remains a valid WHERE statement.
5. Grant Access to a large audience
The advantages of implementing this configuration become most evident when sharing the SAP Analytics Cloud story with a broad audience. As of now, only the user DEMOUSER_SSO is granted access to the story. To facilitate the onboarding of additional users with personalized data access, these three steps provide guidance:
- Each user granted access to the story requires a mapping to an SAP HANA Cloud user through the configured SAML provider. The role of the Analytic Privilege must be assigned to the SAP HANA Cloud user. It can be considered whether the mapping should be a straightforward one-to-one relationship, with each SAP Analytics Cloud user corresponding to one SAP HANA Cloud user, or a many-to-one arrangement, where multiple SAP Analytics Cloud users are linked to a single SAP HANA Cloud user. The first option is advisable for projects with a moderate user count and diverse filtering requirements, while the latter is better suited for scenarios with a high number of users and minimal filtering combinations.
- Each SAP HANA Cloud user must have a minimum of one entry in the "USER_ACCESS_FILTERS" table. The username inputted into this table must precisely correspond with the SAP HANA Cloud user's name.
- The SAP Analytics Cloud Model and Story must be shared with the respective SAP Analytics Cloud user.
Adding multiple users in one batch is possible by executing an SQL script in either the HANA Cloud Cockpit or the SAP HANA Database Explorer. Through the SQL script, you not only add users but also establish a connection to the identity provider and assign the Analytic Privilege role to each user.
6. Conclusion
The underlying scenario demonstrates the feasibility of implementing user-based data access control seamlessly between SAP Analytics Cloud and SAP HANA Cloud accessing live data. It is worth emphasizing that users intending to execute the outlined steps require access to the root HDI container with the tables referenced in the Calculation view, as well as administrative access to the SAP HANA Cloud Cockpit. Overall, this integration use case contributes to an enhanced, secure, and tailored analytics experience within the SAP Analytics Cloud.
