Skip to Content

BusinessObjects Environment Cleanup using Query builder

As a subsequent step to BusinessObjects environment assessment BusinessObjects Environment assessment using Query builder we need to perform the Cleanup activity in the environment. 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 5000 SI_NAME, SI_LASTLOGONTIME FROM CI_SYSTEMOBJECTS

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

          AND SI_LASTLOGONTIME < ‘2013.12.25’ ORDER BY SI_NAME

       b. Users who never logged in to system

           SELECT TOP 5000 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 2000 * 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

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   

50 Comments
You must be Logged on to comment or reply to a post.
  • Hi Mani

    What a blog series Mani !!! Great..

    I am a fresher to SAP.

    Can you please provide the query for the number of logins by each user from a particular date?

    From where we can see the list of objects like SI_LASTLOGONTIME,

    SI_PUBLICATION_DOCUMENTS etc, so that we can see what all objects are there?

    Please help me

    Thanks

    Sandeep

  • HI Mani,

    I tried to use the query to retrieve empty groups (Without any members). It returned all the groups. We get the users from LDAP. Any changes required in the query for LDAP?

    SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USERGROUP’ AND

               SI_ENT_GROUP_MEMBERS.SI_TOTAL=0 ORDER BY SI_NAME

        • Hi Jawahar,

          Please consider below factors in mind.

          – Your LDAP group could have set of Subgroups. You can try extract those groups using SI_ENT_SUBGROUPS in your Select clause.

          – Your Querybuilder result set depends on how you mapped your 3rd party usergroups.

          Please clarify how you are mapping your LDAP group. This will enable us to proceed the empty usergroup extraction.

          Regards,

          Mani

          • Groups coming from LDAP to CMC, there are no sub groups. But with in CMC, some of the groups will be added to group hierarchy depending on the folder security.

  • Hi Mani,

    I may need your help again 😉 , i need a query to fetch the report name, corresponding universe name & its folder path (universe folder path from CMC) using the CUID of a report. is it possible to do this ? If not is there any other way to get this?

    I have 2 universes with same names under different folders and i want to know which report is pointing to which universe. Unfortunately I cannot find details of the full folder path of the universe in the properties of the report (from BI Launchpad reading mode).

    Thanks,

    Avinash

  • Hi Mani,

    Excellent blog. Is there any any ways we can find Webi reports & connection & universes which are not used more than 2 years.

    I am working with an 3.1 to 4.1 upgrade project. The system is running for more than ten years (from business Objects 5.x version) and migrated to 3.1 without cleansing. Now my client wants to remove all the unused reports before migrating to 4.1. Have you tried any query which can help me.

    Thanks,

    Shakthi.

    • I would suggest you identify the required reports first. then classify the required universe and connections that are associated with the reports above. this would be the right way to go. The reason is that the universe might not be modified for past 3 years but the associated reports might have been refreshed on daily basis. it is always go by the report -> universe -> connection route. Hope this helps.

  • Hi Mani

    When I use your code that returns a date, for example your “Users not logged in for the past 6 months” the dates returned are not in a nice format.

    e.g.  SI_LASTLOGONTIME values returned include

    9/9/13 10:16:10 AM

    7/15/13 11:21:21 AM

    12/9/13 3:34:55 PM

    12/10/13 1:45:00 PM

    6/14/13 11:08:00 AM

    So as you can see, day, month and hour  can be 1 or 2 digits, but minute and second are always 2 digits.

    Also the date is returned in US format (month/day/year) even though I am working on a UK system and if you use a date in a WHERE caluse you have to put it in 2013.12.25 format.

    So is there anyway to format the returned date?

    thanks

    Andrew

  • This is indeed very Useful info dear Manikandan Elumalai

    Is there a way I can write query and get following information

    SI_DESTINATIONS>>SI_DEST_SCHEDULEOPTIONS>>SI_MAIL_ADDRESSES

    and emaid id of person to whom report is schedule to go

    I use following query but this info is not appearing

    SELECT
    SI_DESTINATIONS.SI_DEST_SCHEDULEOPTIONS.SI_MAIL_ADDRESSES,
    SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE,

    SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_NTHDAY, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_MONTHS

    FROM CI_INFOOBJECTS

    WHERE SI_SCHEDULE_STATUS !=8  AND SI_RECURRING = 1

    • Hi Subhash, You cannot query more than one level within a property bag. You need to use SDK for sure. SI_DESTINATIONS.SI_DEST_SCHEDULEOPTIONS is allowed but not  SI_DESTINATION.SI_DEST_SCHEDULEOPTIONS.SI_MAIL_ADDRESSES. Thanks Mani

  • Can you help what query to use if I want to get duplicate reports , Execution rate , Unused report , dates last used?

    I tried Activity universe but it times out or may be not configured correctly.  I also used standard audit reports but they also either dont work or time out.

    Thanks

    Vishal

  • Hi Mani,

    Really very helpfull information. Can you please help me in finding the most frequently used reports(Webi) in BO. Client wants to know this information. They want to know which reports are frequently used which are not. By that way we can do some cleanup activity. Do you have any query that fetches this information.

    Thanks in Advance

    Regards,

    Rekha.

  • Hi Manikandan-

    I bookmarked your blog posts and I use them all the time, thank you very VERY much!

    I am in the process of updating some of the unverse code that drives data objects.  I want to be able to test all the reports that use the current code with the new code.

    Is there a way to get a list of all the reports that use a particular data item?  The goal would be to make a draft item with the new logic, test all the reports for validity and then replace the data item with the new one before deleting the old one.   A list like this would give us a list for testing.

    Thanks for any help-

    Patti 🙂

  • Just checking to see if there is any way?

    Hi Manikandan-

    I bookmarked your blog posts and I use them all the time, thank you very VERY much!

    I am in the process of updating some of the unverse code that drives data objects.  I want to be able to test all the reports that use the current code with the new code.

    Is there a way to get a list of all the reports that use a particular data item?  The goal would be to make a draft item with the new logic, test all the reports for validity and then replace the data item with the new one before deleting the old one.   A list like this would give us a list for testing.

    Thanks for any help-

    Patti 🙂

  • After identifying unused BI content through the above queries, how to clean them up through query builder? if so can you mention the commands pls.

    since we were looking to cleaning the instances in the users BI Inbox which are older then 10 days or with X number of instances. can this be done through query builder and the queries?

    thanks,

    Vish

  • Hi Mani,

    I was trying to get the blank user groups using the code above and it lists all groups for me

    SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USERGROUP’ AND  

               SI_ENT_GROUP_MEMBERS.SI_TOTAL=0 ORDER BY SI_NAME

    I searched for one group which had users and replaced the parameter SI_ENT_GROUP_MEMBERS.SI_TOTAL=0 with

    SI_REL_GROUP_MEMBERS.SI_TOTAL=0 and it rturns proper results, can you confirm if this is correct?

    _panda_

    • that many instances will be a problem via Instance Manager, do you have limits specified for number of instances to keep? If limit is not set and instances are so much, SDK is the way to go, but you have to be carefult while executing those commands…!!

      _panda_

  • This series is GREAT.

    Bookmarked first, now “digging in”

    My FIRST query is to “how many (webi) docs do we have, and can I have that number per (business entity) toplevelfolder ?

    As we have given the “business entities” their own folder (high level, not necssarily 1st or 2d though) and their reports thus reside “in their own tree” , I ‘d like to know whether it is possible to find “how many reports each tree has”.

    I do NOT want to count instances (yet), nor do I need info on “personal folders” as we are NOT planning to migrate those from BO XI3.6 to BI 4.x

    Can I make subqueries in query builder ?

    (This starts to look as if I will NEED that book/guide on QB … )

  • It appears SI_GROUP_MEMBER.SI_TOTAL=0 will not work, try this instead:

    SELECT SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USERGROUP’ AND SI_REL_GROUP_MEMBERS.SI_TOTAL=0 AND SI_GROUP_MEMBERS Is NULL ORDER BY SI_NAME


  • This below query is giving Universe list  with reports too Any guess?


      b. Universes without WebI reports

              SELECT * FROM CI_APPOBJECTS

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

    • If it is so. the report could have lost its universe reference in its si_webi property bag. Have you checked whether any of the reports have their references for their universes (SI_Universe)

      Thanks

      Mani

  • I have asked this question before and have not received an answer.

    Is it possible to write a query builder query that will return all the WebI reports that contain a particular universe item?

    For example, all the WebI documents in the HR universe that contain Employee Retirement Date?

    Thank you for any answer-

    Patricia Ciavarelli

    • Try this, be sure to replace ‘Your Universe Name’ with a real one from your system. I tested this on BI 4.1 SP5

      SELECT SI_NAME,SI_KIND,SI_UNIVERSE FROM CI_InfoObjects, CI_AppObjects WHERE si_kind = ‘WebI’ AND Parents(“SI_NAME=’Webi-Universe'”,”SI_NAME=’Your Universe Name'”)


      NOTE:It may include user folders, so you might want to add SI_PARENT_FOLDER or add this to the where clause — AND SI_ANCESTOR=23 for public folders only. Use SI_ANCESTOR=18 for Personal Folders.

      To get all Universes, you can use this query

      SELECT SI_NAME, SI_ID from ci_appobjects where si_kind=’Universe’

      This only gets you a list of WebI reports, there isn’t a way to get objects used in each report except via SDK as the others mentioned. For that, your code must open every WebI report to get those.

  • Hi Mani,

    Do you have a method to find Crystal reports that have not been viewed online in x days?  We would like to cleanup some old unused Crystal Reports if we could first identify which reports have not been viewed in a while.

    thanks,
    Jim

  • Dear Mani,

    I am trying to execute your mentioned query to retrieve list of Connections Without Universes (.UNX) but its throwing connections who already have Universes built over them:

    Select * From CI_APPOBJECTS Where SI_KIND=’CCIS.DATACONNECTION’ AND SI_CONNUNIVERSE.SI_TOTAL=0

    Any help will be appreciated.

    CH

  • Dear Mani,

     

    Could you please help me how to identify the number of users who have got EDIT access so that I  can provide only view access. which would reduce Business Expert licence.

  • Hi Mani,

     

    Could you please let me know how to reduce Business Expert licence. what are the factors that I have to consider and reduce my licence.

  • Hi Mani

    how do I get at data when multiple sub rows are return?

    SELECT  SI_NAME , SI_ALIASES

    FROM CI_SYSTEMOBJECTS

    WHERE SI_KIND=’USER’

     

    This returns multiple aliases for a user and I want to show which have SI_DISABLED = false

    But this does not work

    SELECT  SI_NAME

    FROM CI_SYSTEMOBJECTS

    WHERE SI_KIND=’USER’

    AND SI_ALIASES.SI_DISABLED= 0

     

    and I cannot find a CHILDREN or PARENT relationship for this

     

    thanks

    Andrew