Posts Tagged ‘Query Builder’

New Version of the Query Builder Guide is Released!

In January I committed myself over on our Facebook page to completing the long-promised updates to the Business Objects Query Guide. Last night, technically just one day later than promised, I delivered on that promise.

Many Improvements to Our Most Popular Guide

1) First of all, the hyperlinks within the document are fixed. There are more of them and now they ALL work linking different parts of the document and providing links to the web as well.
2) I include details about Business Objects Business Intelligence platform 4.0, a.k.a. BI 4.0, a.k.a. XI 4.0.
3) Added MetaData.DataConnection to Appendix A’s si_kind cross-reference table.
4) Corrected error regarding “Supplemental” fields (link) such as those in Processing Info.
5) Cleaned-up sample queries and added a couple new queries.
6) The biggest improvement, aside from the fixed hyperlinking is that I added NEW section focussing on the IsAllowed function. This function is great, it allows you to filter query results based on the security rights of a specified user or user group! There is very little written about this function anywhere, and certainly not more than in the Query Builder guide.

Get the Latest Version of the Query Builder Guide

Therefore, if you have not yet purchased your Business Objects Query Guide, now is the time to get it. If you are already reaping the many benefits of owning the Business Objects Query Builder guide, then now is the time to pick up your free upgrade (please see your original purchase email receipt for details, contact me if you can’t find them).

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.

Hardly Working? No, Hard at Work on the Query Builder Guide

I feel I owe a small explanation for the lull in the new article publishing on the site. I have remained active at responding to article comments, but I haven’t published a new article in nearly a month (although I just added some details to the latest article on BO XI 3.1 Fix Packs). Anyway, I have been busy on BusinessObjectsTips.com topics, this is sure!

I have been working on a big update to our popular Query Builder guide. As many of us have discussed through email, I have been updating the guide, originally written based on BO XI R2, to include all of BO XI 3.1 new functionality. All of the XI R2 functionality still works perfectly in XI 3.1, but XI 3.1 brings some exciting new stuff like “Path Queries” and “Relationship Functions”. Writing and testing out examples always takes more time than I estimate, as also does my day job and personal life . I hope to finish and publish it, the new guide version, not my personal life, by the end of this month. Of course, as always you can find the guide here (link) and as always this new version will be made available freely to all previous Query Builder customers.

OK, back to work now. Thanks for your patience.

[UPDATE - March 6, 2010]: I have just published the promised guide update. Please see my new posting “New Query Builder Guide Version Published – Includes XI 3.x Updates and Relationship Functions“

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

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.

Have You Heard of Path Queries in Query Builder?

Did you know that there is a tweak that you can do to your Business Objects server’s Query Builder main page “query.jsp” so that it can handle “Path Queries”? Do you know what a “Path Query” is? Do you know why you should care about “Path Queries”?

I was originally introduced to Path Queries by a posting at an SAP Business Objects Blog on the topic by Ted Ueda. The article got me really excited to try out this new syntax and seemingly simpler method of querying my CMS metadata.

Path Queries are very different from SQL queries and they are basically written like this path://InfoObjects/Root Folder/My Folder/*. This simple example would return all of the objects in the “My Folder” folder. Cool, huh?

Path Query syntax actually gets a lot deeper. It allows you to specify relationships between objects (the above example defaults to a parent-child relationship), specify filters, sort order, and they can even function as a kind of sub-select. For example, returning all groups for which the user Administrator is a member.

If you would like to learn more, keep an eye out for future articles on Path Queries or better yet, get your hands on our popular “Query Builder Guide” which completely explains Path Queries and the exciting SQL “Relationship Queries”.

New Lower Price on Query Builder Guide

I just wanted to let visitors know that we have just lowered the price of the “BusinessObjectsTips.com Query Builder Guide” to $27.00. We have heard a lot of good things about the guide from its students since its release late last year. It truly is the “The Best Query Builder Guide Ever Written”!

Just as one very small example of the benefit of the guide let me share the following excerpt:

“Query Builder’s SQL engine is not case-sensitive. This applies to both inputs in the Query Builder GUI and also queries manually added to the top text area. For many this is a welcome feature as it improves efficiency in query entry and clear query results. Others may loathe this feature as it provides less filtering functionality for object values with only case variations.”

Querying the Busines Objects XI CMS InfoStore Database Tables

