BusinessObjects Board

repository tables definition

security Supervisor (4.x - 6.x)
#1

Hi,
I have to query the repository tables of BO 5.1 to get information on reports like the number of tabs in the reports, number of dataproviders, are there any alerters used in the reports etc.
There are about 50 tables for the repository with tables like obj_m_documents, unv_universe etc.
I do not know the column definitions and what they store.
Could someone please provide me with a document of the complete definitions list for all columns of all tables for the repository. This is really urgent.

Thanks in advance.

Regards,
John Gonsalves

john_g :india: (BOB member since 2006-01-17)

Where do Report objects sit in the repository?
#2

Try using the universe. Available on BOB as a download;

SteveBickerton :canada: (BOB member since 2002-08-15)

#3

Thank you.
But that wasn’t of much help.
I am looking for specific fields in the reports like
1)Number of Data Providers and info
2)Number of Alerts used
3) Number of prompts used.
4) Number of objects used
5) Number of user defined objects used
6) Charts in reports
7) number of tabs in the report
8) are the reports sectioned?
etc.

Can someone let me know if such data is available from the repository tables.
It would be best if i got the definitions of each column in all tables because the column names are not of much help in figuring out these details.

Thanks and regards
John

john_g :india: (BOB member since 2006-01-17)

#4

The repository contains detailed universe and security information. Detailed report information is lacking.

I’m sure someone has a VB script that does some of what you are looking for. You most likely can find this on BOB too. You can also look on www.bonymaug.com and look for a presentation called Dr. Variable. But a more appropriate script is probably on BOB.

Does your company have EQM deployed? If so, this will provide some of the information you are looking for.

SteveBickerton :canada: (BOB member since 2002-08-15)

#5

Moved to Supervisor forum.

Nick Daniels :uk: (BOB member since 2002-08-15)

#6

Hi, i am struggling with this 6.5 repo universe! i am on version 6.5, and the following key table is missing OBJ_M_USRATTR. I know you mentioned that this table was not available in version 5, but i do not have it in 6.5 either. This is probabaly the only table i need? Do you know where i can get something similar for XI rel 2? I will tell you what i am doing and then maybe you have a better solution. I want to pull a report with user/groups and their security restrictions per Universe (row restrictions). ANY help would be appreciated!

thanks
Jason

JBasterfield (BOB member since 2006-06-28)

#7

Hi and welcome to B :mrgreen: B

After the migration from BO 5.x to BO 6.5 it may happen that so called “magnificent seven” tables are not created in the BO repository. OBJ_M_USRATTR is one of them.

For more infornation please have a look at this post and this one as well.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#8

Hi thanks for the welcome and pointing me to the other posts. I have had a quick look at them, but they do not mention what to do if you do not have the OBJ_M_USRATTR table in the repository universe? We did not do an upgrade, but rather a new installation. Could this be the reason? what can i do now? I really just need a way to get a list of users and groups along with the row restrictions for each universe (per user/group). Does you know if the Managero.unv some people have talked about will have anything different in?

Thanks again for replying!

JBasterfield (BOB member since 2006-06-28)

#9

Regarding “magnificent 7” tables:
Have you tried integrity check of the universe domain in Supervisor (menu Tools -> Repository -> highlight universe domain -> button Integrity)?

To create missing 7 BO repo tables you can use the following script:


create table UNV_TABLE_DATA ( TABLE_ID NUMBER(10) not null, UNIVERSE_ID NUMBER(10) not null, TAB_DATATYPE CHAR(2) not null, TAB_SLICE NUMBER(6) not null, TAB_DATAVALUE VARCHAR(240) not null);
create table UNV_OBJECT_KEY( OBJECT_ID NUMBER(10) not null, KEY_ID NUMBER(10) not null, UNIVERSE_ID NUMBER(10) not null, KEY_STATES VARCHAR(35) not null, KEY_POSITION NUMBER(10) not null);
create table UNV_JOIN_OBJECT( JOIN_ID NUMBER(10) not null, UNIVERSE_ID NUMBER(10) not null, KIND_OBJECT VARCHAR(35) not null, OBJECT_ID NUMBER(10) not null);
create table UNV_COLUMNS( TABLE_ID number(10) not null, UNIVERSE_ID number(10) not null, COLUMN_ID number(10) not null, COLUMN_NAME varchar(110) not null, COLUMN_PROP varchar(35) null, PKCOL_ID number(10) null, COL_POSITION number(10) not null);
create table UNV_COLUMN_DATA ( COLUMN_ID number(10) not null, UNIVERSE_ID number(10) not null, COL_DATATYPE char(2) not null, COL_SLICE number(6) not null, COL_DATAVALUE varchar(240) not null);
create table UNV_OBJ_COLUMN( OBJECT_ID number(10) not null, COLUMN_ID number(10) not null, UNIVERSE_ID number(10) not null );
create table UNV_X_UNIVERSES( UNIVERSE_ID NUMBER(10) not null, UNI_N_FORMAT NUMBER(10) not null, UNI_N_BLOCKID NUMBER(10) not null, UNI_BLOB_CONTENT LONG RAW not null);
create unique index TABLE_DATA_PK on UNV_TABLE_DATA (TABLE_ID, UNIVERSE_ID, TAB_DATATYPE, TAB_SLICE);
create unique index OBJECT_KEY_PK on UNV_OBJECT_KEY (OBJECT_ID, KEY_ID, UNIVERSE_ID);
create unique index JOIN_OBJECT_PK on UNV_JOIN_OBJECT (JOIN_ID, UNIVERSE_ID, KIND_OBJECT, OBJECT_ID);
create unique index COLUMNS_PK on UNV_COLUMNS (TABLE_ID, UNIVERSE_ID, COLUMN_ID);
create unique index COLUMN_DATA_PK on UNV_COLUMN_DATA (COLUMN_ID, UNIVERSE_ID, COL_DATATYPE, COL_SLICE);
create unique index OBJ_COLUMN_PK on UNV_OBJ_COLUMN (OBJECT_ID, COLUMN_ID, UNIVERSE_ID);
create unique index X_UNIVERSES_PK on UNV_X_UNIVERSES (UNIVERSE_ID, UNI_N_FORMAT, UNI_N_BLOCKID);
comment on table UNV_TABLE_DATA is 'Table of data associated with a table for store the sql of table virtual';
comment on table UNV_OBJECT_KEY is 'Table containing universe objects-KeyObjects';
comment on table UNV_JOIN_OBJECT is 'Table containing the list of objects have a propagation of the outer join';
comment on table UNV_COLUMNS is 'Table containing universe table and columns';
comment on table UNV_COLUMN_DATA is 'Table of data associated with a column for store the sql or info';
comment on table UNV_OBJ_COLUMN is 'Table containing the column of table referenced by an object';
comment on table UNV_X_UNIVERSES is 'a universe in blob format';

