Downtime Announcement: Please note the SAP Community Wiki will be unavailable due to a system upgrade on Thursday, September 24th between 6 and 7 AM CEST
Skip to end of metadata
Go to start of metadata

Overview

  • The majority of the tables that make up the 'CMS Database' can not be queried using SQL directly against the database, since their content is held in a proprietary encrypted format.
  • The Query Builder tool, enables you to query the full CMS database and return meaningful results, d-encrypting its contents.
  • There is no limit to the scope of the queries, you can query all the content, including content not normally accessible through the CMC or BI LaunchPad.

Usage

Access Query Tool by using the URL of the form: http://MyServer:Port/AdminTools

For an overview please refer to this SCN article

 

(lightbulb)    AdminTools Session should be opened with Administrator account if possible, since this account has faster security rights check than with standard account (even compared to a user part of Administrators group). This means that response will come faster, and no unnecessary resources will be consumed by CMS.

 

How to obtain query processing statistics


Use flag "SHOW STATS" at the end of the query to get detailed statistics about execution in CMS
example: select SI_NAME, SI_ID from ci_infoobjects where SI_KIND='Webi' order by SI_SIZE desc SHOW STATS


It will return a table at the end of standard results, with information such as:

    • SI_QUERY_COUNT : number of elements filtered and returned
    • SI_QUERY_TOTAL_TIME : amount of time spent executing query in CMS (overall duration)
    • details about duration and operations for each sub-part of execution (SI_QRY_PARSE, SI_QRY_WHERE, SI_QRY_ORDER_BY and SI_QRY_SELECT)
    • SQL queries sent to CMS database, as well as corresponding durations and number of elements fetched


This can be helpful in analyzing performance and optimizing custom CMS queries, and may be requested by SAP Support when working on incidents related to performance.


Flag is only leveraged when session is opened with a user member of Administrators group, due to security concerns with such detailed statistics and SQL information.


Popular SQL commands

DescriptionSQLComments
Number of Webi documents (excluding instances)select top 100000 si_id from ci_infoobjects where si_kind='Webi' and si_instance=0 
Number of Webi instances only (not the master doc)select top 100000 si_id from ci_infoobjects where si_kind='Webi' and si_instance=1 
Number of Crystal Reports (excluding instances)select top 100000 si_id from ci_infoobjects where si_kind='CrystalReports' and si_instance=0 
Number of Deski documents (excluding instances)select top 100000 si_id from ci_infoobjects where si_kind='FullClient' and si_instance=0 
Number of Deski instances only (not the master doc)select top 100000 si_id from ci_infoobjects where si_kind='FullClient' and si_instance=1 
Number of Publications (excluding instances)select top 100000 si_id from ci_infoobjects where si_kind='Publication' and si_instance=0 
Number of Publication instances only (not the master doc)select top 100000 si_id from ci_infoobjects where si_kind='Publication' and si_instance=1 
Number of recurring schedules (any type)select top 100000 * from ci_infoobjects where si_schedule_status=9 
List of server settingsselect * from ci_systemobjects where si_kind='Server' 
List of events + settings

select * from ci_systemobjects where si_kind='Event'

 
Named Usersselect si_name from ci_systemobjects where si_nameduser = 1 
Concurrent Usersselect si_name from ci_systemobjects where si_nameduser = 0 
All objects that don’t have an SI_FILES property at all.select SI_NAME,SI_KIND from CI_INFOOBJECTS where SI_FILES is nullNote that this will only return objects which don’t have an SI_FILES property at all.  Objects that do have an SI_FILES property but the number of files is zero (SI_NUM_FILES) will not be returned
All objects that have an SI_FILES property but the filesize is 0

select SI_FILES from CI_INFOOBJECTS where SI_FILES.SI_NUM_FILES=0

 
List all plugins that have a least 2 icons associated with themselect * from CI_SYSTEMOBJECTS where SI_PLUGIN_OBJECT=1 and SI_PLUGIN_ICON_2_A is not null 
Web Intelligence documents ordered by nameselect SI_NAME,SI_ID from ci_infoobjects where si_kind='Webi' order by SI_NAME asc 
Web Intelligence documents ordered by size (largest first)select SI_NAME,SI_ID from ci_infoobjects where si_kind='Webi' order by SI_SIZE descNote that even though SI_SIZE is not requested, only in the ordering, it will bring the parameter into the results set
All .UNV universes ordered by nameselect * FROM CI_APPOBJECTS WHERE SI_KIND='Universe' order by SI_NAME asc 
All .UNX universes ordered by nameselec * FROM CI_APPOBJECTS WHERE SI_KIND='DSL.Universe' order by SI_NAME asc 

Plug-ins where that was no icons associated with them

select * from CI_SYSTEMOBJECTS where SI_PLUGIN_OBJECT=1 and SI_PLUGIN_ICON_1_A is null 
Lists information on the Install Node and EnterpriseNode (SIA)SELECT * FROM CI_SYSTEMOBJECTS, CI_APPOBJECTS, CI_INFOOBJECTS WHERE SI PARENTID IN (53,59)If you have trouble when applying Updates because the AddNode.bat/addnode.sh fails look for the SI_VIRTUAL_ID and make sure it matches the id in the _boe.install.
All the users within and under a particular group

SELECT top 200000 SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='MyGroupNameHere'") AND SI_KIND='USER'

Given a particular group name (replace MyGroupNameHere accordingly) this query will return ALL the users in the group and any sub groups. this can help with compliance or licensing.
All users that have never logged-in to the BI Platform

