How to implement Matrix Security using Hierarchies in SAP BPC NW

[alessio_pulejo2]

In this blog I will show you a possible approach to implement a matrix security based on 2 different hierarchies in the same dimension.

In particular I will describe how you can use Business Add-Ins (BADI) for BPC to enhance the standard security model:

1. BADI_UJE_DYNAMIC_DAP will be used to dynamically generate a “Deny” rule for every member that is not visible in one of the hierarchies.
2. BADI_UJ_SQE_POST_PROCESS will recalculate total members without the amount posted on denied members.

1. Business scenario

Many organizations use a group level matrix with two reporting lines by geography and by functions. Therefore, in our BPC’s entity dimension there will be a geographic hierarchical view and a functional hierarchical view.

E.g.: we can consider a computer retailer company which operates in Europe and America selling and providing assistance on Personal Computers.

In our Profit Centre dimension we can describe this by having 2 different hierarchies:

a. Geographic Hierarchy where company’s profit centres report to their region

b. Functional Hierarchy where company’s profit centres report to their function

If we need a role for the Europe’s Sales Manager, we won’t have any total node available for him, because he will need access only nodes highlighted in light blue.

2. The options available in the standard model

The standard model is able to cover the most of possible scenarios and we can choose between different kinds of approach.

1. Assign leaves directly to the users and do not provide any access to both the hierarchies to this type of user. Only bas members will be available and any aggregation between them has to rely on a property instead of hierarchy.

b. Update the dimension in order to merge the two hierarchies: in our example it will mean to create new nodes for Europe Sales and America Sales.

c. Put the second hierarchy in a different dimension: first hierarchy (usually the geographic one) remains in the Entity dimension, but the second one (the functional) is moved to a user defined dimension.

So in our example we will have to duplicate profit centres bas members in 2 dimensions and then build our 2 hierarchies.

Members shown in grey will be restricted to Europe Sales Manager with a “Deny” Rule in Data Access Profiles.

3. In which situations could we need a non-standard solution?

We will need a custom solution, if we have a big entity dimension, with many different hierarchical nodes in the 2 hierarchies.

1. If we give access only to leaves, usability of the system will be affected:
   1. In data access profile maintenance, a specific Read/Write Access rule has to be defined for every leave;
   2. In reporting, we will have to use multiple selections of members in Page Key range: more are the leaves that we have to select and more difficult will be to build and to maintain the report.
2. If we merge the hierarchies, instead:
   1. we will need to create a node for every intersection between functions and regions we need to consider in securities and reporting
   2. we will generate inconsistencies between BPC dimension and original dimension imported from ECC/BW
3. If we move the second hierarchy to a different dimension:
   1. If the enhancement is requested in an optimization phases, it will imply a massive review of every logic and report;
   2. It will provide additional value if we have to give the users the ability to build reports that are broken down along the geographic hierarchy as well as the functional hierarchy at the same time, but it will be only an additional complexity in case we only need to maintain securities (e.g. we will have an additional secured dimension in the model and this will also affect performances);
   3. It will make more complicated to read and save data: we will have to query the model from total PC sales and to save the data to the same profit centre selected in the entity dimension. This means that we may need to implement the Shared Query Post Process Badi and the Write Back Pre Process Badi.

4. The custom solution

In this section, I will assume you are already confident in creating BADI and writing ABAP for BPC.

As explained before, I will also assume that we have an entity dimension with thousands of members and many different hierarchical levels between the 2 hierarchies. 

4.1 How to generate dynamically a deny rule using the BADI_UJE_DYNAMIC_DAP?

As explained in the introduction, I want to dynamically generate a deny rule for every member that is not visible in the intersection of the two members.

E.g.: Access will be given to Europe and PC Sales and the badi will automatically add the deny rule for Europe PC Services and America Sales for Business.

In this way, user will be able to see only:

In the Dynamic DAP badi implementation I will maintain a filter to activate the enhancement only for the profile ids that require the hierarchy matrix security.

E.g.

ENVIRONMENT = SALES_PC

PROFILE_ID = GEO_FUNC_USER (the dynamic Data Access Profile ID)

In the implementing class instead, I will call the method to generate the Deny rules.

Following this approach only the profile IDs that start with A_ENT_D_FUNC will apply the new rules; all the others will work applying the standard behaviour. 

The “ct_access” internal table will already contain the member access rules defined in the Data Access Profile, which in our example will consists of:

1. Read/write access to Europe
2. Read/write access to Sales

In “exclude_method”, we will have to add the Deny rules, in order to give access only to the intersection of bas members visible in both hierarchies.

For each access rule, I will perform following steps:

• Get the bas members of the parent specified in the rule
• Identify the hierarchy to which he belongs
• If the bas member is found to be in both hierarchies, mark it as accessible
• Generate a deny rule for every remaining bas members that are not owned in both hierarchies
• Generate a deny rule for every parents that have only denied bas members below

4.2 How to recalculate totals with the Denied member value using UJ_SQE_POST_PROCESS Badi?

The totals, shown in BPC reports, will still include the value on Denied Members: therefore we need to use the Shared Query Badi to recalculate them.

In the filter of the badi we can only specify the Environment and the Model; therefore in the ABAP we will have to force the badi to work only for users who have a data access profile starting with A_ENT_D_FUNC assigned.

Therefore in method IF_UJQ_SQE_POST_PROCESS~POST_PROCESS you will have find if the user has the Hierarchy Security Matrix Data Access Profile and execute a different logic for this kind of users.

The post process method will execute an additional read to get the value of denied members and then it will remove this value from the total. Below the steps in details:

• Replace selection of parent Entity member with his denied bas members. Please note that the selection of the entity can be both in:
  • it_axis: if entities are exploded in one of the axis
  • it_slicer: if entity is selected in context or in page or it is a single member in axis
• Read the Denied members value, bypassing security (boolean parameter i_pass_by_security of standard method if_ujo_query~run_axis_query_symm is set to true).

    

• Remove the denied members values from the original query:
  • If the entity is in slicer, just remove the total value on the denied member
  • If the entity is in axis, look up for the parent of the denied member in the axis and decrease it

5. Conclusion

I wanted to give an example of the flexibility offered from SAP BPC as a planning tool, which provides you the possibility to implement complicated scenarios as the one described above.

As a tool it provides so many ways to reach a business requirement and it is up to the solution architect to choose the one to achieve the best result in term of usability, performances and maintenance cost.