Skip to Content
Author's profile photo Manikandan Elumalai

BusinessObjects Environment Assessment using Query builder

BusinessObjects environment assessment is one of the critical steps whenever you go for migration or an upgrade. Identifying the existing objects in current deployment and cleaning up the unnecessary and unused objects will help you to design and size your system properly with optimal performance. In this document I want to show simple steps to estimate BI contents using Query builder.

Have a look here BusinessObjects Query builder – Basics  for Query builder basics. You should fire the below environment assessment queries in Querybuilder.

1. Estimating User Community

User count

          SELECT COUNT (SI_ID) FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘USER’

Named user count

          SELECT COUNT (SI_ID) FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘USER’ AND SI_NAMEDUSER=1

Concurrent user count

            SELECT COUNT (SI_ID) FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘USER’ AND SI_NAMEDUSER=0

User group count

            SELECT COUNT (SI_ID) FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘USERGROUP’

2. Estimating BI Content

Universe count

           SELECT COUNT (SI_ID) FROM CI_APPOBJECTS WHERE SI_KIND = ‘UNIVERSE’

Connection count

            SELECT COUNT (SI_ID) FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘CONNECTION’

Count of crystal reports

            SELECT COUNT (SI_ID) FROM CI_INFOOBJECTS WHERE SI_KIND = ‘CRYSTALREPORT’

Count of Full client reports

           SELECT COUNT (SI_ID) FROM CI_INFOOBJECTS WHERE SI_KIND = ‘FULLCLIENT’

Count of WebI reports & Instances

           SELECT COUNT (SI_ID) FROM CI_INFOOBJECTS WHERE SI_KIND = ‘WEBI’

Count of WebI reports (Only report templates)

           SELECT COUNT (SI_ID) FROM CI_INFOOBJECTS WHERE SI_KIND = ‘WEBI’ AND SI_INSTANCE=0

Count of WebI report instances

           SELECT COUNT (SI_ID) FROM CI_INFOOBJECTS WHERE SI_KIND = ‘WEBI’ AND SI_INSTANCE=1

Count of Folders

           SELECT COUNT (SI_ID) FROM CI_INFOOBJECTS, CI_APPOBJECTS, CI_SYSTEMOBJECTS WHERE SI_KIND = ‘FOLDER’

     * Please note I have included all three virtual tables for the folder count as it may exist in all the categories

Count of Categories

           SELECT COUNT (SI_ID) FROM CI_INFOOBJECTS WHERE SI_KIND = ‘CATEGORY’

Count of Program Objects

           SELECT COUNT (SI_ID) FROM CI_INFOOBJECTS WHERE SI_KIND = ‘PROGRAM’

Count of Shortcuts

           SELECT COUNT (SI_ID) FROM CI_INFOOBJECTS WHERE SI_KIND = ‘SHORTCUT’

Count of Agonistic documents

           SELECT COUNT (SI_ID) FROM CI_INFOOBJECTS WHERE SI_KIND IN (‘EXCEL’,’PDF’,’WORD’,’RTF’,’TXT’,’POWERPOINT’)

Count of Calenders

           SELECT COUNT (SI_ID) FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘CALENDAR’

Count of Events

          SELECT COUNT (SI_ID) FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘EVENT’

Count of Hyperlinks

          SELECT COUNT (SI_ID) FROM CI_INFOOBJECTS WHERE SI_KIND = ‘HYPERLINK’

3. Estimating Servers/Server groups and metrics

Server count

            SELECT COUNT (SI_ID) FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘SERVER’

Server group count

           SELECT COUNT (SI_ID) FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘SERVERGROUP’

Server metrics

           SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘SERVER’

At the end of the assessment you should be able to provide the below matrix

Parameters of Assessment value
Users
Named users
Concurrent users
User groups
Universes
Connections
Crystal reports
Full client reports
WebI report
WebI report templates
WebI report instances
Folders
Program Objects
Categories
Shortcuts
Calenders
Events
Agonistic documents
Servers
Server groups
Hyperlinks

Query Builder Blog series

Basics

             BusinessObjects Query builder – Basics

               BusinessObjects Query builder – Best practices & Usability

Sample Queries

             BusinessObjects Query builder queries

               BusinessObjects Query builder queries – Part II

               BusinessObjects Query builder queries – Part III

               BusinessObjects Query builder queries – Part IV

               BusinessObjects Query builder – Exploring Visualization Objects

              BusinessObjects Query builder – Exploring Monitoring Objects

              BusinessObjects Query builder – Exploring Lumira & Design studio Objects


Use cases

               BusinessObjects Environment assessment using Query builder

               BusinessObjects Environment Cleanup using Query builder

               BusinessObjects Query builder – What’s New in BI 4.0   

