Skip to Content
Actions

BusinessObjects Query builder – Best practices & Usability

June 12, 2013 | 13,090 Views |
 bo querybuilder
Follow RSS
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 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

             BusinessObjects Environment Cleanup using Query builder

             BusinessObjects Query builder – What’s New in BI 4.0

To report this post you need to login first.

34 Comments

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

  5. Former Member

    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

    (0) 
  6. Former Member

    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.

    (0) 
    1. Manikandan Elumalai Post author

      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.

      (0) 
  7. Former Member

    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.

    (0) 
  8. Former Member

    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

    (0) 
  10. Former Member

    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

    (0) 
  11. Former Member

    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

    (0) 
    1. Former Member

      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

      (0) 
      1. Former Member

        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

        (0) 
  12. Former Member

    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

    (0) 
    1. Former Member

      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

      (0) 
      1. Former Member

        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

        (0) 
        1. Former Member

          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

          (0) 
          1. Former Member

            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

            (0) 
  14. Former Member

    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,

    (0) 
  15. Former Member

    Hi Mani,

    COUNT gives the number of items

    Is there any way to get distinct values?

    like select distinct(si_kind) from ci_infoobjects.

    (0) 
  16. Former Member

    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,

    (0) 
      1. Former Member

        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,

        (0) 
  18. Former Member

    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.

    (0) 
  19. Former Member

    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 ???

    (0) 
    1. Joe Peters

      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)

      (0) 

Leave a Reply