The question has come to my mind many times over the years and this month it has come to my inbox.
Thanks to Pluto for inspiring me to write this article.

The question: Is it possible to peer into the Business Objects XI CMS InfoStore (a.k.a. database repository) without Query Builder or the BOXI SDK????

The answer: No

All of my research and experience tells me the following:
Much of the meta data, the useful part especially, is stored in the CMS InfoStore in an encrypted binary format. No one has been able to decrypt this format yet, but I can’t really say that anyone has tried very hard. I certainly haven’t.

Some Things You Can Do with the CMS InfoStore’s Database Tables

The CMS InfoStore is mostly held in the database table named “CMS_INFOOBJECTS5″. Some other less significant data is held in the following tables: CMS_RELATIONS5, CMS_IDNUMBERS5, CMS_ALIASES5, and CMS_VERSION_INFO.

The structure of the CMS_INFOOBJECTS5 table in Oracle is as follows:

OBJECTID INTEGER NOT NULL,
PARENTID INTEGER NOT NULL,
TYPEID INTEGER NOT NULL,
OWNERID INTEGER NOT NULL,
LASTMODIFYTIME VARCHAR2(32 BYTE) NOT NULL,
OBJFLAGS INTEGER NOT NULL,
USERFLAGS INTEGER,
SCHEDULESTATUS INTEGER,
NEXTRUNTIME VARCHAR2(32 BYTE),
ALIASES RAW(255),
CRC VARCHAR2(32 BYTE) NOT NULL,
PROPERTIES BLOB NOT NULL,
SI_GUID VARCHAR2(56 BYTE),
SI_CUID VARCHAR2(56 BYTE),
SI_RUID VARCHAR2(56 BYTE),
SI_INSTANCE_OBJECT INTEGER,
SI_PLUGIN_OBJECT INTEGER,
SI_TABLE INTEGER,
SI_HIDDEN_OBJECT INTEGER,
SI_NAMEDUSER INTEGER,
SI_RECURRING INTEGER,
SI_RUNNABLE_OBJECT INTEGER,
SI_TYPEID_MACHINE INTEGER,
SI_KEYWORD VARCHAR2(255 BYTE),
SI_KEYWORDISTRUNCATED INTEGER,
LOV_KEY VARCHAR2(18 BYTE),
OBJNAME VARCHAR2(255 BYTE),
OBJNAMEISTRUNCATED INTEGER

The “properties”, “aliases”, and “objname” fields are where most of the goodies are to be found and these are the fields that are encrypted or in some way pretty much unusable to us without the SDK or Query Builder.

A New Hope for CMS_INFOOBJECTS5

Don’t completely give up on querying this table directly. There are still quite a few valuable data that can be retrieved from the table. Firstly, you should know that the following fields are indexed and there for with a large CMS InfoStore database you will want to use these for filtering your query:

PARENTID
OWNERID
TYPEID
LASTMODIFYTIME
NEXTRUNTIME
SCHEDULESTATUS
OBJECTID
SI_GUID
SI_CUID
SI_RUID
SI_INSTANCE_OBJECT
SI_PLUGIN_OBJECT
SI_TABLE
SI_HIDDEN_OBJECT
SI_NAMEDUSER
SI_RECURRING
SI_RUNNABLE_OBJECT
SI_TYPEID_MACHINE
SI_KEYWORD
SI_KEYWORDISTRUNCATED
LOV_KEY
OBJNAME (this is encrypted though)
OBJNAMEISTRUNCATED

Not all of these fields will have data, but some will and those that sporadically have data might meet your needs perfectly. I don’t really have a strong background in querying this table directly. So we are going to have to crack this nut together. Hopefully we can share our progress in the comments of this post and through updates from me to this article.

A Sample Use of a Direct Query on the CMS InfoStore

For example, I may want to extract a list of all si_cuid (the most unique identifier of an object) and objectid for objects that were created or modified on January 24th. To do this I would use the following query:

SELECT
objectid,
si_cuid
FROM
CMS_INFOOBJECTS5
WHERE
lastmodifytime LIKE ('2009 01 24%')

If you look to the table structure you will see that the field lastmodifytime is of a character type and therefore it can be queried as presented above.

Why would you want to do this query directly on the database and not in Business Objects’ Query Builder?

