BusinessObjects Board

Query Builder

general discussion
#1

Hi

Can somebody help me to understand what is mean by Query Builder in BO and where can I find it and its uses?

Thanks.

stvel (BOB member since 2007-10-04)

Help! About CI_INFOOBJECTS
Query Builder
CMS Database Structure
find reports dependant on event
BO Query Builder - List of Reports
determine reports based on a particular universe
query builder syntax
Query Builder Issue
#2

You can get some info out of Query Builder, here are some notes to help:
BO XI R2 Query Builder Notes

  • Queries are limited to returning 1000 objects
  • Cannot use subqueries as IN lists
  • The order of fields in the SELECT has no effect, it renders the results in it’s own order.
  • SI_KIND for CI_INFOOBJECTS includes ‘Webi’, ‘Pdf’, ‘Excel’, ‘Folder’, ‘FullClient’, ‘FavoritesFolder’, ‘Inbox’, ‘PersonalCategory’, ‘Shortcut’, ‘MyInfoView’, ‘AFDashboardPage’
  • SI_KIND for CI_APPOBJECTS includes ‘Universe’, ‘Folder’, ‘MetaData.DataConnection’,‘ReportConvTool’, ‘WebIntelligence’, ‘Discussions’, ‘InfoView’, ‘CMC’, ‘busobjReporter’, ‘Designer’, ‘AdHoc’
  • SI_KIND for CI_SYSTEMOBJECTS includes ‘User’, ‘UserGroup’,’‘Connection’,secWinAD’, ‘secLDAP’, ‘secWindowsNT’


Note: Query Buiilder Navigation: Use the XI Launchpad URL and Query Builder is listed in the lefthand navigation links.
I red somewhere on forum:

Some Queries:

To get all universe data including a WebI report list
SELECT *
FROM CI_APPOBJECTS
WHERE SI_KIND=‘Universe’ AND SI_NAME=‘CCE Mass Channel’

Then, use the list of IDs to get a list of report names that use this Universe
SELECT SI_NAME,SI_DESCRIPTION, SI_ID,SI_AUTHOR,SI_PARENT_FOLDER,SI_UNIVERSE,SI_HAS_PROMPTS
FROM CI_INFOOBJECTS
WHERE SI_ID IN (nnnnn,nnnnn,nnnnn)

How do I get a list of all connections matching a substring
SELECT SI_NAME
FROM CI_APPOBJECTS
WHERE SI_KIND=‘MetaData.DataConnection’ AND SI_NAME LIKE ‘%CFD%’

To list all WebI reports when objects exceed 1000
SELECT SI_TARGETID,SI_KIND,SI_UPDATE_TS,SI_NAME,SI_ID,SI_SENDABLE,SI_OWNER,SI_FILES,SI_DESCRIPTION,SI_UNIVERSE,SI_PARENT_FOLDER,SI_OWNERID,
SI_SUBMITTERID,SI_STARTTIME,SI_ENDTIME,SI_WEBI_PROMPTS,
FROM CI_INFOOBJECTS
WHERE SI_KIND=‘Webi’ and SI_NAME > ‘L’

then use <= ‘L’

To list all report folders containing a string
SELECT *
FROM CI_INFOOBJECTS
WHERE SI_NAME LIKE ‘%Dealer%’ AND SI_KIND=‘Folder’

For Universe folders, use CI_APPOBJECTS

To list all Webi reports containing a string
SELECT *
FROM CI_INFOOBJECTS
WHERE SI_NAME LIKE ‘%Dealer%’ AND SI_KIND=‘Webi’

SELECT SI_NAME, SI_SHORTNAME, SI_DESCRIPTION, SI_ID, SI_PARENT_FOLDER, SI_OWNER, SI_CREATION_TIME
FROM CI_APPOBJECTS
WHERE SI_KIND=‘Universe’

aniketp :uk: (BOB member since 2007-10-05)

#3

Some facts are wrong in the above post.

  • Queries are not limited to returning 1000 objects. If we have CMS database of bigger size then what do you think we can get the info.

There is a registry setting on the server that we need to change. :blue:

XXX :madagascar: (BOB member since 2007-09-04)

#4

You can use TOP n to retrieve more than 1000 rows:

