SAP BusinessObjects

EPM RIG

How-To Guide

How To… Use the BPC Mass User

Management Custom Task With SQL

Server Integration Services (SSIS)

Applicable Releases:

SAP BusinessObjects Planning and Consolidation, version for the

Microsoft platform

Applicable Releases: 5.1, 7.0, and 7.5

Version 1.10

September 2010

© Copyright 2010 SAP AG. All rights reserved.

No part of this publication may be reproduced or

transmitted in any form or for any purpose without the

express permission of SAP AG. The information contained

herein may be changed without prior notice.

Some software products marketed by SAP AG and its

distributors contain proprietary software components of

other software vendors.

Microsoft, Windows, Outlook, and PowerPoint are

registered trademarks of Microsoft Corporation.

IBM, DB2, DB2 Universal Database, OS/2, Parallel

Sysplex, MVS/ESA, AIX, S/390, AS/400, OS/390,

OS/400, iSeries, pSeries, xSeries, zSeries, z/OS, AFP,

Intelligent Miner, WebSphere, Netfinity, Tivoli, Informix,

i5/OS, POWER, POWER5, OpenPower and PowerPC are

trademarks or registered trademarks of IBM Corporation.

Adobe, the Adobe logo, Acrobat, PostScript, and Reader

are either trademarks or registered trademarks of Adobe

Systems Incorporated in the United States and/or other

countries.

Oracle is a registered trademark of Oracle Corporation.

UNIX, X/Open, OSF/1, and Motif are registered

trademarks of the Open Group.

Citrix, ICA, Program Neighborhood, MetaFrame,

WinFrame, VideoFrame, and MultiWin are trademarks or

registered trademarks of Citrix Systems, Inc.

HTML, XML, XHTML and W3C are trademarks or

registered trademarks of W3C®, World Wide Web

Consortium, Massachusetts Institute of Technology.

Java is a registered trademark of Sun Microsystems, Inc.

JavaScript is a registered trademark of Sun Microsystems,

Inc., used under license for technology invented and

implemented by Netscape.

MaxDB is a trademark of MySQL AB, Sweden.

SAP, R/3, mySAP, mySAP.com, xApps, xApp, SAP

NetWeaver, and other SAP products and services

mentioned herein as well as their respective logos are

trademarks or registered trademarks of SAP AG in

Germany and in several other countries all over the world.

All other product and service names mentioned are the

trademarks of their respective companies. Data contained

in this document serves informational purposes only.

National product specifications may vary.

These materials are subject to change without notice.

These materials are provided by SAP AG and its affiliated

companies ("SAP Group") for informational purposes only,

without representation or warranty of any kind, and SAP

Group shall not be liable for errors or omissions with

respect to the materials. The only warranties for SAP

Group products and services are those that are set forth in

the express warranty statements accompanying such

products and services, if any. Nothing herein should be

construed as constituting an additional warranty.

These materials are provided “as is” without a warranty of

any kind, either express or implied, including but not

limited to, the implied warranties of merchantability,

fitness for a particular purpose, or non-infringement.

SAP shall not be liable for damages of any kind including

without limitation direct, special, indirect, or consequential

damages that may result from the use of these materials.

SAP does not warrant the accuracy or completeness of the

information, text, graphics, links or other items contained

within these materials. SAP has no control over the

information that you may access through the use of hot

links contained in these materials and does not endorse

your use of third party web pages nor provide any warranty

whatsoever relating to third party web pages.

SAP NetWeaver “How-to” Guides are intended to simplify

the product implementation. While specific product

features and procedures typically are explained in a

practical business context, it is not implied that those

features and procedures are the only approach in solving a

specific business problem using SAP NetWeaver. Should

you wish to receive additional information, clarification or

support, please refer to SAP Consulting.

Any software coding and/or code lines / strings (“Code”)

included in this documentation are only examples and are

not intended to be used in a productive system

environment. The Code is only intended better explain and

visualize the syntax and phrasing rules of certain coding.

SAP does not warrant the correctness and completeness of

the Code given herein, and SAP shall not be liable for

errors or damages caused by the usage of the Code, except

if such damages were caused by SAP intentionally or

grossly negligent.

Disclaimer

Some components of this product are based on Java™. Any

code change in these components may cause unpredictable

and severe malfunctions and is therefore expressively

prohibited, as is any decompilation of these components.

Any Java™ Source Code delivered with this product is only

to be used by SAP’s Support Services and may not be

modified or altered in any way.

Document History

Document Version Description

1.10 Updated for version 7.5

1.00 First official release of this guide

Typographic Conventions

Type Style Description

Example Text Words or characters quoted

from the screen. These

include field names, screen

titles, pushbuttons labels,

menu names, menu paths,

and menu options.

Cross-references to other

documentation

Example text Emphasized words or

phrases in body text, graphic

titles, and table titles

Example text File and directory names and