Short answer: performance and possibility. What I mean to say is that you can control more precisely the performance of the query you write against the table. In fact, a similar query done through Query Builder might perform so slow that you it would not be possible except if you ran it directly against the database table. The truth is that many valid reasons to directly query the database will still end up with you taking the output of that query and then running it through the SDK or Query Builder to obtain all of the properties corresponding to that output.

What other database tables can I query?

CMS_RELATIONS5 and CMS_VERISON_INFO seem to have the only other bit of useful data. CMS_VERSION_INFO has a single row with the repositories version info; I am not certain that you can detect service pack or fix pack level here, in fact, I think you cannot. The CMS_RELATIONS5 table seems to be interesting, but I have not come up with a useful reason to query it beyond tinkering.

For your reference the CMS_RELATIONS5 table has the following structure in Oracle:

PARENTID INTEGER NOT NULL,
CHILDID INTEGER NOT NULL,
ISMEMBER INTEGER NOT NULL,
ORDINAL INTEGER,
RELATIONSHIPID INTEGER NOT NULL

None of the data appears to be encrypted. It also appears to have almost a 1-to-1 ratio between its record count and the count of the objects in the repository as reported in Business Objects Central Management Console. The value of this table might increase if we were to discover the meaning of different relationshipid values.

Conclusion: Where do we go from here?

I feel that I have only given you enough information to advance your tinkering on the true back-end. Honestly, this is a new frontier for me. I hope to post more information here as I find the time to experiment with directly with the database or if I hear from anyone else that has experience they would like to share. I invite you to share your findings as you play with querying the database. Use BO Query Builder concurrently to help you understand more about what you are seeing. If you find you need some help with Query Builder then I do recommend that you take a look at our Business Objects Query Builder Guide

Using BO Query Builder to Detect Report-Universe Binding Status

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 for a report not bound to ANY universe

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.

A Favorite from Query Builder – Active User List

Sometimes you can’t get Business Objects’ Central Management Console (CMC) to return the “Metrics” page and you need to know how many users are logged in to the system. Other times the CMC “Metrics” page works, but in the case of system administration, you may want to know who is logged in to your Business Objects XI CMS InfoStore. In either case, your best bet is to turn to BO Query Builder for the information you need.

In an effort to avoid artificial suspense building let me give you the query first and then tell you a little about it if you still have the time to read further:


SELECT TOP 3000
*
FROM
CI_SystemObjects
WHERE
si_kind = 'Connection' AND
si_parent_folder = 41 AND
si_authen_method != 'server-token'
ORDER BY
si_name


About the Query

This query returns all of the information available in the CMS InfoStore for all users that are currently logged in. The results are presented alphabetically by user name. IMPORTANT NOTE: the results will not be filtered by distinct user; therefore if a user account is used by multiple people or multiple applications are used you can expect to see multiple logins. The field si_lastlogontime might be useful in making sense of multiple entries.

At the moment I am writing this I started to wonder if scheduled jobs created by a user result in a login returned by this query. I will look in to this and follow-up if someone doesn’t already test this out and leave a comment.

I invite you to play with this query and try to figure out what is so magical about “si_parent_folder = 41“. Please share your thoughts and discoveries by leaving a comment here. I also recommend that you take a look at our newly published “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.

Business Objects Query Builder Functions: Max, Min, and Sub-Queries

If you have spent some time with Business Object’s Query Builder you may have arrived at the conclusion that the SQL that it supports is rather simplistic. I don’t blame you, when I first started using Query Builder I felt the same was after spending just a few hours struggling with the tool. Most of the QB queries that I could find on the Internet were rather simplistic and they left me thinking that the tool was much more limited than it really is.

Case In Point: Getting the Functionality Max and Min Functions and Sub-Queries

If you have used SQL with databases you no doubt know that the functions MAX and MIN are quite popular and powerful. Logically they return the maximum or minimum value of a particular field. In Query Builder the functionality is a little different, but maybe in a good way. When this functionality is invoked all of the records with the maximum or minimum value for the particular field are returned.

The following syntax excerpt was swiped from our “Business Objects Query Builder Guide“, let’s review it and continue our discussion:

MAX and MIN and Much More
The ALL operator can be quite powerful and a bit confusing. It allows you to get as close to a sub-query, or MAX and MIN functions, as is possible with Query Builder.
The ALL operator should only be used between two properties or fields that are of the same data type. ALL can be used to identify the maximum or minimum value when used in conjunction with two of the same properties. When used with different fields or properties it can be used to compare property 1 to all possible values of property two.

