Does anybody know which repository table stores the database connection information to a universe for a specific user?
In the SUPERVISOR one can right click on the universe and select PROPERTIES in order to set different connections for different users. Does anybody know where this is stored in the repository tables?
Samita Chitre
Braun Technology Group
251 North Illinois Street
Suite 1200
Indianapolis In 46204
(317)-822-4507
Listserv Archives (BOB member since 2002-06-25)
There seems to be some confusion about universes that describe the repository tables. For some reason, BOI does not seem to be very organized or forthcoming about these. Through discussions with local reps and the help desk in San Jose, we have ended up with 3 different universes at our site: 1. prodrepo - I was told this was “old” and was not accurate when I could not get the results I wanted. A colleague says the only problem she had with it was that it kept records of users even after they were deleted. It uses the obscure object names such as M Actl N Actorid. So San Jose sent me this one …
2. Manager0- This works great but does not have all of the tables that were included in the prodrepo universe (no user information). But it does have “English” object names that are easy to understand. 3. userunv - In the meantime, I found out this one was sent to my colleague. It is very similar to Manager0 and it does contain user information. So this is the one I am using now. I have had to change some of the joins to “outer joins” to get the correct results.
Perhaps BOI technical support can clear up the confusion at the conference in Orlando.
If you want any of these, I can send them to you via email.
Regards, Linda Caron
Listserv Archives (BOB member since 2002-06-25)
In a message dated 98-08-24 10:41:49 EDT, you write:
Does anybody know which repository table stores the database connection
information to a universe for a specific user?
In the SUPERVISOR one can right click on the universe and select PROPERTIES in order to set different connections for different users. Does anybody know where this is stored in the repository tables?
Samita Chitre
Well, I’ve been carrying this email around in my inbox for quite a while now meaning to get to an answer… and I think I have one. After much searching and prowing through the repository documentation (available in HTML form on www.busobj.com, or download and unzip to keep a local copy as I have) the following SQL code should give you what you need.
Warning, it isn’t pretty!
The result columns are the user name, the universe affected, the original universe connection, and the remapped connection name. Getting to the names instead of the ID’s is what causes the code to be so involved. Note: the connection table is used twice, once aliased as “original_connection” and the second time used as “remap_connection”.
select obj_m_actor.m_actor_c_name
, obj_m_universes.m_uni_c_longname
, orig_conn.m_cntn_c_name original_connection
, remap_conn.m_cntn_c_name remap_connection
from obj_m_univcst
, obj_m_reslink
, obj_m_actorlink
, obj_m_actor
, obj_m_connection remap_conn
, obj_m_connection orig_conn
, obj_m_universes
where obj_m_univcst.m_unic_n_unilinkid = obj_m_reslink.m_res_n_id and obj_m_reslink.m_res_n_resid = obj_m_universes.m_uni_n_id and obj_m_reslink.m_res_n_actlinkid = obj_m_actorlink.m_actl_n_id and obj_m_actorlink.m_actl_n_actorid = obj_m_actor.m_actor_n_id and obj_m_univcst.m_unic_n_cntid = remap_conn.m_cntn_n_id and obj_m_universes.m_uni_n_cntid = orig_conn.m_cntn_n_id
The tables involved are all in the security domain, and are listed next.
obj_m_univcst This is the “magic” table, details below obj_m_reslink Resource Links
obj_m_actorlink User Links
obj_m_actor Base User Table
obj_m_connection Connection info, aliased as “remap_conn” obj_m_connection Connection info, aliased as “orig_conn” obj_m_universes Base Universe Info
The obj_m_univcst table contains the list of resources in a universe that are remapped. One of these items could be a new connection string. It appears that if the connection string has been remapped then the column m_unic_n_cntid has a non-zero value. Otherwise the value for that column is zero. So only users with actual remapped connections should show up for this query. A user that has other universe limits redone but a standard universe connection should not show up on the list.
Note: no database specific functions or outer joins are used in this solution, so it should be portable to any repository struction.
Caveat: I have tested this sql in a very limited environment. You should test and verify under your own situation.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)
Hi everyone,
We are using Webi 2.5. I have set up the user activity log so that it goes to our repository database. I am using the Audit25 Universe found on the BO CD. To this universe I have added the Obj M Actor table so that I can pull an activity report of only currently active users. What I need now is to find where the Group that is set up in the Supervisor module can be found so that I can break down this report by group. Does anyone know which table this is stored in?
Thanks for your help!
Mike Ciance
CIT
Listserv Archives (BOB member since 2002-06-25)
One user might belong to Multiple groups right? In that case where will you display the user (under which group?). Just a warning!
To get the group information you can look at obj_m_actorlink table. for explanation about this table look at repository documentation freeware directory of BO cd.
Let me know if you need more info.
Vasan
Mike.Ciance@CIT.COM on 05/01/2000 09:59:18 AM
Hi everyone,
We are using Webi 2.5. I have set up the user activity log so that it goes to our repository database. I am using the Audit25 Universe found on the BO CD. To this universe I have added the Obj M Actor table so that I can pull an activity report of only currently active users. What I need now is to find where the Group that is set up in the Supervisor module can be found so that I can break down this report by group. Does anyone know which table this is stored in?
Thanks for your help!
Mike Ciance
CIT
******************************************************************************* Note: The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Ernst & Young LLP
Listserv Archives (BOB member since 2002-06-25)
Thanks Vasan. I’ll take a look at it.
We don’t have users in more than one group currently so that should not be an issue. Thanks for the warning though. I’ll keep it in mind if we ever do.
Mike
Listserv Archives (BOB member since 2002-06-25)
In a message dated Mon, 1 May 2000 9:57:28 AM Eastern Daylight Time, “Ciance, Mike” Mike.Ciance@CIT.COM writes:
<<
We are using Webi 2.5. I have set up the user activity log so that it goes to our repository database. I am using the Audit25 Universe found on the BO CD. To this universe I have added the Obj M Actor table so that I can pull an activity report of only currently active users. What I need now is to find where the Group that is set up in the Supervisor module can be found so that I can break down this report by group. Does anyone know which table this is stored in? >>
The group information is stored in the same table: OBJ_M_ACTOR. The relationships between users and groups (or groups and groups, since groups can contain other groups) is located in the OBJ_M_ACTORLINK table. The ACTORLINK table is recursive; it relates to itself. To use it you will have to determine the “deepest” level of recursion (meaning how many levels of user and groups there are in your current installation) and create aliases in your universe to represent this.
If you have access to the original BusObj distribution CD, you can find this and other information on the repository tables in the “freeware” directory.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)
Thanks Dave & Vasan,
I updated the universe this morning with the appropriate aliases and joins and ran my report including the group. It works great! Thanks again to both of you for your help.
Mike Ciance
CIT
The group information is stored in the same table: OBJ_M_ACTOR. The relationships between users and groups (or groups and groups, since groups can contain other groups) is located in the >OBJ_M_ACTORLINK table. The ACTORLINK table is recursive; it relates to itself. To use it you will have to determine the “deepest” level of recursion (meaning how many levels of user and groups there are in your current installation) and create aliases in your universe to represent this.
If you have access to the original BusObj distribution CD, you can find this and other information on the repository tables in the “freeware” directory.
Listserv Archives (BOB member since 2002-06-25)
Is there a way to convert the UTC times stored in DS_PENDING_JOB to ‘normal’ date formats?
Joanne
Listserv Archives (BOB member since 2002-06-25)
In a message dated 00-11-02 13:38:59 EST, you write:
Is there a way to convert the UTC times stored in DS_PENDING_JOB
to ‘normal’ date formats?
Yup.
Oh, so you want a formula? 
If memory serves, you are using Oracle, correct? The UTC format is stored as the number of seconds since midnight on Dec 15, 1970 (I think). So to convert the UTC format to a real date, do this:
to_date(‘15-DEC-1970’,‘DD-MON-YYYY’) + (ds_pending_job.date_field / (60 * 60 * 24) )
In other words, start with Dec 15, add the number of days shown in the UTC format. Converting the UTC format to days is done by dividing by (60 * 60 * 24), which is the number of seconds in a minute, number of minutes in an hour, and number of hours in a day.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)
Joanne,
I don’t remember the formula, but if you search the Tech Support web site for UTC, you should find what you need.
Michael Welter
Sr. Technical Analyst
Verizon Wireless
Is there a way to convert the UTC times stored in DS_PENDING_JOB to ‘normal’ date formats?
Listserv Archives (BOB member since 2002-06-25)
Hi Joanne.
There’s a calculator floating around Business Objects that can convert these date/time values. You might ask the BO S/E about it. It will converts one date at a time (in either direction, utc to normal or normal to utc). Additionally, tech support should (that’s a big should) be able to point you to an explanation of the algorythm used to convert UTC time code.
-John Harris
-American Honda Motor Co.
Listserv Archives (BOB member since 2002-06-25)
Dave’s answer is, as usual, the right one. Just thought I’d throw in the actual document from tech support.
Listserv Archives (BOB member since 2002-06-25)
While we are talking about this -
I have universe defined against the repository tables but I have not been able to produce a simple query producing username and configuration (like whether they have access to BO,WebI, both).
I know this can be done from the Supervisor module but I am trying to produce a nice clean/simple format.
username, bo, webi
username, bo,
username, , webi
any ideals?
Thanks, Jeff
***************************************************************************** CONFIDENTIALITY NOTICE: This e-mail transmission, and any attachments, is intended only for the use of the individual or entity named above and may contain information that is confidential, privileged and exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of any of the information contained in this transmission is strictly PROHIBITED.
If you have received this transmission in error, please immediately notify us at postmaster@kci1.com
Kinetic Concepts, Inc.
Listserv Archives (BOB member since 2002-06-25)
In a message dated 01-06-11 11:03:59 EDT, you write:
I have universe defined against the repository
tables but I have not been able to produce a simple query producing username and configuration (like whether they have access to BO,WebI, both).
I know this can be done from the Supervisor module but I am trying to produce a nice clean/simple format.
username, bo, webi
username, bo,
username, , webi
Remember that each user can exist in multiple groups, so your report really has no meaning without the group name as well. You need the group name, the user name, and the role the user has in that group. The information that you need is contained in the OBJ_M_ACTOR and OBJ_M_ACTORLINK tables. These tables are recursive, so you will use each of them twice. The SQL looks something like this:
select users.m_actor_n_id user_id
, users.m_actor_c_name user_name
, link.M_ACTL_N_ACTORTYPE user_role
, groups.m_actor_c_name group_name
from obj_m_actor users
, obj_m_actorlink link
, obj_m_actorlink parent
, obj_m_actor groups
where users.m_actor_n_type = 16
and users.m_actor_n_id = link.m_actl_n_actorid and link.m_actl_n_fatlinkid = parent.m_actl_n_id and parent.m_actl_n_actorid = groups.m_actor_n_id
The restriction for actor type = 16 means that group definitions are ignored.
This select gives you the user name, the code for their role, and their group where they perform that role. The important piece is in uppercase: M_ACTL_N_ACTORTYPE. That field contains a “bit map” value, where the binary bits denote which applications are on or off. The values, according to the documentation on my cd, are:
BOAT_GENERALSUPERVISOR 2
BOAT_SUPERVISOR 4
BOAT_DESIGNER 8
BOAT_USER 16
BOAT_DS 32
My docs are old; there is an additional value of 64 that means “Versatile”. I don’t remember which version of BusObj introduced the concept of a versatile profile.
To make matters more confusing, these values can be combined. Someone that is both a Supervisor and a Designer would have a value of 4 + 8 or 12. If you wanted to get fancy, you would do a binary bit test for each value. Since there are only six “interesting” values, I would simply hard code the items in a decode / case / other RDBMS equivalent. For example, for Oracle my final query looks like:
select users.m_actor_n_id
, users.m_actor_c_name
, decode(link.M_ACTL_N_ACTORTYPE,
2, ‘General Supervisor’,
4, ‘Supervisor’,
8, ‘Designer’,
12, ‘Supervisor/Designer’,
16, ‘Business Objects’,
32, ‘BCA Queue’,
64, ‘Versatile’, ‘Other Unknown at this time’) User_Role , groups.m_actor_c_name group_name
from obj_m_actor users
, obj_m_actorlink link
, obj_m_actorlink parent
, obj_m_actor groups
where users.m_actor_n_type = 16
and users.m_actor_n_id = link.m_actl_n_actorid and link.m_actl_n_fatlinkid = parent.m_actl_n_id and parent.m_actl_n_actorid = groups.m_actor_n_id
You can “presume” the applications available for any of these profiles. Anyone has access to BusinessObjects / Webi. Designers have access to BO and Designer. And so on. The only role where you cannot “presume” the applications is Versatile. For that role, you would need to look further into the OBJ_M_RESLINK table, joining to the OBJ_M_ACTORLINK table. The resource records are type 6. It gets more complicated from there, but if you are not using the Versatile user profile, you don’t need to worry about this part.
If you can replicate this SQL in your universe, then you can create this report.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)