Assigned Tags

      30 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      As always one more good one from you Mani:)

      Author's profile photo Former Member
      Former Member

      Hello Mani, thanks for the useful examples. Where can i find a complete refrence of the functionality? For example i want to extract all SI_LOCALE from the SI_DATA off all users ..

      Thanks a lot

      Author's profile photo Former Member
      Former Member

      Mani,

      I am trying to find out the users who are disabled in CMC. I found SI_ALIASES and SI_DISABLED=TRUE, But some how my query was not working. Can you please help?

      Author's profile photo Roberto Wrembel
      Roberto Wrembel

      SELECT TOP 3000 * FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'User' and "SI_ALIASES.1.SI_DISABLED"=1

      Author's profile photo Venkateswara Y Guptha
      Venkateswara Y Guptha

      And here is the Java SDK sample code to retrieve the list of disabled users:

      package com.sap.businessobjects.bi.samples;
      
      import java.util.Iterator;
      import com.businessobjects.bcm.BCM;
      import com.crystaldecisions.sdk.exception.SDKException;
      import com.crystaldecisions.sdk.framework.CrystalEnterprise;
      import com.crystaldecisions.sdk.framework.IEnterpriseSession;
      import com.crystaldecisions.sdk.framework.ISessionMgr;
      import com.crystaldecisions.sdk.occa.infostore.IInfoObjects;
      import com.crystaldecisions.sdk.occa.infostore.IInfoStore;
      import com.crystaldecisions.sdk.plugin.desktop.user.IUser;
      import com.crystaldecisions.sdk.plugin.desktop.user.IUserAlias;
      import com.crystaldecisions.sdk.plugin.desktop.user.IUserAliases;
      
      public class listDisabledUsers {
      public static void main (String[] agrs) {
      String cmsname = null;
      String username = null;
      String password = null;
      cmsname = "localhost:6400"; //System Name where you are trying to login
      username = "administrator"; 
      password = "Password2"; // Password for Administrator user
      IEnterpriseSession boSession = null;
      ISessionMgr lSessionMgr = null;
      IInfoStore infoStore = null;
      try {
      	BCM.initializeSAPJCE();
      	lSessionMgr = CrystalEnterprise.getSessionMgr();	
      	System.out.println("LIST OF DISABLED USERS: ");
      boSession = lSessionMgr.logon(username, password, cmsname, "secEnterprise");
      infoStore = (IInfoStore)boSession.getService("", "InfoStore");
      for(int i=0;i<1;i++){
      int x=1;
      IInfoObjects users;
      users = infoStore.query("SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND='User' and \"SI_ALIASES.1.SI_DISABLED\" = 1");
      if(users.size() == 0)
      { break; }
      for(Iterator<?> iuser = users.iterator() ; iuser.hasNext() ; )
      {
      IUser user;
      IUserAliases userAliases;
      user = (IUser) iuser.next();
      userAliases = user.getAliases();
      for(Iterator<?> ialias = userAliases.iterator() ; ialias.hasNext() ; )
      {
      IUserAlias userAlias;
      userAlias = (IUserAlias) ialias.next();
      String authtype= userAlias.getAuthentication();
      if(userAlias.isDisabled())
      {
      System.out.println(x + "- Alias " + authtype + " of user '" + user.getTitle() + "' with id - " + user.getID() + " = Disabled.");
      }
      x++;
      }
      }
      }
      }
      catch (SDKException e) {
      	e.printStackTrace();
      } finally {
      	if (boSession!= null) {
      		boSession.logoff();
      	}
      }
      }
      }
      Author's profile photo Former Member
      Former Member

      Thank You Mani.  It is very helpful....

      Author's profile photo Onkar Velhal
      Onkar Velhal

      Thank you Mani.. You have really helped us to learn using Query Builder.

      Moreover, you can also use SAP BI Platform Support Tool: Read More,

      http://wiki.scn.sap.com/wiki/display/BOBJ/SAP+BI+Platform+Support+Tool

      Author's profile photo Former Member
      Former Member

      thanks

      Author's profile photo Former Member
      Former Member

      Hi Mani

      Is it possible to write a query on any of the virtual CMS repository tables like CI_SYSTEMOBJECTS using SQL server? Or can these tables only be used through the Query Builer?

      Thanks

      Kriti

      Author's profile photo Former Member
      Former Member

      Hello Kriti,

      you must be aware, that the query builder (and the infostore in the SDK) are querying againts the CMS process and NOT against the CMS database. and this is a guarantee, that you have always the same instance of an object. Having a real SQL query you could have just an obsolet version of an object. ... so, be happy about the CMS as partner and NOT a database!

      Sincerely

      Author's profile photo Sebastian Wiefett
      Sebastian Wiefett

      Hi Mani,

      pretty informative - Thanks for that. I have a little challenge, i need to count the users which have the "Interactive Viewing" rights assigned for WebI in a XI 3.1 Environment.

      I currently do not know how to "grab" the Interactive Viewing rights within my Statement.. Do you have any idea?

      Thanks and Regards

      -Seb.

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Thanks Seb. Here you go for the interactive viewer settings

      Int.png

      And the Query which I used for is

      SELECT TOP 10 SI_ID,SI_NAME, SI_DATA FROM CI_SYSTEMOBJECTS WHERE SI_KIND='USER'

      Author's profile photo Sebastian Wiefett
      Sebastian Wiefett

      Thanks for the Information Mani. This pushed me a step forward. I need to know how many users are assigned to these rights. Therefore i created the following Statement:

      SELECT COUNT (SI_ID) FROM CI_SYSTEMOBJECTS WHERE SI_DATA = 'INTERACTIVE_EDITING_VIEWER_USR_PROFILE'

      It throws me a result but i figured out that on any Environment the result is 35 🙂 independent of the amount of total users within the System.

      So i think i miss something and...i have other strenghts then writing SQL Statements.

      So if you can see quickly where my problems reside and can correct it i would much appreciate it.

      If not i continue to struggle with it until i have the result.

      Again, Thanks!

      Regards

      -Seb.

      Author's profile photo Sateesh Kumar Bukkisham
      Sateesh Kumar Bukkisham

      Hi Manikandan ,

      Can we get Last refresh date of a report?

      Requirement : To know reports in public folders not being used for a while.

      Author's profile photo Former Member
      Former Member

      Hi,

      Can anyone help me finding number of users by week, month accessing BI Launchpad using Query Builder.

      Thanks

      Author's profile photo Jawahar Konduru
      Jawahar Konduru

      You need to use Auditing for your purpose. Query builder will not give that results.

      Author's profile photo Former Member
      Former Member

      Thank You Jawahar. Really appreciate your help!

      Author's profile photo Former Member
      Former Member

      Can you please confirm the count for connection ?

      SELECT COUNT (SI_ID) FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'CONNECTION'

      SELECT COUNT(SI_NAME) FROM CI_APPOBJECTS where SI_KIND like '%connection%'

      Both do not seem to be giving out the same output?

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      you might have duplicate in SI_NAME

      Author's profile photo Ravikumar Periasamy
      Ravikumar Periasamy

      Hi Mani / All ,

      I need an query for BO 4.x .

      1. WebI reports without universe

      2. Universes without WebI reports

      3. Universe wise webi reports to get all.

      Thanks in advance.

      -Ravi

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Check this for 1 & 2 BusinessObjects Environment Cleanup using Query builder

      For 3 you may need to use an SDK based utility

      Author's profile photo Ravikumar Periasamy
      Ravikumar Periasamy

      Hi mani, That is not working from 4.x.

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Can you please let me which one the Query is not working for you  as expected? You may need to replace Single Quotes in the query.

      Author's profile photo Jon Fortner
      Jon Fortner

      #3. Get a list of Universes, then try this to find if they have WebI reports:

      SELECT SI_NAME,SI_ID FROM CI_InfoObjects, CI_AppObjects WHERE si_kind='WebI' AND Parents("SI_NAME='Webi-Universe'","SI_NAME='UniverseName'")

      Or you could just count them

      SELECT Count(SI_ID) FROM CI_InfoObjects, CI_AppObjects WHERE si_kind='WebI' AND Parents("SI_NAME='Webi-Universe'","SI_NAME='UniverseName'")

      Keep in mind that Query Builder enforces security, so you must use the Administrator Account to see them all, including ones in User Folders/Inboxes.

      Author's profile photo Former Member
      Former Member

      Can you please let me know if we could apply query to indentify the top X(Say 10) reports which takes long running time. So that we can colelct these reports and do optimization of those reports.

      Author's profile photo Jon Fortner
      Jon Fortner

      I think you have to use the Audit Data to get that. Look for the EVENT_DETAIL_TYPE_NAME of Processing Duration

      Author's profile photo Former Member
      Former Member

      Hello Mani,

      I would like to list the WEBI reports which have run successfully over the last six months. How can query them?

      Many Thanks,

      Vasanth

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      try something like this

      SELECT TOP 10000 SI_ID,SI_NAME,SI_STARTTIME,SI_ENDTIME,SI_SCHEDULE_STATUS FROM CI_INFOOBJECTS

      WHERE SI_SCHEDULE_STATUS in (1,3) AND SI_STARTTIME >= '2015.11.25.00:00:00'

      SI_SCHEDULE_STATUS


      1 - Success

      3 - Failure


      Thanks

      Mani

      Author's profile photo Former Member
      Former Member

      Hello Mani,

      Thank you very much for your prompt response and yes the query gives me the desired results.

      Thanks again.

      Vasanth

      Author's profile photo Jon Fortner
      Jon Fortner

      Mani, when I run the query for Connections, I get only 634. From Designer, I see 904 and in the CMC I see 904. Always using the Administrator account. Any idea on the big discrepancy?

      Oops, I found the issue, those are user or system connections. For Universe connections, use CCIS.DataConnection