Operator Syntax
= ALL
!= ALL
> ALL
>= ALL
< ALL
<= ALL

Examples
SELECT * FROM CI_InfoObjects WHERE si_children >= ALL si_children
(returns all info objects with the maximum number of children in the repository)

SELECT * FROM CI_InfoObjects WHERE si_id != ALL si_parentid
(finds all info objects that are not parents of other objects)

Query Builder MAX and MIN Functionality, not Functions

OK, busted, you got me. Their are no exact MAX and MIN functions that you drop into a SELECT clause. HOWEVER, in your WHERE clause all that you need to get the functionality is to combine the >= and <= operators with the special "ALL” operator. This operator essentially launches a sub query for all values of the valid and same data type property which follows it. Well, you get the point.

Didn’t I Say Something About Using a Sub-Query in Query Builder?

If the operators >= and <= result in MAX and MIN functionality when compared with the same property then what might happen if we try other operators or even different properties on both sides of the "ALL" operator. The various possible combinations are numerous, but you must remember that the compared properties must be of the same data type. I also believe that you are limited to valid properties of the same logical table, for example you cannot compare report properties to universe properties because they come from different logical tables (ci_InfoObjects and ci_AppObjects). Some of the combinations will produce interesting results, such as those given in the last example above.

I invite you to play with these and share your thoughts, discoveries, or comments here in the comments of this post. I also recommend that you take a look at our newly published "Business Objects Query Builder Guide", it probably is "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.

Officially Released: The Best Business Objects XI Query Builder Guide Ever Written!

Perhaps a few visitors to BusinessObjectsTip.com have already noticed that we posted a new guide for BO XI Query Builder on the site last month. Due to some technical difficulties and lots of other lame excuses there was trouble finding and ordering the guide. These obstacles have been overcome now and I would invite you to check out “The Best Business Objects XI Query Builder Guide Ever Written!” We know that you will find it valuable and that it will turn you into a Query Building Genius!

Business Objects XI – Changing BO Report / Universe Relationships

Common Ground – Terminology

Firstly, let me lay down some terminology in order to make this concept easier to discuss. “Classic BO” refers to any version of Business Objects between 5.X and 6.X. “BOXI” or “BO XI” refers to the Business Objects XI Release 1, 2, or 3 (R1/R2/R3), the injection of BO to the Crystal platform.

BO Classic Universe-Report Binding

In Classic BO if you wanted to replace a universe that was deleted from the repository or swap out one version of a universe for another version all that you had to do was place a universe in the repository which had the same name as the universe upon which the report was initially developed. The binding between report and universe was on name. For this reason universe name, for the most part, was the unique identifier for the universe. It made sense and it was a relationship everyone could understand regardless of technical background. When a report was selected from the repository its universe was also found based on the name of the universe listed in the report’s properties.

BO XI Universe-Report Binding

In BOXI, reports are bound to their universes not by the universe name, but by the unique identifier of the universe, the Cluster Unique Identifier (CUID). While this may seem a small change at first glance, one begins to see the full scope when one thinks beyond the simple workflow of universe creation and report creation. For example, what happens when you want to move the universe and report to another BOXI environment? What about if someone deletes the universe and reloads it from a back-up copy on their PC? What if you copy the universe to a different universe folder? Do you know how to answer these questions? Will your answers always result in preservation of the universe’s CUID?

Appearances are Deceiving

At first glance BusinessObjects has brought Full-client, WebI Intelligence, and Universes almost as they were in BO 6.5 (with a few improvements) into the the new Crystal platform with BOXI. The fundamental problem here is the phrase “as they were”. To the end user, it will appear that, despite the InfoView improvements and addition of multiple data providers to WebI, everything else is “as it was”. While familiarity is good, here it also creates the problem. Most end users do not realize that the binding mechanism between report and universe has changed, because for the most part everything else has not, and many training courses do not alert users either to this binding change. Therefore most users will follow development work flows based on the misconception that reports are bound to their universes based on universe name.

How to Maintain Business Objects XI Universe-Report Binding

Getting Started – Creating BOXI Universes and Reports

