Querying the Busines Objects XI CMS InfoStore Database Tables
The question has come to my mind many times over the years and this month it has come to my inbox.
Thanks to Pluto for inspiring me to write this article.
The question: Is it possible to peer into the Business Objects XI CMS InfoStore (a.k.a. database repository) without Query Builder or the BOXI SDK????
The answer: No
All of my research and experience tells me the following:
Much of the meta data, the useful part especially, is stored in the CMS InfoStore in an encrypted binary format. No one has been able to decrypt this format yet, but I can’t really say that anyone has tried very hard. I certainly haven’t.
Some Things You Can Do with the CMS InfoStore’s Database Tables
The CMS InfoStore is mostly held in the database table named “CMS_INFOOBJECTS5″. Some other less significant data is held in the following tables: CMS_RELATIONS5, CMS_IDNUMBERS5, CMS_ALIASES5, and CMS_VERSION_INFO.
The structure of the CMS_INFOOBJECTS5 table in Oracle is as follows:
OBJECTID INTEGER NOT NULL,
PARENTID INTEGER NOT NULL,
TYPEID INTEGER NOT NULL,
OWNERID INTEGER NOT NULL,
LASTMODIFYTIME VARCHAR2(32 BYTE) NOT NULL,
OBJFLAGS INTEGER NOT NULL,
USERFLAGS INTEGER,
SCHEDULESTATUS INTEGER,
NEXTRUNTIME VARCHAR2(32 BYTE),
ALIASES RAW(255),
CRC VARCHAR2(32 BYTE) NOT NULL,
PROPERTIES BLOB NOT NULL,
SI_GUID VARCHAR2(56 BYTE),
SI_CUID VARCHAR2(56 BYTE),
SI_RUID VARCHAR2(56 BYTE),
SI_INSTANCE_OBJECT INTEGER,
SI_PLUGIN_OBJECT INTEGER,
SI_TABLE INTEGER,
SI_HIDDEN_OBJECT INTEGER,
SI_NAMEDUSER INTEGER,
SI_RECURRING INTEGER,
SI_RUNNABLE_OBJECT INTEGER,
SI_TYPEID_MACHINE INTEGER,
SI_KEYWORD VARCHAR2(255 BYTE),
SI_KEYWORDISTRUNCATED INTEGER,
LOV_KEY VARCHAR2(18 BYTE),
OBJNAME VARCHAR2(255 BYTE),
OBJNAMEISTRUNCATED INTEGER
The “properties”, “aliases”, and “objname” fields are where most of the goodies are to be found and these are the fields that are encrypted or in some way pretty much unusable to us without the SDK or Query Builder.
A New Hope for CMS_INFOOBJECTS5
Don’t completely give up on querying this table directly. There are still quite a few valuable data that can be retrieved from the table. Firstly, you should know that the following fields are indexed and there for with a large CMS InfoStore database you will want to use these for filtering your query:
PARENTID
OWNERID
TYPEID
LASTMODIFYTIME
NEXTRUNTIME
SCHEDULESTATUS
OBJECTID
SI_GUID
SI_CUID
SI_RUID
SI_INSTANCE_OBJECT
SI_PLUGIN_OBJECT
SI_TABLE
SI_HIDDEN_OBJECT
SI_NAMEDUSER
SI_RECURRING
SI_RUNNABLE_OBJECT
SI_TYPEID_MACHINE
SI_KEYWORD
SI_KEYWORDISTRUNCATED
LOV_KEY
OBJNAME (this is encrypted though)
OBJNAMEISTRUNCATED
Not all of these fields will have data, but some will and those that sporadically have data might meet your needs perfectly. I don’t really have a strong background in querying this table directly. So we are going to have to crack this nut together. Hopefully we can share our progress in the comments of this post and through updates from me to this article.
A Sample Use of a Direct Query on the CMS InfoStore
For example, I may want to extract a list of all si_cuid (the most unique identifier of an object) and objectid for objects that were created or modified on January 24th. To do this I would use the following query:
SELECT
objectid,
si_cuid
FROM
CMS_INFOOBJECTS5
WHERE
lastmodifytime LIKE ('2009 01 24%')
If you look to the table structure you will see that the field lastmodifytime is of a character type and therefore it can be queried as presented above.
Why would you want to do this query directly on the database and not in Business Objects’ Query Builder?
Short answer: performance and possibility. What I mean to say is that you can control more precisely the performance of the query you write against the table. In fact, a similar query done through Query Builder might perform so slow that you it would not be possible except if you ran it directly against the database table. The truth is that many valid reasons to directly query the database will still end up with you taking the output of that query and then running it through the SDK or Query Builder to obtain all of the properties corresponding to that output.
What other database tables can I query?
CMS_RELATIONS5 and CMS_VERISON_INFO seem to have the only other bit of useful data. CMS_VERSION_INFO has a single row with the repositories version info; I am not certain that you can detect service pack or fix pack level here, in fact, I think you cannot. The CMS_RELATIONS5 table seems to be interesting, but I have not come up with a useful reason to query it beyond tinkering.
For your reference the CMS_RELATIONS5 table has the following structure in Oracle:
PARENTID INTEGER NOT NULL,
CHILDID INTEGER NOT NULL,
ISMEMBER INTEGER NOT NULL,
ORDINAL INTEGER,
RELATIONSHIPID INTEGER NOT NULL
None of the data appears to be encrypted. It also appears to have almost a 1-to-1 ratio between its record count and the count of the objects in the repository as reported in Business Objects Central Management Console. The value of this table might increase if we were to discover the meaning of different relationshipid values.
Conclusion: Where do we go from here?
I feel that I have only given you enough information to advance your tinkering on the true back-end. Honestly, this is a new frontier for me. I hope to post more information here as I find the time to experiment with directly with the database or if I hear from anyone else that has experience they would like to share. I invite you to share your findings as you play with querying the database. Use BO Query Builder concurrently to help you understand more about what you are seeing. If you find you need some help with Query Builder then I do recommend that you take a look at our Business Objects Query Builder Guide
select
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
translate(replace(objname,’BE’,’.’),’)+-/13579;=?ACEGIKMOQSUWY]!”{‘,’ABCDEFGHIJKLMNOPQRSTUVWXYZ’),
‘`|>’,’1′),
‘`|@’,’2′),
‘`|B’,’3′),
‘`|D’,’4′),
‘`|F’,’5′),
‘`|H’,’6′),
‘`|J’,’7′),
‘`|L’,’8′),
‘`|N’,’9′),
‘M|N’,'_’),
‘M|Z’,'-’),
‘@’,’ ‘),
‘`|<’,’0′),
‘B~P’,'/’),
‘M}Z’,':’),
‘B|C’,'(‘),
‘B|D’,')’),
‘B~K’,'@’)
trans,cms.objname,cms.si_cuid,
cms.parentid,
decode((select typeid from CMS_INFOOBJECTS5 cmsp where cmsp.objectid = cms.parentid),311,’Webi Report’,308,’Deski’,1,’Folder’,(select typeid from CMS_INFOOBJECTS5 cmsp where cmsp.objectid = cms.parentid)) parent_typeid,
cms.objectid,to_date(substr(cms.lastmodifytime,1,19),’YYYY MM DD HH24 MI SS’)+1/24 lastmodifytime
,decode(typeid,311,’Webi Report’,308,’Deski’,1,’Folder’,typeid) typeid
from CMS_INFOOBJECTS5 cms
It’s not complete but should bring back something useful
For SQL Server try
select
objectid,
parentid,
ownerid,
lastmodifytime,
upper(left(objectname,len(objectname)-2)) ObjectName,
typeid,
type,
si_cuid
from
(
SELECT
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
cast(objname as varchar(2000))
,’S',’v')
,’M',’s')
,’A',’m')
,’)',’a')
,’+',’b')
,’C',’n')
,’-',’c')
,’/',’d')
,’O',’t')
,’E',’o')
,’1′,’e')
,’3′,’f')
,’G',’p')
,’5′,’g')
,’7′,’h')
,’W',’x')
,’U',’w')
,’Q',’u')
,’I',’q')
,’9′,’i')
,’:',’i')
,’;',’j')
,’K',’r')
,’=',’k')
,’?',’l')
,’[','y')
,']‘,’z')
,’!@’,’ ‘)
,’B~S’,'&’)
,’!BO’,’.’)
,’B|C”‘,’(‘)
,’!B|D’,')’)
,’M|Z’,'-’)
,’M}L’,',’)
,’M|N’,'_’)
,’M}Z’,':’)
,’!B{B’,””)
,’`|’,’1′)
,’`|@’,’2′)
,’`|B’,’3′)
,’`|D’,’4′)
,’`|F’,’5′)
,’`|H’,’6′)
,’`|J’,’7′)
,’`|L’,’8′)
,’`|N’,’9′)
,’{‘,”)
,’!',”)
,’”‘,”)
,’@',”)
ObjectName,
case
when TypeID = 262 then ‘Webi Report’
when TypeID = 314 then ‘Deski Report’
when TypeID = 283 then ‘PDF’
when TypeID = 267 then ‘Text’
when TypeID = 323 then ‘Excel’
when TypeID = 266 then ‘Universe’
when TypeID = 278 then ‘Publication’
when TypeID = 299 then ‘Connection’
when TypeID = 19 then ‘User type 19′
when TypeID = 18 then ‘User type 18′
when TypeID = 47 then ‘User type 47′
when TypeID = 48 then ‘User type 48′
when TypeID = 8 then ‘Shortcut’
when TypeID = 1 then ‘Folder’
when TypeID = 20 then ‘Groups’
when TypeID = 13 then ‘Server’
when TypeID = 16 then ‘BO Server’
when TypeID = 21 then ‘Event’
when TypeID = 24 then ‘License Key’
else ‘Other’
end Type,
*
FROM [BOXI_CMS].[dbo].[CMS_InfoObjects5]
) BORepository
I cut and pasted the code above into SQL Server and there are a ton of Formatting issues/problems. Did you try the query in SQL Server?
The above code is formatted for Oracle. The following code, which I adapted from the same poster on another blog will display some key fields for all recurring isntances in SQL Server:
SELECT TOP 100 PERCENT ObjectID, ParentID, OwnerID, LastModifyTime, UPPER(LEFT(ObjectName, LEN(ObjectName) – 2)) AS ObjectName, Type,
SI_RECURRING, Properties, SI_TABLE, SI_KEYWORD, ScheduleStatus, NextRunTime, SI_INSTANCE_OBJECT, SI_RUNNABLE_OBJECT, TypeID,
ObjFlags, UserFlags, Aliases, CRC, SI_GUID, SI_RUID, SI_PLUGIN_OBJECT, SI_HIDDEN_OBJECT, SI_NAMEDUSER, SI_TYPEID_MACHINE,
SI_KEYWORDIsTruncated, LOV_KEY
FROM (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(ObjName
AS varchar(2000)), ‘S’, ‘v’), ‘M’, ‘s’), ‘A’, ‘m’), ‘)’, ‘a’), ‘+’, ‘b’), ‘C’, ‘n’), ‘-’, ‘c’), ‘/’, ‘d’), ‘O’, ‘t’), ‘E’, ‘o’), ’1′, ‘e’), ’3′, ‘f’), ‘G’, ‘p’), ’5′, ‘g’), ’7′, ‘h’),
‘W’, ‘x’), ‘U’, ‘w’), ‘Q’, ‘u’), ‘I’, ‘q’), ’9′, ‘i’), ‘:’, ‘i’), ‘;’, ‘j’), ‘K’, ‘r’), ‘=’, ‘k’), ‘?’, ‘l’), ‘[', 'y'), ']‘, ‘z’), ‘!@’, ‘ ‘), ‘B~S’, ‘&’), ‘!BO’, ‘.’), ‘B|C”‘, ‘(‘), ‘!B|D’,
‘)’), ‘M|Z’, ‘-’), ‘M}L’, ‘,’), ‘M|N’, ‘_’), ‘M}Z’, ‘:’), ‘!B{B’, ””), ‘`|’, ’1′), ‘`|@’, ’2′), ‘`|B’, ’3′), ‘`|D’, ’4′), ‘`|F’, ’5′), ‘`|H’, ’6′), ‘`|J’, ’7′), ‘`|L’,
’8′), ‘`|N’, ’9′), ‘{‘, ”), ‘!’, ”), ‘”‘, ”), ‘@’, ”) AS ObjectName,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(SI_KEYWORD
AS varchar(2000)), ‘S’, ‘v’), ‘M’, ‘s’), ‘A’, ‘m’), ‘)’, ‘a’), ‘+’, ‘b’), ‘C’, ‘n’), ‘-’, ‘c’), ‘/’, ‘d’), ‘O’, ‘t’), ‘E’, ‘o’), ’1′, ‘e’), ’3′, ‘f’), ‘G’, ‘p’), ’5′, ‘g’), ’7′, ‘h’),
‘W’, ‘x’), ‘U’, ‘w’), ‘Q’, ‘u’), ‘I’, ‘q’), ’9′, ‘i’), ‘:’, ‘i’), ‘;’, ‘j’), ‘K’, ‘r’), ‘=’, ‘k’), ‘?’, ‘l’), ‘[', 'y'), ']‘, ‘z’), ‘!@’, ‘ ‘), ‘B~S’, ‘&’), ‘!BO’, ‘.’), ‘B|C”‘, ‘(‘), ‘!B|D’,
‘)’), ‘M|Z’, ‘-’), ‘M}L’, ‘,’), ‘M|N’, ‘_’), ‘M}Z’, ‘:’), ‘!B{B’, ””), ‘`|’, ’1′), ‘`|@’, ’2′), ‘`|B’, ’3′), ‘`|D’, ’4′), ‘`|F’, ’5′), ‘`|H’, ’6′), ‘`|J’, ’7′), ‘`|L’,
’8′), ‘`|N’, ’9′), ‘{‘, ”), ‘!’, ”), ‘”‘, ”), ‘@’, ”) AS SI_KEYWORD2,
CASE WHEN TypeID = 262 THEN ‘Webi Report’ WHEN TypeID = 314 THEN ‘Deski Report’ WHEN TypeID = 283 THEN ‘PDF’ WHEN TypeID = 267
THEN ‘Text’ WHEN TypeID = 323 THEN ‘Excel’ WHEN TypeID = 266 THEN ‘Universe’ WHEN TypeID = 278 THEN ‘Publication’ WHEN TypeID
= 299 THEN ‘Connection’ WHEN TypeID = 19 THEN ‘User type 19′ WHEN TypeID = 18 THEN ‘User type 18′ WHEN TypeID = 47 THEN ‘User type 47′
WHEN TypeID = 48 THEN ‘User type 48′ WHEN TypeID = 8 THEN ‘Shortcut’ WHEN TypeID = 1 THEN ‘Folder’ WHEN TypeID = 20 THEN ‘Groups’
WHEN TypeID = 13 THEN ‘Server’ WHEN TypeID = 16 THEN ‘BO Server’ WHEN TypeID = 21 THEN ‘Event’ WHEN TypeID = 24 THEN ‘License Key’
ELSE ‘Other’ END AS Type, ObjectID, ParentID, TypeID, OwnerID, LastModifyTime, ObjFlags, UserFlags, ScheduleStatus, NextRunTime,
Aliases, CRC, Properties, SI_GUID, SI_CUID, SI_RUID, SI_INSTANCE_OBJECT, SI_PLUGIN_OBJECT, SI_TABLE, SI_HIDDEN_OBJECT,
SI_NAMEDUSER, SI_RECURRING, SI_RUNNABLE_OBJECT, SI_TYPEID_MACHINE, SI_KEYWORD, SI_KEYWORDIsTruncated, LOV_KEY,
ObjName, ObjNameIsTruncated
FROM dbo.CMS_InfoObjects5) AS BORepository
WHERE (SI_RECURRING = 1) AND (NOT (SI_INSTANCE_OBJECT = 1)) AND (ScheduleStatus = 9) OR
(ScheduleStatus =
ORDER BY UPPER(LEFT(ObjectName, LEN(ObjectName) – 2))
***************
ALSO, as a bonus, the following code is the Crystal Syntax for a formula to convert the NextRunTime field to a DateTime field in Crystal Reports.
Enjoy!
Local NumberVar YYYY;
Local NumberVar MO;
Local NumberVar DD;
Local NumberVar Hours;
Local NumberVar mm;
Local NumberVar ss;
Local NumberVar hh;
YYYY := ToNumber(Split({CMS_InfoObjects5.NextRunTime}, ” “) [1]);
MO := ToNumber(Split({CMS_InfoObjects5.NextRunTime}, ” “) [2]);
DD := ToNumber(Split({CMS_InfoObjects5.NextRunTime}, ” “) [3]);
Hours := ToNumber(Split({CMS_InfoObjects5.NextRunTime}, ” “) [4]);
mm := ToNumber(Split({CMS_InfoObjects5.NextRunTime}, ” “) [5]);
ss := ToNumber(Split({CMS_InfoObjects5.NextRunTime}, ” “) [6]);
hh := (If(Hours + 20 > 24) then (Hours – 4) Else (Hours + 20));
DateTime(YYYY,MO,DD,hh,mm,ss)
Hi,
I stumbled on this posting while searching for ways to query the Business Objects database schema. I’m specifically focused on trying to list the crystal reports, folders in which they are located and the sql (SELECT) stmt generated by Crystal for each report.
I’m using BO 12 and SQL Server 2005.
Is there any additional advice/guidance/scripts you would be willing to share or direct me to?
Thanks,
Chris.
Hi Chris,
I could totally be wrong, but the only place I know to get SQL is in the auditing data (if you have it enabled for Document Refresh). I am not sure if this captured for Crystal Reports though, I can only speak of WebI reports. Also as of BO XI R2 SP4 the path to the document is captured with each refresh (in the auditing data). So it would seem that I am saying the best place to get exactly what you need is in the auditing data. Of course, the limiter there is that the report actually had to be refreshed at least once after the auditing was enabled.
Regards,Julian
Thanks, I appreciate the advice and the prior postings/sample SQL on this topic.
Regards,
Chris.
Hi,
Does someone know how to get ‘kind’ Information in InfoObject to know if a line result is for an User, a group, Universe, Document,…. ?
Thanks
If I understand the question correctly…
There is a direct correlation between the database table field
CMS_InfoObjects5.typeidand the logical fieldsi_obtype. One day I went through all of the “kind” to find their “obtype” and then I mapped these to the “typeid”. This mapping table is in the Business Objects Query Builder guide that we sell here on this web site, so I won’t post the whole table. But I can tell you thatCI_InfoObjects.si_kind "webi" = CI_InfoObjects.obtype "267" = CMS_InfoObjects5.typeid "267". If you need help mapping any other kinds let me know, or you could buy our little Query Builder guide and get teh complete mapping table and much more (a shameless plug, I know).Hi,
Does someone has an idea about how to reset password of administrator account directly in le CMS database. I’m thinking that, if it is possible to decrypt CMS informations, maybe it is possible to do that.
Thanks.
It is not possible to decrypt it, but you can blunt force reset it by deleting the record corresponding to the Administrator account (possibly just the password). This resets it to blank, or rather, no password. See this for more details:
http://www.ehow.com/how_5005044_reset-business-objects-administrator-password.html
Also more discussion on the topic here:
http://www.forumtopics.com/busobj/viewtopic.php?t=87029