In Business Objects how can I get a list of reports using a specific universe?

Posted on by 169 Comments

I have been asked the following related questions a few times in the past months and I thought it would be a good idea to post a public answer to them. First the questions:

  • Is there any way to get a list of reports that are using a specific universe?
  • If I change this universe which reports will be affected?

The Answer – Yes, Definitely, Using Query Builder

The short answer to these questions is “Yes, you can”. The slightly longer answer is, “Yes, this can be done through Query Builder, the BO Auditing data*, or through the Business Objects SDK.” For the purposes of this article, I will confine my further comments to the Query Builder and BO Auditing data solutions; the BO SDK solution requires a set of skills and even licenses that many BO Developers and Administrators do not possess.

I haven’t been keeping track, but I expect that those who have emailed me this question have not studied The Best Query Builder Guide Ever Written. I just wanted to point out that our Query Builder Guide answers these questions, and provides so much more.

The Resolution using Query Builder

For those of you familiar with SQL or just the concept of tables you might think that all you have to do is join up the record of the specific universe with all of the records of the reports where it is used. Well, this is partially true. Firstly, if you know something about query builder you know that reports and universes are stored in separate logical tables and you know that you can’t really join two tables together. The solution here is two part: (1) query the record for the specific universe retrieving all associated report IDs and then (2) query for for each of those report IDs to get the reports’ identifying information.

Query the Universe’s Reports

This is a straightforward query, but be careful if you do not have the universe’s object ID, because it is possible to have two universes with the same name and you wouldn’t want to get wrong data from the start. Here is the SQL statement you need to put in Query Builder:

SELECT
si_id,
si_name,
si_webi,
si_cuid
FROM
CI_AppObjects
WHERE
si_name = 'UNIVERSE_NAME' AND
si_kind = 'Universe'

From this you will get some additional universe info and a list of all reports that are bound to this universe. Copy that list of report object IDs to a text editor and proceed.

Query the Report’s Identifying Information

Take that list of report object IDs from the previous step and parse the list so that each ID is separated by a comma. Then substitute that list for the string “111111,222222,33333 below in the SQL:

SELECT
si_id,
si_name,
si_universe,
si_cuid
FROM
CI_InfoObjects
WHERE
si_id IN (111111,222222,33333) AND
si_kind = 'WebI' AND
si_instance = 0

The output should be your desired list of reports. Now that you see the “si_universe" property you might ask yourself why I don’t query the report table for my universe’s object ID. Good idea, but Business Objects’ Query Builder query language doesn’t yet support filtering on “property bags”, and si_universe, just like “si_webi” are property bags. They hold multiple values in a single property.

The Resolution using BusinessObjects Auditing Data

If you have enabled auditing on your environment’s report servers then you can look to your Auditing data for an answer to this question. However, auditing data is not meta data; it’s records are created as the objects are used. Therefore if a report is never used then it will not be present in this data set. If this is not a concern, or if you would like to know which reports have been used belong to a certain universe, then querying the auditing data is an option for you.

Querying the Auditing Records

