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
I like the whole query builder series, thanks for sharing Mani.
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
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.
For deployment of Audit universe and reports refer here.
SAP BusinessObjects 4.0 Auditor Configuration & Deployment End to End
Regards,
Mani
Any way to determine all the reports that use a specific object in a universe?
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 Mani,
Can you reply to my question when you get a chance? Thanks
Any update on my question Mani?
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 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
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,
thanks, Do you have any query for which the reports are not run refreshed for past 3 years.
Thanks,
Shakthi.
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?
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
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.
There is no out of box functionality. But you can refer here http://scn.sap.com/thread/3296823
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_
Do you have 3rd party authentication? My Query will be applicable for only Enterprise authentication.
Thanks for clarifying Mani, and yes we do have AD and SAP authentication types enabled.
_panda_
Mani , I would need to delete all the Successful Instances that are piled up in Xi 3.1 Env. Whats the best Possible Way?
I would say Instance Manager or you can also limit the number of instances instead of deleting them all...!!
_panda_
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
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_
Hi Mani,
Is ther any way to identify duplicate reports(in BO 3.1) in repository with their query ?
Thanks & Regards,
Sachin K
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
How to establish those links back?
Is it Via "Update objects in CCM" and "RepoScan" options only
You need to go with SDK code something like this Remapping Universes for Webi Reports
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
No. You need to use SDK or some 3rd party tools
Ah, disappointing. Thank you for the reply though.
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
Dell, do you have sample RESTful code that retrieves the object names from a report?
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
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
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