Skip to Content
Author's profile photo Manikandan Elumalai

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   

Assigned tags

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

      I like the whole query builder series, thanks for sharing Mani.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog 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

      Author's profile photo Former Member
      Former Member

      Any way to determine all the reports that use a specific object in a universe?

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      Hi Mani,

      Can you reply to my question when you get a chance? Thanks

      Author's profile photo Former Member
      Former Member

      Any update on my question Mani?

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog 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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      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

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

      Hi Avinash,

      You may required to go for a SDK code as this is not possible using a single query. Have a look here. http://www.forumtopics.com/busobj/viewtopic.php?t=202819

      Regards,

      Mani

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog 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.

      Author's profile photo Former Member
      Former Member

      Hi Mani,

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

      Thanks,

      Shakthi.

      Author's profile photo Andrew Dale
      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog 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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Patricia Ciavarelli
      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 🙂

      Author's profile photo Patricia Ciavarelli
      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 🙂

      Author's profile photo Raghuwansh Raghuwansh
      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

      Author's profile photo Former Member
      Former Member

      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.

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

      There is no out of box functionality. But you can refer here http://scn.sap.com/thread/3296823

      Author's profile photo Former Member
      Former Member

      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_

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

      Do you have 3rd party authentication? My Query will be applicable for only Enterprise authentication.

      Author's profile photo Former Member
      Former Member

      Thanks for clarifying Mani, and yes we do have AD and SAP authentication types enabled.

      _panda_

      Author's profile photo Former Member
      Former Member

      Mani , I would need to delete all the Successful Instances that are piled up in Xi 3.1 Env. Whats the best Possible Way?

      Author's profile photo Former Member
      Former Member

      I would say Instance Manager or you can also limit the number of instances instead of deleting them all...!!

      _panda_

      Author's profile photo Former Member
      Former Member

      That would not really help in deleting 20000 Historical instances in my case Panda. It can limit the Instances .

      I checked below   but i'm not a SDK Guy. Looking for easy option using Query builder or Excel based Macro.

      I checked this  : http://scn.sap.com/community/bi-platform/java-sdk/blog/2013/03/19/cleaning-up-large-numbers-of-instances

      Author's profile photo Former Member
      Former Member

      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_

      Author's profile photo Former Member
      Former Member

      Hi Mani,

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

      Thanks & Regards,

      Sachin K

      Author's profile photo Hans RENS
      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 ... )

      Author's profile photo Jon Fortner
      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

      Author's profile photo Former Member
      Former Member


      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

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog 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

      Author's profile photo Former Member
      Former Member

      How to establish those links back?

      Is it Via "Update objects in CCM" and "RepoScan" options only

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

      You need to go with SDK code something like this Remapping Universes for Webi Reports

      Author's profile photo Patricia Ciavarelli
      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

      Author's profile photo Jawahar Konduru
      Jawahar Konduru

      No. You need to use SDK or some 3rd party tools

      Author's profile photo Patricia Ciavarelli
      Patricia Ciavarelli

      Ah, disappointing.  Thank you for the reply though.

      Author's profile photo Dell Stinnett-Christy
      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

      Author's profile photo Jon Fortner
      Jon Fortner

      Dell, do you have sample RESTful code that retrieves the object names from a report?

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy

      Not yet - I'm working on writing a utility for that right now but it may be a couple of weeks before it's ready.

      -Dell

      Author's profile photo Jon Fortner
      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.

      Author's profile photo Jim Hinman
      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Andrew Dale
      Andrew Dale

      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

      Author's profile photo Brian Sutherland
      Brian Sutherland

      Hi Mani,

       

      I need you help to find the deleted user accounts, i don;t have the audit enabled.

       

      Is there any query to fetch all these details like deleted user account name and who deleted, date and time.....etc

      Thanks @ Regards,

      Rajendra