their paths, messages,

names of variables and

parameters, source text, and

names of installation,

upgrade and database tools.

Example text User entry texts. These are

words or characters that you

enter in the system exactly as

they appear in the

documentation.

<Example

text>

Variable user entry. Angle

brackets indicate that you

replace these words and

characters with appropriate

entries to make entries in the

system.

EXAMPLE TEXT Keys on the keyboard, for

example, F2 or ENTER.

Icons

Icon Description

Caution

Note or Important

Example

Recommendation or Tip

Table of Contents

1. Scenario ............................................................................ 2

2. Introduction ....................................................................... 2

3. Step By Step Solution ...................................................... 3 3.1 Registering the task within Visual Studio................... 3 3.2 Configuring the Task within SSIS .............................. 6 3.3 Execute the Data Manager Package ....................... 12

4. Best Practice for using this tool ..................................... 15

Appendix A – Table Definitions ............................................ 16

Appendix B – What is Included with this How-To ................ 17

1. Scenario

This guide will explain how an admin user or a consultant can import and/or export security information with SAP BusinessObjects Planning and Consolidation, version for the Microsoft platform. SAP Business Objects Planning and Consolidations (hereafter refered to as „BPC_MS“) provides a frontend wizard based interface that administrators can use to export and import security. Tthis wizard only allows the adding or removing of a single user at one time. If there are hundreds or thousands of users for a particular BPC-MS system, adding that number of users will not be easy using the current user interface.

This guide provides a convienient workaround by making it more practicle to mass add users through the execution of a SSIS package with a Data Mamager custom task.

Caution: This tool will not provide all audit features so it SHOULD NOT be used in the production system. SAP recommends using it only for an initial implementation and only by an authorized administrator of customer’s system. After loading all user information in an initial load, it is strongly suggested that you delete this custom package so that no one can use it again in the production system.

2. Introduction

BPC_MS releases 5.1, 7.0 and 7.5 leverage the SQL Server Integration Services portion of Microsoft SQL Server 2005 or 2008 Suite to perform ETL(Extraction, Transformation and Loading) activities.

The custom task OsoftTaskUserSecurity(2008).dll allows the management of the mass security process. This custom task allows system admins to both export as well as import BPC user security information.

The database tables that this task focuses on are:

tblUsers – User Master Table

UserProfile – Profile Table

UserTeamAssign – User’s assignment to Teams

UserTeamProfileAssign – Table containing assignments of users and teams to Profiles

Profiles

Using BPC standard security interface, admins can add only one user at a time. The purpose of this custom task is to allow adding multiple users in one step versus adding each user individually.

The steps below were performed on a copy of ApShell (ApShell_7M) within a BPC 7.0, version for the Microsoft Platform system.

3. Step By Step Solution

Using the custom task to mass upload security is broken up into multiple steps.

1) Registering the task within Visual Studio 2) Configuring the task within SSIS 3) Executing the task

3.1 Registering the task within Visual Studio

1. Register the task by copying the file OSoftTaskUserSecurity.dll into two locations:

BPC\WebSrvr\bin (or PC_MS\WebSrvr\Bin)

as well as

“..\Microsoft SQL Server\90\DTS\Tasks\”

Note: In case of SQL 2008, copy OSoftTaskUserSecurity2008.dll to

“..\Microsoft SQL Server\100\DTS\Tasks\” folder

After finish copying to appropriate dll file, register OSoftTaskUserSecurity.dll into global assembly cache

(GAC).

Click Windows Start button and select Run, Type “Assembly” and click OK.

It will open the system’s “assembly” folder.

Drag and drop OSoftTaskUserSecurity(2008).dll from BPC\WebSrvr\bin folder into the open Assembly

folder.

Scroll down assembly folder and verify OsoftTaskUserSecurity(2008) exists in the folder.

Note: Name will be different based on the dll. Below screenshot shows two dlls are registered.

Note: SAP BPC 7.5 MS changed resource file name so user has to register old resource dll in BPC 7.5 MS system.

Copy attached Ev4ResSvrDmm.dll to <install drive>:PC_MS\WebSrvr\Bin folder.

Click Windows Start button and select Run and type the following command and click OK.

regsvr32 <install drive>:PC_MS\WebSrvr\Bin\ Ev4ResSvrDmm.dll

2. Open Visual Studio 2005(2008) and create an Integration Services Project

3. Right Click on the Toolbox and Click ‘Choose items..’ from the context menu.

Select the ‘SSIS Control Flow Items’ tab

Find the ‘OSoftTaskUserSecurity’ or ‘User Security Task 2008’ in the list and select the Checkbox.

4. Now the new custom task is visible in the toolbox.

And Drag drop or double click to add it.

3.2 Configuring the Task within SSIS

1. Double click on the task to open the Task User-interface within the Integration Services Project.

