BusinessObjects Board

Repository tables in XI

server administration XI Server Discussion
#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)

#21

Hi,

I think you cannot find these informations.

Regards

#22

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

#23

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)

#24

Did you check out the utility Dwayne linked in the post just prior to yours?

Dave Rathbun :us: (BOB member since 2002-06-06)

#25

Wonderful! What a fantastic tool, this makes my day. Thank you.

bwboxi (BOB member since 2002-09-05)

#26

Does anyone here know of a query in Query Builder that will tell me all universes, and which connection each universe uses?

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

#27

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

#28

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. :roll_eyes:

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

#29

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 :slight_smile:

ese-aSH :fr: (BOB member since 2007-05-22)

Sorting universes into folders; print overview
#30

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 :us: (BOB member since 2006-08-30)

#31

Hi, I’m very interested in your utility that exposes all of the CMS metadata for reporting with an ad-hoc universe. Can you give more information on this ?

Thanks,

eocula (BOB member since 2005-04-01)

#32

Hi,
I have installed BOXIR2 for the first time and I heard that BO will create 5 tables but strange thing has happend in my installation:
I have 6 tables created:
CMS_ALIASES5 TABLE
CMS_CCFRONTIER5 TABLE
CMS_FRONTIER5 TABLE
CMS_IDNUMBERS5 TABLE
CMS_INFOOBJECTS5 TABLE
CMS_ROOTFRONTIER5 TABLE

Can someone throw some light on this.

Thanks in advance
Cheers,
Uday

udayboxi (BOB member since 2008-06-21)

#33

what is strange ? I think it is the normal new xi repository tables.

bernard timbal :fr: (BOB member since 2003-05-26)

#34

Eocula,

Since you asked, more information / free trial can be found be clicking the System Profiler BOB banner, or by going to http://www.analytics8.com/systemprofiler

crystal01 :us: (BOB member since 2006-08-30)

#35

Hello,

Going thru this thread I came to know that CI_INFOOBJECTS is one of the “hidden” tables in XI repository. Are there any more? Is there any documents/write-up that lists & talks about these tables and their usage?
Does anyone know? Please share.

Thanks,
Buddy!

Buddy :india: (BOB member since 2006-08-04)

#36

Before pointing the CMS to a new data source, I viewed the CMS table in MySQL (database wherein repository database resides) and it has 8 tables.

cms_aliases5
cms_ccfrontier5
cms_frontier5
cms_idnumber5
cms_infoobjects5
cms_relations5
cms_rfrontier5
cms_versioninfo

But when I pointed the CMS to the new data source (MS SQL Server 2005), All tables except cms_ccfrontier5, cms_frontier5 and cms_rfrontier5 were visible. What are the roles of this 3 tables in CMS database?

Xtin :philippines: (BOB member since 2009-05-20)