SELECT TOP 20000 SI_NAME, SI_LASTLOGONTIME FROM CI_SYSTEMOBJECTS WHERE SI_NAME NOT IN ('Administrator','Guest') AND SI_KIND='USER' AND  SI_LASTLOGONTIME IS NULL ORDER BY SI_NAME

 
All users that have never logged-in to the BI Platform that are under a particular group

SELECT top 200000 SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='MyGroupNameHere'") AND SI_KIND='USER'  AND SI_NAME NOT IN ('Administrator','Guest') AND SI_KIND='USER' AND  SI_LASTLOGONTIME IS NULL

 
Objects that a given Group/User has access too. (Here limited to only the Public folders)select si_name from ci_infoobjects where si_parentid=23 and IsAllowed(<groupid>,<rightid>)GroupID (or UserID) is accessible within the CMC properties. si_parentid=23 is for the Public folders. IsAllowed takes the GroupID (or UserID) and the right ID. For the right ID refer to the section 'Which Right'
List of all plug-ins installed into the CMS repositoryselect * from ci_systemobjects where si_plugin_object=1 
List of all applications installed in to the CMS repositoryselect * from ci_appobjects where si_parentid=99 

 

Screenshots

 22.jpg

Download

 The tool is provided out of the box.

Additional Resources

1201157 - Sample administrator queries for Query Builder

1542511 - How to access query builder on Web Application Container Service (WACS) deployment

1854982 - Using query builder to find number of reports for certain schedule statuses

1733964 - How to get list of events using Query Builder?

1876670 - How to find the children of a particular group in BI 4.0 through Query Builder

1229734 - How to find which Web Intelligence reports were created with a specific universe, using Query Builder

1895241 - How to list all reports present in Users Inbox and Personal Folder using Query Builder

1870838 - How to find the Web Intelligence reports per folder in the BusinessObjects Enterprise using the Query

1354397 - How to retrieve information of report instances using Query Builder?

1735539 - Query to list all Universes for which a user has "Edit Objects" rights via Query Builder

Requirements / Dependencies

If using a 3rd Party Web Application Server, or you are manually deploying Web Applications, then you will need to deploy the Web Application 'AdminTools'.

 

 

  • No labels

16 Comments

  1. Former Member

    Excellent Article very informative..Thanks for putting this on SCN

  2. Former Member

    Great article !!! Is it possible to get report level data like dimensions and the report query generated for a report from the repository ?Thanks !!

    1. You can use the Query Builder only to retrieve universe and connections, prompts, etc. You will require the SDK to retrieve dimensions or measures used by a report as that information won't be stored in the CMS database but inside the report.

  3. I'm sorry, I have not found how you can find the information that is included when you create a user in the cmc. For example these scripts you've provided, don't give an actual name. For example if I created a user such as Account Name: 12345, Full Name John Doe, etc. When I search using your query,  I may get the "SI_NAME"  which may be equal to 12345 but what about the rest of the metadata or the John Doe?  I also have not found where I can see what objects are available. Where can I find what fields are in the tables.

    Thanks in advance. Scott

  4. I apologies, I obviously didn't look hard enough.

  5. How can I get a list of all of the fields in these tables? I tried Describe CI_INFOOJBECTS but Query builder doesn't like that.

  6. How can you join the tables?

  7. Excuse me, but the following SapNote does not contain any information: 1735539 - Query to list all Universes for which a user has "Edit Objects" rights via Query Builder

    Can you kindly verify it?

    Thanks!

     

    1. Working fine for me (here the main infomation from the note)

      1. Login to Query Builder with Administrator account using the following link:
          http://servername:portnumber/AdminTools

      2. Execute the below query to get the SI_ID of the user for whom we need to check the right
      select si_id,si_name from ci_systemobjects where si_kind = 'User' and si_name = 'Username'

      3. After this execute the below query to get the list of all universes for which that user has "Edit" rights
      select si_name from ci_appobjects where si_kind = 'Universe' and IsAllowed("SI_ID of the User from the first query",6)

      Note : Value for Edit rights is "6"

  8. Thanks I see all content too now! Probably I had an IE cache problem....

  9. Former Member

    How can I get the Instances which took the maximum time to execute?

    1. Former Member

      I want to know the query for the query builder

      1. Hi Yamini, from Query Builder, you can only extract start and end time of any instance. There isn’t an "execution time" field. In new BI 4.2 SP3 release there is a new driver who allows you to query CMS db without the limitations of query builder, but with all the features of a webi report. So, with it,  you can extract all instances, calculate the "execution time" creating a variable ad hoc in the report and obtain the max execution time you want. 

        Url: https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3

        Bye!

        1. Former Member

          So I suppose the only way to get this done through Query builder is to persist the data for all the instances at the run time and get the execution time using start and end date to get maximum time taken by the instances... Thanks anyways!

  10. Dear Matthew, again a useful page/info by you (smile)

    I can't open/view the note 1895241. Result is "

    Sorry!

    SAP Note/KBA 1229734 has been removed.

    The SAP Note/KBA you were trying to reach has been removed. This is usually the result when content is outdated.

    "

    Any idea?

    Greets

    Roberto

    1. Hi Roberto,

      The KBA 1895241 should work if navigating to https://launchpad.support.sap.com/#/notes/1895241

      The KBA 1229734 has been archived.

      Thank you,

      Mark