Create a table with users and group users

[Former Member]

Hello people,

I'm working with sap bo 4.1 SP5 IDT and I'm trying to develop a multi-source universe populated with 2 tables:

1 table will come from an Excel and it has the column USER_GROUP and some other attributes and measures

The other table, and the one I have problems to develop, should be an autogenerated table with 2 columns, USER and USER_GROUP. I've been trying to create this kind of table with the variable @BOUSER, but I can't find the variable for the USER_GROUP.

I've been searching to create a List of Values table populated with a Query that returns both the USER and USER_GROUP from some table from the CMS repository, but 1) I cannot find the correct query and 2) I've read that CMS tables are encrypted, so I don't know where to look now

Anyone knows where can I find or how to create a simple table with USERS and GROUP_USERS from the BI Platform?

Thanks all in advance.

[former_member207052 · Accepted solution]

1) I cannot find the correct query and 2) I've read that CMS tables are encrypted, so I don't know where to look now

There is NO way to read users/groups from CMS database directly and load them into your table(other than going through query builder or sdk and some ETL jobs).

But first, what are you trying to achieve? to configure security? There are multiple ways you can secure your data including

1. Security Editor in IDT & Profiles (user and/or group level)
   1. when a new user is created and added to a group automatically he/she can see the data depending on the group's access.
2. Database security (configure db credentials in user's BO enterprise account) (user level)
3. Custom security (user level)
   1. Create a security table with the username and what they see configuration.
   2. Create a mandatory filter that will be applied across the entire universe.
   3. Remember, you can do this only at user level and not at group level (with @ BOUSER)

you can refer standard IDT user guide to learn more about the first method which might be right way for you.

[Former Member · Accepted solution]

My final objective is not to provide security but some sort of personalization for the user who opens the report. Ex: User X opens the report, apply X filters to the report, user Z opens the report, apply Z filters to the report.

If I create a user list I don't want to maintain it, thats what I want a User_Group list, cause user_groups are more stable than users.

Regarding to the first option you wrote:

Narashimman K S wrote:


There is NO way to read users/groups from CMS database directly and load them into your table(other than going through query builder or sdk and some ETL jobs).

Can you please give me more information about this? I have found this script SAP BusinessObjects User and Group list in Excel | SCN but I get an error when executing it.

[former_member207052 · Accepted solution]

Got it. Remember, there are chances that once user might belong to different groups and you may need to consider this while doing the personalization.

I cleaned up the code a bit and the script is available in the below location. Tested & works for me.

https://www.dropbox.com/sh/zel3fjht7xetrwt/AABbDDDv3d6RFq9LVw9BHCSLa?dl=0

Additionally, you might be interested in java sdk, check the below link for sample.

Java BusinessObjects Enterprise SDK Samples - Business Intelligence (BusinessObjects) - SCN Wiki

[former_member207052 · Accepted solution]

Additional Notes:

Change the server details and file name in the vb script. If you are using windows 7 or 8 (basically any 64 bit platforms) make sure you are using 32 bit cscript executable.

To execute: open command prompt and type the below command after modifying the script.

c:\Windows\SysWOW64\cscript.exe c:\users\YOUR_NAME\Desktop\test.vbs

Optionally, you can enhance the script to replace the excel file, get rid of the message boxes I included for debugging and many other such things.

Once the script works for you, you can directly insert the records into the database without writing it to excel file. You can create a batch file and schedule the file to run once every day or so. This will make sure your list is up to date.

Message was edited by: Narashimman K S

[Former Member · Accepted solution]

Thank you very much for your help Narashimman, but I have a problem executing the script. It gives me an error: ActiveX component cannot create this object:'CrystalEnterprise.SessionMgr'

Code: 800A01AD

I have windows 7, Excel 2010, and BI Client Tools 4.1 SP5.

[former_member207052 · Accepted solution]

did you follow my instruction in the above post?

To execute: open command prompt and type the below command after modifying the script.
c:\Windows\SysWOW64\cscript.exe c:\users\YOUR_NAME\Desktop\test.vbs

[Former Member · Accepted solution]

Yeah, finally I could obtain the list of Users and their groups, however, the format its a bit strange because the groups are spreaded all around the Excel. Ex:

But well, thank you very much, this is a bit step.

Now I will try to develop an script for schedule this and make some magic with Data Services to read the Excel and obtain something similar to this table:

USER - USER_GROUP

X1 - Everyone

X2 - Everyone

X2 - Administrators

X2 - Cryptographic Officers

X3 - Everyone

...

...

Thanks

[former_member207052 · Accepted solution]

It is not a big deal to get the results in the format you expect. Will see if I have some spare time and will post the updated code.

[former_member207052 · Accepted solution]

I've updated the script and it is available here.

The output will be something like below :

Now you have multiple options, choose the one that works best for you.

Directly write these results to your database (without DS) by updating the vb script or

you can use this excel as source to build your DS workflow as you mentioned or

Just schedule & save the excel in a shared location and build a unx on top of this.

[Former Member · Accepted solution]

You are awesome Narashimman. Thank you very much!!!

[Former Member]

Just visit the following link :

http://scn.sap.com/community/bi-platform/blog/2012/10/11/businessobjects-query-builder-queries

there you will find the required info as below:

User/UserGroups queries

To find the number of users in a group

SELECT SI_NAME,SI_GROUP_MEMBERS FROM CI_SYSTEMOBJECTS

WHERE SI_KIND = 'USERGROUP' AND SI_NAME='ADMINISTRATORS' 

To extract all the users from specific user group

SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS
WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='ADMINISTRATORS'")

[Former Member]

Thank you swapnil, I've playing with the query builder but I cannot manage to list the users and their user_group in only one query. I can list both the users and the user_groups but I don't know how to join them.

By the way, how can I implement the Query Builder on the Universe?