Skip to Content

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   

To report this post you need to login first.

47 Comments

You must be Logged on to comment or reply to a post.

  1. sandeep s

    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

    (0) 
    1. Manikandan Elumalai Post author

      Thanks Sandeep. You cannot get this information (Logon by day) using Query Builder as this is no where stored in CMS database instead in Auditor if you enabled auditing. below pre developed audit reports should help you.

      • Users by Day – Logon events
      • Users by Day
      • Users by Day and Hour – Logon events and sessions

      For deployment of Audit universe and reports refer here.

      SAP BusinessObjects 4.0 Auditor Configuration & Deployment End to End

      Regards,

      Mani

      (0) 
  2. Jawahar Konduru

    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

    (0) 
        1. Manikandan Elumalai Post author

          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

          (0) 
  3. Avinash Kumar

    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

    (0) 
  4. Shakthi Raj Natarajan

    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.

    (0) 
    1. Manikandan Elumalai Post author

      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.

      (0) 
      1. Shak N

        Hi Mani,

        thanks, Do you have any query for which the reports are not run refreshed for past 3 years.

        Thanks,

        Shakthi.

        (0) 
  5. Andrew Dale

    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

    (0) 
  6. Subhash Upadhyay

    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

    (0) 
    1. Manikandan Elumalai Post author

      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

      (0) 
  7. Vishal D

    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

    (0) 
  8. surekha l

    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.

    (0) 
  9. Patricia Ciavarelli

    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 🙂

    (0) 
  10. Patricia Ciavarelli

    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 🙂

    (0) 
  11. Raghuwansh Raghuwansh

    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

    (0) 
    1. Jawahar Konduru

      You cannot use query builder to clean up the instances or unused reports. Query builder is just to show what is availble in repository. You cannot use DML commands.

      Use instance manager to clean up the instances.

      (0) 
  12. Smruti Panda

    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_

    (0) 
    1. Smruti 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_

      (0) 
  13. Sachin K

    Hi Mani,

    Is ther any way to identify duplicate reports(in BO 3.1) in repository with their query ?

    Thanks & Regards,

    Sachin K

    (0) 
  14. Hans RENS

    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 … )

    (0) 
  15. Jon Fortner

    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

    (0) 
  16. Sivakanth Panchagnula


    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

    (0) 
    1. Manikandan Elumalai Post author

      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

      (0) 
  17. Patricia Ciavarelli

    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

    (0) 
        1. Dell Stinnett-Christy

          The information about which objects are used is contained in the reports themselves, not in the CMS database.

          If you’re on version 4.x, you can use the RESTful web services to get the information about which objects are used in a report.  Unfortunately, you would have to look at all of the reports because there is no way to query for just one object within the report.

          -Dell

          (0) 
    1. Jon Fortner

      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.

      (0) 
  18. Jim Hinman

    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

    (0) 
  19. Chaitanya Bhure

    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

    (0) 

Leave a Reply