Manikandan Elumalai

June 12, 2013 1 minute read

BusinessObjects Query builder – Best practices & Usability

After series of blogs on Query builder, in this blog I would like to share best practices with the typical usability in Query builder. Here we go

Useful Functions in Query Builder

Function	Desription
COUNT	Retrieves the number of distinct values of a property
TOP	specifies the maximum number of object to be returned

– Count of Universes

SELECT COUNT (SI_ID) FROM CI_APPOBJECTS WHERE SI_KIND = ‘UNIVERSE’

– Top 10 WebI reports order by their number of Instances

SELECT TOP 10 * FROM CI_INFOOBJECTS WHERE SI_KIND = ‘WEBI’ ORDER BY SI_CHILDREN DESC

Useful operators in Query Builder

Operators
=
!=
<
>
<=
>=
IN
LIKE
BETWEEN
ALL

– Users created between a date ranges

SELECT SI_ID,SI_NAME, SI_CREATION_TIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘User’ AND SI_CREATION_TIME BETWEEN ‘2013.06.01′ AND ‘2013.06.10’

– To get the list of all WebI and FullClient reports

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND IN (‘WEBI’ ,’FULLCLIENT’)

– User who is member of more number of groups

SELECT SI_ID, SI_NAME, SI_USERGROUPS FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘USER’ AND SI_USERGROUPS >= ALL SI_USERGROUPS

Working with Date/Time

– Reports that are last updated on 28^thMay 2013 (Date comparison)

SELECT SI_ID, SI_NAME, SI_UPDATE_TS FROM CI_INFOOBJECTS WHERE SI_KIND = ‘WebI’ AND SI_UPDATE_TS = ‘2013.05.28’

– Users who are created on or after 18^thMar 2013 3PM (Date/Time comparison)

SELECT SI_ID, SI_NAME, SI_CREATION_TIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘User’ AND SI_CREATION_TIME >= ‘2013.03.18.15:00:00’

Frequently used properties of Infoobjects

InfoObject property	Property description
SI_ID	Unique id generated for the Infobject within the environment
SI_NAME	Name of the InfoObject
SI_KIND	Type of the Infoobject
SI_CUID	Cluster Unique Identifier of the InfoObject
SI_GUID	Global Unique Identifier of the InfoObject
SI_RUID	Unique id of the Infobject within the Object package
SI_PARENTID	Parent Objects SI_ID
SI_OWNERID	SI_ID of the InfoObject’s owner
SI_CREATION_TIME	Creation time of InfoObject
SI_UPDATE_TS	Last updated time of InfoObject

Here is the query with all the properties listed above in a single query

SELECT SI_ID, SI_KIND, SI_NAME,SI_CUID, SI_GUID, SI_RUID, SI_PARENTID, SI_OWNERID, SI_CREATION_TIME,SI_UPDATE_TS FROM CI_INFOOBJECTS WHERE SI_KIND=’WEBI’

Hope you find this interesting.

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

35 Comments

You must be Logged on to comment or reply to a post.

Former Member

June 12, 2013 at 5:30 pm

Excellent document, one question, can i used to both version 3.1 and 4.0

Thanks!

Manikandan Elumalai

Blog Post Author

June 13, 2013 at 5:24 am

Hi Vives,

Thanks for your comments. You can use this for both the versions.

Regards

Mani

Former Member

August 27, 2013 at 5:52 pm

Good Documents.. Thank you SCN for the knowledge sharing.

Former Member

August 27, 2013 at 6:22 pm

great post..

Shoban kumar

September 2, 2013 at 10:06 am

Nice document Mani...

Former Member

December 8, 2013 at 3:33 pm

Hi Mani

I need your help in Querying CMS to get the Connection Info for OLAP Analysis tool in BO4.0.

Please note we are looking to show the BEX Query Names used in the Report.

Any suggestion are most well

Regards

Dammi

Former Member

January 3, 2014 at 11:44 am

Good post Mani!!

I have a question here regarding query builder:

Can we find out, one particular universe object(dimension/measure) is used in which all reports? If yes, can you please provide the query.

Thanks in Advance!!

Tiji.

Manikandan Elumalai

Blog Post Author

January 3, 2014 at 12:27 pm

This is not possible as this is no where stored in CMS database. I would suggest you to try this with BOMM (BO Metedata manager) or Information steward to perform your required lineage analysis.

Former Member

