Skip to Content

Continuing with Query builder blog series here is another one with more emphasize on features of BI 4.0. Below are the features of consideration

    • Users homepage is enhanced to view his/her latest documents and Alert notification.
    • Connection specific enhancements
    • Universe  enhancements
    • Monitoring

And here is the respective queries for each consideration

Alert Notification and recent document Query for User

SELECT SI_ID, SI_NAME, SI_RECENT_DOCUMENTS,SI_RECEIVED_ALERTNOTIFICATIONS

             FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’

              

               This will be extracted for each user in the system.

Probes

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’MON.Probe’ AND SI_INSTANCE =0

Please note I am just extracting probes alone not its instances. To retrive the proble instances you need to use SI_INSTANCE =1 in the where clause.

Connections

             SELECT * FROM CI_APPOBJECTS  WHERE SI_KIND = ‘CCIS.DATACONNECTION’

                    this will extract all the data connections i.e. Relational and OLAP from the repository

Universe

             SELECT SI_ID,SI_NAME,SI_SPECIFIC_KIND, SI_KIND FROM CI_APPOBJECTS WHERE SI_KIND= ‘DSL.MetaDataFile’

                  We can further categorize the individual components of universe using SI_SPECIFIC_KIND and this is the component list for the .unx universe.

 

      • DSL.BusinessLayer
      • DSL.DataFoundation
      • DSL.Connection
      • DSL.Universe
      • DSL.DataSecurityProfile
      • DSL.DataSecurityOptions
      • DSL.BusinessSecurityOptions
      • DSL.ConnectionShortcut

Hope this is interesting. Feel free to leave your comments. Thanks for reading.

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.

