administrative tool - query builder

system · February 22, 2006, 4:02pm

Can someone point me to any documentations for Query Builder (in administrative tools)?

Any data model diagram or data dictionary?
Is this stored in the repository database tables?

This is very different from the BO v651 architecture. Also I’ve looked in the database tables created for CMS, there are only 7 tables in there and they do not have match with the sample queries.

Thanks

ku916 (BOB member since 2004-12-14)

system · December 20, 2006, 10:38pm

I haven’t found a document, but here are some Queries to get you started.

BO XI R2 Query Builder Notes
To get a full list of Universes with relevant info for migration activities:

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

To get a list of Universes that match a pattern:

SELECT SI_CUID, SI_ID, SI_NAME, SI_DESCRIPTION
FROM CI_APPOBJECTS
WHERE SI_KIND='Universe' AND SI_NAME LIKE 'DX_N%'

To get detailed info on a particular Universe, Note the SI_ID and use this

SELECT *
FROM CI_APPOBJECTS
WHERE SI_KIND='Universe' AND SI_ID=1030

To get a list of all Application Objects and what kind they are:

SELECT SI_CUID, SI_ID, SI_NAME, SI_DESCRIPTION, SI_KIND
FROM CI_APPOBJECTS

To get a list of Full Client reportS, run this SQL:

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

Leave the Where clause off to see all

To get a list of reports in a particular folder, 1st you need the parent folder ID, then use it to list reports

To find the parent ID, that is the folder ID, run this query and look for your report names

SELECT SI_NAME, SI_KIND, SI_DESCRIPTION, SI_PARENTID FROM CI_INFOOBJECTS WHERE SI_KIND IN ('FullClient', 'Webi')

For me, the PARENT_ID I was interested in was 383. Now run this query to get a list of Full Client and WebI reports from that folder.

SELECT SI_NAME, SI_KIND, SI_DESCRIPTION FROM CI_INFOOBJECTS WHERE SI_KIND IN ('FullClient', 'Webi') AND SI_PARENTID=383

pcgeekus (BOB member since 2003-10-16)

system · December 20, 2006, 10:51pm

There is no documentation or data model on that regard. The Query Builder is a very primitive tool for very simple query. If the query is returning hundreds of records, the HTML display is practically useless.

The SDK can provide you more flexibility and more power. And the java_doc gives you a world of information. But of course, you need to have programming skill.

Hope this helps.

substring (BOB member since 2004-01-16)

system · January 25, 2007, 6:35am

Hi
I came across this tool -‘Query Builder’. Tried to look more into it.
I understand that Query Builder is a simple tool for querying. But am still not very clear about it and would like to know what is actually being queried, what are the tables that can me queried and when would this query builder actually be used?

Any help would me appreciated.
Thanks
Ahila

ahilas (BOB member since 2006-03-29)

system · January 31, 2007, 8:10pm

thanks!

I started to build query in query builder. Its not too difficult to figure out, but its a pain in the ass way to do something simple. But I’m haviing update the TIMESTAMP field, its not Oracle I know, cause it failed on SI_TIMESTAMP = sysdate and SI_TIMESTAMP = ‘21-NOV-06’

Here is a log of what I’ve done

1st look for objects with similar report name

SELECT * FROM CI_INFOOBJECTS WHERE SI_NAME LIKE '%Business Summary%'

it returned 4 rows as there are 4 history instances.

the SI_TIMESTAMP field is the timestamp shown in Webi as instance time.
they you can get SI_OBJID the for that instance
12799 is my object ID, run this to confirm its the report instance I want

SELECT * FROM CI_INFOOBJECTS WHERE SI_ID = 12799

UPDATE CI_INFOOBJECTS SET SI_TIMESTAMP = sysdate WHERE SI_ID = 12799 -- failed

UPDATE CI_INFOOBJECTS SET SI_TIMESTAMP = '21-NOV-06' WHERE SI_ID = 12799 -- failed

Carlton

ku916 (BOB member since 2004-12-14)

system · February 1, 2007, 2:39pm

I believe the format for the timestamp is:

‘2006.11.26.00:00:00’

kgyoung (BOB member since 2006-08-31)

system · February 1, 2007, 2:51pm

nope it does not work.

UPDATE CI_INFOOBJECTS set SI_ENDTIME = ‘2006.11.26.00:00:00’ WHERE SI_ID = 12844

There was an error retrieving data from the server: An error occurred at the server : Not a valid query.

I’ve just read the developer lib on business objects, it said the property that I’m trying to update is READ-ONLY.

Carlton

ku916 (BOB member since 2004-12-14)

system · February 2, 2007, 4:00pm

I don’t think you can use Update. You are querying the system database. It should not allow Updates, should only allow Selects.

A_bouser (BOB member since 2004-10-05)

system · February 6, 2007, 9:50pm

Correct. Although you can implement a vbscript to update the system tables through the backend.

crystal01 (BOB member since 2006-08-30)

system · March 23, 2007, 6:55pm

Is there a way to just build a universe off of the CMC and just run queries off of it without using query builder.

We have auditor but it does not proivde the same flexibility of reporting directly off the CMC.

Zac Adam (BOB member since 2002-11-12)

system · March 23, 2007, 7:51pm

That’s not possible. It is because the data in system database are in binary format. You cannot query it without going thur SDK.

I believe if we raise enough hell, BOBJ will probably give us a flexible tool to access the CMC information. But we might have to wait till release 3, or at least SP3.

substring (BOB member since 2004-01-16)

system · March 27, 2007, 1:18pm

The administrative tools provided in XI are really pretty disappointing. Their answer to everything seems to be that you can “just write a program to do it”. For example I brought up to BO the fact that they could make the instance manager much more useful by allowing changes to the jobs directly from there, or at least a little more control than they currently allow.

I guess they aren’t interesting in creating useful admin tools because they mainly sell to the business users.

b_demann (BOB member since 2003-03-20)

SebastienGoiffon · March 27, 2007, 1:24pm

Hi,

That’s exactly my feeling also !!

Regards

system · March 27, 2007, 1:26pm

Yeah the audit universe is pretty poor. I couldnt find much documentation on the tables and it doesnt give me what I need, without a lot of legwork.
The administration has always been a low spot for BO.

Zac Adam (BOB member since 2002-11-12)

system · February 28, 2013, 10:41am

Someone knows if is it possible to get more detailed information on query builder about a universe? Like class, objetcs, objects description, object SQL.
I need to make a spread sheet only with these information.

bogardenia (BOB member since 2013-02-28)

system · February 28, 2013, 12:52pm

Not possible using query builder, since that info is not in the repository. But check out the universe documentor in Bob’s Downloads – that should do what you need.

Joe

joepeters (BOB member since 2002-08-29)