BusinessObjects Environment Cleanup activity using Query builder

BusinessObjects Environment assessment using Query builder

This includes identifying unused BI content (Users, Universes, reports,etc.). We are going to see more in detail about identification of unused objects using Query Builder in this document. 

Let us see how to identify unused objects in each category listed below

 

 1. User Community

        a. Users not logged in for the past 6 months

           SELECT TOP 1000 SI_NAME, SI_LASTLOGONTIME FROM CI_SYSTEMOBJECTS 

          WHERE SI_NAME NOT IN ('ADMINISTRATOR','GUEST') AND SI_KIND='USER'

          AND SI_LASTLOGONTIME < '2013.06.30' ORDER BY SI_NAME

 

       b. Users who never logged in to system

 

           SELECT TOP 1000 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

 

       c. Users not in any group except Everyone group

 

           SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND='USER'

          AND SI_USERGROUPS.SI_TOTAL=1 ORDER BY SI_NAME

 

       d. User groups without user

 

           SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND='USERGROUP' AND

           SI_ENT_GROUP_MEMBERS.SI_TOTAL=0 ORDER BY SI_NAME

 

 

2. BI Content

 

       a. WebI reports without universe

 

          SELECT TOP 1000 * FROM CI_INFOOBJECTS

          WHERE SI_KIND='WEBI' AND SI_UNIVERSE.SI_TOTAL=0

 

       b. Universes without WebI reports

 

          SELECT * FROM CI_APPOBJECTS

          WHERE SI_KIND='UNIVERSE' AND SI_WEBI.SI_TOTAL=0

 

       c. Universes without connection association

 

          SELECT * FROM CI_APPOBJECTS WHERE SI_KIND='UNIVERSE'

          AND SI_DATACONNECTION.SI_TOTAL=0

 

       d. Connections without Universes (Only from BI4.x onwards)

 

          SELECT * FROM CI_APPOBJECTS

          WHERE SI_KIND= 'CCIS.DATACONNECTION'

          AND SI_CONNUNIVERSE.SI_TOTAL=0

       e. Categories without documents

          SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND='CATEGORY'

          AND SI_DOCUMENTS.SI_TOTAL=0

 

       f. Folders without any content (Only with in public folders)

 

          SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND='FOLDER'

          AND SI_CHILDREN=0 AND SI_ANCESTOR = 23

 

       g. Publications without subscriptions

 

          SELECT SI_ID, SI_NAME, SI_PUBLICATION_DOCUMENTS FROM CI_INFOOBJECTS

          WHERE SI_KIND='PUBLICATION' AND SI_PUBLICATION_DOCUMENTS.SI_TOTAL=0

 

 

3. Servers and Server groups

 

       a. Servers that are disabled

 

           SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND='SERVER' AND SI_DISABLED=1

 

       b. Server groups without servers

 

           SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND='SERVERGROUP'

          AND SI_GROUP_MEMBERS.SI_TOTAL=0 

See also

SAP Business Objects Query Builder

Query Builder:queries for extract reports refreshed after o before a specific date