Unlike previous versions of SAP BusinessObjects, the system database in the XI releases is encrypted and cannot be manually queried or modified by the system administrators. SAP BusinessObjects includes one tool called Query Builder, which can be used to perform some queries against the system database, but the power of these queries is very limited.

While working with SAP BusinessObjects XI, I have been in a couple of situations when access to the system database has been a must. After doing some internal research and web searches I found a way to query / delete some records of the CMS DB using an easy decryption algorithm. It is very important to say that querying / modifying directly in the system database is not supported by SAP BusinessObjects, but it can help us in some extreme situations.

In this blog post I will focus on the table CMS_INFOOBJECTS5 that stores the information of all the objects that we have in the system (each object is a row). In BusinessObjects XI R3.1 the table is called CMS_INFOOBJECTS6 but the structure and encryption algorithm are the same as in R2.

CMS_INFOOBJECTS5 Table Description

When accessing the table CMS_INFOOBJECTS5 we see that it contains a lot of columns. However we will just focus on the following:

• ObjectID:  The identifier of the row (object) in the table
• ParentID:  The parent’s ObjectID of the object (We can make a self-join)
• TypeID:  Numerical code that describes the object type.
• OwnerID:  The ObjectID of the owner of the object (We can make a self-join)
• LastModifyTime:  Last time the object has been modified
• CRC: The CRC Code
• SI_CUID:  Cluster Unique Identifier of the object into the system
• ObjName:  Name of the object (Encrypted)
• Properties:  Field that contains the properties of the object

ObjName Encryption

When we try to query the CMS_INFOOBJECTS5 table, this field is encrypted. The encryption code used is as follows:

This list is not complete, but we have the most important characters we need in order to understand what is stored in the field.

Note that after decrypting the fields we will see that the last three characters of the field are some sort of code (It appears that this code is related to the object type). We can ignore this.

TypeID Decode

The most important codes and descriptions for the different object types are:

Sample Query

The following sample query for Oracle includes the information described above and shows us some useful information about our system. For SQL Server we should use the replace function for all the characters instead of using the Oracle’s translate function.

select
objectid,
parentid,
LastModifyTime,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
translate(objname,’)+-/13579;=?ACEGIKMOQSUWY]’,'ABCDEFGHIJKLMNOPQRSTUVWXYZ’),
‘!`|<’,’0′),
‘!`|>’,’1′),
‘!`|@’,’2′),
‘!`|B’,’3′),
‘!`|D’,’4′),
‘!`|F’,’5′),
‘!`|H’,’6′),
‘!`|J’,’7′),
‘!`|L’,’8′),
‘!`|N’,’9′),
‘!M|N”‘,’_'),
‘!M|N’,'_’),
‘!M|Z’,'-’),
‘!B|C’,'(‘),
‘!B|D’,')’),
‘!@{‘,’ ‘),
‘!.{‘,’.') as Decoded,
decode(typeid,1,’Folder’,331,’WebI’,295,’DeskI’,337,’Universe’,263,’Connection’,342, ‘Word’,323,’PDF’,336,’Text’,285,’Excel’,324,’PPT’,20,’Group’,16,’Service’,24, ‘License’,typeid) objtype
from BOREPO.CMS_INFOOBJECTS5
order by objectid

Example Situations / How to proceed

As I explained at the beginning of the post, I have been in two situations when I needed to modify the table to fix serious problems in the system:

1. Impossible to access Performance Manager: We set up a connection for Performance Manager with a large number of tables. When we tried to access it again to change the connection, the system crashed.
2. Duplicate licenses: We were adding a new license for the system, and accidentally clicked the “add” button twice. As a result, two licenses were added to the system and we were not able to delete either of them or add new ones.  In addition, we kept getting a “duplicated license” error.

It is very important to say that we cannot edit any field of the row or we will corrupt the system; we can only delete rows. If we delete a row that the system is expecting to have (i.e. the Performance Manager configuration), the system will recreate the row with the default values once we start the CMS service.

When making modifications to the system database, the process to follow is:

1. Stop all the BO services
2. Delete the desired row
3. Restart  all the BO services

Note that this restart will take some extra time.

If you have any feedback or questions about this method, I will be happy to read and answer your comments.

This entry was posted on Tuesday, January 5th, 2010 at 7:25 am and is filed under SAP Business Objects. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.