UME Mass User Creation/Data Export Import

Applies to:

SAP NW Portal UME

Summary

Mass User Creation of Portal Users, esp. involving transfer of UME user master data from one portal system to another may prove to be a tough task and turn out to be manual and laboriously task unless one has knowledge of Java and Java Beans to programmatically control UME behavior. In this document I am explaining the method through which, with help of excel function, we can streamline the process of user creation involved in UME export and import. This is applicable for selective user creation of an user list out of total user base of one system into another. Also applicable for mass user creation in any portal if we have user attribute matrix ready.This may not be a clean or neat solution but hope this helps and helps and gets you initiated in case you are working on scenarios as described in the document.

Author(s):           Prashant Tripathi

Company:           IBM India Pvt. Ltd

Created on:        23rd May 2013

Author Bio

Prashant Tripathi is working in IBM and has about 5+ yrs of experience as a SAP Security consultant working on a variety projects and SAP platforms/products.

First step is to export user master data from UME:

Please note: Recommended to review and change (if applicable) value of following parameters to allow all users to be selected:

ume.admin.search_maxhits

ume.admin.search_maxhits_warninglevel

The key issue is that you have to create some of the users of the above list in another system. Say total no. of users exported from above list is 9000 and there are 300 users who are part of the above list but are not available in another system which is now going to replace the current system with 9000 systems as the new QA system for quality assurance/integration testing. For simplicity lets assume that portal groups and roles are same in both the systems.

It would be a big manual effort on your part to either try to compile the list of all the users along with their attribute mapping from the export file since the initial data cannot be easily filtered into data which we require for those 300 users. With this being the issue – I have tried to use excel functions and come up with a standard complete user attribute mapping excel format for all of the users in system which can then be used as a platform to extract data for the 300 users using vlookup. Finally you can remodify the vlookup data into the format that UME uses for export. All these are explained step by step in following pages:

Stage 1: Understand the total no. of attributes in your list:

You can check this by checking for occurrence of uid row in your list

Excel Formula involved:

=IF(ISNUMBER(SEARCH(C$1,$A2)),$B2,””)

and once you get the row numbers – just subtract the two successive row numbers till end of the list:

Here column A contains the row numbers where uid occurs, column B contains difference between two consecutive entries in column A and column C is sort of column B by value in descending order  – signifying that there are 12 user attributes at max which are in present in original user master sheet (Discard [User] identifier – which is not an attribute)

Stage 2: Once you know by the Math involved in stage 1 – that at max say for example there are 12 rows difference between one set of user attributes and another – then we know that at max there are 12 attributes involved in your original list.

Now prepare 12 columns with column header being the user attributes that are in involved in the original list. This will ensure that for an user whether that attribute is present or missing – we have a standardized format where for all users – all attributes are listed – so that we can quickly assess the whole system in terms of user attributes without need to do a control + F repeatedly.

Just as we did in stage 1 – find out row numbers for each of the 12 columns:

(3) Further Math to determine every user master row:

Now copy the above data as value in another sheet:

And use the following formula to determine the unique row numbers for each record:

Column P is subtraction of two consecutive rows and let column Q containing uid values remain the same, for column R onwards use following formula:

=IF(MIN(INDIRECT(“c”&($Q2)):INDIRECT(“c”&($Q3)))<>0,MIN(INDIRECT(“c”&($Q2)):INDIRECT(“c”&($Q3))),0)

This selects in Column C – which is last_name (refer figure before the immediately previous figure) –the value which falls between two occurrences of uid so that we get the actual row number for last_name in the same row – just to the right of uid. (As we want user master in one row per user)

Similarly apply this formula for each of the user attributes and find the row number in the same row for each of the unique user master record.

Finally apply the offset formula to get the row number values:

=IF(ISERROR(OFFSET(Sheet1!$A$1,Sheet4!Q2-1,0)=TRUE),””,OFFSET(Sheet1!$A$1,Sheet4!Q2-1,0))

This formula implies that in sheet 1 (original list of UME export data) search for row number contained in cell no. Q2 and output the value contained there.

Please note: “Q2-1” implies that row no. for last_name is 3 but from 1st record its 2 hence “3-1”.

Similarly follow this formula for rest of columns and get the value:

Stage 4: Finally after you have got the user master in one row – all columns – for each user record – copy the data and paste it as value in another sheet.

Then apply vlookup for those 300 users that you were searching for and extract user master attributes for all these users in one go!!

Stage 5: Returning the data into UME import-able format:

Last stage involves recreating the user data in UME format so that we can create users from one system to another selectively provide portal group and portal roles are already available in the target system where the missing users are to be created.

Copy the vlookup data of stage 4 as value in another excel sheet:

Then create some additional columns as described in following figure:

Column M – is for offset function to be used in Column Q

Column N – its just to check the total no. of attributes for an user per row

Column O – is for offset function to be used in Column Q. Its calculated as following:

=QUOTIENT(M2,13)+0 -> it is used for finding out the right row number in column A from where data is to be offset

Column P – is for offset function to be used in Column Q

=IF(MOD(M2,13)=1,0,IF(MOD(M2,13)=0,12,MOD(M2,13)-1)) -> calculates the attributes from 1 to 12 for each row. 13 is used because there are 12 attributes and one more row for [User]

Finally we have the data ready for using the final excel function in column Q:

Formula:

=IF(P2=0,”[User]”,IF(P2<>12,OFFSET(INDIRECT(“A”&($O2+2)),0,P1),OFFSET(INDIRECT(“A”&($O2+1)),0,P1)))

Formula Explanation:

• (a) 1st requirement is to ensure first row is always “[User]”.  Hence the check on p2=0
• (b) 2^nd requirement is to identify the right row and right column for offset function, for this we are using combination of column O and column P – here we have a sample size of 243 users i.e 243 rows – which means we are going to have 243×13 rows in column Q (1 for [User] and 12 for attributes).

  Formula is to read from column A – left to right – sequentially all the user attributes till the last column attribute Group and then to move over to next user master record row – > which always occurs when value in P column is 0 (whenever remainder of division of value in column M by 13 is 1) -> Refer row 14 in one of the following figures also highlighted by the following sign:

Stage 6: Next step is to  copy the data into another excel sheet as value and import it into target system:

You can filter out the blank values and #N/A (which came up because of VLOOKUP returned #N/A for those users which were not part of UME export list from source system) before you import the data and may want to test importing initially in a batch size of 1-5 users – just to do spot checks and ensure portal groups/roles are in place for the import to happen successfully.