First of all you do not need to do anything special to create the proper CUID-level binding between the universe and the report. Creating a new report and selecting the universe will create the proper binding. In fact, if you copy a report, that is properly bound to a universe, you will end up with a duplicate report that is properly bound to it’s universe. Like I said before, if this is all that you are doing then you need not think differently about Universe and Report Binding. The problem is that most of us do more with reports and universes then just create them once, save them, and leave them alone.

Making Changes – Proper Universe Editing

Editing a report and a universe requires no special instructions as long as you obey some guidelines. Universes require the most delicate and precise handling. A universe that is imported from the repository, edited, and exported back to the repository will maintain it’s CUID and relationship to its reports. If you create a copy of the universe, using either CMC or Designer, the copy will receive its own CUID and it will not be associated with the original’s reports. Moving a universe between universe folders will maintain the CUID; HOWEVER if a universe of the same name exists in the destination DO NOT overwrite the universe. This will definitely cause the moved universe to receive a new CUID (this is true at least in Business Objects XI Release 2). NEVER USE THE OVERWRITE FUNCTIONALITY OF DESIGNER, terrible, unreliable results are nearly guaranteed. By the way, moving a universe cannot be done in CMC, BO Designer is required.

NOTE: A CUID is a unique identifier. It is impossible for two objects in the world (or same BO XI platform for that matter) to possess the same CUID, unless IMPORT WIZARD (or some other such tool) is used to “clone” the object from one environment to another.

Making Changes – Proper Report Editing

With regards to retention of the universe-report binding, editing a report does not require much delicacy at all, beyond that one would normally employ. However, if your goal is to retain the Business Objects report’s CUID (for reasons of cross-environment synchrony, for example) then there are some rules of engagement. A new CUID will only be created for a report if the report is duplicated, for example: the report is copied, the report is saved using “Save As”, the Import Wizard is used (various options could result in a duplicate with a different CUID).

NOTE: Once you understand the basics you will begin to ask yourself more profound questions such as… How can I tell if a report is not bound to its universe? Many unbound reports continue to work fine, why is that and if this is true why should I worry? How can a revert back to an old version of a universe without losing my universe’s CUID? Why do unbound reports spontaneously go berserk? These are all excellent questions that I will answer if you confirm to me (through your comments) that you are interested in the answers.

Closing Thoughts

In order to maintain CUID parity/synchrony within your Business Objects XI environment and across multiple BOXI environments you will need to learn more than can be covered in the scope of a single article. If this article has piqued your interest or assisted you in understanding the basics, and you would like to learn more then please leave your comments. I could present a couple more focused articles on this topic. Nevertheless, I would also like to know if there is interest in a more comprehensive guide that we could make available for sale at a reasonable price. If so, I would invest more time in this (many hours).

Query Builder: Improving Query Performance

Business Objects Query Builder is a free application provided with Business Objects Enterprise XI (BOXI). It is uses the BOE SDK to provide a simple web user interface for submitting queries against the CMS InfoStore and returning their results.

Query Performance Concerns: Indexed Properties

As you begin to experience the wonders of Query Builder you will find that certain queries run quickly and other run very slowly, or not at all. It is important to remember that you are still querying a database and certain portions of that database have been indexed for improved performance in Business Objects. Among the lucky indexed properties you should find the following. For improved performance use these in your query’s selection criteria (WHERE clause):

SI_CUID
SI_GUID
SI_HIDDEN_OBJECT
SI_ID
SI_INSTANCE_OBJECT
SI_KIND
SI_NAME
SI_NAMEDUSER
SI_NEXTRUNTIME
SI_OWNERID
SI_PARENTID
SI_PLUGIN_OBJECT
SI_RECURRING
SI_RUID
SI_RUNNABLE_OBJECT
SI_SCHEDULE_STATUS
SI_UPDATE_TS
SI_INSTANCE


Optimizing Query Builder Queries

Query performance is improved by using indexed properties in your selection criteria (WHERE clause); however, the order of these can additionally optimize your query’s performance. All selection criteria with indexed properties should be placed first in the WHERE clause. The BusinessObjects Enterprise CMS InfoStore, and therefore Query Builder, processes queries from top to bottom and left to right. Therefore the selection criteria should also be ordered from the most restrictive to the least restrictive. For example, SI_NAME = ‘My Report’ should be place before SI_KIND = ‘WebI’ in the query. It may not be the prettiest query but it will be the fastest.

For example:

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