Hi Community,
I hope all is well! I am looking to devise a strategy for a sound ODI 12c-centric integration solution for automating Security with PBCS. There is some literature on this (noted at the end of this abstract) but it is very high-level and perhaps overly simplistic - I am in need of a little more information/details to get me confident to move forward with an elegant and robust solution.
We will be aiming to integrate an On-Premise ODI 12c solution with EPBCS (current is an on-premise Planning application).
The Current State
1. The current state (on-premise ODI with on-premise Planning) makes use of the ImportSecurity utility to load the SecFile.txt. Shared Services in Planning is updated when the ImportSecurity.cmd file is run on the server with the latest security.
2. Contract is a dimension, and we have security enabled for the contracts (Dimension members) each contract (each has its own COR (person tied to ). Users are assigned to security groups that have designated READ, WRITE etc access to certain members, descendants of members.
3. ODI Load Plan steps are initiated in sequence to get the load to the final target load state. A series of MDX scripts called by Windows bat and SQL procedures/packages are invoked to get the security and its users/CORs to be loaded and reflected properly in Shared Services.
4. New Contracts/Users to Security Groups process is controlled via LDAP and Active Directory. COR user security for the Planning application is generated automatically with a connection through Microsoft Active Directory. An LDAP query is run to pull all user first and last names and corresponding network IDs from Active Directory (AD). The COR Contract Mapping step is used to populate the SPENDPLN_COR_Contract_Mapping table, where CORs are matched to their corresponding contracts. This step also creates the SPENDPLN_COR_Security_Group_Import.mxl file on SD1 which contains the code to add users to contract groups. The second step, Execute SPENDPLN_COR_Security_Group_Import.mxl executes the MAXL file to add the new security in Shared Services.
Risks/Considerations In Need Of Triage
1. I am aware that many would suggest to move everything over to LCM in EPM Automate on the application side (non-ODI) - however a lot of the current functionality is controlled in ODI. Given the SQL transformations that occur to manipulate flat files and data for final transformation and load into EPBCS via the load utility - I would hate to have to re-develop this entire solution on the application side.
2. There is no LDAP for EPBCS - so how would an ODI-centric design authenticate the user when importing entries from ODI into EPBCS?
3. I am aware that EPM Automate replaces the use of MaxL for these purposes - would this require the use of EPM Automate solely to automate this process? I am going to assume custom Groovy scripts will have to be devised in JSON format to physically load the data in .json to EPBCS from ODI?
4. Is this design going to require sole RESTful API design - not just EPM Automate wrapper - for this functionality to work? What RESTful calls will I need to replace what the Load Utility does?
5. Performance-wise - can RESTful handle in just one API call a load file with multiple records for each load - or would this require several asynchronous RESTful calls for every record in a flat file?
6. What Groovy scripts will I need to devise likely for this to work?
7. Are there max security groups an instance of EPBCS can handle?
Conclusion
Overall, just looking for some sound guidance to determine how this can work as I'd hate just to maintain all security in EPBCS-alone with EPM Automate . Any help from the community will be appreciated!
Oracle Support/Metalink Articles
Mass Import Security Access into PBCS or EPBCS (Doc ID 2293345.1)
Export/Import Security in PBCS (Doc ID 1993255.1)
Other Links
http://www.orahyplabs.com/2017/03/pbcs-quick-tips-bulk-upload-security-permissions-pbcs.html