Quick How-To Guide: Migration of Security from MS versions to NW Versions
The skill set required to be a solid subject matter expert in BPC is very wide ranging even wider than most think. Here is an example of concrete thinking and creative thinking. Many would say there is no migration path for security from MS versions of BPC to NW versions of BPC. What?
Creative thinkers know that there is a path that makes it easier. There is a tool for migrating MS BPC Security to other MS BPC systems. It gives insight into tables and table locations, but is already largely automated. There is a tool that allows for the export of NW BPC security and the import of that information into other NW BPC systems. In other words, there is an “indirect” direct path for migration for those that are creative.
While not 100% aligned, macros and VB can easily cleanse the MS output to NW style csv files for import. With only one small outage between the systems this method improves security migration performance by almost 87% PER 100 USERS. (from 5 days to 3 hours – Many Users, A few Team and 3 other Profiles) In cases of multi hundreds of users, many teams and substantial numbers of profiles this concept has immeasurable benefit. The result of time savings only gets better as this tool never takes longer than 3 or 4 hours. That can be significant.
I offer 5 tips for creating the proper processes for moving your security structure in MS BPC to NW BPC in literally hours instead of days. You should be able to gain the insight necessary to create your own ideas for tools that will reduce your security migration to minutes instead of hours.
Tip 1 – Understand The Basics
The first thing to know is that all the information that the Net Weaver BPC security system needs to have is in the Microsoft System. You should know where the tools are in the how to guides from the SAP EPM-RIG group.
Download these and install the Microsoft items into BPC as required and download the ABAP tool for the Net Weaver platform. You are on your way. You also need to get access to the Microsoft SQL server database that supports your appset. You will need to access some tables directly for read access so that you can complete this task.
Tip 2 – The Outage To Fill
There are two tables that the BPC tool (OSoftTaskUserSecurity.dll) for MS does not extract. You need to brush up on your SQL or you need to remember how to CUT and PASTE, and go to table SecuTaskAccess and to table SecuTaksRoleAssign. Download these to excel or a txt file. You will need them to complete your files for Net Weaver import.
Tip 3 – Know your Mappings
Why not just be transparent. You can build a tool yourself or you can cut and paste your way to a file structure that will work. It is your choice. Even cutting and pasting this data is far superior for BPC implementations of 100 users or more and several teams and access profiles vs. hand entry of all new security. Certainly for a few users and very few teams, there is no need for a special tool or thinking. In our practice we have encountered or 600 users in the BPC system and more than 150 member access profiles. We felt it would take weeks to hand re-key this data. That is when this tool had its genesis. We cut and pasted our way to a solution then wrote VBA to automate the whole thing. That effort took 3 days. The migration of security took 4 hours. Here is what you need to know.
1. Migration of Users
The table extract tool provides output from Microsoft in a text file called tblUsers.txt. The Net Weaver BPC system requires three of these fields for its users.csv file for import. The MS version supplies more fields than are required.
2. Migration of Teams
The table extract tool provides output from Microsoft in a text file called teams.txt. The Net Weaver BPC system requires these fields for its teams.csv file for import. These files are 100% aligned with the exception of the name.
3. Migration of Team Assignments
The table extract tool provides output from Microsoft in a text file called userteamassign.txt. The Net Weaver BPC system requires these fields for its teamassignments.csv file for import. In the MS file, the file has one extra field and the value Y in the Microsoft file must be changed to X in the NW file to signify the team leader.
4. Migration of Task Profiles.
Here is where you need to get your thinking cap cleaned up and put on. The table extract tool from Microsoft does not provide all of the output you need in a single text file. You need to go and gather some additional information. It takes several sources of data from the Microsoft tool to get all that you need.
- From profiles.txt from the Microsoft tool you will need to have the records that are marked as Profile Class TSK . These become the single H record for each TSK profile in the taskprofiles.csv file for the NW version. Be careful to not migrate the SystemAdmin or PrimaryAdmin TSK entries as they will already exist in the system. You should manage those directly in NW for the kind of management access you want to provide to those administrator roles.
- The Data from the SecuTaskAccess table in the MS version of BPC is equivalent to the K records required for the taskprofiles.csv file. That mapping is 100% aligned except that you need to add the K record in the first column of your spreadsheet model.
- The Data from the SecuTaskRoleAssigns table in the MS version of BPC is equivalent to the R records required for the taskprofiles.csv file. That mapping is 100% aligned except that you need to add the R record in the first column of your spreadsheet model.
- The data from the UserTeamProfilesAssign.txt file from the Microsoft tool will be used to create the T and U records in the taskprofiles.csv for the NW platform. Be sure that your cut and paste or tool only extracts assignments of teams and users to profiles where the profile is a TSK type and not SystemAdmin or PrimaryAdmin. This is tricky programming and layout thinking, but there is a solution if you want this automated. You will need to convert Y to T and N to U for inclusion in the final taskprofiles.csv file for NW import. You can use UserProfile.txt for the U record if you want an easier programming effort.
5. Migration of Member Access Profiles.
- The data from Memberaccess.txt from the Microsoft platform maps the data required for the memberAccessProfiles.csv for the NW Platform. A quick examination of the layouts offers a quick program VBA to rearrange the data into the format and record types required. The value for Read / Write in MS is 2 and the value in NW is W. You may need to create some artificial data so that you can see how the values for read and write access are translated from numbers in MS to Alpha in NW. When you see the data you will see the solution.
- The data from the UserTeamProfilesAssign.txt file from the Microsoft tool will be used to create the T and U records in the memberAccessProfiles.csv for the NW platform. Be sure that your cut and paste or tool only extracts assignments of teams and users to profiles where the profile is a(n) MBR type. This is tricky programming and layout thinking, but there is a solution if you want this automated. You will need to convert Y to T and N to U for inclusion in the final memberAccessProfiles.csv file for NW import. You can use UserProfile.txt for the U record if you want an easier programming effort.
6. Migration of Task Profile Assignments.
You can migrate assignments of teams and users to task profiles in a separate file called taskprofileassignments.csv. You would refer to item 4(1) above for how that data can be extracted from Microsoft files and mapped to this file.
7. Migration of Member Access Profile Assignments.
You can migrate assignments of teams and users to member access profiles in a separate file called memberAccessProfileAssignments.csv. You would refer to item 5(2) above for how that data can be extracted from Microsoft files and mapped to this file.
All of this can be executed for clients by clients with a cut and paste operation that substantially improves the time to migrate. For consulting firms routinely involved in migrations and management of BPC systems, a programmed spreadsheet using VBA is possible. Depending on your true BPC skill set, which should include a certification in a control language like PL SQL or VB or C++, this programming effort might not take any longer than a week. This tool will reduce your security migration portion of your assignments to literally minutes. I know it has for us.
Tip 4 – Put your Data Together in a Spreadsheet
The best method for doing this once you have all the files you need from the tool and your direct SQL query process for the two other tables, you should import the data from the text files as show.
Figure 1. Here is a Primer And Hints
Tip 5 – Write your VBA to Create your Output Files
From here it is simply recording macros in some cases and in a few cases doing some for next or other types of loop data examination and manipulation to make sure you get all files into the proper format for the csv files required by Net Weaver for BPC.
Figure 2. Buttons that call recorded Macros or VBA code to cleanse and format Microsoft Files to NW Files