Function | Desription |
---|---|
COUNT | Retrieves the number of distinct values of a property |
TOP | specifies the maximum number of object to be returned |
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 28th May 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 18th Mar 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
Excellent document, one question, can i used to both version 3.1 and 4.0
Thanks!
Hi Vives,
Thanks for your comments. You can use this for both the versions.
Regards
Mani
Good Documents.. Thank you SCN for the knowledge sharing.
great post..
Nice document Mani…
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
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.
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.
Thanks Mani for the info!!
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.
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
Sorry…didn’t see your earlier post. I got what I wanted. Thanks for the great work. Very helpful.
Nice stuff.. helps a lot!!
Regards,
Atul B
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
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
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
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
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
Hi
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
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
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
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
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
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
Thanks for sharing Mani. Helpful Information.
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,
Hi Mani,
COUNT gives the number of items
Is there any way to get distinct values?
like select distinct(si_kind) from ci_infoobjects.
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,
SELECT SI_NAME,SI_ALIASES FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’
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,
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
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.
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 ???
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)
@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
But my requirement is know the names of the Webi reports.
Any thoughts.
Adil