Getting Microsoft’s PowerBI working with BW 7.4 (sp19) and Hana 2.0 (2.00.33)

[chris_johnson3]

Our company chose MS Power BI as a first step into self serve reporting and Analytics. We were asked to connect our BWonHana system so users could get at their data, instead of our IS team creating reports for them. These are the steps that we finalized on to get MS PowerBI to talk to our data residing on BWonHana.

• Create a BW user and populate the DBMS tab in SU01 (there are lots of documentation on making sure that the DBMS tab in SU01 is there, DBCO connection DBMS_USER_MST)
  
  
  
  1. In hana, this user needs to have select on _SYS_BI and execute on REPOSITORY_REST
  
  
  
  


• Activate the External SAP Hana View:

RSA1, Infoprovider and then settings tab

In Hana, BW Modeling tool



 

• You need to populate the table RS2HANA_AUTH_STR, SAP recommends adding the entries via report RS2HANA_AUTH_RUN. When you run this report, it creates the hana role: bw2hana/SCHEMA_SAP_BW_MODEL_GENERATION. This role has the Object privileges and Analytic privileges.

Our case, we have Queries and Cubes, we must add both the cube and query to that table. This is needed because of the procedure that is created when you turn on the external hana view:



CREATE PROCEDURE "_SYS_BIC"."bw2hana/SAPPB1_0COSTCENTER" (OUT filter VARCHAR(30000)) LANGUAGE SQLSCRIPT SQL SECURITY DEFINER READS SQL DATA AS v_filter VARCHAR(30000); CURSOR cursor1 FOR SELECT "VALUE" FROM "SAPPB1"."RS2HANA_AUTH_STR" WHERE "INFOPROVIDER" = '0COSTCENTER' AND "TLOGO" = 'IOBJ' AND "HANAMODELFL" = 'I' AND "DBMSUSER" = SESSION_USER; BEGIN OPEN cursor1; FETCH cursor1 INTO v_filter; CLOSE cursor1; filter := v_filter; END;

At this point, you can Use Power BI with SAP BW queries. The only issue, is all the cubes and queries are view able to the user. If they don’t have access to it via the table RS2HANA_AUTH_STR they will not be able to see the data, but they will see the structure.



 

While this works, I find that it could be cleaned up. (If you use Lumera, then this isn’t the case, it only shows the query/cubes you have authorization for)

 

• Our fix was to create a Hana Role to allow the users to only see what they have access to.

As a system user, we added _SYS_BI, the query’s (Select access) and Repository_rest (execute)



Then as the BW user, add the Analytic Privileges



Once the role is created, go back to the user and remove the role bw2hana/SCHEMA_SAP_BW_MODEL_GENERATION and then add the role you just created. Now you only see the queries that the user has access to.

We also run update statements to directly add the entries to RS2HANA_AUTH_STR: (you can see the values you will need to change if you look at the table and see when the entries are)

insert into SAP.RS2HANA_AUTH_STR values (‘Role’,'TEST2','"0CHRT_ACCTS" LIKE ''%'',' ','ELEM','X','TEST1',20181217152104,'TEST2')

insert into SAP.RS2HANA_AUTH_STR values (‘cube’,'TEST2','"0CHRT_ACCTS" LIKE ''%'',' ','CUBE','X','TEST1','20181207171310','TEST2')

 



 

1. There are a couple of issues, if you change the query or cube, and then transport it to production, you must re-do the role in hana. The act of activating is regenerates the privileges and clears it from the role.


2. If you create a new query and transport it. It regenerates the SAP_BW_MODEL_GENERATION role and assigns it to all the users. (which lets them see all the queries and cubes, but not the authorization to access the data)

We run the report RSUSR_DBMS_USERS to mass remove the role:



So in Conclusion, MS Power BI is a good tool for us to start our journey on self serve reporting and analytics. There are a couple little idiosyncrasies on how it connects to BWonHana, but our users are loving the ability to get to their data. (even though they don’t know it ? )