BusinessObjects Board

Repository tables in XI

server administration XI Server Discussion
#1

Hi All,

I have installed BO XI using MySql as my repository database. How can i find what are the repository tables present. Or is there any standard set of repository tables (like CI_INFOOBJECTS,…)

TIA
dinesh

dinesh2005 :india: (BOB member since 2005-09-12)

To find webi report folder name using metadata
where is the system database?
SQL - CMS - BOXIR2SP1
where can I see the Repository in B.O Xir2
Repositry tables
#2

You can connect to your CMS database and view the tables
We also have our CMS database residing on the SQL Server and the table can be viewed.

bopro :us: (BOB member since 2005-10-31)

#3

You can view the database tables, but keep in mind that it is no longer as easily editable as the legacy BO repository. Furthermore, all report objects are now stored in the FRS and merely pointers in the system database.

substring :us: (BOB member since 2004-01-16)

#4

Thanks for the reply.

I am able to connect to the CMS database. But i dont see any tables like CI_INFOOBJECTS . But when i use Query Builder provided by BO i am able to run query against the table.

Please help.

Thanks & regards
Dinesh

dinesh2005 :india: (BOB member since 2005-09-12)

#5

Some of the system tables are hidden and/or encrypted. They don’t want people to mess around with them. And frankly, why do you have to query the table?

substring :us: (BOB member since 2004-01-16)

#6

I was trying to find the CUID and was trying to relate all the report names with the file names. i was able to find it only from the CI_INFOOBJECTS tables. So outof curiosity want to know if there are tables like this which is not shown directly

dinesh

dinesh2005 :india: (BOB member since 2005-09-12)

#7

Yes, _InfoObjects is one of the tables in the CMS databasein addition to 5 other tables.

bopro :us: (BOB member since 2005-10-31)

#8

Hi Bopro,

You have anyidea about the other 5 tables. If so please let us know.

Thanks & Regards
dinesh

dinesh2005 :india: (BOB member since 2005-09-12)

#9

The other tables(4 not 5) are

_ALAISES
_VERSIONINFO
_RELATIONS
_IDNUMBERS

bopro :us: (BOB member since 2005-10-31)

#10

Correction,

The first table was _ALIASES

bopro :us: (BOB member since 2005-10-31)

#11

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 :india: (BOB member since 2004-12-07)

#12

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 :canada: (BOB member since 2004-12-13)

#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 :india: (BOB member since 2004-12-07)

#14

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 :india: (BOB member since 2004-12-07)

#15

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 :us: (BOB member since 2002-09-19)

#16

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=1030

To 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_APPOBJECTS

To get a list of Full Client reportS, run this SQL:

SELECT SI_ID, SI_NAME, SI_KIND FROM CI_INFOOBJECTS WHERE SI_KIND='FullClient'
  • Leave the Where clause off to see all

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=383

pcgeekus :us: (BOB member since 2003-10-16)

Hyperlinks within the reports in Favorites?
#17

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)

#18

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 :us: (BOB member since 2004-01-16)

#19

You can get some info out of Query Builder, here are some notes to help:
BO XI R2 Query Builder Notes

  • Queries are limited to returning 1000 objects
  • Cannot use subqueries as IN lists
  • The order of fields in the SELECT has no effect, it renders the results in it’s own order.
  • SI_KIND for CI_INFOOBJECTS includes ‘Webi’, ‘Pdf’, ‘Excel’, ‘Folder’, ‘FullClient’, ‘FavoritesFolder’, ‘Inbox’, ‘PersonalCategory’, ‘Shortcut’, ‘MyInfoView’, ‘AFDashboardPage’
  • SI_KIND for CI_APPOBJECTS includes ‘Universe’, ‘Folder’, ‘MetaData.DataConnection’,‘ReportConvTool’, ‘WebIntelligence’, ‘Discussions’, ‘InfoView’, ‘CMC’, ‘busobjReporter’, ‘Designer’, ‘AdHoc’
  • SI_KIND for CI_SYSTEMOBJECTS includes ‘User’, ‘UserGroup’,’‘Connection’,secWinAD’, ‘secLDAP’, ‘secWindowsNT’

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.

First run this query to get a list of report IDs
SELECT SI_NAME,SI_DESCRIPTION,SI_E65_COMMENT,SI_SHORTNAME,SI_KIND,SI_UPDATE_TS,SI_ID,SI_E65_ID,SI_OWNER,SI_WEBI,SI_FILES,SI_PARENT_FOLDER,SI_DATACONNECTION,SI_DERIVEDUNIVERSE,SI_COREUNIVERSE
FROM CI_APPOBJECTS
WHERE SI_KIND=‘Universe’ AND SI_NAME LIKE ‘CCE%’

To get all universe data including a WebI report list
SELECT *
FROM CI_APPOBJECTS
WHERE SI_KIND=‘Universe’ AND SI_NAME=‘CCE Mass Channel’

Then, use the list of IDs to get a list of report names that use this Universe
SELECT SI_NAME,SI_DESCRIPTION, SI_ID,SI_AUTHOR,SI_PARENT_FOLDER,SI_UNIVERSE,SI_HAS_PROMPTS
FROM CI_INFOOBJECTS
WHERE SI_ID IN (nnnnn,nnnnn,nnnnn)

How do I get a list of all connections matching a substring
SELECT SI_NAME
FROM CI_APPOBJECTS
WHERE SI_KIND=‘MetaData.DataConnection’ AND SI_NAME LIKE ‘%CFD%’

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’

then use <= ‘L’

To list all report folders containing a string
SELECT *
FROM CI_INFOOBJECTS
WHERE SI_NAME LIKE ‘%Dealer%’ AND SI_KIND=‘Folder’

For Universe folders, use CI_APPOBJECTS

To list all Webi reports containing a string
SELECT *
FROM CI_INFOOBJECTS
WHERE SI_NAME LIKE ‘%Dealer%’ AND SI_KIND=‘Webi’

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 :us: (BOB member since 2003-10-16)

#20

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)