New Query Builder Guide Version Published – Includes XI 3.x Updates and Relationship Functions

I have finally completed my nearly 3-month long update to our Query Builder Guide. Like most things I do, the deeper I got into it the more I found that needed work.

What kept me going, and delaying my previously promised delivery time, was the realization that aside from Ted Ueda‘s famous blog entries on Path Queries and Relationship Functions, I was building the only “how-to” guide for those Query Builder and SDK Query functionalities. I dug a little deeper into each one and broke them down into their basic components, making it easy to understand how to use them and to also be able to respect their limits.

I decided that this version of the guide was such a milestone for the guide that I jumped straight paste the 1.1 version and splurged on bestowing it with the 1.2.0 Version. The truth is that the guide itself grew by 50% in file size! Yes, I added that much new textual content!

Click here to order the latest Business Objects XI Query Builder Guide

This new version includes numerous additions to bring the guide current up to Business Objects Enterprise XI 3.1. However, for those of you not yet on BO XI 3.x, please take note of something I explain clearly in the guide:

“Query Builder is based on the BOE SDK and both tools in their BO XI 3.1 versions support all of the older methods and syntax used in BO XI R2. In fact, syntax that was labeled as deprecated in XI R2 continues to function even in XI 3.1. It is certain that XI 3.x brought some new functionality, and this guide will distinguish these, but rest assured most of what you can do in Query Builder is applicable to all BO XI releases.”

In fact, BO XI R2 administrators will greatly benefit from the newly added “Relationship Queries” section. This nearly secret functionality take Query Builder to a whole new level.

Future Customers

For the many of you to whom I committed to deliver this BO XI 3.x guide update earlier, I apologize for my tardiness and I sincerely thank you for your patience and understanding. To those of you still on the fence about whether or not to get this guide, I believe I have now packed it with enough value to make your decision a “no-brainer”.

Click here to order the latest Business Objects XI Query Builder Guide

Current Customers

To the now hundreds of you who have already purchased a previous version of our Query Builder guide, thank you again for your business and come get your free updated version. Please refer to your order confirmation email to obtain the instructions on how to get your COMPLETELY FREE updated guide. If you have any trouble, just email us and we will get it for you ASAP.

Future Improvements Coming Still

If left in a vacuum I might never publish any updates because I would never feel they are complete. This time is no different. I wanted to include more sample queries for Relationship Queries and Path Queries, but I decided it was better to publish the guide now that those tutorial sections are complete and then publish another version when I have added some additional, wonderful samples of each. As you can see, we are always working to improve this guide.

OK, now I think I will publish this little posting and get on with what is left of my beautiful Saturday afternoon. I hope someone out there appreciates my little sacrifice. 😉

Enjoyed this post? Share it!

 

