Using BO Query Builder to Detect Report-Universe Binding Status

Posted on by 25 Comments

The loss of binding between reports and universes is a common problem experienced by BO XI users. Often the issue can go unnoticed for quite a while, but like a dormant disease it can spontaneously begin to demonstrate severe symptoms that can result in the loss of your report. Sound serious enough? It is! The following article is intended to help you detect report-universe unbinding proactively, before they cause you serious trouble.

The best tool for detecting of Report-Universe Binding Status is Query Builder. As far as I know there is no third part tool or BO utility for this other than Query Builder, but honestly, Business Objects’ Query Builder works quite well and it is available to you for free. Here and the queries that you will need:

Business Objects Query Builder Query: Universe Binding Status

This query brings back a limited set of properties for the desired universe. I lifted it from our Query Builder Guide. Like most problems there are multiple ways to attack and starting by looking to the universe is one way.

SELECT
si_id,
si_name,
si_webi,
si_cuid
FROM
CI_AppObjects
WHERE
( si_name = '' OR si_id = ) AND
si_kind = 'Universe'

You need to put in the universe name (upper or lower case is not important) or the Object ID of the universe (most people are more familiar with name, but object ID (si_id) provides more precise results. From this query we will see only the reports to which this universe is bound. It won’t list the reports to which it should be bound. The report name nor the report’s query name are given here, but the reports si_id or object ID is given here. And so you my need to do some additional queries to identify the reports listed here. Which leads us to the other way to start looking at this issue.

Business Objects Query Builder Query: Report Binding Status

For many people this is the query they will lead with in QueryBuilder. Usu

SELECT
si_id,
si_name,
si_universe,
si_cuid
FROM
CI_InfoObjects
WHERE
( si_name = '' OR si_id = OR si_parentid = ) AND
si_kind = 'WebI' AND
si_instance = 0

Using the query will require you to provide at least one of the following: report name (si_name), report object ID (si_id), or parent folder object ID (si_parentid). This query’s result will list all of the universes to which the report(s) is/are bound. How can a report be bound to more than one universe? Multiple queries in the report (also called the classic name of “data providers”). This fact makes this query the most important query in my opinion. If it fails to list an expected universe then you have identified and unbound report. My sympathies and congratulations!

What does an Unbound Report Look Like in Query Builder?

Words are often not as valuable as pictures. Now pictures of words, well their value is questionable, but not in this matter. The following image show what the report Query Builder query looks like when a report is showing that it is bound to its universe (just a single universe in this example):

Query Builder output of a report properly bound to its universe

Query Builder output of a report properly bound to its universe


In the rather unfortunate case that a report is not bound to its universe you will see output that looks like this:
Query Builder output for a report not bound to ANY universe

Query Builder output for a report not bound to ANY universe


Note:This query will return only WebI or Web Intelligence reports. You will need to modify it if you are interested in other reports in your CMS InfoStore.
blank space creating image
blank space creating image

A Note About the Query Builder “si_universe” Property

Many properties in Query Builder are compound in nature, meaning they have multiple values for a single object. For example and report can have multiple universe and in fact its si_universe property has multiple sub-properties (si_total and the universes’ object IDs). We call properties like si_universe property bags. The unfortunate fact here is that in Query Builder you cannot filter on a property bag. Therefore, you cannot create a query that only returns all of the unbound reports. 🙁

What about the CUIDs?

If you have read my other articles such as “Business Objects XI – Changing BO Report / Universe Relationships” then you know that the relationship between reports and universes is really at the CUID level and not the Object ID level. Well, the truth is that Object IDs are specific to an environment, but CUIDs are portable between environments (with the write methods). Anyway, Query Builder and the CMS InfoStore will show the binding at the Object ID level, but this binding will look different in another environment to which you have correctly and successfully migrated the report and universe because the Object IDs will be different (but the CUIDs will be the same). Anyway, for now, just know that the CUID is very important with regards to report-universe binding; however, when detecting the status of that binding they are not important.

Important Note about Business Objects’ Import Wizard

I said earlier that there was no other tool for identifying unbound reports, but I fibbed a bit. It is possible to use Business Object’s Import Wizard to detect unbound reports. If you select a report and the option to automatically select its universes then if Import Wizard fails to select all of the expected universes you know that the report is in an unbound state. This is a tedious way to detect report-universe unbinding, BUT if you find yourself using Import Wizard and you are experiencing an issue with Import Wizard selecting reports’ universes then you have a good indicator that something is probably wrong with the report-universe binding.

Knowing is Half the Battle

The next logical question is, “Now that I have identified an unbound report, how can I correct it?”. That my friends is a topic for another article. My family and hobbies are calling to me now; please remind me if I forget to write that article soon.

Want to Know More About Query Builder?

I recommend that you take a look at our “Business Objects Query Builder Guide“, it is most likely “The Most Complete Business Objects XI Query Builder Guide Ever Written” and it will help you to discover and master the secrets of Business Object’s Query Builder, such as the one discussed in this article.

Enjoyed this post? Share it!

 

25 thoughts on “Using BO Query Builder to Detect Report-Universe Binding Status”

  1. Julian,

    Thanks for this article.Really nice stuff that one should know about.

    I have been googling through for an issue I am facing with our Webi reports deployment and came across this article.

    I now understand the binding relationship between a report and its relevant univerese.

    I have a problem with our WEBi reports deployment.Please can you help me understand where the problem could be?

    We have a universe and some Webi reports developed on it. They(Universe and reports) were deployed in the production server as part of the initial deployment.

    Recently our developers have done some modifications on the Universe as well as to the existing reports. They have also developed some new Webi reports based on the new version of Universe.

    As per the request we deployed the Universe into production using the Designer and used the Import wizard to deploy the Webi reports.

    The Webi reports(after deployed in production env) gives an error message when refreshed. Also when the report is Edited I see the classes and objects which are not relevant to the corresponding universe.

    One of the points in the above article says the Univese-report binding can be found using the Import wizard. Yes, I found it and I understand that there is a releation ship still existing(The universe is autmatically selected when the report is selected to be imported).

    Still the report errors out.

    Can you extend any help on this please?

  2. Hi Aravind, In such cases I would recommend using Query Builder to confirm the report-universe association of each report. If a report passes this test and still is having issues after the import I would go back to the source environment (from where they were imported) and confirm that the report has no similar issues. Sometimes an administrator needs to rely less on developer information and get his/her hands dirty.

    By the way, when you do the Import Wizard report binding test, which is set as your source environment? Are you using it to test both the source and then again to test the destination. You can test the destination environment by running Import Wizard setting your destination environment as the source and setting this session’s destination as a BIAR file.

    Now, you said you used Designer to import the universes. Sorry, this was a bad move. In doing so, you almost certainly gave the universes a new CUID when you imported them. In doing so, Import Wizard probably made its best guess as to which universe belongs with each report. If you were you I would delete the reports and universes (if you have not changed them or created new reports on these universes) in the destination environment and run Import Wizard again and this time select BOTH THE REPORTS AND THE UNIVERSES in the same Import Wizard session.

  3. Hello Julian,

    Thanks for your immediate response. I have gone through your suggestion. I have verified the Universe-report bonding using the Query builder and found the relation ship is broken.

    Hence as per your suggestion I have deleted the Universe and corrupted reports in the destination and imported them using the Import wizard in a single session.

    This has resolved the issue.Many thanks for your support.

    Would you be able to help me on the points below?

    What is the best way if ONLY an universe has to be deployed in the production environment?(The universe already exists in the destination environment). Is it the import wizard/Designer import export option( I understand from you comment above that this should not be the case)?

    What is best way to deploy both latest versions of Univereses and Reports in production from Dev?

    What is best way to deploy the latest versions of Reports in production from Dev?

    Thanks,
    Aravind Yedire.

  4. Hi Aravind, Import Wizard is the best way to go, unless you have BO XI 3.1 and you have deployed Life Cycle Manager. Designer should only be used to develop universes or to import a universe change it and then export it back to the same environment. Never use Designer to migrate, promote, or import a universe from one environment to another if you ever intend to import any report associated with the universe from the source environment.

    If you only have universe changes to migrate from Development to Production then you are generally safe in using Import Wizard to migrate those changes without selecting the reports. If you only have report changes to migrate I highly recommend that you migrate the changed reports along with their unchanged universe. Import Wizard will actually warn you if you do not select the reports’ universe. Nevertheless, during some experiments I have noticed that often reports will retain their universe association with select the universe in the same Import Wizard session IF the universe already exists in the destination environment. Either way, good luck and select your Import Wizard options carefully.

  5. Hi Julian,
    I came across with the following issue:
    I have complex multiqueried multiple reports build on the universe. we modifyed our data warehouse and decided to build new universe. It has slighly different structure then original one. Also the difference that new one includes linked master universe.
    When I tried to update existing reports with link to new universe I went to every query and mapped old objects to new. To my big dissapointment reports got scrued up. Any advice what it the best way of doing this and how to prevent update reports problem?
    Thanks a lot,
    Ilin

  6. Hi Ilin, First I would make sure that the new universe is good by creating new reports from it. Just to be sure. Next, I would probably try and map one query at a time, do a save as after each mapping and then refresh the report as well. In other words break up the same tasks into batches. You will end up with quite a few copies of the report at the end, but you will hopefully uncover your problem or even avoid it by letting the report adjust slowly to the significant changes you are throwing at it.

  7. Hi Julian,
    We are using XIR3 and migrating from Development to Integration and then Acceptance environments. We are using the same system for 3 environments, one server- one repository for 3 environments; DEV, INT and ACC . I created 3 folders for reports; one for each environment and reports that belong to that environment. Also 3 folders under Universe object including Development, Integration, and Acceptance universes. We have different database and connection for each universe.
    I tried to use the Import Wizard to promote the reports and universes from DEV to Integration. Since the source and destinations are the same no matter what option I choose nothing happens. How can we migrate reports and universes when the source and destination are the same?

  8. Hi sherry, this is a bit off-topic, but quite intriguing. I like what you are doing, but you may be expecting something more from Import Wizard than it is capable of doing. Import Wizard could create duplicate copies of your objects if you select the “Merge” and “Rename Duplicates” options. Experiment with this on some test reports in sub-folders and universes. I have done it before to replicate content and generate all new CUIDs for the replicated objects with minimal effort. But it is not for everyone, or at least not for anyone that does not study carefully the impacts and pay attention to the selected options.

    Since Source and Destination are the same I think you may consider having an administrator just create duplicate copies using CMC. The problem you will run into is when you want to duplicate a universe and reports. You can duplicate both, BUT then you would have to manually map the duplicate reports over to the duplicate universes. Since this is tedious and error-prone, you may find that a “Merge” and “Rename” Import Wizard session is your best choice. Play with it, and please report your findings.

  9. Hi Julian,
    I myself am an Admin to a BO XI R2 setup. I have a query regarding Import Wizard and importing /Exporting Universes. As you have sadi in your answer to Aravind’s query, importing changed Universes should be done by Import Wizard. I am however confused as to which options to select/deselect while Importing or Exporting ONLY Universes.
    What I have is a Universe on Live, and the same Universe on another Server, but with a few new Objects added. Now I want to replace the Universe on Live with this one, but I do not want to export anything else with it, just the new objects. Could you shed some light on which options in Import Wizard would have what kind of effect??
    Thanks
    Chinmay

  10. Hi Julian,

    I have a query . I want to purdata data from Infoview Reports BOXI R2 version .
    First part
    Do we have command to find the size of the report using query ?

    Second Part
    How to purse data from those report instance ?

  11. Hi Hrishikesh,

    Certain attributes of reports are only contained within the report itself. I invite you to do a simple query in Query Builder, such as:
    SELECT * FROM SI_InfoObjects WHERE si_id = XXXXXXXXX
    where XXXXXXXXX is the object ID of the report and you will see all that is available to you. Each version of BO can have different properties available from this query, so this is a very good exercise for you.

    Best Regards,
    Julian

  12. Julian,

    I was reading your article and you mentioned about article that you will write for fixing unbound reports.

    Is this new article ready for people like us facing problems with unbound reports?

    IF not, could you please give me some idea to correct unbound reports. The problem is we have thousands of reports unbounded (as per Query Builder) and all of them are running fine may be because of universe short name relationship.
    But I still want to put back the binding to avoid unnecessary problems in the future.

    The only solution that I know is manually pointing the report to unvierse. I’m looking for some process that automatically puts binding to all reports.

    Your help is much appreciable.

  14. Julian,

    Thanks for the reply..I read all your articles..but none of them specified how to put back the binding other then manually opening the report.

    Please let me know if I’m missing something?

    Thanks
    Raj

  15. Hi Rajesh, you are correct. For now this is all that has been confirmed. I even raised cases to SAP-BO on this and they said it was the only way. I suspect that it could be done via SDK, but I have not played with it yet.

  16. Hi Julian. Nice article. Have you found out if it’s possible to bind a universe with reports via SDK? Thanks!!

  17. Hi Sue, thanks for asking. Unfortunately, I haven’t an SDK method for setting a report’s universe. I will ask around again and see if anyone can give me a lead.

  18. Julian,

    In the WHERE clause, we are using SI_UNIVERSE.SI_TOTAL = 0 to filter for unbound reports. It seems to be working. Is the issue you mentioned about not using property bags in the WHERE clause only an issue if you do not define a property as we have? I just want to make sure we are fooling ourselves somehow. Thanks.

  19. Did you publish another article about unbounded reports? We just recently migrated from R2 to 3.1 and now we noticed that when we open certain reports, they are using a different universe in R3.1 compared to R2! We are fixing them manually but I noticed with your queries above that they are still unbound. Any harm in them being unbound?

  20. Hi Anand, I have not published anything more yet. Sorry.

    Yes, there is harm in a report being unbound. It could jump to a different, related universe without the user noticing.

    I am interested to know what you are doing in 3.1 to fix them manually. In every case I have noticed fixing them manually in the WebI report, results in them being bound to their universe when viewed in Query Builder.

  21. Yes after our migration, the reports just started connecting to a different universe.

    I opened some, changed the universe in WebI and then saved them again. Or I use WRC, import them, change the universe and export again. In either of these cases, if I run QueryBuilder, they are still unbounded.

    The only way I found to make them bounded again is to open the report in WRC (save it locally) then delete the file in InfoView then upload my local copy. I suppose the same thing would work if I uploaded the file under a new name, deleted the old and then renamed this new file to the same name as the old but I didn’t test that.

    This is a very slow process though! We have a few hundred universes so selecting a new universe takes minutes… and we have found a few hundred unbounded reports.

    Do you think that if they remain unbounded that they can change what universe they use again? I mean if I open the report, fix the universe, save but noticed that it remains unbounded, that one day it could go back to the wrong universe? This is my main concern.

  22. Hi Anand, the method you describe should fix the binding between the report and the universe. I am going to have to take a closer look. You are using XI 3.1, right? Which patches do you have installed?

    To the best of my experience I have noticed that if a report appears unbound in Business Objects Query Builder then that report will always fail-over to the universe short-name in the report’s metadata. Since universe short name is not a unique value it is very likely that a report could randomly fail-over to any universes in the repository having that short name.

  23. Hello Sir,

    Thanks for your articles. They are really interesting. We do have similar issue with biding of Universe. We will run queries according to your suggestion and see if we could fix CUID.
    But I have another question. When I go to Query Properties, Universe Name is not Enabled for my users. Which security should I be giving them so that they could change Universe Name.

    Thanks

    Minal Nemani

  25. Your article says “The unfortunate fact here is that in Query Builder you cannot filter on a property bag. Therefore, you cannot create a query that only returns all of the unbound reports.”

    It is now possible to query one of the property bag properties to determine if a report is unbound. For example:

    SELECT *
    FROM CI_INFOOBJECTS
    WHERE SI_UNIVERSE.SI_TOTAL= 0
    AND SI_KIND = ‘Webi’

Leave a comment

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