January 6, 2014 at 7:36 am

Thanks Mani for the info!!

Former Member

April 25, 2014 at 2:16 pm

Hello Mr Manikandan,

Excellent work, I have a Question,

Can I use group functions, SUM for example,?

Can I use "Group by" ??

Tanks you.

Best regards.

Ritesh Kothari

May 2, 2014 at 4:35 pm

Hi Mani,

These are great posts.

I have a question.

I want to find list of reports within a particular folder either in 3.x or in 4.x environment. Is there any way to do it via Query builder?

Thanks,

Ritesh

Ritesh Kothari

May 2, 2014 at 4:57 pm

Sorry...didn't see your earlier post. I got what I wanted. Thanks for the great work. Very helpful.

Former Member

May 5, 2014 at 4:58 am

Nice stuff.. helps a lot!!

Regards,

Atul B

Former Member

May 8, 2014 at 11:30 am

Hi,

It could be possible understand if there is a query for extracting the list og groups and associated users in only one query? we have many users (>1000) and many groups. So it could be very helpful to extract the list of groups with associated users in a query.

Thank you

Best Rehards

Former Member

July 22, 2014 at 6:30 am

HI Emanuela,

You can probably use the code mentioned in below link:

http://scn.sap.com/docs/DOC-6258

This is for XI 3.1 but you can optimize this for BI 4 easily.

Regards,

Jatin

Former Member

May 15, 2014 at 8:42 am

HI Mani,

There is huge performance issue when we include date/time in where clause.

Is there something missing on repo indexing side or am I missing something while designing the query?

Here is an example:

SELECT top 100 SI_ID, SI_NAME, SI_OWNER from CI_INFOOBJECTS WHERE SI_INSTANCE = 1 and SI_KIND != 'MDAnalysis' and si_recurring = 0 and si_endtime >= '2014.05.14.18.00.00'

SELECT top 100 SI_ID, SI_NAME, SI_OWNER from CI_INFOOBJECTS WHERE SI_INSTANCE = 1 and SI_KIND != 'MDAnalysis' and si_recurring = 0 and si_endtime >= '2014.05.14'

These query times out. (I can increase the timeout from registry, but not planning at this moment)

SELECT top 100 SI_ID, SI_NAME, SI_OWNER from CI_INFOOBJECTS WHERE SI_INSTANCE = 1 and SI_KIND != 'MDAnalysis' and si_recurring = 0

Now this one returns instantly.

Any tips would be really helpful.

Thanks in advance.

Regards,

Prakhar T

Former Member

July 22, 2014 at 6:19 am

Hi Prakhar,

Instead of using SI_RECURRING=0 try using SI_SCHEDULE_STATUS=9.

schedulestatus 9 would include all your pending ones and another should be for schedulestatus 0 for all the running instances.

This would decrease the query cost.

Query should be:

SELECT
SI_ID, SI_NAME, SI_SCHEDULE_STATUS, SI_ENDTIME
FROM
CI_INFOOBJECTS
WHERE
SI_ENDTIME >= '2014.05.14.18.00.00'
AND (SI_SCHEDULE_STATUS = 9 OR SI_SCHEDULE_STATUS = 0)

Hope this one helps.

Regards,

Jatin

Former Member

July 22, 2014 at 6:46 am

HI Jatin,

Thanks for the response.

Actually I am trying to pull data only for pending instances.

If I use si_schedule_status=9 alone; without si_recurring=0, it gives me pending + recurring.

To overcome the issue I explained above, we tried to reduce the number of instances (including those in personal folders). Though the performance was not equivalent to query without date filter, but it started work.

Our DBA had few recommendations, which were not implemented and are kept as backup plan in case of contingency.

Regards,

Prakhar T

Former Member

July 21, 2014 at 6:16 am

I'm trying to build a software that collects information about the number of users/connections/reports etc. per day on a BO server. The program is being written in Java so we are using JDBC to connect to the database. Can these queries be run through the program to extract the aforementioned information?

If not, how can I extract the required information using the audit tables themselves?

Thanks a lot

Kriti

Former Member

July 22, 2014 at 6:32 am

Hi Kriti,

Do you require number users logged in, reports refreshed and connections used per day?

If yes, first two can be achieved easily from the AUDIT sample reports and for the connections used you would need to create a query on audit tables to fetch a cross data from the reports refreshed data and report metadata information.

Regards,

Jatin

Former Member