30 thoughts on “New Query Builder Guide Version Published – Includes XI 3.x Updates and Relationship Functions

  1. Hi, I purchased your guide. All of the sample queries in your guide do not correspond to tables/fields in my CMS database.

    For example, I do not have tables named ‘CI_InfoObjects’ or ‘CI_AppObjects’. I do have a table named ‘cms_infoobjects6’ – which seems to have some of the fields in your sample queries – but not all of them.

    What am I missing? FYI – this is a new install of BOXIR3.1

  2. Great question and you are 100% correct accept for a very small detail. It seems you skipped the section that explains where to put the queries (the first section). Queries against the logical tables ‘CI_InfoObjects’ or ‘CI_AppObjects’ must be run either directly through the Business Objects Enterprise (BOE) Software Development Kit (SDK) or through the Query Builder application (which is a free utility provided with BOE). In BO XI 3.1 this tool can be found in the “AdminTools” web application and if this app is deployed to your server it can be accessed through the URL: “http://yourserver/AdminTools/”.

    The metadata that you want to be able to query is encrypted and stored in CLOBs inside mostly inside the table ‘cms_infoobjects6?. The data is further abstracted by being organized into the logical tables named ‘CI_InfoObjects’, ‘CI_AppObjects’, and ‘CI_SystemObjects’. Unfortunately, BO has made it so no one can access most of the data locked inside of ‘CI_AppObjects’ without the BOE SDK or Query Builder. Nevertheless, the guide that you purchased also includes and section that will help you query the ‘cms_infoobjects6? table directly and extract the very few pieces of information that are available directly.

    Thanks for your purchase and please do let me know if you have anymore questions.

  3. I will try an write a full article on this topic as this question is a bit off topic. From a Query Builder perspective the only significant advantages are the addition of Path Queries and an increase in the number of relationship types.

    In general BO XI 3.1 gives you WebI Auto-Save, WebI Rich Client, Patch Support beyond Jun 2010, Engineer Support beyond Jun 2011, Custom Access Levels, Aggregate Awareness, … I know there are more, but this is what stands-out in my mind right now.

  4. Julian,

    I purchased your guide, and it appears to be exactly as described. I have not been able to start Query Builder and was hoping you might be able to help.

    When I use the following url:
    http://servername:6405/admintools/querybuilder/ie.jsp

    I get the error: “404 Missing Page”

    When I use the this url (no port number):
    http://servername/admintools/querybuilder/ie.jsp

    I get the error “The page cannot be found…”

    I found the file ie.jsp in the …\admintools\querybuilder folder so it seems the url is correct. Do you have any idea what I am doing wrong?

  5. I’m glad the guide lives up to your expectations. I do understand it is frustrating not to be able to access Query Builder and start applying what you are learning. You will definitely need the port numbers unless your web server is on port 80, all other ports will require the port in the URL. If you are certain that the port “6405” is correct, then I suggest trying the URL: http://servername:6405/AdminTools/ the default page in “AdminTools” should get you where you need to be.

    Also, if you are running a Java server then please notice that EVERYTHING in the URL is case-sensitive, for example “AdminTools” instead of “admintools”. Please let us know if any of this helps or not.

  6. How do we create a universe with two different rdbms like one is oracle another is sequel?

  7. I think that one option might be some kinds of middleware that would allow you to create pointers tables in one of the DBMS that point to the other.

    But perhaps linking one universe to the other is the cleanest solution (go to properties of one universe and link to the other). I don’t use this feature as it is known to be unstable or problematic; however, it is supported.

  8. Hi Julian

    I´m trying to use query builder with 3.1 and I’m having the same result as Rick.

    I can´t find the solution.

    Did you find de solution?

    Thanks in advance

  9. Hi Gachi, I am not sure that I understand your problem completely. It seems to me you are saying that you cannot access Query Builder in your environment(s). Without more information it is difficult to help you, but I will try:

    1) Are you certain that the application/deployment named “AdminTools” is deployed to your web application server? For example, if you are using the default Apache Tomcat are you certain that “AdminTools” is deployed to it?

    2) A simple test to see if “AdminTools” is deployed is to just hit that application on the same server and port in the URL you use to hit CmcApp. For example, in Apache Tomcat you may use http://yourserver.yourdomain:8080/CmcApp/ to reach CMC, so you could test Query Builder by hitting the following: http://yourserver.yourdomain:8080/AdminTools/

    PLEASE comment on the results of the above tests and provide more information about your system if possible.

  10. Hi Gachi, in that case you need to deploy the “AdminTools” application to your web app server. BTW, that port number is an odd port, what web app server are you using. The 6400-6410 ports are considered reserved for use by BO, so I would avoid them for web servers.

  11. Hi,

    We are implementing BOXI 3.1 environment where in we are using BO SDK to customize the logging and logout as per our needs. We are logging by creating a valid logon token and then redirecting the control to Infoview URL which is generated as shown below

    alert(“infoViewURL: ” + “”);

    <%
    out.print(infoViewURL + "?ivsLogonSession=" + java.net.URLEncoder.encode(serializedSession));
    //response.sendRedirect(infoViewURL+"?ivsLogonToken=" + URLEncoder.encode(logonToken));

    response.sendRedirect(infoViewURL + "?ivsLogonSession=" + java.net.URLEncoder.encode(serializedSession));

    } catch(SDKException e) {

    out.print("ERROR ENCOUNTERED” + e.getMessage() + “”);

    }

    %>

    Everything works fine with the above code

    Issue :

    In the above code the Infoview URL that will be coming on the infoview main page window will contain the Server name and port number. We do not want the port number to appear in the address bar it. Is it possible to do that as this is very critical for our project implementation.

    Please help!!!

    –Ak

  12. Hi Ak, I usually don’t allow off-topic comments, but I don’t really know of any other article on this topic. When you say “Server name and port number” I understand that you are talking about something like “myserver.domain.com:8080”. This may not be your port number, of course. If you want to do away with this port number then I must ask you how can you log directly in to your InfoView? Do you use a port number? If you do, and you don’t want to then you are asking mostly a web server question, not a BO question. The only way that a standard web browser can access your web server without a port is if the port is 80 (for http) or 443 (for https). So either you need to change your web server’s port or you need a proxy web server (such as Apache or IIS with a proper module that allows the web server to proxy for the web app server.

  13. Hi Julian,

    I apolozie for going of the topic, I was not aware of the process I will take care of the same in future.
    Thanks for the reply, I will take this up with the server admin

    Thanks again

    Ak

  14. Hi Julian,

    I apologize for going of the topic, I was not aware of the process I will take care of the same in future.
    Thanks for the reply, I will take this up with the server admin

    Thanks again

    Ak

  15. I purchased the Query Builder Guide on 2/8/10.
    I see Julian’s note from 3/6/10 that it was updated for XIR3.
    My old download links don’t work anymore.
    How do I get the new version?
    Here is my info from the original purchase.
    Customer Name: TIMOTHY ?. ???????
    Customer Email: tim.????@?????.com
    Transaction ID: gc-934##########

  16. Hi Tim, thanks for your question, I know this has not been as clear as I would like it to be. After you purchase the guide you receive an email with the subject “BusinessObjectsTips.com Query Builder Guide Purchase” and in this email there is a section that reads as follows:

    “FUTURE-PROOF”
    You have also purchased a license to any future updates of the guide. In the FUTURE, you will find newly released versions of the guide at the following link: http://www.businessobjectstips.com/….

    The link contained in the email includes a functioning secure URL which all customers receive. If you cannot locate your email you may also contact us by email with the email address or transaction idea or your name and we will quickly take care of getting you what you need.
    Thank you for highlighting this issue.

  17. Julian,

    I needed to find out what query I can use to find out what Universes users have access to. Is this possible ? I know one data is is within “CI_SYSTEMOBJECTS” and other is within “CI_APPOBJECTS” but I do I know how to link them.

    Thanks Kindly,

    Ajay Gupta
    SaiCom Consulting Inc,
    Your Canadian Connection

  18. Hi Ajay, I can tell that you are already quite well oriented to the data model from your statements. The trouble is just as you state. Those data elements are in two different logical tables. To make matters worse the Query language does not just open joining. Honestly, I think that most people accomplish what you are asking through the SDK. There you can query your different data elements and use logic to combine them as well. What you seek should be easier to obtain, but it is really quite difficult.

  19. Hi Julian,

    I am trying to find what type of user entitlement on each applcation/folder. Example:
    User-X WebIntelligence -No Access, User-Y Designer Full Control.something like this,could you please hint me with sample queries.

    Thanks
    Sheshu

  20. Hi Sheshu, Querying objects and application security is the “Holy Grail” on Query Builder. Honestly developing the query has become so complex that I have abandoned is and reverted to CMC or Security Viewer when I needed this information. If I learn anything new on this I will be sure to write a new article on the topic.

  21. Hi,

    Can you provide any feedback on how to deploy AdminTools if it is not deployed?

    Thanks!

  22. Hi John,
    On a server where you have the full server install you should be able to run the following command to deploy AdminTools to Tomcat:

    /deployment/wdeploy.sh tomcat55 -DAPP AdminTools deploy

    The above is for Unix/Linux, but it will work for Business Objects on Windows as well, just it will be “wdeploy.bat“. If you want to know more you will want to look at the published Business Objects document “Web Application Deployment Guide”.

  23. Hi Julian,

    I’m searching a way to list all schedulings waiting for a certain event. One way is to list all recurring instances via

    SELECT * FROM CI_INFOOBJECTS WHERE SI_INSTANCE != 0 AND SI_RECURRING = 1

    and search the event id with the web browser.

    Of course this is not the smart solution I’m looking for but I couldn’t find a way to pre-select a certain event id in the query.

    Do you know a way to get this information or is this not possible?

    Thanks a lot

    Jean-Luc

  24. Hi Jean-Luc, can you tell us which property you are looking at to visually identify the event ID? I suspect that it may be in a property bag, and as a result there is no way to filter on it using Query Builder.

  25. Hi Julian,

    sorry for my late answer. I fear it is exactly as you mention it: When you have a recurring instance that waits for an event the event-id is stored in the SI_DEPENDENCIES-property. I was not able to find a way to filter on the listed event-ids in this property(-bag). Do you know a technique to manage this?

    Thank you and best regards

    Jean-Luc

  26. Hi,
    I purchased your Query Builder Guide: it is very useful and complete. Maybe you can give me some tips on how to return all webi reports in a category and its sub-categories.
    I tryed with the following SELECT, but it doesn’t work and returns only the reports at the first level.

    SELECT SI_ID FROM CI_INFOOBJECTS WHERE
    SI_KIND = ‘Webi’
    AND
    DESCENDANTS(“SI_NAME=’Category-Document'”,
    “SI_NAME=’start_category'”)

    I’m working with BO Enterprise XI R2 and 3.1

    Thanks.

Leave a comment

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