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

Enjoyed this post? Share it!

 

62 thoughts on “Querying the Busines Objects XI CMS InfoStore Database Tables

  1. Hi Marek, I just tried to help with the copy and paste by changing Steven’s code to the “code” type in WordPress. I hope this makes it easier to copy and paste. Please us know. BTW, sorry, I don’t have this in MS SQL Server compatible form.

  2. Marek,

    The above string that you’ve posted “countrY o}T region o}T citY_dc_1”

    May I ask that you paste here what the string is actually supposed to be… from Query builder.. using the si_id of the object:

    select si_name from ci_infoobjects where si_id = [here is your si_id of the object]

    this will allow me to check that I have mapped the ‘missing’ characters in the decoding SQL.

    Thanks
    Steven

  3. I have been working on generating a MSSQL function to perform the translation of the OBJNAME field in INFOOBJECTS6 table.

    If anyone would like to see a copy of the PL/SQL please respond and I shall pop it into an email for you.

    Steven

  4. I’ll paste here for those to see…. although copying from the site may be a problem, no issues to re type though 🙂

    Call it in a query by SCHEMANAME.DecryptText([OBJNAME])

    eg.

    select SCHEMANAME.DecryptText([OBJNAME])
    from cms_infoobjects6

    —————————————
    CREATE FUNCTION [SCHEMANAME].[DecryptText]
    (@InString varchar(255))

    RETURNS varchar(255)
    AS
    BEGIN
    — Declare the return variable here
    DECLARE @Decrypted varchar(255)

    — Add the T-SQL statements to compute the return value here

    — Remove the end characters from the encoded string and discard them
    SELECT @Decrypted = left(
    @InString,
    (len(@InString)

    CHARINDEX(‘;!’,reverse(@InString),0))-1)
    COLLATE SQL_Latin1_General_CP1_CS_AS

    — Perform the replace operations.
    — I have placed a single operation for each translation to make modifying the code easier going forward.
    — you could generate a single replace(replace()) type operation which would be harder to read

    SELECT @Decrypted = replace(@Decrypted,’BE’,’.’)
    SELECT @Decrypted = replace(@Decrypted,’A|N’,’_’)
    SELECT @Decrypted = replace(@Decrypted,’A}]’,’:’)
    SELECT @Decrypted = replace(@Decrypted,’)’,’a’)
    SELECT @Decrypted = replace(@Decrypted,’]’,’z’)
    SELECT @Decrypted = replace(@Decrypted,’B|-‘,'(‘)
    SELECT @Decrypted = replace(@Decrypted,’B|1’,'[‘)
    SELECT @Decrypted = replace(@Decrypted,’B|3′,’]’)
    SELECT @Decrypted = replace(@Decrypted,’B{B’,””)
    SELECT @Decrypted = replace(@Decrypted,’A}?’,’,’)
    SELECT @Decrypted = replace(@Decrypted,’-‘,’c’)
    SELECT @Decrypted = replace(@Decrypted,’1′,’e’)
    SELECT @Decrypted = replace(@Decrypted,’3′,’f’)
    SELECT @Decrypted = replace(@Decrypted,’5′,’g’)
    SELECT @Decrypted = replace(@Decrypted,’7′,’h’)
    SELECT @Decrypted = replace(@Decrypted,’9′,’i’)
    SELECT @Decrypted = replace(@Decrypted,’`|’,’1′)
    SELECT @Decrypted = replace(@Decrypted,’`|@’,’2′)
    SELECT @Decrypted = replace(@Decrypted,’`|B’,’3′)
    SELECT @Decrypted = replace(@Decrypted,’`|D’,’4′)
    SELECT @Decrypted = replace(@Decrypted,’`|F’,’5′)
    SELECT @Decrypted = replace(@Decrypted,’`|H’,’6′)
    SELECT @Decrypted = replace(@Decrypted,’`|J’,’7′)
    SELECT @Decrypted = replace(@Decrypted,’`|L’,’8′)
    SELECT @Decrypted = replace(@Decrypted,’`|N’,’9′)
    SELECT @Decrypted = replace(@Decrypted,’`{?’,’$’)
    SELECT @Decrypted = replace(@Decrypted,’A|Z’,’-‘)
    SELECT @Decrypted = replace(@Decrypted,’A}/’,’-‘)
    SELECT @Decrypted = replace(@Decrypted,’A~L’,’^’)
    SELECT @Decrypted = replace(@Decrypted,’B~?’,’*’)
    SELECT @Decrypted = replace(@Decrypted,’B~U’,’%’)
    SELECT @Decrypted = replace(@Decrypted,’!E~&’,’~’)
    SELECT @Decrypted = replace(@Decrypted,’B~M’,’&’)
    SELECT @Decrypted = replace(@Decrypted,’B|/’,’)’)
    SELECT @Decrypted = replace(@Decrypted,’B~S’,’*’)
    SELECT @Decrypted = replace(@Decrypted,’@’,’#’)
    SELECT @Decrypted = replace(@Decrypted,’B~=’,’@’)
    SELECT @Decrypted = replace(@Decrypted,’/’,’d’)
    SELECT @Decrypted = replace(@Decrypted,’=’,’k’)
    SELECT @Decrypted = replace(@Decrypted,’A{?’,’,’)
    SELECT @Decrypted = replace(@Decrypted,’E}P’,”)
    SELECT @Decrypted = replace(@Decrypted,’E}R’,’=’)
    SELECT @Decrypted = replace(@Decrypted,’B~G’,’/’)
    SELECT @Decrypted = replace(@Decrypted,’G’,’p’)
    SELECT @Decrypted = replace(@Decrypted,’C’,’n’)
    SELECT @Decrypted = replace(@Decrypted,’O’,’t’)
    SELECT @Decrypted = replace(@Decrypted,’E’,’o’)
    SELECT @Decrypted = replace(@Decrypted,’?’,’l’)
    SELECT @Decrypted = replace(@Decrypted,’K’,’r’)
    SELECT @Decrypted = replace(@Decrypted,’+’,’b’)
    SELECT @Decrypted = replace(@Decrypted,’;’,’j’)
    SELECT @Decrypted = replace(@Decrypted,’A}U’,’;’)
    SELECT @Decrypted = replace(@Decrypted,’A~T’,’£’)
    SELECT @Decrypted = replace(@Decrypted,’M’,’s’)
    SELECT @Decrypted = replace(@Decrypted,’I’,’q’)
    SELECT @Decrypted = replace(@Decrypted,’Q’,’u’)
    SELECT @Decrypted = replace(@Decrypted,’S’,’v’)
    SELECT @Decrypted = replace(@Decrypted,’W’,’x’)
    SELECT @Decrypted = replace(@Decrypted,’U’,’w’)
    SELECT @Decrypted = replace(@Decrypted,’A’,’m’)
    SELECT @Decrypted = replace(@Decrypted,’#’,’ ‘)
    SELECT @Decrypted = replace(@Decrypted,'{‘,”)
    SELECT @Decrypted = replace(@Decrypted,’!’,”)
    SELECT @Decrypted = replace(@Decrypted,’^’,’!’)
    SELECT @Decrypted = replace(@Decrypted,’£’,’?’)
    SELECT @Decrypted = replace(@Decrypted,'”‘,”)

    — Return the result of the function
    RETURN upper(@Decrypted)

    END

    GO
    —————————————-

  5. I tried this Decrypt function on CMS_InfoObjects7 tables and it does not appear to work. Do you have an updated Decrypt function for the v7 tables?

    Thanks,
    Yurd

  6. I wanted to be able to create a report or list of reports run for a specific time range and filter by certain users and then be able to see the report name, when it was run and by who. Also, if possible, I would like to be able to see what parameters were used when running the report. Is this possible?

  7. Hi,

    Did anyone work out how to decrypt the values in CMS_InfoObjects7?
    I’m after a simple report which shows me report names and their version number.

  8. I have the function working in both V6 and V7 of the Infoobjects. Please also bear in mind that this function is written for SQLServer database. Some changes would need to be made to work in Oracle and other databases. @Srinivas – I will attempt to email you the function as requested.

  9. I am trying to retype you code. I cant tell what some of the characters are. Epecially the Double quote looking thing. In your last line: SELECT @Decrypted = replace(@Decrypted,’”‘,”) – What kind of characters are these?

    I am trying this in SQL server and it is not working. Any hints?

  10. HI Steven,

    I would like to decrypt CMS_INFOBJECTS6 Table for SQL Server to actually see the following things:

    No of Failed reports withing a specific range.
    Need to see the Server properties and set alerts on Webi service on any of metrics.

    please if you can email me.

  11. Hi,

    I tried following query:

    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]!”{JP’,’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','@') objnm,objname from cms_infoobjects7 where parentid = 38481;

    The output that I got is :
    CASEJB -)M1!;B
    VALIDATION REPORTSJ S)?9/)O9EC!@{K1GEKOM!;P

    In above output 'JB' and 'J " is extra in the end.
    Can some one suggest how to fix this.

    Regards,
    Prabhjot

Leave a comment

Your email address will not be published. Required fields are marked *