BusinessObjects Query builder queries - Part II

[former_member182521]

Dear Folks,

This is the continuation of my previous blog on Query builder queries

http://scn.sap.com/community/bi-platform/blog/2012/10/11/businessobjects-query-builder-queries 

Here we go

To list Universe that doesn’t associated with any WebI reports

SELECT si_id,si_name,si_webi,si_cuid FROM CI_AppObjects

WHERE si_kind = 'Universe' and SI_WEBI.SI_TOTAL=0

To list Universes with more than one connections (multi source universe)

SELECT si_id,si_name,si_webi,si_cuid FROM CI_AppObjects

WHERE si_kind = 'Universe' and SI_DATACONNECTION.SI_TOTAL>1

To list WebI reports that doesn’t associated with any universe

SELECT TOP 50000 si_id,SI_NAME FROM CI_Infoobjects

WHERE si_kind = 'WebI' AND SI_INSTANCE=0 and SI_UNIVERSE.SI_TOTAL=0

To list reports and documents those are in public folders including Sub folders. (Excluding instances, personal documents and inbox documents)

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND IN ('FullClient', 'Txt', 'Excel', 'Webi', 'Analysis', 'Pdf', 'Word', 'Rtf', 'CrystalReport', 'Agnostic') AND SI_RUNNABLE_OBJECT = 0 AND SI_INSTANCE_OBJECT = 0 AND SI_ANCESTOR = 23

Find all the WebI reports that use a specific universe

SELECT SI_ID, SI_NAME, SI_WEBI, SI_OWNER FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS

Where PARENTS ("SI_NAME = 'Webi-Universe'", "SI_NAME = 'Universe Name'")

To List of all Groups with Subgroups

Select SI_ID, SI_ALIASES, SI_DESCRIPTION, SI_NAME, SI_USERGROUPS, SI_GROUP_MEMBERS from CI_SYSTEMOBJECTS  where si_kind = 'UserGroup'

To get a list of Full Client reports

SELECT SI_ID, SI_NAME, SI_KIND FROM CI_INFOOBJECTS WHERE SI_KIND = 'FullClient'

To get a list of available Calendars 

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_PARENTID=22

To get a list of Users along with their personal folder

SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=18

To get a list of Users along with their inbox

SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=48

To get a list of available categories

SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=45

To count total number of connection on a particular day (Today)

SELECT count (SI_CREATION_TIME) FROM CI_SYSTEMOBJECTS

WHERE SI_LASTLOGONTIME> '2013.02.14.00.00.01' AND SI_KIND = 'Connection'

To get total number of unique users logged in to the system on a particular day (Today)

SELECT count (SI_NAME)  FROM CI_SYSTEMOBJECTS

WHERE SI_LASTLOGONTIME> '2013.02.14.00.00.01' AND SI_KIND = 'Connection'

Points to consider while querying  

• the default limit for returning objects would be 1000 objects normally. In order to get more than 1000 objects we need to use ‘Top N’ function before the column listing in the query. For ex.  SELECT Top 2000 * FROM CI_INFOOBJECTS where Si_KIND='WebI’
• You are not allowed to use Sub queries.
• The order of columns in the SELECT clause has no impact as the results will be rendered in its own order

Hope you find this interesting. Just give a try in your environment and share your findings. Keep reading!

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