July 22, 2014 at 6:51 am

Hi Jatin

Sorry for the ambiguity in my previous comment. I require number of users logged in at a given time, number of connections being used at a given instant, and total number of reports run in a day.

I'm only a beginner when it comes to BO so it would be a huge help if you could explain your suggestions in greater detail.

For your reference, I'm working with BO 4.1.

Thanks a lot.

Kriti

Former Member

July 22, 2014 at 7:32 am

HI Kriti,

Configure AUDIT in CMC and then you would need to configure the following:

Have a look at the AUDIT events setup from ADMIN guide chapter 20.2.2.

Once this is done you need to bring in the AUDIT universe and sample reports.

Refer SAP NOTE 1592124.

In these sample reports you can find a report for no of users logged in.

For reports refreshed, try creating a webi report based on the objects in the activity universe.

Regards,

Jatin

Former Member

July 22, 2014 at 7:38 am

Hi Jatin

Thanks for the speedy response.

Just wanted to clarify that we have to extract the aforementioned data through a Java code by running queries on the audit tables. We are looking for the tables and SQL queries which might be useful to get the data.

Thanks

Kriti

Former Member

July 24, 2014 at 12:05 pm

HI Kriti,

Please check the admin guide for schema and other details of auditing.

[Page 626 for Details]

[Page 924 for Schema]

http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp3_bip_admin_en.pdf

It would be good if you open a new discussion for SQL assistance and samples. It seems your requirement is out of scope from query builder/BO SDK standpoint.

Regards,

Prakhar T

Shakeeluddin Khaja

January 15, 2015 at 6:47 am

Thanks for sharing Mani. Helpful Information.

Former Member

February 4, 2015 at 11:36 pm

Thanks for great document Mani !! It's really helpful.

One question regarding Query Builder - I am trying to find out how many times a WebI report has been run (not as scheduled report/instance).

What query builder query should I use for this purpose?

Thanks,

Former Member

May 21, 2015 at 2:04 pm

Hi Mani,

COUNT gives the number of items

Is there any way to get distinct values?

like select distinct(si_kind) from ci_infoobjects.

Former Member

October 7, 2015 at 1:12 pm

Hi Mani,

I'd like to create a user's list with disable account.Does SI_DISABLED column is the key and in this case what syntax to use ?

Best regards,

Manikandan Elumalai

Blog Post Author

October 7, 2015 at 3:44 pm

SELECT SI_NAME,SI_ALIASES FROM CI_SYSTEMOBJECTS WHERE SI_KIND='USER'

Former Member

October 7, 2015 at 4:08 pm

Thanks for reply,

I've already done this but can not filter on SI_DISABLED = 'true'

(SI_DISABLED being a sub level of SI_ALIASES)

Any suggestions ?

Regards,

Former Member

February 1, 2016 at 8:48 pm

Heres a great 3rd party tool that I found that builds these queries for you:

InfoStore Query Builder (with export to Excel)

http://Bukhantsov.org

Former Member

October 21, 2016 at 12:14 pm

My Requirement:

I want to fetch the details of reports( Report name and Path) that was build using the column name"Rate_Set"

Can anyone help to provide me a sample query for this? Its quite urgent.

Former Member

July 6, 2017 at 6:33 am

I have a query :

select distinct si_obtype from ci_infoobjects where si_instance=1

but it doesnt work , how can i get distinct data ???

Joe Peters

July 6, 2017 at 2:07 pm

There's no support for DISTINCT in CMS queries. You will either need to dump the output of that query into something else (Ultraedit, Excel, etc.), or repeatedly run that same query, excluding known IDs each time, for example: select si_obtype from ci_infoobjects where si_instance=1 and si_obtype not in (342,453,237)

Adil Khan

June 9, 2020 at 8:11 pm

@Manikandan Elumalai

I may be missed out somehow in your Blog. Please help out find the Webi reports's name using specification connection.

I was able to find CUID from CMC and I ran this query:

SELECT *
FROM CI_APPOBJECTS
WHERE SI_ID = '20206926' --> '20206926' is the CUID of connection.

Buy running above query I was able to find all Webi CUIDs

SI_FHSQL_WEBI_DOCUMENT

1	17668905
2	17668919
3	17669395
4	18571097
5	19974233

But my requirement is know the names of the Webi reports.

Any thoughts.

Adil

BusinessObjects Query builder – Best practices & Usability

Assigned tags