Skip to Content
Author's profile photo Manikandan Elumalai

BusinessObjects Query builder – What’s New in BI 4.x

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   


Assigned Tags

      35 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thanks a lot for such a great information!!!

      Author's profile photo Former Member
      Former Member

      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!

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Thanks for your words Scott.

      I would suggest you to have a look at Semantic Layer SDK to get the clear picture of ho wit is being handled. Can we refer here. https://help.sap.com/javadocs/bip/40/slsdk/en/index.html

      Regards

      Mani

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Satish Soni
      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…

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog 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..

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

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

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog 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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Masood Aftab
      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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      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!

      Author's profile photo Satish Soni
      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

      Author's profile photo Jon Fortner
      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

      Author's profile photo Craig Wilson
      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'")

      Author's profile photo Former Member
      Former Member


      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 .

      Author's profile photo Former Member
      Former Member

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

      Author's profile photo Former Member
      Former Member

      Can we see properties for the Analysis for Excel workbooks as well if we save them to BI Platform?

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Jon Fortner
      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?

      Author's profile photo Patricia Ciavarelli
      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

      Author's profile photo Jon Fortner
      Jon Fortner

      That information is only in Audit Data. I'd count the events by user and audit event type over 3 months.

      Author's profile photo Patricia Ciavarelli
      Patricia Ciavarelli

      Thanks for the help 🙂

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Try to deploy the Audit sample available here.  Unlock the Auditing database with a new Universe and Web Intelligence Documents for BI4.1/BI4.2.

      I would suggest you to utilize documents 10, 11 & 12 which should perfectly give you the required metrics.

      Thanks

      Mani

      Author's profile photo Patricia Ciavarelli
      Patricia Ciavarelli

      Thanks for the help 🙂

      Author's profile photo Ramanaidu Kolasani
      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.

      Author's profile photo Stuart Knapp
      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? 

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog 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

      Author's profile photo Former Member
      Former Member

      Hello Manikandan. Can you help me with this query? I need a simple table with these 6 fields. However, the query fails with the last field. Or it does not give me any results. Also changing the name of the last field.

       

      Select SI_CREATION_TIME, SI_STARTTIME, SI_ENDTIME, SI_UPDATE_TS, SI_SCHEDULE_STATUS, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE, SI_DESTINATIONS.1.SI_DEST_SCHEDULEOPTIONS.SI_OUTPUT_FILES.1

       

      from CI_INFOOBJECTS

      where SI_INSTANCE = 1

      and SI_SCHEDULE_STATUS in (3)

      and si_owner= 'nome.surname'

      and SI_CREATION_TIME >= '2017.11.02'

      and SI_CREATION_TIME <'2017.11.03'

      and SI_STATUSINFO.SI_SUBST_STRINGS !='0'

       


      Other tested options:

      SI_DESTINATIONS.1.SI_DEST_SCHEDULEOPTIONS.SI_OUTPUT_FILES.1

      SI_DESTINATIONS.1.SI_DEST_SCHEDULEOPTIONS.SI_OUTPUT_FILES

      SI_DESTINATIONS.SI_DEST_SCHEDULEOPTIONS.SI_OUTPUT_FILES

      SI_DEST_SCHEDULEOPTIONS.SI_OUTPUT_FILES

      SI_SCHEDULEINFO.SI_DESTINATIONS.SI_DEST_SCHEDULEOPTIONS.SI_OUTPUT_FILES

       

      Author's profile photo Amy Su
      Amy Su

      Where can I download Query Builder?

      Author's profile photo Sanjay Pore
      Sanjay Pore

      Hi Manikandan,

      How can I get the list of webi reports using free hand sql connection i.e relation connection in BI 4.2.