There are many ways to mine this data source for the data you need. For most folks the best, easiest method is to use the “Activity” universe. Here you will find the objects you need. A tutorial on this universe and the auditing data is out of scope here, but I’d like to put together a guide on the topic if I hear of any interest. Another way to mine your data is directly against the database tables (these tables are not encrypted like the Business Objects XI CMS Infostore. This certainly allows for better query tuning, but it will increase the complexity for you. You could even use the SQL of a report created against the “Activity” universe as a starting point, but be careful as that universe is full of derived tables.

Final Thoughts

Both of these steps might seem difficult at first, but with a little practice you will find that you can whip out answers to these kinds of questions rather quickly. In fact, now you have the tools to answer the reverse as well: “How can I get a list of universe using a specific set of reports?”. Now you know, and knowing is half the battle.

Enjoyed this post? Share it!

 

169 thoughts on “In Business Objects how can I get a list of reports using a specific universe?”

  1. Hi Julian,

    I want get the list of Report names, that are using an set of objects from the universe.

    is it possible to write query in Query builder?

    Regards
    Shala

  2. Hi Shaila, no this data is not stored in the CMS repository database and therefore nothing can query it out. As I have suggested many times to folks, the data is present in the auditing tables. Each refreshed Business Objects Web Intelligence report has all of its objects used recorded in the auditing tables each time the report is refreshed. The limit is that the report must be refreshed and auditing must be enabled.

  3. How do I pull a list of all of the Win AD groups used and which reports they give access to?

  4. Hi Jody, sorry for the delay. No mater what you will need at least two separate kinds of queries (this will only work in BO XI 3.1 or BI 4.0).

    First you will need to query out all of the groups using Windows AD (This will get you started, but needs a filter to include only Win AD groups:

    SELECT * FROM CI_SystemObjects WHERE si_kind = ‘Group’).

    Then you will need to take each group’s object ID (si_id) and run a query against the reports that filters them to only return those where each user group has access. Something like this would do the trick:

    SELECT * FROM CI_InfoObjects WHERE si_kind = ‘WebI’ AND IsAllowed(3029, 3)

    This will return all reports where the group having object ID 3029 and view rights to the report.

    The IsAllowed function only works in Business Objects XI 3.1 and later and it is unsupported and undocumented by SAP. I can tell you that I am documenting in the Query Builder guide version that I plan to release by tomorrow.

  5. Hi Julian,
    We have been told Query builder is not availbale in R3. How do we run this query? Is it something that we need to buy separate brom SAP?

  6. Hi Reeta, whoever told you that needs to go on your list of people to second-guess and question. Business Objects XI 3.1 (which I assume is what you are calling R3) certainly does have Query Builder. So also does the latest release of Business Objects called Business Intelligence platform 4.0 (BI 4.0). The web application “AdminTools” must be deployed for you to be able to use it. As far as I know Query Builder and “AdminTools” is free, always has been.

    It is possible that the person who setup your web applications did not deploy AdminTools, or they did not know that AdminTools is where Query Builder lives. Either way, please let me know if you have found it on your system or been able to deploy it.

  7. Thank you so much for your quick reply. It is a possibility that they did not know where query builder lives while deploying. Can you give some steps how we can deploy it now?

  8. Hi Reeta,

    You will require a Java web application server for using AdminTools(Query Builder) war file to be deployed on. Probably you are using WACS/.NET as a combination thats the reason why they must have said you cannot use AdminTools

    Regards,
    Ravi

  10. Any info about how to get the list of scheduled reports with its status ( success/Failed) for a perticular user? Any help appreciated.. 🙂

  11. Hi Jayant, Business Objects does not store very much historical auditing information in the CMS InfoStore Repository. However, schedule jobs are actually stored there. The trouble is that if you have any instance limits on your system you will only be able to query on the instances that remain after your limits have been applied. Therefore, let’s say you have a 10 instance limit applied across the system. If you have a job that runs every 15 minutes all day long, then in 2.5 hours you will have generated 10 new instances and each 15 minutes after that the CMS may delete COMPLETELY the oldest instance applying the instance limit of 10 because a new one has arrived to replace it. Anyway to query these all you need is this:
    SELECT *
    FROM CI_InfoObjects
    WHERE si_kind = 'WebI'
    AND si_instance = 1
    AND si_recurring = 0
    AND si_owner = 'Your-Particular-User'
    You will need to decide what you want to add to the SELECT clause after analyzing the output using *.

    You may also find information about completed schedules in the auditing data. You need to have auditing enabled and specifically auditing for jobs. Business Objects stores auditing data in the tables Audit_Event and Audit_Detail. Identifying scheduled jobs from this data will be difficult, however.

  12. Hi ,

    How to get Report Owner(Report Created By) information using the audit tables.

    Thanks & Regards
    Venkat

  13. Hi Venkat, in Business Objects the property “report owner” is not recorded in the auditing tables, I think. To the best best of my knowledge the name of the user that opens a report or refreshes a report is the only user name recorded. This is because the auditing data is intended to be a historical log of events in the system. By the way, you can look at the “Detail_Type” table and see what kinds of details are recordable with auditing. Of course, each event will have different details associated with it.

    However, as you probably know, report owner is available as an attribute in Query Builder. Nevertheless, Query Builder has not event logging, just metadata (with a few exceptions).

  14. Hi,
    I have over 500 reports, how can I find out which reports are being used. Meaning I would like to see something like when was the report/report last instance viewed?
    Thanks in advance
    Sridhar

  15. Hi Srihar, this information is only available int he auditing tables if you have auditing enabled for document view/reads.

  16. Hi Julian,

    I’m working to write a report that shows me colums in universe and which report is using this specific column.

    Is this information stored in the Audit?

    Thanks in advance

    Ercole

  17. Hi Ercole, sorry for my delayed response. I assume that when you say “specific column” you are referring to a specific object in a universe. If this is a correct assumption then the answer is “yes”. In Business Objects the auditing event of “Document Refresh” records the report, the universe(s), and the objects selected from that universe. This data is stored in XI R2 and XI 3.1 in the Audit_Event, Audit_Detail, Audit_Event_Type, and Audit_Detail_Type tables. In BI 4.0 this data is stored in the ADS_Event, ADS_Audit_Detail, ADS_EVENT_TYPE_STR, etc. tables.

    To get the data to be present two things must be true. Auditing is enabled for document refreshes AND the report must have been refreshed since auditing was enabled. Good luck.

  18. Can you please publish the SQL to get all used and unused objects in an Universe (may be they are used as fileter or direct select). This will help us clean the universe. Both 3.1 and 4.0 will be great.

  19. Hi Sridhar, sorry I cannot provide the “the SQL to get all used and unused objects in an Universe”; that is something that Business Objects makes quite difficult.

    To capture used objects the best you can do would depend on having auditing enabled (document refresh) and it would depend on the “used” meaning that a user refreshed a report and the refresh was captured by the auditing. With all of this you would still need to do some data mining, but I might be able to provide a query for this.

    Unused objects would be VERY difficult because there is no way to get all objects in a universe without using the COM SDK and that would involve much more than just a query.

  20. Thanks Julian,
    Currently we are taking VB Macro approach.
    1. Get the master list of all Objects into a Spreadsheet.
    2. Get subset list of all used objects (Objects in select or in where clause) in any WEBI report
    3. Compare to the 2 list and eliminate unused objects from the Universe to keep it clean.

    The VM Macro to get step 2 is difficult to develop. I am also thinking of MetaData Manager which the user guide says can give you “Impact Analysis”.

    Anyways – I really appreciate your answers and any help to provide a query to get used objects (Objects in select or in where clause in any WEBI report) belonging to a particular universe truly help.
    Thanks for all knowledge base you are providing.

  21. Hi Julian,

    We are starting development of a new Universe with around 500 objects.(15 tables 4 facts ,11 dimensions).
    Could you please let me know what all things which we add add for better end user experience.

    also , advantages and disadvantages of Context and shortcut joins.

    your help is always highly appreciated.

    Thanks,

  22. Hi Mayur, your question is very good, but it requires at least an 8 hour in-depth conversation to honestly answer. In short I can say that users will appreciate logical groupings of objects that may depart from their table allocation. Some users will want lots of universe-level conditions they can just drop in their report. Descriptions on objects can help avoid confusion. There is too much to say here…

  23. Hi,

    Is there any way to get the report name and its user names in business objects

    Thanks in advance

  24. Hi Krish, that would depend on what you mean by “its user names”. If you mean get the report’s owner or creator, then this can be obtained through a simple query in Query Builder: SELECT * FROM ci_InfoObjects WHERE si_kind = 'WebI' AND si_name = 'Your WebI Report Name'.

    If you want to know which uses have access to a particular report, well this gets more complicated. Security has many levels and is not straightforward in Query Builder queries.

  25. Hello Julian,

    Thank you for your response. I was busy with requirement phase.
    Please let us know if we can discuss on call , about New Universe Development.
    Please let me know your contact details, I’ll call you .
    Your help is always highly appreciated.

    Thanks,
    Mayur

  26. Hi Julian

    Can you please tell me , out of Short Cut Join and Context which one is the best option?

    Thanks,
    Mayur

  27. Hi julian,

    We are in need of getting the list of universes pointing to a particular database. is it possible to get using report builder query??

    Thanks in advance.
    Srini

  28. Hi Srini, this is a good question. You can do this with Query Builder, not “report builder”. To do this follow these steps:
    1) You will need to know in advance which Business Objects Connection objects use the database you are concerned about. You can’t query all BO Connections to help with this information because the DB connection details are encrypted
    2) Substitute name(s) of the connection(s) in the following advanced Relationship Query and run it in Query Builder:

    SELECT
    *
    FROM
    CI_AppObjects
    WHERE
    Children("SI_NAME='DataConnection-Universe'","SI_NAME='Substitute_Your_Connection's_Name_Here'")

    By the way, if you want to learn more about QueryBuilder and advanced query techniques like the one above I highly recommend (and so do many others) our comprehensive Query Builder Guide (link).

  29. Another shortcut query to get the list of reports using a sungle query could be

    SELECT TOP 10000 si_id, si_name,SI_AUTHOR,SI_LAST_RUN_TIME,SI_PARENTID, SI_ISTANCE from ci_appobjects, ci_infoobjects where PARENTS(“SI_NAME=’Webi-Universe'”, “SI_NAME='<Universe Name'")
    Any comments to this post are very much appreciated. I am sorry of someone has already posted this above as I have not gone through all the comments

  30. Can you anyone please help me on the below query?
    I have to extract Report names along with Category name in which it is stored. Is there any way to do this. I have to extract from Corporate categories not by Folders.. You reply will be really appreciated.

  31. Hi,

    I am trying to find a way where I can get a list of reports that arn’t viewed in Business Objects. I have auditing turned on and I can view the most and least viewed reports but how do I use this to filter the ones not viewed??

    Can I also export this list. I am new to Query builder and I didn’t see any option for exporting a query results.

  32. Hi Ajanta, this is possible by first getting a list of all reports possible and then subtracting all reports which appear in your auditing data. To get the list of all reports you need to either manually run a query in Query Builder or you need to do a little SDK development.

  34. Hello,

    I’m trying to extract a usage list from all user’s InBox & Favorites, I would like to spot out high usage of user & ask them for housekeeping.

    Is it possible to do it on query builder?

    Thanks a lot

  35. Hi Terry, great question. User inboxes are the worst handled resource in Business Objects. I don’t think Query Builder can precisely show you the worst offenders. Aggregate questions like this go against the CMS repository design. If I come up with something I will be sure to share it.

  36. I found an Excel tool from BOB web site under BOB’s download category \ XI 3.1 – File Store Disk Space Consumption Calculator.

    It’s working fine & I can extract all of them once 🙂

  39. Hi Julian,

    Hope you are doing great..
    I am always very happy with your response, here i have few questions and need to know whether all these are achievable by using Activity Universe.
    Questions:

    1. Any way to distinguish between a newly-created report and a report refresh?

    2. Can we identify scheduled reports?

    3. Need to add: Region, Country, Function (of the user)

    4. Can we find out how many reports are downloaded?

    5. Do we have any time dimension, i.e. number of hours each user was logged in to the system, active or idle? (separate report)

    6. What other data is available in Auditor?

    7. If a user opened a public document, made an edit, ran it, then saved it as a personal doc… how would that show up on this report?

    your help is highly appreciated.
    Thanks for what you do…

  40. Hi Mayur, thansk for the feedback. Let me try to answer:
    1) I assume you mean a new report that is refreshed once during the creation. No, I don’t think so, unless you look at the report name attribute. A new report will always be named “Document” while a regular report refresh could have a specific name (if your processes require it).
    2) I think you can because these are executed by the Administrator user (I think), so any refreshes done by Administrator should mostly be scheduled reports.
    3) No, not possible unless you have a table of all users and you join to it using user name as the PK-FK.
    4) I don’t think so.
    5) I found hard to determine and a little unreliable. But it would be a complex.
    6) Document views, document edits, etc. Take a look at the auditor settings.
    7) This would show us as a refresh of the doc with that user’s name associated with it.

  41. Thanks a lot Julian for sharing valuable info..your help is highly appreciated..thanks again.

  42. Hi Julian,

    With query Builder we can get all the information related to reports and universes. can you please tell me is it possible to fetch all these informations in a report level i.e without using Query builder????

  43. No Ashok, sorry, this is not possible. If it were so there would not be so much interest in Query Builder.

    However, if you were to use the BO SDK to extract this data and place it in a database for reporting then I would be forced to change my answer. I have done this kind of CMS Repository ETL many times when I really must have a certain kind of meta data. To be honest, there are 3rd party apps out there that do this too, such as QBXcell.

  45. Hi Julian,

    Some really very good information here. It answered most of my doubts..However, I still have a couple of them. hope you’ll be able to help:
    1. Where does Query Builder fetches data from. Is it CMS Database. Because if Auditing is not enbaled for named user login, I can still view the last logon time for a user by using query builder.
    2. I read somewhere about Web Auditing – where one logs userID/IP addresses, etc of users accessing web applications (CMC, Infoview, etc). can you tell me where can I find this log or direct me towards some resources on the same.

    any help will be much appreciated.

    Regards

  46. Hi Avichal, I am glad you have found this useful and I appreciate your clarifying questions.
    1) Query Builder pulls from the CMS Database. It uses the same SDK queries as InfoView/BI LaunchPad and CMC. The CMS Database is encrypted and abstracted such that you must use the SDK to “talk” with it. Query Builder is the easiest free way to query the CMS database directly and retrieve almost all available data in a read only manner.
    2) I am not aware of BO capturing the IP address of a user and righting that to any BO auditing tables. Certainly all BO auditing will capture the authenticated user’s user ID, but never their IP address (to the best of my knowledge). Logs from your web server will have this information, but I don’t think that they won’t capture the BO user ID.

  48. Hi Julian,

    I want to get the below details,

    Report Name Universe Name Dimension/metric name Source table
    Deski Report merchandising date_id dim_date
    Deski Report merchandising product dim_product
    Deski Report merchandising total_sales fact_sales
    Deski Report merchandising total_cost fact_sales

    Basically the report_name,universe name, the objects used in the report and from which table the objects are created.

    Can we get this information

  49. Hi Julian,

    Can I use Query Builder to get a list of reports using a specific universe context?

  50. Hi JtSal, no, it is impossible to get a list of reports using a specific universe context because this information is not stored in the CMS InfoStore. It is stored only in the binary file of the report which is not queriable through Query Builder.

Leave a comment

Your email address will not be published. Required fields are marked *