Hi,
can anyone tell me whether it is possible to get the universe access log from the repository tables. i mean like which user logged in, what time, which universe he accessed etc. i would be happy if any one can tell me the table name.
venira
venira 
(BOB member since 2004-12-07)
Object usage & access history is only stored in the Auditing database, which is available when Auditor is enabled as an Add-on to BOE Professional, or it is included with BOE Premium. The CMS repository has an “as-of” representation of all managed objects in the repository, and no “history” excepting the list of scheduled instances of reports currently managed.
Note: direct Querying of the CMS database through reporting tools, depending on the tool used, the security account permissions, and the RDBMS underneath it, can cause performance, stability, and reliability issues with the CMS Service if Table Locks or Page Locks occur by the tool (cursor usage is a typical example of this), and direct SQL access to the repository cannot take the Enterprise ACL-based inheritance security model into account, thus is definitely not recommended nor supported.
“Query” access to the managed objects in the repository should be done through the Administration SDK only, to allow the CMS services to manage all database usage, get performance the benefits of Caching, and consistently apply security. The “Query Builder” tool you see (example JSP application) calls the SDK and does not make a JDBC call to the database directly.
Note: if you need to get the “object metadata” from the CMS repository on your reports or other managed content into a structure suitable for directly building reports, there are several partner tools from APOS and others that do just that.
snagoski 
(BOB member since 2004-12-13)
So. if my understanding is not wrong, it is not possible to et the userlog of designer from the repository metadata in BOXI. Please correct me if i am wrong.
Regards,
RAVI
venira (BOB member since 2004-12-07)
can any one tell me what is APOS?? is it not possible to directly querying the repository database??is it mandatory to use any reporting tool like APOS?
please clarify my doubt.
regards,
R
venira (BOB member since 2004-12-07)
In the BusObj Administration Launchpad, there is a link to a repository query tool (forgive me, I can’t remember the exact name). It is a crude tool, and I honestly haven’t spent much time trying to use it. It’s more of a SDK demo really.
Dwayne Hoffpauir 
(BOB member since 2002-09-19)
The tool was Query Builder. Given the age of this post, I’m guessing you already know that. Here are some sample queries you can run:
BO XI R2 Query Builder Notes
To get a full list of Universes with relevant info for migration activities:
SELECT SI_NAME, SI_SHORTNAME, SI_DESCRIPTION, SI_ID, SI_PARENT_FOLDER, SI_OWNER, SI_CREATION_TIME
FROM CI_APPOBJECTS
WHERE SI_KIND='Universe'To get a list of Universes that match a pattern:
SELECT SI_CUID, SI_ID, SI_NAME, SI_DESCRIPTION
FROM CI_APPOBJECTS
WHERE SI_KIND='Universe' AND SI_NAME LIKE 'DX_N%'To get detailed info on a particular Universe, Note the SI_ID and use this
SELECT *
FROM CI_APPOBJECTS
WHERE SI_KIND='Universe' AND SI_ID=1030To get a list of all Application Objects and what kind they are:
SELECT SI_CUID, SI_ID, SI_NAME, SI_DESCRIPTION, SI_KIND
FROM CI_APPOBJECTSTo get a list of Full Client reportS, run this SQL:
SELECT SI_ID, SI_NAME, SI_KIND FROM CI_INFOOBJECTS WHERE SI_KIND='FullClient'To get a list of reports in a particular folder, 1st you need the parent folder ID, then use it to list reports
To find the parent ID, that is the folder ID, run this query and look for your report names
SELECT SI_NAME, SI_KIND, SI_DESCRIPTION, SI_PARENTID FROM CI_INFOOBJECTS WHERE SI_KIND IN ('FullClient', 'Webi')For me, the PARENT_ID I was interested in was 383. Now run this query to get a list of Full Client and WebI reports from that folder.
SELECT SI_NAME, SI_KIND, SI_DESCRIPTION FROM CI_INFOOBJECTS WHERE SI_KIND IN ('FullClient', 'Webi') AND SI_PARENTID=383pcgeekus (BOB member since 2003-10-16)
thanks for this information, it’s great…
how did you come across this ? I can’t find any documentation that
helps me build queries…
also could this be run outside the Query Builder (ie in Oracle SQL Plus)
CI_INFOOBJECTS
must be a view ? of does Query Builder hide that and translate it internally… grumble if that’s the case…
thanks
OH, i just realized, it can’t work outside of QB because of the encryption anyway
davidmc (BOB member since 2006-08-03)
There is no documentation. Most people get them by trial and error. For example, if you do a “select distinct si_kind from ci_infoobjects”, you will find out all the names they use in si_kind. Yeah, “distinct” does not really work in Query Builder… very POOR design, but you still can get the list.
These SQL scripts must be run either with Query Builder or your own application using the SDK. You cannot run them on SQLPlus because the system database are in binary. Query Builder is built with SDK behind it.
Hope this helps.
substring (BOB member since 2004-01-16)
You can get some info out of Query Builder, here are some notes to help:
BO XI R2 Query Builder Notes
SAMPLE QUERIES YOU CAN RUN IN QUERY BUILDER
Use the XI Launchpad URL and Query Builder is listed in the lefthand navigation links
What Reports Use my Universe?
NOTE: If you delete a universe and then re-import it, this will not list the reports until you re-save them. It’s best to restore a Universe from a locally saved file rather than re-migrate from 5.x/6.x.
To get all universe data including a WebI report list
SELECT *
FROM CI_APPOBJECTS
WHERE SI_KIND=‘Universe’ AND SI_NAME=‘CCE Mass Channel’
To list all WebI reports when objects exceed 1000
SELECT SI_TARGETID,SI_KIND,SI_UPDATE_TS,SI_NAME,SI_ID,SI_SENDABLE,SI_OWNER,SI_FILES,SI_DESCRIPTION,SI_UNIVERSE,SI_PARENT_FOLDER,SI_OWNERID,SI_SUBMITTERID,SI_STARTTIME,SI_ENDTIME,SI_WEBI_PROMPTS,
FROM CI_INFOOBJECTS
WHERE SI_KIND=‘Webi’ and SI_NAME > ‘L’
To list all report folders containing a string
SELECT *
FROM CI_INFOOBJECTS
WHERE SI_NAME LIKE ‘%Dealer%’ AND SI_KIND=‘Folder’
SELECT SI_NAME, SI_SHORTNAME, SI_DESCRIPTION, SI_ID, SI_PARENT_FOLDER, SI_OWNER, SI_CREATION_TIME
FROM CI_APPOBJECTS
WHERE SI_KIND=‘Universe’
pcgeekus (BOB member since 2003-10-16)
Hi,
Can any one tell me name of the metadata table and column name behind BO which stores the universe column name and column description.
Thanks in Advance
CTuser (BOB member since 2007-04-10)
Hi,
I think you cannot find these informations.
Regards
On the XI platform, the universe is stored as a binary file on the filesystem part of the repository. Other than a very few pieces of information used for managing security, little is stored in the database part of the repository. In other words, the universe details are not able to be queried.
You can use the Designer SDK to extract the information from a local copy of the universe, however. See this utility … Document a universe using Excel and the Designer SDK … for an example.
Dwayne Hoffpauir (BOB member since 2002-09-19)
Hello,
Is it possible to list the classes and objects of a universe? This seems like a basic request, but has been challenging for BO to do. In BO classic, this would have required a custom query against repository tables. In BOXI, we have lost this visibility. When we asked BO, they said Designer can output a hardcopy printout (no sorting, filtering, or file) or we can go buy a MetaData Manager tool. All we want is a list of classes and objects of a universe! Any information would be appreciated, thank you.
bwboxi (BOB member since 2002-09-05)
Did you check out the utility Dwayne linked in the post just prior to yours?
Dave Rathbun (BOB member since 2002-06-06)
Wonderful! What a fantastic tool, this makes my day. Thank you.
bwboxi (BOB member since 2002-09-05)
Does anyone here know of a query in Query Builder that will tell me all universes, and which connection each universe uses?
MichaelWelter 
(BOB member since 2002-08-08)
Hi Michael,
Not sure if you can find this info using a single SQL query. Using sdk sure. But for both sdk and sql query you won’t get the info on connection overloads.
Regards
Sebastien
Hi, Sebastien. I don’t need the overloads. I just need a lidt of universes, and the connections used by them. The CMC knows the connection attached to a universe, so there must be some way to query that. 
MichaelWelter (BOB member since 2002-08-08)
hi all
do you know which column correspond to the universe keywords ?
theres a method IUniverse.getKeyword() so I guess there’s a way to fill the IInfoObject 
ese-aSH 
(BOB member since 2007-05-22)
Michael,
To get the universes and their associated connections, you can first query for unv:
select * from ci_appobjects
where si_kind = ‘Universe’
You’ll notice in the results there is a si_dataconnection column. This propertybag stores the si_id of the connection used by the universe.
universe connection query:
select si_name, si_id from ci_appobjects
where si_kind = ‘MetaData.DataConnection’
You can either take a pull of both 1) universes and 2) connections and link them yourself manually on the contents of the si_dataconnection field to find the lineage, or you can combine the two programatically. If you’re going to do it programatically, there’s a method available (getDataConnections() I believe) that will decode the contents for you.
I have written a utility that exposes all of the CMS metadata for reporting with an ad-hoc universe, if you are interested in hearing more.
Hope this helps.
crystal01 (BOB member since 2006-08-30)