Querying the Busines Objects XI CMS InfoStore Database Tables

68 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. Hello Steven

   Could you kindly send the function in email please?

   Thanks
   Srinivas

9. 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.

10. 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?

11. 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.

12. 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

13. Hi,

    I am looking to create a report that gives all the tables and columns used in a particular report. How can this be achieved? directly from BO repository tables?

14. HI,

    I have to Reset Administrator Password for CMS ( CMS database).But we are using cms_infoobjects7 table . In internet every where cms_infoobjects6 WHERE (ObjectID=12);

    So how can i build up query >>DELETE FROM cms_infoobjects7 WHERE (ObjectID=??????);

    Please help me.

15. plz update, waiting for reply

16. Hello
    BO XI 3.1 and CMS is on Oracle datebase
    I need a list from all Deski Reports with create date, last call date and all Folderinformation
    Can you help me

17. I want to get list of all the users in Business Objects. Our Organization doesn’t have Query Builder, so is there any other way for listing all the users? The version we use is XI 3.1.

18. How to capture datasource name for crystal reports on BI CMS universe provided by SAP ?