How to – transform BW authorization hierarchies for reuse in SAP BusinessObjects
SAP BusinessObjects Data Services 3.x, 4.x, SAP BusinessObjects Enterprise Platform 3.x, 4.x
The purpose of this article is to explain how to transform a BW profit center hierarchy using the SAP BusinessObjects Data Services ETL tool in order to leverage profit-center based BW user authorizations in SAP BusinessObjects 3.1 reporting tools (Explorer, Webi and SBO Mobile) where there is no built-in hierarchy support.
Please note that whilst the new SAP BusinessObjects 4.x product version includes the BICS connector which provides direct access to BW hierarchical data, the technical workflow described below remains valid as an alternative option, especially in the case where your corporate data is consolidated into a data warehouse outside SAP BW.
Author(s): Nicolas Cottin, Lucy Rimmer
Created on: 3 May 2012
Nicolas Cottin and Lucy Rimmer are part of the SAP Global IT Business Intelligence & Enterprise Data Management team, whose mission is to provide insights with reliable and innovative Business Intelligence and Data Management Solutions and thus empower all SAP employees to make more sustainable decisions.
In the following case study, the requirement was that users would log into SAP BusinessObjects Web Intelligence reports using their SAP identification numbers and only see financial data for the Profit Center nodes for which they had authorizations in BW.
So the tables containing the profit center hierarchy and user authorizations needed to be imported from BW to the SAP BusinessObjects 3.1 side and then adapted for use in this environment. In BW it is sufficient to assign a user access to a top node; the system then automatically assigns the user access to all descendant nodes below this parent node. However, this implicit hierarchical assignment is not available on the SAP BusinessObjects 3.1 platform, so here an extended mapping must be defined in order to explicitly assign the user access to all descendants of the top profit center node.
If User1 is granted access to the EMEA node, and User2 to the CONS node, we will have to generate an authorization table with the following structure for the SAP BusinessObjects 3.1 platform authorization logic:
In BW on the other hand, the following structure is sufficient:
It is important to manage the risk of ending up with a huge authorization table on the SAP BusinessObjects 3.1 platform side depending on the total number of nodes in the profit center hierarchy and the number of users.
The following technologies and steps were necessary in order to leverage the BW Profit Center-based authorizations in BOE:
- Open Hub and SAP BusinessObjects Data Services to extract Profit Center and Authorization data from BW into a local datamart
- Data Services to transform the Profit Center and Authorization data into the BOE authorization table
- SAP Business Universe designer in order to implement this authorization restriction at data retrieval level for use in the front-end Webi and SBO Mobile reports.
Further details concerning these steps are given below:
1. Extract Profit Center and Authorization data from BW
a. Replicate BW Profit Center authorization data
All the Profit Center authorizations are stored in the custom table ZCONS_PC_AUTH which has the following structure:
Here only the User_Name, Profit_Center_Value, and Date To/From fields are relevant for us.
In the current example a data source was created on top of this authorization table in order to push its contents to OpenHub via an intermediate DSO, although instead we could have used a virtual provider by implementing an ABAP function module to retrieve the data directly from this authorization table.
b. Replicate BW Profit Center hierarchy data
The Profit Center hierarchy table (/BI0/HPROFIT_CTR) displayed below was also replicated from BW using the same extraction logic.
Here, NodeID, NodeName and ParentID will be relevant for extraction.
Below is a screenshot from SAP BusinessObjects Data Services showing how the data is loaded from one of the OpenHub datasources into a DB2 database for use on the SAP BusinessObjects reporting side:
2. Transform the Profit Center and Authorization data into the BOE authorization table
Once both tables are replicated into the target database, the SAP BusinessObjects Data Services transformation component can be used to adapt this data in order to obtain the most appropriate tables for SAP BusinessObjects 3.1 platform authorization logic.
Flatten the Hierarchy:
The source of this dataflow is the table /BI0/HPROFIT_CTR with NodeID, ParentID and NodeName:
By using the flatten component, and passing the required parameters as shown in the screenshot above, we can get the structure below with as many columns as levels. (Please note that we used the “nodeid” field as opposed to the “childid” field as the child column, since the former did not give the desired results.)
Pivot the Flat Table
In order to have all the NodeName values in a unique column, we now have to pivot the flat table created above. This will allow us to retrieve all the descendant nodes (CURRENT_LEAF) for a specific top node (PIVOT_DATA1).
This HIER_FLAT_PIVOT table will be really useful for the mapping to the authorization table.
Load authorization table ZCONS_PC_AUTH:
Then from the previous BW_PC_AUTH and HIER_FLAT_PIVOT tables we can populate a new table which will list all descendants for a given top node.
HIER_EXPLICIT_NODE table output:
The last step is now to map this table to the authorization table in order to get the final table with the
mapping User <-> Node Name:
Below is the output of the PC_USER_AUTH table:
3. Implement this authorization restriction at data retrieval level for use in the front-end SBO Mobile reports
Link the Authorization table to all fact tables:
2. Create a mandatory predefined universe filter as per below in order to capture the UserID with the BOUSER variable:
The mandatory filter will ensure that all WebI reports created using the associated fact tables will only display information related to the profit centers for which a record exists in the User Authorization table for the user who is currently logged in.
4. Leverage the authorization table for applying Profit Center authorizations within an Explorer information space
Create 2 dimension objects in the universe on top of the authorization table:
Index a security information space including these two dimensions:
(do not expose this information space on the Explorer homepage)
Then in your business data information space, use the Personalization feature to link this security information space to the technical information space and thus apply the Profit Center authorizations to your data:
SAP BusinessObjects Data Services: http://wiki.sdn.sap.com/wiki/display/EIM/Data+Servic