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. The Query Builder is definetly the tool to get a list of reports with the linked universes. 2 remarks though:
    – In case of ‘unbound reports’, the information is not available in the Query Builder. The “SI_UNIVERSE = 0” shows no relation anymore. The information can be found in the binary (.WID) files and can be retrieved via the SDK. Merge both ‘sources’ together and you have an exhaustive list.
    – SP5 contains a .JAR-file to re-establish the link between reports and universes for unbound reports.

  2. Hi Geert,
    Thanks for the remarks/tips. I do agree with you that Query Builder cannot actually generate a query that returns all “unbound” reports; however, it is possible to return all reports matching some other filter condition (parent folder, naming convention, etc.) and then from this output one could determine which reports are unbound. I’m glad that many people are comfortable with the SDK, but honestly, many are not or they do not have the license to use it. Without the SDK, or some other purchased add-on, BO Query Builder is the best tool available for the task. I would love to be wrong, of course.

    BTW, thanks for the tip about SP5’s JAR. I really need to look into it.

    Julian

  3. In Business Objects how can I get a list of crystal reports using a specific Data source(Data Connections) ?

    I tried using this SQL

    SELECT SI_ID, SI_NAME ,si_processinfo.si_logon_info FROM CI_INFOOBJECTS WHERE SI_KIND = ‘CrystalReport’

    This SQL gives me the total list.

    I need to go to si_processinfo.si_logon_info.si_logon1.si_server value. But when I use this in the query, it doesnot give anything for this column

    SELECT SI_ID, SI_NAME ,si_processinfo.si_logon_info.si_logon1.si_server FROM CI_INFOOBJECTS WHERE SI_KIND = ‘CrystalReport’

    Can you please let me know how to write this query.

  4. I can’t research this now, but I highly suspect that “si_logon_info.si_logon1.si_server” refers to a property bag. It is not possible to limit, filter, or specifically refer to property bags in Query Builder. This is a limit of the tool. For example, you cannot query for all reports that have a particular universe because si_universe is a property bag. You also cannot create a query that only returns the first universe. Sorry, not possible.

    Julian

  5. Is there any key field we can use to join the two tables CI_InfoObjects and CI_AppObjects?

  6. Sorry. Not possible. Query Builder does not permit any real joins.

    The key fields you ask for do exist, but you would need to manually or programmatically (SDK, Macro, etc.) execute one query and take the result and use it to execute the second query.

  7. Incidentally, this process is considerably easier in 3.1, using the Relationship Query. Which, really, is just a quick, automated way of doing exactly this query. But the output is much nicer, and it’s much easier that writing out the SQL.

    But, that brings me to my follow-up question: Is there a way to determine which, if any, reports reference a particular class or object within a given universe? Our DBA is looking to remove a table from the database. How can I tell which reports need to be modified?

  8. Hey Marshall,

    Good to see you back man! Hope work hasn’t been too hard on ya.

    I just had an epiphany on this very topic today!

    First the official way, I hear is to get Metadata Manager (3.x only) and this will help answer this question.

    Now, for Julian’s hack of the day…

    Assuming you are auditing all document refreshes on all reports servers then you should have this data stored within your auditing data for all reports that have been refreshed. How so you ask?

    Easy, the “Document Refresh” event, (old number 19, I think) captures lots of junk and writes many records to the Audit_Detail table for all of this junk. The amount of junk increases depending on your service pack level, but I can tell you in XIR2 SP4 it captures all of the objects that are used in the query(s) of the report.

    So with some fancy data mining and some semi-uniquely named objects you might be able to crack this nut for all of the reports that should matter (the ones that have been refreshed).

    Julian

  9. Ah. Well, as we are not auditing document refreshes, that is not an immediate solution. Thanks for the tip, though!

    I’ll have to look into the Metadata Manager.

  10. Hello,

    Do you know a any way to make an object in Webi get the complete path to the report?

    Example: BO_Projet/BLABLA/Administrativo/Reporte2

  11. Yes, but only if you can use “Path Queries”. These are included at least in XI 3.1, but I have read, not confirmed, that you can build it into XIR2 opendocument.jsp (a kind of hack).

  12. Hi Julian,

    Thanks for a nice article.

    I have a question. You describe how to get a list of WebI reports that use a specific universe. Is there a way to do the same for DeskI reports? Just by changing “si_kind = ‘WebI'” condition in the second Query Builder query?

    Thank you.

  13. Hi Marek,

    Glad you find it helpful.

    You asked

    Is there a way to do the same for DeskI reports? Just by changing “si_kind = ‘WebI'” condition in the second Query Builder query?

    Yes, definitely. I forget right now what it is for Deski, but that is it. Please post the SQL if you get it to work. I’ll flog myself later for now having it on the top of the pile of random ideas I call my brain.

  16. Hi Julian,
    Great article and very helpful tips.

    I have a question. I am using BOXI 3.1 We like to retire some reports of EMEA region, that comes from specific data sources. These are WebI and Crystal Reports that uses datasources like databse tables, Universes, Business Views. I know the list of EMEA database tables. Is there any way I can map which reports are using these tables (either directly or through Universe or Business Views)?

    Thanks in advance

    Thanks
    Aurobindo

  17. Of course if it is just by universe the article tells you already how to do that. But if you want by table then you will either need Metadata Management (a BO tool) or you will have to depend ont he SQL that is captured in the auditing tables (with Document Refresh enabled). This data, however, will only reflect reports that have been refreshed.

  18. I’m new to BO, but couldn’t one query be written using a subquery similar to this?

    SELECT
    si_id,
    si_name,
    si_universe,
    si_cuid
    FROM
    CI_InfoObjects
    WHERE
    si_id IN (
    SELECT
    si_id
    FROM
    CI_AppObjects
    WHERE
    si_name = ‘UNIVERSE_NAME’ AND
    si_kind = ‘Universe’
    ) AND
    si_kind = ‘WebI’ AND
    si_instance = 0

  19. Hi Jarod,

    Nope, that syntax won’t fly. Query Builder and the BO SDK do not support nested SQL statements nor do they support joins. Nevertheless, what you propose is more or less what you have to do manually, generate the universe-document object id list and then query the reports “table” and get teh report details.

    Thanks, Julian

  20. This also, can be very usefull to get a List Business Objects Reports for Specific Universe:

    SELECT si_id, si_name,si_universe,si_cuid
    FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
    Where PARENTS(“SI_NAME=’Webi-Universe'”,”SI_NAME =’Name of the Universe'”)

    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

  21. Hi Ricardinho,

    Excellent comment, thanks!

    I tested this on a BO XI R2 environment and it failed miserably, but I tested it in my BO XI 3.1 environment and the syntax passed, now I just need to create some WebI docs to see if it works!

    Please share any additional information you have, or anyone else!

    Thanks! Julian

  22. Hi there,

    Is it possible to build a query to identify a report in which a specific string is used (a database column, a stored procedure , etc… )
    Thanks,

    Augustin

  23. Hi Augustin,

    I think your question is similar to that of Aurobindo Saha above. There is no such information stored in the CMS Infostore that you can reach through the CMS Query Language.

    Of course, you could just try something like this and see if any of the available fields help:

    SELECT
    *
    FROM
    ci_InfoObjects
    WHERE
    si_id = 9999999

    Where 999999 is your report’s object id. Sorry, ultimately this is what I have done and still do when such questions come up.

    Regards, Julian

  24. Hi there, first off, great site, love the info you give us.

    I have a quick question, can the Query Builder (XIR2), be used to pull the specific Folder location of the reports?

    I’ve pulled a listing of all reports bound to a specific universe, but it would really be useful to know in what folder each one is located.

    Thanks,
    Kal

  25. Hi Kal, Somehow I missed replying to your comment. I hope I am not too late. Reports that are not instances (si_instance = 0) have the object ID of their folder stored in SI_PARENTID and the CUID of the folder is stored in the SI_PARENT_FOLDER_CUID.

    As usual, in XIR2 QB you will not be able to get the folder name and report data listed using just one query (no joins are allowed). But at least you will get to become familiar with the object IDs and CUIDs of your folders.

  26. I would like to know everyone that new functionality introduce in Business Object Xi 3.1 to know the relationship between objects.

    Select the required object
    Click on Check relationsip to get lsit of dependency object.

    Thanks

  27. I just received a strange auditing request from the Business for a way to track what documents B.O. users are sending to other users’ B.O. Inboxes. Ideally they want a ‘Sent Items’ folder view like in Outlook. I’ve never encountered such a request. I already know an Admin can do this via the CMC by looking in a given person’s Inbox to see what’s in there and who sent it, but I don’t know of a way to generate a report for this. Is there a wholesale way to determine who sent what documents to whom?

  28. BO Sleuth, you can only do this using the SDK. I think that the output from Query Builder would not be very useful. Auditing doesn’t store this details as far as I know.

  29. Hey All,

    Just coming back with some long overdue follow-up comments on Ricardinho’s (thanks again) posted new BO XI 3.x syntax.

    I tested this a lot in my BO XI 3.1 environment and found that it worked great!


    SELECT
    si_id,
    si_name,
    si_universe,
    si_cuid
    FROM
    CI_INFOOBJECTS,
    CI_SYSTEMOBJECTS,
    CI_APPOBJECTS
    WHERE
    PARENTS("SI_NAME='Webi-Universe'","SI_NAME =''")

    I will be adding this and other updates to our Business Objects XI Query Builder Guide.

  30. Does the SI_Webi also work for Desktop Intelligence reports? If not, what’s the equivalent of SI_WEBI for Deski reports?

    Thanks,
    Sleuth

  33. meredianblues, does this produce what you want?:
    SELECT
    si_id,
    si_name,
    si_universe,
    si_cuid
    FROM
    CI_InfoObjects
    WHERE
    si_name = '' AND
    si_kind = 'WebI' AND
    si_instance = 0

  34. Hi Julian
    Is there any way to get the associated universes even for the “Unbound Reports”. I used this sql to get the list of universes associated with reports: SELECT
    si_id,
    si_name,
    si_universe,
    si_cuid
    FROM
    CI_InfoObjects
    WHERE
    si_id IN (111111,222222,33333) AND
    si_kind = ‘WebI’
    but this skips the unbound reports.
    Just wondering is there any way to get the universe associated even for unbound reports.
    Please let me know.

  35. Hi Smitha, I don’t think that this is possible. Unbound Reports are funny animals because they have some legacy/classic BO (pre-crystal) link to the universe short name. This link is somewhere in the *.wid file, I think and not anywhere in the CMS InfoStore. If you used the SDK you could perhaps return all reports and iterate through each one just output the reports that have no si_universe. SI_UNIVERSE is a property bag, so Query Builder won’t left you filter on it directly.

  37. If I ever get the chance, I would like to write a JSP SDK page that would retrieve all of the unbound reports. Unfortunately, even with this, you won’t be able to retrieve the list of universes that the unbound reports are attempting to connect to.

  38. Hi Julian,

    I have Question related to access levels in Business objects. Question is i want to know how many folders in public of a server(XiR3) having Full Control access to a user or group.

    am not able to find a column in CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS which resides the value of access level(FULL CONTROL,SCHEDULE,VIEW ON DEMAND AND VIEW).

    Could you please help me out to fetch result set to question above or even if i came to know column name for access level values.

    Thanks,
    Sambasiva

  39. Something I learned the other day in XI 3.1….You can now through the CMC see the reports which rely on a universe by right clicking the universe and selecting Tools –> Relationships..

    This will list all the reports which use the universe…

  41. Is there any query that I can list all the Crystal Reports and their assoiciated stored procedure.

    Thanks in Advance

  42. I don’t have one of those to look at so I can’t really say. My initial reaction is that it would not be possible since I have never seen stored procedures stored in the CMS InfoStore, but that doesn’t mean they are not there. You could email me the result of a query of your report’s record “SELECT * …” and I might see something.

  44. I heard someone once propose a macro that could somehow open a report and get this information. I don’t see how this could work from WebI, so there is no such direct method.

    However, if auditing in enabled and the report is refreshed once then you could hit your auditing tables and you should find after joining up the “Audit_Event” and “Audit_Detail” tables a list of every object used in the report. If you need more details, I can try to build a standard SQL query that could do this for you. Let me know.

  46. Sorry for the delay. Here is the SQL. You will want to adjust the final WHERE clause to your own filtering needs. As it is now, it works on an Oracle database and it returns data for all refreshes for the previous 24 hours.


    SELECT
    ae.event_id,
    et.event_type_description,
    ae.user_name,
    ae.start_timestamp,
    ae.duration,
    ae.error_code,
    sp.server_fullname,
    dt.detail_type_description,
    TO_CHAR(TRIM(ad.detail_text))
    FROM
    Audit_Event ae,
    Audit_Detail ad,
    Event_Type et,
    Detail_Type dt,
    Server_Process sp
    WHERE
    ae.server_cuid = ad.server_cuid AND
    ae.event_id = ad.event_id AND
    ae.event_type_id = et.event_type_id AND
    ad.detail_type_id = dt.detail_type_id AND
    ae.server_cuid = sp.server_cuid AND
    UPPER(et.event_type_description) IN ('DOCUMENT REFRESH','DOCUMENT REFRESHED') AND
    UPPER(dt.detail_type_description) IN ('OBJECT NAME') AND
    ( ae.start_timestamp BETWEEN SYSDATE-1 AND SYSDATE )

  47. Hi Julian,
    Thanks for the Query, let me check and get back to you with changes i have done for query.

    Mean time i have one more query:
    Currently i need to fetch data for recurring schedules based on set of events and a particular universe.Here am finding details seperately in infosystems and systemobjects. not getting idea to merge and see the data. which will be my expected results.

    IF you have any idea on the same share with me.

    Thanks,
    Sambasiva

  48. It is not possible to merge results from Query Builder like that. The best you can do is use a Relationship Function, which will perform like a sub query. Unfortunately, I have never done this and I can’t tell you if the relationship between scheduled instances and events is covered by the Relationship Functions.

    I have yet written much about Query Builder and Relationship Functions publicly, but it is covered in the newest version of the Query Builder guide if you have one.

  49. Is there any way to find out the Business Objects reports which are using a specific Dimension or Measure object?

    Kindly help!!!!

  50. Hi Shalini, please see the question asked above by “sabri eker”. The answer is the same, “Auditing” data mining.

Leave a comment

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