However, the better way would be to build the BO repository again from the scratch.

Regarding Managero.unv:
Sorry I do not use it, so I cant help here. Maybe someone else will do.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#10

Tried the integri thing, but it says that it is fine?

Having a closer look at the universe. That Attribute table that i am missing is actually not going to help me.

Do you know how i go about finding out how to get a user/group listing, with each universe they have access to as well as the row level security enforced per user?

I need this for auditing purposes and cannot seem to get the correct info out of the repository tables? I can query either the 6.5 repo or the XI rel 2 repo?

Thanks
Jason

JBasterfield (BOB member since 2006-06-28)

#11

If it says it is OK then you do not need to create thise new BO repo tables, of course.

All what I can provide you with is this SQL:

SELECT   obj_m_actor.m_actor_c_name AS username,
         group_actors.m_actor_c_name AS group_name,
         obj_m_universes.m_uni_c_longname AS universe_name,
         obj_m_universes.m_uni_c_filename AS universe_file_name,
         'group' AS inherited_from
    FROM obj_m_actor,
         obj_m_actorlink recursive_link,
         obj_m_actor group_actors,
         obj_m_reslink,
         obj_m_actorlink,
         obj_m_universes
   WHERE obj_m_actor.m_actor_n_id = obj_m_actorlink.m_actl_n_actorid
     AND obj_m_actorlink.m_actl_n_fatlinkid = recursive_link.m_actl_n_id
     AND recursive_link.m_actl_n_actorid = group_actors.m_actor_n_id
     AND recursive_link.m_actl_n_id = obj_m_reslink.m_res_n_actlinkid
     AND obj_m_reslink.m_res_n_resid = obj_m_universes.m_uni_n_id
     AND recursive_link.m_actl_n_id != 1
     AND obj_m_actor.m_actor_n_type = 16
     AND obj_m_reslink.m_res_n_restype = 2
UNION
SELECT   obj_m_actor.m_actor_c_name AS username,
         '-' AS group_name,
         obj_m_universes.m_uni_c_longname AS universe_name,
         obj_m_universes.m_uni_c_filename AS universe_file_name,
         'Profile' AS inherited_from
    FROM obj_m_actor, obj_m_reslink, obj_m_actorlink, obj_m_universes
   WHERE obj_m_actor.m_actor_n_id = obj_m_actorlink.m_actl_n_actorid
     AND obj_m_reslink.m_res_n_resid = obj_m_universes.m_uni_n_id
     AND obj_m_actorlink.m_actl_n_id = obj_m_reslink.m_res_n_actlinkid
     AND obj_m_actor.m_actor_n_type != 1
ORDER BY 1, 2

that lists the users and their permissions to the universes. However I am not somehow confident that it is 100% correct. I copied it from some web page but did not have a time to check/correct it. Please review it if it useful for you in some extent.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#12

The query is giving not exact result. Can you give correct one…Please

prasadbi (BOB member since 2006-06-29)

#13

Hi,

It’s not possible to find this with simple SQL. You need three informations :

  • rights between users / groups and universes including a first level of inheritance
  • rights between users / groups and universes domains including a second level of inheritance
  • And finally if you have some universe overloads calculated rights on universes including a third level of inheritance !!!

Welcome in the security rights management under BO !
But remember under Xir2 it’s harder :wink: not harder but powerfull !

Regards

Universes with corresponding users in group level.