SELECT TOP 1000000 * FROM ci_infoobjects

joepeters :us: (BOB member since 2002-08-29)

#5

http://www.waynewestbrooks.com/?page_id=20

MikeD :south_africa: (BOB member since 2002-06-18)

#6

Can you tell us what is that setting ?

Moderator edit: Rude comment removed. - Michael

aniketp :uk: (BOB member since 2007-10-05)

#7

Hi Aniket,

Can you please help me getting the users list with their associated folders using query builder in R3 ?

I tried with your queries, but couldn’t get the result.
I even checked with the below URL for the properties on CI_INFOOBJECTS, the result is same. The properties such as SI_USERGROUPS doesn’t give any result.

http://devlibrary.businessobjects.com/BusinessObjectsXIR2SP2/en/en/BOE_SDK/boesdk_dotNet_doc/doc/boesdk_net_doc/html/QueryLanguageReference2.html#1175703

Can you help on some queries with BO XI R3.
[/url]

nvlashok (BOB member since 2005-10-20)

#8

YOU STAAARRRRRRRR!!! WOO HOO!!! :mrsbob: :mrgreen: :mrsbob: :mrgreen: :mrsbob: :mrgreen:

We’ve breached 1000 reports on our weekend run now and this was causing us some real issues when checking statuses. NOT ANYMORE THOUGH!!

Thanks Joe - Thanks BOB!

rob_bo :uk: (BOB member since 2005-08-04)

#9

Query Builder does not expose security information so it is not possible to do what you are looking for with this tool.

BoB LoblaW :us: (BOB member since 2007-10-23)

#10

CAN i get information from CMS that when event is triggered how many jobs are running based on it.

i have 15 events triggered during the day so i need to know trace how many jobs based on each event is running or failing.

BOnd007 :india: (BOB member since 2009-08-11)

#11

I don’t know of a way to accomplish this efficiently without involving the SDK. It may be feasible to use a very manual method if you don’t have many jobs using an event and you use XI 3.x. Get the event ID then query for all your pending jobs using and event with this query:

select si_name, si_scheduleinfo from ci_infoobjects where si_runnable_object = 1 and si_scheduleinfo.si_dependencies.si_total > 0

You would then use the find feature in the browser to find instances of the event ID.

BoB LoblaW :us: (BOB member since 2007-10-23)

#12

One of the SAP support team folks briefly touched on Query Builder when I logged a message on a different subject. He was vague as to the status of it.

Is it ‘AE’-ware available on a user-beware basis, or a fully-supported part of the suite?

twofivepie :uk: (BOB member since 2008-10-16)

#13

I have not heard of anything to suggest Query Builder is unsupported. In fact, SAP techs often use it when troubleshooting tickets with my system and I use it almost daily.

BoB LoblaW :us: (BOB member since 2007-10-23)

#14

Is there a manual?

twofivepie :uk: (BOB member since 2008-10-16)

#15

The closest thing to a manual I’ve seen is the developer guide. There is a section titled How do I use the query language to retrieve objects from the CMS repository? which provides a basic overview.

BoB LoblaW :us: (BOB member since 2007-10-23)

#16

i need to get information of all users and their groups…how should i go about this.

i am using BO XIR3.1.

BOnd007 :india: (BOB member since 2009-08-11)

#17

For group membership, you could start with something like the following:

select top 50 si_id, si_group_members, si_usergroups
from ci_systemobjects
where si_kind = ‘UserGroup’

The contents of the si_group_members will contain the SI_IDs of the users within the group.

You can then lookup information on the users:

select top 50 si_id, si_name, …
from ci_systemobjects
where si_kind = ‘User’

With this approach you’ll need to hack together the 2 sources of information to get anything approaching a report format.

crystal01 :us: (BOB member since 2006-08-30)

#18

MikeD :south_africa: (BOB member since 2002-06-18)

#19

how i can get disable users list from query builder. i am using BO XI R3.1.
i tried to use tool mentioned above but it gives session manager error.

BOnd007 :india: (BOB member since 2009-08-11)

#20

do anyone have PDF of Query Builder Guide ? if could share that would be very great help…
thanks in advance.

Vills :india: (BOB member since 2007-10-24)