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
Thanks a lot for such a great information!!!
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!
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
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
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.
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…
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..
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
Great posting - quick question - how can I get a list of all recurring schedules wheich are e-mailed to a particular e-mail address
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.
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
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
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
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.
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!
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
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
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'")
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 .
Is there any possibility to find out the objects in universe(dimensions/measures/details) which are not used in particular period ???
Can we see properties for the Analysis for Excel workbooks as well if we save them to BI Platform?
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
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
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?
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
That information is only in Audit Data. I'd count the events by user and audit event type over 3 months.
Thanks for the help 🙂
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
Thanks for the help 🙂
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.
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?
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
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
Where can I download Query Builder?
Hi Manikandan,
How can I get the list of webi reports using free hand sql connection i.e relation connection in BI 4.2.