32 Comments

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

  1. Scott Galaise

    Great post!  I am starting to deal with the new unx universes with the sdk more and more.  I realize this post is in reference to query builder directly, but as anyone who has used the SDK knows, these queries are the backbone of the SDK when dealing with these objects.  My question is in regards to the new DSLUniverse objects.  I can’t seem to find an IDSLUniverse object in the SDK and have instead had to deal directly with the properties for the base IInfoObject.  Do you or anyone else know if this has been ignored?  I know they are pushing the REST api and slowing abandoning the traditional SDK (or so it seems), but there are still a lot of use cases for this.

    Thanks!

    (0) 
      1. Scott Galaise

        Thanks for the quick response Mani!  I have taken a look at the SL SDK, but it just doesn’t seem to bridge the gap from the existing SDK.  I am in the process of writing a custom object for the DSL Universe that uses the IInfoObject properties.

        Thanks,

        Scott

        (0) 
  2. Brett Medalen

    Great post, Mani.  Do you know of a way to get information like the number of concurrent users at any given time from the Query Builder?  Also is there any other license reporting available?  Thanks.

    (0) 
    1. Satish Soni

      Reporting as such is not available however here is what I have found,

      Query for Named User:

      SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_NAMEDUSER=1 and SI_KIND=’user’

      Query for Concurrent User:

      SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_NAMEDUSER=0 and SI_KIND=’user’

      For Licensing in BOXI 3.1

      https://websmp110.sap-ag.de/~sapidb/011000358700000557752013E (Use Adobe r change extension to PDF)

      Tool: https://websmp205.sap-ag.de/~form/handler?_APP=00200682500000001943&_EVENT=LIST&EVENT=LIST&ENR=67837800100200021327&SWTY…

      (0) 
    2. Manikandan Elumalai Post author

      Thanks Brett. There is no real time licence audit available in BO, Still you can utilize audit capability in order to do the licence utilization over a period of time by connection, sessions,etc..

      (0) 
  3. vinod tn

    Hi Mani,

    Thanks for the wonderful post.

    Could you please help me with a query to find out  the reports created over a BICS connection.

    Thanks & Regards,

    Vinod TN

    (0) 
  4. Manoj Magudia

    Great posting – quick question – how can I get a list of all recurring schedules wheich are e-mailed to a particular e-mail address

    (0) 
    1. Manikandan Elumalai Post author

      SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_DESTINATIONS FROM CI_INFOOBJECTS WHERE SI_RUNNABLE_OBJECT = 1 AND SI_KIND IN (‘FullClient’,’Webi’, ‘CrystalReport’) SI_SCHEDULEINFO.SI_DESTINATIONS will give you the Schedule destinations.

      (0) 
      1. Kumar UP

        Hi Manikandan,

        We are facing issue in WEBI Change source from UNV to UNX Universe.

        Reports having query filter with “Results from another query(Any/All)”  is not mapping from UNV to UNX.

        Can you please let me know to get  list of reports having above query filter type.

        Thanks in Advance,

        Kumar

        (0) 
      2. Manoj Magudia

        Thanks for the info – I’ still have an issue. The query you provide works fine but I’m trying to filter ouit on just one e-mail and its keeps provide no results even though I can see chadules set up with reports being send to the e-mail address as per the query below:

        SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_DESTINATIONS, SI_MAIL_ADDRESSES.SI_SCHEDULEINFO.SI_DESTINATIONS FROM CI_INFOOBJECTS WHERE SI_RUNNABLE_OBJECT = 1 AND SI_KIND IN (‘FullClient’,’Webi’, ‘CrystalReport’) AND SI_MAIL_ADDRESSES.SI_SCHEDULEINFO.SI_DESTINATIONS = ‘manoj.magudia@db.com’

        ANy help mose appreciated

        (0) 
  5. Masood Aftab

    Hi Manikandan,

    Can we get the list of reports using a particular object in universe by querying through query builder? Is there any other way to get that?

    Please help.

    Regards

    Masood

    (0) 
  6. Raj U

    Hi Mani,

    How can we get the volume of data each scheduled instance holds?? Either succeeded or failed instances (volume of data processed before failing for failed ones).

    Thanks in Advance.

    (0) 
  7. Samir Parmar

    Hi Mani, et all

    Great post mate. I desperately need some help on a query on bi4.1 .

    I need something on this line:

    SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS

    WHERE PARENTS(“SI_NAME=’SL_DOCUMENT-DSL.Universe'”,”SI_NAME =’EIH NPS Universe'”)

    I want to find out report names associated with the a .unx universe. I have been struggling with this for a very long time and any help would be awesome and much appreciated.

    Thanks!

    (0) 
    1. Satish Soni

      Run the following query :

      select si_name,si_webi,si_parent_folder from ci_appobjects where si_kind=’Universe’

      Assuming the SI_ID of the Webi report is 1234, run the query below to find the name of the Webi report:

      select si_name from ci_infoobjects where si_id=1234

      Universe information is available on ci_appobjects and webi related information is available in ci_infoobjects

      Hope This helps 🙂

      -Sat

      (0) 
    2. Jon Fortner

      List all reports for a Universe

      select si_id, si_name from ci_infoobjects, ci_appobjects where PARENTS(“SI_NAME=’Webi-Universe'”, “SI_NAME=’Your Unv Name'”)

      select si_id, si_name,si_files,si_description from ci_infoobjects, ci_appobjects
      where PARENTS(“SI_NAME like ‘%Universe'”, “SI_NAME=’activity'”)

      See Ted Ueda’s blog at http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/11238 for more about relationships

      (0) 
    3. Craig Wilson

      For .unx universes, Samir’s post is very close, but there are a couple of small mistakes. Remove the SL and add .unx.  So it should be:

      SELECT SI_NAME FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS

      WHERE PARENTS(“SI_NAME=’Document-DSL.Universe'”,”SI_NAME =’EIH NPS Universe.unx‘”)

      (0) 
  8. sreeni koppuravuri


    Hi,


    I am looking for some help on these metadata sqls for Business Objects.


    1) Is there a way to find out list of impacted reports in a universe if we drop a existing filed from data base?  in other wards, how to get the list of reports which are using a particular field ( eg: account number ).

    Eg: if Loan Number is used in 5 reports , using this meta data queries I should get those 5 reports names

    2) How to get the Universe name, report name, report elements used in that report from meta data tables

    3) How to get the  Universe name, report name,  report business layer elements , report data foundation layer elements from metadata sqls

    4) How to get the list of reports using Custom SQLs .

    (0) 
  9. srinivas A

    Is there any possibility to find out the objects in universe(dimensions/measures/details)  which are not used in particular period ???

    (0) 
  10. varun garaga

    Hi,

    is there  query to check all invalid objects because it was taking lot of time if i am doing check integrity it is a huge universe please could you help me in this it would be great

    Thank You

    (0) 
  11. shyam kumar

    Hi,

    Please anyone help me on this!!!

    >In our Universe (Universe name=AAA) we have an Object (Company_Code)

    >On top of this ‘AAA’ Universe we have 50 Reports

    >Some of the Reports are Using the ‘Company_Code‘ object.We want to know these report names OR cuid’s

    >We are on BOBJ 4.1 , SP3

    (0) 
    1. Jon Fortner

      Sorry, Query Builder can only link Universes to WebI Reports, not which Objects are used in them. To get that, you have to use SDK code to open and list objects in all reports, store that in a Database, then query it.

      To get the WebI list, try this:

      SELECT SI_NAME,SI_ID FROM CI_InfoObjects, CI_AppObjects WHERE si_kind=’WebI’ AND Parents(“SI_NAME=’Webi-Universe'”,”SI_NAME=’UniverseName'”)

      Then use SDK code to loop that list, open the report and get an object list. Sorry, never wrote that code. Anyone have a snipit?

      (0) 
  12. Patricia Ciavarelli

    Hi Mani-

    I recently had a named user that left the company.  Rather than allow other users to petition for the seat, I would like to assign it based on data… most reports, most often logged in, something of this nature.

    Do you have any recommendations on how to make some reports that would tell me who are the heaviest users of the system so that I can assign my currently empty named user seat?

    Thanks-

    Patti

    (0) 
  13. Ramanaidu Kolasani

    Hi Manikandan

    We were able to get the list of users who have access on BO when we were using BO R-3 with BO Query Builder.

    But now we are not getting any result when using BO R-4 with BO Query Builder.

    Please look into this.

    (0) 
  14. Stuart Knapp

    Hi Mani,
    Is there a syntax to query sub-items within the SI_Destinations section..specifically the SI_OUTPUT_FILES .. to see all of the locations that reports are going? 

    (0) 
  15. Manikandan Elumalai Post author

    Hi Stuard,
    No.You cannot specifically select a specific sub item with in property. Let me know your requirement in detail so that I can give you an workaround if feasible.

    Thanks
    Mani

    (0) 

Leave a Reply