Note: If you are using BPC 75 MS release 7.5, it may prompt an ActiveX error message dialog box but click ok button and it will not make any issue.

2. Select the option Export Excel File from Table. The select the tables that you wish to export (these are the formats you will need to populate and later use to populate to mass import security).

Choose a folder path to where you will export your user security files.

Each table you export has a specific purpose and each of these tables and their various purposes are described later in the document.

(See Appendix A)

3. Click on the “Preview in the Table” button and you can then display the entries in each of the tables you have chosen to export.

You can choose which table you wish to preview by leveraging the preview in the table dropdown in the user security dialog.

Click on the OK button

4. Please set each property with proper value

5. To begin we will run the package in debug mode in order to export the files. Click on the button in Visual Studio to execute the package.

6. When the task is green and has completed successfully, you should then see files in the location that you specified in the task.

7. Now that you have exported the file formats successfully open up one of the files in Excel and make whatever necessary modifications are required.

8. Once you have made updates to the different files you have exported from the security tables you can then upload your modifications. In order to do this, create a new SSIS Package from within Visual Studio.

9. Next drag and drop the OSoftTaskUserSecurity task into the SSIS Control Flow Pane. Double click on the task.

Select the Load Data from Excel to Data radio button on the UI screen.

NOTE: Some tables don’t allow using ‘Append’ because of index of table.

Therefore, Using ‘Delete and Append’ is better for avoiding errors.

10. Save your configuration and hit the OK button. Save your SSIS Package and place it within your BPC File service on the application server.

Note: You don’t need to do all this steps if you just use the delivered SSIS package which comes with this how-to guide.

You can export and import using the delivered package. (Appendix B)

Place that DTSX file (SSIS Package) in the same location on the Application Server.

3.3 Execute the Data Manager Package

1. Login to BPC for Excel. From the Action Pane select Manage Data Maintain Data Management Manage Packages (organize list)

2. Right click in the Yellow Pan under Package Name and select the Add Package option from the context menu.

3. Once the Add Package prompt pops up select the dropdown option from the right hand side of the file input field. Select the package with the prefix User Security (The same SSIS Package attached to this guide) and hit the Open button.

4. Enter the other details into the prompt for the package you are adding to your BPC application.

Click the Add button and the Save button.

5. From the Home screen of the Action Pane click on Manage Data Run a Data Manager Package.

From the prompt select the Run button after you have selected our newly added package.

6. From the prompt you can then select from BPC for Excel (remember the prompt screen is entirely configurable) whether or not you wish to:

a. Export / Import

b. Append/Overwrite

c. Folder Path which determines where your files are stored

d. The tables you wish to export or import separated by commas

(See Appendix A for details)

Note: Again, some tables don’t allow using ‘Append’. Therefore, please export all data first and use ‘Overwrite (delete and append)’ option for avoiding errors.

7. Hit the Finish button and confirm your package ran successfully! You should now understand how to import and export the security data from a BPC, version for the Microsoft Platform system leveraging the attached tasks.

Note: To use Excel_UserSecurity (2008) package, application server must have Microsoft Excel program.

4. Best Practice for using this tool

Here is the best practice for setting user security based on the our experience,

Let’s assume there is no security information in the new application.

1. Add team, profile and all related information using admin console

2. Add just 1 users for each profile using admin console

3. Export all security tables into files using data manager package.

4. Read file using excel

5. Add users using current data in the table

6. Save excel file as txt file.

7. Import security file using data manager package.

Appendix A – Table Definitions

Profiles – A list of all of the profiles (both task and member access) in the system including their

descriptions

Teams – A list of all of the Teams and their corresponding descriptions

tblUsers – Table of Users containing all of the user’s metadata information (email, name, etc.)

UserProfile – This table defines the User to Profile mapping

UserTeamAssign – This table defines the mapping of Users to Teams

UserTeamProfileAssign – This table defines mappings of users and teams to corresponding

profiles.

MemberAccess – This table defines the data permissions for all of the applications within your

application set.

Caution – Manipulating Security in this fashion can be dangerous if the person who is performing this in

not knowledgeable with respect to the user management model in BPC.

Best practice is just to use this to add / remove users (tblUsers table) and to update user to team

mappings (UserTeamAssign)

Appendix B – What is Included with this How-

To

OSoftTaskUserSecurity.dll – New Security Task for SQL 2005

OSoftTaskUserSecurity2008.dll – New Security Task for SQL 2008 File_UserSecurity(2008).dtsx – a sample package for importing and exporting data using flat file

Excel_UserSecurity(2008).dtsx – a sample package for importing and exporting data using Excel file

(Note: Application server should have Excel program to use this package.)

UserSecurity_Export_Import.dtsx – Sample SSIS Package with Prompts pre-defined for BPC

Ev4ResSvrDmm.dll – Resource dll for BPC 7.5

Download the attached files

www.sdn.sap.com/irj/sdn/howtoguides