What is a Universe Overload?

What a cool and confusing name!

I have to admit that the name “Universe Overload” tends to send my imagination going to exciting science-fiction worlds and different dimensions. I think the person that coined the phrase must have chosen it for that reason, because for me it does not seem to represent. To calm my imagination and to help the phrase prompt more appropriate understanding I tend to replace the word “Overload” with “Override”. In general, “Universe Overloads” override the default restrictions placed on the universe objects.

Then what are “Access Restrictions”?

Prior to BO XI 3, “Universe Overrides” were called “Universe Overrides”. In Business Objects XI 3.1 “Universe Overrides” we renamed (like so many other things) “Access Restrictions”. The incremental boringness of this new name is off-set by an equal increase in the descriptiveness of the name. “Universe Overrides” or “Access Restrictions” are simply object restrictions, table mapping, and row restrictions which are directly added to a universe.

How can I add or modify a Universe Overload?

You can modify/view these “Access Restrictions” by opening the universe in Designer. Then select Tools > Manage Security… > Manage Access Restrictions. Nevertheless, if you did not know that already then you will need more than this brief article to get you safely going into the exciting and futuristic world of “Universe Overloads”.

Backing Up and Restoring Universes While Maintaining CUIDs

Import Wizard BIAR File – The ultimate backup

The sections that follow this one will tell you how to create a backup of a universe on your machine from which you are running Designer. The steps in those sections are “tricks” that allow you to maintain the Business Objects XI ’s universe’s CUID. With practice they can be executed quickly and on the fly to permit the Universe Designer/Developer to create a back-up more quickly than if they were to use Business Object XI’s Import Wizard’s BIAR file backup method.

With regards to maintenance of the Universe’s CUID and general backup/snapshot capabilities Import Wizard’s BIAR file backup has proven to be quite reliable. This is not to say that I have never seen issues, but these have been limited to large BIAR files and large numbers of objects. Creating a BIAR file backup of a single universe or just a few should 99% of the time be reliable. The drawback is that creating such a backup on the fly can be a rather significant workflow disruptor, you must chose just the right settings in Import Wizard, and because you must switch to Designer, you can only backup what has been loaded to the CMS. All of these attributes might steer you to look for another on the fly backup technique, if so, keep reading…

Make Back-up of a universe

1. Using Business Objects Designer import the universe. Then making no changes, close the universe. This will create a fresh, unaltered copy of the universe on your local computer.

2. Locate the universe file (*.unv) and the folder (has the same name as the universe file) in the following folder path on the computer on which you are running Designer:C:Documents and SettingsApplication DataBusiness ObjectsBusiness Objects 11.5Universes@DevelopmentWorking Area

3. Select both the universe file and the folder (hold CTRL and use mouse). Then right-click either the selected file and chose “WinZip -> Add to Zip File…” Note: Any compression tool will do.

4. Enter a name that is meaningful to you; it may indicate the version of the universe, the date/time, or editor. Click “OK” and notice the new Zip file you created. This is your backup.

Restore a back-up of a universe

As has been stated many times on this website, you must be very careful with Universes and their CUIDs. Many logical work flows for handling universes can result in changing CUIDs and lost report bindings. Nevertheless, if properly back-up a universe can be restored to a prior state by following this work flow.

1. Open Designer, but do not open any universe2. Locate the desired corresponding Universe folder and zip file. They should be located in:

C:Documents and SettingsApplication DataBusiness ObjectsBusiness Objects 11.5Universes@DevelopmentWorking Area

3. Back-up the existing universe file and folder to a new Zip file4. Delete the existing universe file and folder5. Unzip the backed-up universe file and folder to this same location. Note: They must be in the

same local folder as the universe file and folder they are replacing.6. Open the universe file that you just unzipped and export it to the folder in which you are

working:

7. You will receive a prompt similar to the following. Click “Yes”.

Prompt reads, “A newer version of this universe exists in the repository. If you continue with the export you may overwrite existing changes. Do you want to continue?

8. WARNING: if you receive any messages asking you to Move, Copy, or Overwrite a universe then you may not be restoring to the exact location that the universe previously resided. Prompts asking to overwrite the universe will be received if the universe you are attempting to restore does not have the same CUID as the backup universe. In this case, double-check your directories and analyze everything in Query Builder.

9. At the end of the export close the universe and import the universe you just exported.10. Verify that this is the correct universe.11. You have successfully restored the universe

For most of us the @Prompt function syntax can be a bit confusing at first. Sure we catch on quickly, however, we often need to review the business objects @Prompt syntax available online or refer to a previously created @Prompt. After we get comfortable with the @Prompt and the available options such as custom list of values, constrained/free, mono/multi, and default/purge values (link to article), many of us are ready to take our @Prompts to the next level.

Prompt Conditions – Fancy Lines in a SQL WHERE Clause

Of course, if we are discussing @Prompt then we are discussing work done in Business Objects’ Designer. I suppose you could hard-code one in the SQL of a WebI report, but if you are doing that then I don’t think you are ready for the next level. Anyway, back to Designer, @Prompts are placed in condition objects (the little filters). Conditions become part of the WHERE clause of a report’s SQL. Therefore, much of what you can do with the WHERE clause in SQL can be replicated in Designer’s condition objects. This means that you can include AND and OR logic, for starters. This is where the magic starts, because “OR” logic is the drive behind optional prompt conditions. Anyway yo get the technical discussion started here is an example of basic business objects @prompt syntax:

Products.product_num IN @Prompt('Enter Product Number(s)','A',,MULTI,FREE)

Multiple @Prompt Applications Using @Variable

Let me take one step back and discuss the BO syntax briefly. @Prompts are translated into SQL by BO with the values submitted to them. Sometimes this translation is frustrating (dates date types for example). The interesting thing is that this translation can occur for each instance of the @Prompt in the report’s SQL. So you could place it in various parts of your report’s WHERE clause and each would be executed and evaluated. HOWEVER, the prompt values are all consolidated under a single @Prompt text string. This because the unique identifier of the @Prompt is the text displayed to the end user, you know the first string following the “@Prompt(“. Another way to reuse a prompt in the SQL of a report is to use the @Variable function with the exact same prompt text string. This allows you to use the input from the user without replicating all of the rest of the syntax in the @Prompt. Using @Variable is preferable where possible because it will avoid having to make changes to an @Prompt in too many locations (when changes are inevitably needed).

Creating a True Condition

SQL WHERE clauses function much like most logical programming; they evaluate to “True” or “False”. I don’t really want to get into a tutorial on logic, but it is important that you understand a bit about it. In short, this is what you need to know:

true and true = truetrue and false = falsetrue or false = truefalse or false = false

In order to create an optional prompt you will need to create a clause that has the ability to evaluate true based on a valid parameter value or a submitted keyword that you have configured for the prompt. Therefore you want an OR clause. Here is the key to the whole thing and it looks like this:

Employee.employee_id = @Prompt('Enter a value (* to bypass filter)','A','',MONO,FREE)

OR'*' = @Variable('Enter a value (* to bypass filter)')

The keyword that is pre-configured in this prompt is: * (the asterisk character). So do you see it? If I enter a valid value for Employee.employee_id then my optional prompt clause will return those employees. If I enter a “*” character (minus the quote characters) the prompt clause will evaluate “TRUE” for all records and this will make as though the enter Prompt clause were not even present. It has been bypassed.

Standard Practices with Business Objects Optional Prompts

I have seen various keywords used over the years. The “*” (asterisk) is the most common. The word “All” is used by some, but when using letters you have to be prepared to handle or prevent mixed case responses. I have also seen the keyword added to a List of Values and the Business Objects prompt set to constrained to prevent upper/lower-case issues. This would for MONO or MULTI prompts, but please know that putting multiple values together with the keyword will still result in a complete TRUE clause/statement (or bypassing of it). Often the keyword is included in the prompt text or at least in any auxiliary training documentation. Remember, the keyword doesn’t help anyone who is not aware of it or how to use it. Finally, the keyword should not be similar, close to, or the same as any possible valid value for the prompt condition (nothing close to Employee.employee_id in this case); this would REALLY confuse the end-users of the BO prompt.

Optional Prompts in Business Objects XI 3

BO finally caught up with its competitors with the release of BO XI 3.0. In this release it began to include the ability to make report-level prompts optional through a check box. The unfortunate part is that this requires the user to create his or her prompts in the report and not follow the best practice of creating them in the universe. I haven’t yet seen any way to make a universe-level @Prompt optional based on configuration initiated at the report level. Perhaps this is by design; it allows the universe author to perfectly control both the composition and the optional nature of the prompt.

If you haven’t yet seen how to make a report level prompt option, please take a look at the image below. To get to this point you need (1) open the report or create a new one, (2) click on “Edit Query”, (3) locate a report-level prompt or create one, (4) click on the prompt properties button, it looks like a question mark in a blue circle over-lapping a small window with an “X”, (5) click on the “Optional prompt” check box, (6) click “OK”, (7) switch to the “Edit Report”, (8) test the prompt and save the report.

Modify Report > Edit Query > Prompt Properties > Optional Prompt

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:

SELECTsi_id,si_name,si_webi,si_cuidFROMCI_AppObjectsWHEREsi_name = 'UNIVERSE_NAME' ANDsi_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:

SELECTsi_id,si_name,si_universe,si_cuidFROMCI_InfoObjectsWHEREsi_id IN (111111,222222,33333) ANDsi_kind = 'WebI' ANDsi_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.

What is an Unbound Report and Why Should I Care?

You may have read something on this web site or in one of our guides that mentioned “unbound reports” in a less than favorable light. Hopefully this article explains what we mean by this.

What is an unbound report?

With the migration to the Business Objects XI platform many things changed. One of these changes was the method used to connect (or bind) documents to universes. I wrote an article on this binding and relationship, it is called “Business Objects XI – Changing BO Report / Universe Relationships“. So I won’t revisit that discussion beyond saying that in BOXI reports and universes are primarily and securely bound to each other through a set of unique identifiers of the objects: CUID and Object ID. If a report loses this relationship to its universe we call it and “unbound report”.There may be other names for this phenomenon, but I believe this is the one the experts in SAP Business Objects use, and if they don’t I like it and I think it fits very well.

What causes a report to become unbound from its universe?

The basic event that occurs causing a report to become unbound from its universe is that the unique identifier of the universe is removed from the system. Some of the actions that remove this unique identifier may seem rather innocuous, but they can all be potentially deadly to your reports:

1. Deleting a Universe: OK, this one is not innocent, but it is the most obvious and it actually has a few variants

2. Replacing a Universe Using Designer: Many people use Designer to migrate universes from a development environment to a production environment. If during export you are prompted to “overwrite” a universe and you accept… kiss your report binding good-bye. If you quickly delete the original universe and then upload the new one, same story.

3. Improper Use of Import Wizard: The “Merge” option causes so much trouble because it is not understood properly by many. If you use this or even if you use the much preferred “Update” option and you see that a new universe named with a “(2)” suffix was created, you haven’t ruined your report binding yet, but you next actions might. You must manually bind each report to the new universe with the “(2)” using the Java Report Panel to transfer the binding and make it safe to remove the old version of the universe. Failure to do this will fill your day with regrets.

The truth is that there are many permutations of actions that can lead to this. Many of them start with improperly creating backups of the universe (using Save as) and generating new unique identifiers for the modified universe. Others start with bad migration or promotion techniques.

But It Worked Fine in Business Objects 6.5

You are correct, but that doesn’t change anything. Remember, the game changed with Business Objects XI. The fusion with Crystal Reports created all new rules that Business Objects has barely explained to its customers. In BO65 the report-universe relationship was name based. Thinking this is still the case is the biggest issue facing Business Objects XI administrators and developers. Now it is all about unique identifiers and you can only maintain them by using the properly workflows.

I Followed a “Bad” Workflow but My Report Still Works, Why?

Many times unbound reports will continue to perform just fine, for a while. The first question is how long with that while be. The second question is, are you sure the report is using the right universe??? The CMS record of the report has meta data that lists a universe short name which many times will allow a report to find a universe to use. The problem with this is that it is unreliable and universe short names can change and be duplicated. In such cases a well-behaved unbound report might start to use a universe other than the one you intend or it might just fail. If it uses an unintended universe this could be a HUGE problem as it may not raise any errors to the end-user and the end-user may be making business decisions on wrong, bogus, or out-dated information!!!

The Nearly Unrecoverable Error – Error: WIS 00501

If your report cannot locate a universe, even with using the hidden short name stored in the report’s meta data then this is the error you will see:

Refreshing DataUniverse not found. See your BusinessObjects Administrator. (Error: WIS 00501)(Error: INF )

Once you see this error, you should expect the worst. In some rare and mostly undocumented cases some lucky BO developers have been able to recover from this issue by replacing the original universe. However, please don’t count on this; it is only worth an attempt.

So What are the Proper Development and Object Promotion Workflows?

I have hinted at a few and skimmed over others. To be honest there is a lot to discuss on this topic that exceeds the scope of a simple article. I am working on a new guide that details these workflows and how to properly design or retrofit a Business Objects XI system to best avoid these pitfalls and provide the maximum stability. As the guide materializes I will post some of the content here. I also hope to receive comments that will help shape part of the guide as well.

Can I Connect/Use Oracle 11g Database with Business Objects XI?

Business Objects Enterprise XI Release 2

Any version of BOXI R2 equal to or greater than Business Objects XI R2 Service Pack 5 includes an Oracle 11g driver. However, if you find your instance is still lurking below SP5 you are not without hope. According to Oracle, 10g drivers can successfully be used to connect to Oracle 11g databases. Business Objects does not support using R2 SP4, or lower, to connect to and Oracle 11g database, but that doesn’t mean that you can’t do it. It only means they won’t take any “Customer Messages” on any issues related to such connections, unless the issues can be recreated on database supported for your BO XI service pack level.

Be sure to check your service pack’s “Support Platforms” for the particular supported implementation of Oracle 11g middleware. For example, XI 3.1 SP2 states clearly for Linux/Unix machines running CMS you must install Oracle 10.2 middleware to connect to your Oracle 11.1 database.

Business Objects Enterprise XI 3.X

Starting with Business Objects XI Release 3.1 Business Objects has included an Oracle 11g driver. Now if you are on XI 3.0 and you can’t jump to 3.1 just yet, you might want to borrow a trick from Administrator’s of XIR2 systems lower than XI R2 SP5 and use an Oracle 10g driver to connect to that cutting edge 11g database. Just don’t expect BO to support you on that venture.

Selective Operators: Allowing Users to Decide Which Operator to Use

Everyone once in a while you come across something that makes you say, “that is great, now why didn’t I think of that?”. Recently I came across such a thing: an idea of how to make operators in the WHERE clause of a query definable by the user at the moment of report refresh through creative use of SQL and Designer. Honestly I think everyone could benefit from a greater understanding of SQL. This like piece of code is the proof of that:

Selective Operators in Business Objects XI Web Intelligence or Desktop Intelligence at Run-Time

( 'Lesser than or Equal' = @Prompt('Select Operator:','A',{'Lesser than or Equal','Equal','Greater than or Equal'}, MONO,CONSTRAINED) AND Emp.salary <= @Prompt('Enter Salary:','N',,MONO,FREE) )OR( 'Equal' = @Variable('Select Operator:') AND Emp.salary = @Variable('Enter Salary:') )OR( 'Greater than or Equal' = @Variable('Select Operator:') AND Emp.salary >= @Variable('Enter Salary:') )

The value in this case does not come from fancy Business Object prompt syntax, but rather from creative combinations of SQL and prompts. Note: Please remember this is just an example that you should use to learn the technique and then adapt it to your database’s SQL and to your business requirements.

How it Works

Although this example code has three clauses they can all be bound to each other through their placement in a single universe-level condition, or you could place them in multiple conditions for mixing and matching at the report level. The power of this logic is that it uses one dedicated prompt to collect the desired operator from the user: ‘Lesser than or Equal’,'Equal’, or ‘Greater than or Equal’. Then using hard-coded string values it creates a scenario where only one of the three statements can be true.

And Example with SQL Substitution

In each statement the hard-coded operator text is matched with the actual operator following “Emp.salary”. Therefore, if the user selects “Equal” for the @Prompt(‘Select Operator:’) and “1000″ for the @Prompt(‘Enter Salary:’) then the following substitutions will be made for the actual SQL run by Business Objects:

( 'Lesser than or Equal' = 'Equal' AND Emp.salary <= 1000 )OR( 'Equal' = 'Equal' AND Emp.salary = 1000 )OR( 'Greater than or Equal' = 'Equal' AND Emp.salary >= 1000 )

Because of the magic of SQL and logic only the clause ( 'Equal' = 'Equal' AND Emp.salary = 1000 ) can execute; the others are ignored because the first part of the SQL evaluates to FALSE. In the rules of logic a statement of “FALSE and TRUE” is always FALSE. In this case we don’t know if the second part could evaluate to true, because we don’t know the underlying data, but regardless the entire clause is FALSE when one of the components is FALSE and it is linked to the others using “AND”.

A Few of the Other Details

OK, I feel I got a little technical on the logic, but I think the examples speak clearly enough just in case I lost someone. Again, the whole point that I am making with this code is that it allows users at run time (a.k.a. report refresh) to select the operator for they want to use. If a user wants they can also use the Java Query Panel and change the operators all they want, but they would need access and training to accomplish this.

Some notes: In order to keep it simple I only used the @Prompt statement once and I use @Variable for all of the other instances of the prompt’s use. This helps to avoid problems if I accidentally set the @Prompt differently and it makes it easy to modify Prompt syntax later. The parenthesis around each clause is necessary. Please let us know what you think and share your modifications or spin-offs or alternate uses if you have any.

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).

Extended @Prompt Syntax: Default Value, Purge Values

During my report development efforts the other day I knew that I needed a business objects universe-level prompt, but I also knew that it was mandatory to “purge” the prompt values. Like a good little Business Objects developer I always purge all of the data from my reports and reports’ prompts before I publish them for User Testing and promotion to production; however, in Business Objects XI R2 I have found that WebI reports have a strangle-hold on prompt values and they don’t want to give them up without a fight.

So I started looking around to see if I was just missing something obvious. What I found on my search was some completely unsupported extended @prompt syntax. The funny thing was that I did not have to install anything to enable it. The syntax worked immediately, just by passing in the additional parameters. Note: this is unsupported officially by BO, but BusinessObjects obviously developed it for some larger client and slipped it into the XI R2 code. Therefore, there is no promise that this will be supported in the future, but there is hope.

Anyway, on to the syntax:

@Prompt(‘1) Enter Product Family’,'A’,'Product Hierarchy\Product Family’,multi,free,[Not_Persistent],[<{default_value(s)_or_leave_blank}>],[User:#])

With this syntax you can now add 3 additional/optional parameters after you universe-level prompt’s “free/constrained” option.

1. Prompt Persistence: Default value is “Persistent”; however, the value “Not_Persistent” is the reason most Business Objects WebI report writers want to use this syntax. Using “Not_Persistent” removes the last prompt used with the report; it purges the value. This can be very important in many settings because it can be the difference between an end-user accidentally running the report for some unwanted parameter value.

2. Default Value(s): If left blank no default value used. If a value or values is desired then the set should be surrounded by { } characters and separated by commas (much like hard-coded LOV values earlier int he @Prompt parameters). This feature might seem to counter the “Not_Persistent” setting; however, it may be possible that you always want your Web Intelligence report prompt to include the default “*” value even while you are testing or running it for other values. Therefore, some might find that using this option with the “Not_Persistent” option provides value and extended functionality to their WebI reports.

3. User:#: This particular value has been ignored by many, but it also provides some interesting value. It allows the universe business objects developer to define the relative order of the prompt. Yes, no more not-so-tricky “1.”, “1)”, or “1-” prefixes. In fact, I believe this option provides better value because it defines order of universe-level prompts and it two prompts have the same “User:#” value then they are sorted alphabetically.

The following are some examples of potentially usage of this extended @Prompt Syntax:

@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',multi,free,Not_Persistent,{'Kiwi','Guava'},User:0)@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',MULTI,FREE,Not_Persistent,{'*'},User:9)@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',multi,free,Not_Persistent,,User:1

I have found this syntax to be invaluable in my BusinessObjects Designer and Web Intelligence report development efforts. As good little programmers we know that universe-level prompts are a “best practice”; however, without this syntax they can retain last-run prompt values and cause confusion and even misleading and blatantly wrong business intelligence. This syntax finally brings universe-level prompts into the 21 century and makes our jobs all a little easier and maybe even more secure.

Tip: Including “All” or “*” In Your LOV

Try hard coding “All” or “*” in the object’s LOV, in the universe. You could do this by editing the object’s LOV and adding a query with UNION that will only return the word “All”. You may have to make sure that the LOV for the object is returning a character data type for this to work properly.

The Over-Credited Destination Job Server

Would you get a little upset if someone else kept taking all the credit for the work you do. Would you get down right furious when that person couldn’t fix any issues relating to their ill-earned reputation when a real problem came along and then you had to come along and save the day without even getting an ounce of credit. You would have to be a saint not to get upset about that, wouldn’t you, or perhaps you would just have to be a Business Objects Reporting/Adaptive Job Server?

The truth of that matter is that the Destination Job Server is not really at fault. He was born with a very bad and confusing name. Then over time IT folk got confused with the name, fixed an issue by configuring destinations on every server and then credited the “Destination Job Server” with being the key solution. While one can learn a lot from forum discussions, some BO forums are ripe with incorrect advice to fix job destination errors by configuring the Destination Job Server’s destinations. So this little article is written in hopes of dispelling a myth and giving credit were credit is due.

Destination Job Server, What Is It Good For?

First of all, let it be known that the Business Objects XI Destination Job Server is only responsible for handling the requests submitted through the “Send To” command within InfoView. Yes, that is right. When you are in InfoView and you select a document (check the box next to it) and then proceed to select to send it to an inbox, email, or other destination. In Business Objects XI InfoView it looks like this:

When you use this InfoView “Send To” functionality you are in effect creating a job that only has the purpose of delivering a file to a destination. This, my friends, is what a Business Objects Enterprise XI Destination Job Server does; nothing more and nothing less.

So what about the “destination DLL disabled. CrystalEnterprise.Smtp:”error I get on my jobs server?

If you have this question still please read the above paragraph one more time and then read my previous article titled “Fixing the Business Objects XI “destination DLL disabled. CrystalEnterprise.Smtp:” Error” and now log in to CMC and configure your destination on all job servers in the cluster. Now you know, and knowing is half the battle.

TRUTH: Business Objects Does Not Create an Excel File, Help!

A couple weeks ago we received the best email question so far. Not only did it ask for more details with regards to one of the articles, but it provided insights worthy of making it its very own standalone article. Truthfully, I would like to publish it here to share the knowledge contained inside (this is always the best way to answer questions sent to us), but also to hopefully bring visibility to a very worthy question that you may be able to answer. Please read this one, it is good, and provide any comments or questions you might had in the comments below.

Our Guest Author wrote the following [minimal edits for context clarification from Julian are in brackets]:

Subject: [Excel] Format issues when Schedule a DESKI report in BOXIR2 SP3

We are trying to schedule a DESKI Report 3 times a week in Excel format to our clients. DESKI report has 1 data provider and 1 report tab only. We are on BOXIR2 SP3.

[A client] has an automated process, which reads the files we send and save it somewhere. They have a problem in reading Excel file which we send it through our BO scheduler. I tried to schedule DESKI document through InfoView using both SMTP and FTP [delivery] options.

From the InfoView -> scheduler1. Excel format is generating Excel readable file — but they want exact Excel file.2. Text file format is generating ‘Tab’ delimiter file3. There is no option for CSV from scheduler [link to related article]

I am really kind of stuck in the middle (between clients, business users, and my manager). Clients want either an Exact Excel file or CSV file on a schedule basis. As there is no other way to schedule a report in CSV Format [link to related article], we have to write macros/scripts to do this. Which I am ready to do, [but] our management and team don’t want to do this workaround. [They want to] avoid macros as it will increase maintenance and users have to depend on IT for this.

New Knowledge Alert – Business Objects Cannot Create True Excel Files, only Excel Readable Files

I have opened a ticket with BO support and this is the answer I got from them.

Business Objects uses its own dll files for exporting a report to an excel format. The file created is not a native excel file and is simply excel readable.

Any file converted to excel using a tool other than excel will always create excel readable file which would be different (but not too much) from the native one created in Microsoft Excel.

Business Objects does not require Microsoft to be installed for this conversion to take place as it relies on its internal files for the conversion. BusinessObjects is designed only for creating the reports, though it can export the reports to the other formats.

But since all the exported files contain similar information such that Microsoft Excel can interpret and render the files, your module should be able to access it. The files exported from Business Objects would not differ greatly from an excel file created in Microsoft. I am not sure what these internal differences would be.

[The following response captures the client's requirement/comments]:

I think we are stuck in this issue now because [BO] was intended to create an “Excel-readable” file, not create an Excel file. My Java module [is] trying to read an Excel file, not an Excel readable file. There [is] nothing wrong at both ends.

Unfortunately, the data file just cannot serve the purpose that I am looking for. We would need to find some other options now.I am not sure if [BO] is able to create a “CSV” file? This is a general purpose type of file format and would not have any format issue in reading. Also, you did provide a TEXT file for my testing, but it’s not a fixed length TEXT file. Seems that tab was used as a delimiter in that text file. I wonder if you are able to create a text file without delimiter?

I know that we can save a report in CSV format also in a fixed length text format – through DESKTOP Intelligencebut is there a way to schedule this.

Is a schedule CSV option available in BOXIR3.1???

Please let me know if there is a way to schedule a DESKI report in CSV format.

Responses from BusinessObjectsTips.com’s Julian

The more I think about this I have to say that macros are the only solution that will meet the needs client, unless they can somehow do SDK integration of some sort and pull the data right off the report.

Another solution might be to create an Excel “converter” application. This could be a standalone Excel file with a macro in it that loads BO’s native Excel readable format and outputs a true Excel format file. Perhaps a standalone executable would be better because it would allow for scheduling (perhaps using a BO Program Object). I don’t really know how to create such an application, but this actually would not require any BO skills.

To the author, please share your progress with us all. To the rest of our audience please share your ideas and comments. Thanks!

OpenDocument – An Introduction to a Powerful Tool

If you are not yet using the OpenDocument function in your reports you might want learn a bit more about it and see if it could not meet some of your current business requirements, or better yet allow you to amaze your business partners by giving them something they didn’t yet know that they needed!

What is OpenDocument

OpenDocument is simply the ability to open a “document” (a.k.a. report, usually) directly; without having to locate the report and even without having to submit any parameters to the report (optional). A properly constructed OpenDocument URL can be placed on an page that can serve up a URL/link and then users can click on it and be taken directly to the report that the URL specifies.

Major Capabilities of OpenDocument

The OpenDocument function provides a few excellent capabilities:

Abstraction: users don’t even need to knwo the report name. They could be given a URL on a web page, email, Word Document, etc. which says “Click Me to Access Your Important Report“

Simplicity/Efficiency: users are spared navigating to a report and selecting predictable parameters each time (static or dynamic, your web coding skills are your only limits)

Linking Reports Together: create summary and detail reports that allow for in-depth drilling on specific data or create other interesting and useful relationships between reports

Additional Information about Business Objects’ Open Document Function

BO XI introduced OpenDocument, replacing the “viewrpt.cwr” command in previous versions of the Business Objects Enterprise. OpenDoc accepts many optional parameters allowing various customizations, such as display format, parameter values, and refresh flags.

A Simple Example of an OpenDocument URL

The following is an example of a very simple OpenDocument URL using a .NET web server:

http://myserver/businessobjects/enterprise115/infoview/scripts/opendocument.aspx?sDoc=My%20Favorite%20Report

Cautionary Note #1: Please note that OpenDocument calls use URLs and therefore they are bound to the special character limitations of all URLs. That is to say that spaces and certain other characters are not permitted. Spaces can be replaced by “%20“; however, Business Objects Web Intelligence also has a native function called “URLEncode()” that can resolve any necessary special character formatting.

Cautionary Note #2: In Business Objects XIR2 it is entirely possible to have two documents with the same name and therefore this simple example should be received in this context. The OpenDocument function supports many parameters that will allow you to pin-point your desired document and in our next article on this topic we will show you how to do this gracefully and With maximum portability across any environment.

Time Saver – Set Preferences for “My Initial View Is…”

Often we find ourselves returning to the same folder within Business Objects’ InfoView. The clicks and waits to get there came be rather annoying and time consuming when they occur repeatedly throughout the day and week. I have a suggestion that can save you some annoyance and time and allow you to focus on your BOXI WebI reports.

Set your Preference for “My Initial View Is…”

If you would like InfoView to take you straight to a certain folder upon successful login you should set your Preference for your initial view to that folder. To do so do the following:

log in to InfoView click the “Preferences” button, next to the “Log off” button in the upper right-hand corner,

the icon looks like a document with three check boxes on the “General” tab locate the “My initial view is…” setting select the options to set it to your desired folder, use the “Browse” button to navigate and

find your desired frequented folder

With your next successful login you will be taken straight to this folder.

BO InfoView WebI Session Timeout = Developer’s Worst Enemy

Business Objects has been encouraging report writers/developers to stop using the “full-client” and start using WebI. There are quite a few obstacles to overcome to be able to make the switch to WebI (training, functionality adjustment, server capacity planning, etc.), but perhaps the most difficult adjustment is learning to development under a timed session.

For many reasons Business Objects administrators configure their systems to timeout a web session and an InfoView session after a certain period of inactivity. With finite resources, which we all definitely have, this is always a good idea. However, this limiting concept has two problems. The first issue is that “full-client”, ZABO, or Desktop Intelligence developers are used to working in a virtually limitless development environment: their own desktop PC. The second problem is that system administrators need to find just the right balance between flexible the valid and acceptable dormant session limits and the obvious session abandonment.

Business Objects Administrator Settings

When Business Objects system admins find that magical median, which will be different for every user community, they need to set it at a few different places:

1. The web server “connection timeout” setting should be increased first. This one is usually stored in seconds, not minutes.

2. The InfoView application within the web server should also have its “Session Timeout” value increased.

3. The web.config file also needs an update to the “” section, setting “” (using the number of seconds in place of the “#” character.

BusinessObjects Report Developer Adjustments

Report developers or writers, including ad-hoc report writers, will need to adjust their methods and habits. Initially, business objects report developers will assume that as long as they save before any periods of inactivity they are safe. However, they will face the harsh facts of this assumption if they are working on the query the whole time.

In BO XIR2 editing the query of a Web Intelligence report does not reset the session timeout timer. Therefore it is entirely possible for a developer to be actively developing a report’s query and find that their session is lost, along with all of their work. In fact, in the WebI java editor panel there are many actions that are registered only on the client and they are not communicated to the server until a logging action takes place such as saving a document. Among these actions you may find general formatting, query editing, and even adding fields to a report.

The safest bet is to require all of your report developers to develop a habit of saving their report every 5 minutes. This will certainly reset the timeout timer and it will ensure that no report development work is lost. From my own personal experience I would say that I have probably lost about 10 to 20 hours of work because of lost session.

Enhancement Request for Business Objects XI R2

To be honest, I would like to see BO add some functionality to the report editor and even InfoView that would help avoid this issue. They could implement an auto-save function. Or perhaps a pop-up box prompt which warns the report developer that they are about to lose their session. Either one

would be a wonderful improvement and would probably save BO users thousands of lost hours of work. Until then save every 5 minutes!

Login is taking long

The other day I answered a different article’s comment with a very brief mention of what I’ve been told by SAP-BO was a common tweak. The potential benefit of this simple tweak is large enough to merit its own article. Thank you MarcV for making this apparent. I would like to answer his questions here.

First of all, what tweak are we talking about?

Place entries in the hosts file on all web application servers/hosts/machines/boxes for each of the machines/hosts/servers/boxes running CMS. This tweak is a workaround to resolve Java’s poor, inefficient implementation of DNS look-up.

The computer’s hosts file lists hosts (meaning computers identifiable on the network) that are to be resolved locally and not through the computer’s DNS (domain name server). New hosts are added by listing the IP address of the new host first, followed by at least one space, then the new host’s fully qualified server name, then optionally one could add at least one space and the new host’s short name. Here are some links to a good tutorial of hosts files and a good discussion of hosts files.

Here is what MarcV said:Julian,

Can you expand on the last comment? I would love to know more about it, as common tweaks should be… common? I have never seen that tweak mentioned anywhere.

Currently, we host everything on one server (yeah, I know). Should we enter the server’s name and IP in the hosts file as if it were on a separate machine? Using Tomcat for the app server, that would affect pretty much everything that gets served up (logins, screen renders, WebI, Web Services…)

I’m all for speeding things up. What aspects would this affect? WebI/C Reports/LOV/???

Do you know of any consolidated source of “common tweaks”? I usually find one there here, one thing there… which means, you only know about the things that you might be researching if you’re noticing problems. Not knowing about Java having a poor implementation for DNS look-up, we might never have touched on that for our BOXI issues.

I agree, it seems that the use of the word common here was unwarranted. I did not use this tweak until it was mentioned to me by a BO support engineer after working through some deployment issues. He and others after him told me this was a common tweak for Java Application Servers and I took them at their word.

To the best of my knowledge the biggest performance improvement that one can expect from this tweak is to speed up initial login; however, it seems to me that some over operations run a little faster after the tweak is implemented.

In the case you have a one-stop-shop and you are running everything on one server, I believe that you may still see a performance improvement with this tweak. However, you may find that there is already an entry for your server in your server’s hosts file.

Repository of Common Tweaks

I wish there were a single location listing tweaks that one implement on their Business Objects XI system. Perhaps there is none, because those with the knowledge either don’t make the time to document and publish them or they don’t wish to give away for free very valuable information. Nevertheless, we can try to build one here if there is interest. I could start a “tweaks” page and through comments we could communicate our known tweaks which I would then incorporate in the main article.

Oracle-BEA WebLogic is not a technology I want to dive deeply into with this web site; however, it is often the Java Application Server of choice for many Business Objects XI environments because of its scalability and it tends to be preferred by other business technologists within companies. I personally work/struggle with it a lot and so I thought it a good idea to share a few issues and their resolutions in this article. I also would welcome anyone else to share their war stories as well. Be warned, I am writing this article to those who are already familiar with configuring/deploying BOXI on WebLogic.

Node Manager Service Won’t Start

This issue may be specific to Windows operating systems or it might apply to others as well. The symptoms are that you can run Node Manager from the command prompt but you cannot run it as a service.CAUSE: If you have encountered this issue, look immediately at your “Path” environment variable. Is the final character a backslash ( \ )? If so this could be causing the script run by the service to fail due to some faulty programming. Most likely someone or some automated script/patch altered the “Path” variable and the problem did not occur until WebLogic was restarted. This issue actaully has nothing to do with Business Objects.FIX: Just remove the backslash ( \ ) as the final character. If you really want you could also append a final semi-colon ( ; ) at the end.

Installing/Deploying Applications Fails

This may be a Unix/Linux only issue. When deploying Business Objects XI applications such as InfoViewApp, CmcApp, PlaformServices, or AnalyticalReporting you notice that you can never get them all successfully installed or started. Individually they may work just fine, but with all of them installed you suddenly start seeing infinite down apps and “Start running” status messages when you start them up. If WebLogic starts to feel like the twighlight zone you should start to question if it is hitting limits imposed by the OS.CAUSE: There is an OS level setting that limits how many open files a single session/instance can have. In Linux this is stored in the “ulimit” under the setting “open files”. If this setting is too small (less than about 2048) you can expect issues. This is due to the fact that Business Objects applications/deployments have a very large number of files and WebLogic “opens” them all during deployment and application serving.FIX: Increase the ulimit for “open files”, a.k.a. “nofiles” to 4000 or 4096 if you like. This should not impose any security threat to your system; however, after running the command “ulimit -a” you may discover that the limit imposed by your OS is too low to allow this increase. In such case, get it increased (see “etc/security/limits” or SAS has a good web page on this, too bad BO has no official comment on the topic). Keep in mind that you will need to also tell WebLogic to use more of these open files. The best way is to do so by appending a line in the “commEnv” file located in the “/weblogic/common/bin” folder.

The Admin Server Won’t Start

This is one of the most perplexing issues because there are not errors to point you in any direction. In short, the Admin Server cannot be started successfully via a service or via the command prompt. If watched closely you see that it progresses fine for a couple seconds and then it freezes. If you look closely at running it through the command prompt you will see that the last entries (“”) are related to security; this is your only helpful clue.

CAUSE: The “ldap” directory has become locked or corrupted by some errant process.FIX: Stop all WL processes. Rename the “ldap” directory or compress it and delete it. Start the Admin Server. Viola! You just fixed it. WL will recreate the “ldap” directory. If you don’t even use WebLogic’s LDAP functionality then just move on, if you do use it, you may have some work still left to do. Alternatively you could look for the “lock” file (*.lck) and delete it.

Please feel free to share your own BO-WebLogic issues and their solutions. I hope this article becomes a place to share some successful resolutions and not a place to ask for help with WebLogic. There are better qualified web sites for that.

If you have deployed your Business Objects XI Central Management Server(s) and your Java Web Application Servers are on the same single machine then this article does not apply to you. If your Java Web Application Server ever needs to run applications (such as InfoView or CMC) that will connect to any CMS that is not running on the Java Web Application Server’s machine then you and your system will benefit from reading this article.

The Java Implementation of DNS Look-up

I’ve been told by a few very knowledgeable folks that either Java as a technology or Business Objects Enterprise’s use of Java results in a situation where if the CMS is not hosted on the same machine as the the Java Application Server then the look-up of the IP address of the CMS machine(s) is very inefficient and is the cause of much lag in the case of such actions as logging in. For example, an InfoView log in action may take up to 10 seconds extra due to iterative inefficient resolution of the CMS machine’s IP address from the host name.

Building a Short-Cut

One way to put an end to this inefficiency is to place hard coded entries in the hosts file of the machine hosting the Java Application Server. If your server is configured to first check the hosts file before hitting the network’s DNS server then you will see a huge boost in the performance of log in actions. This is simply due to the fact that the poor implementation of DNS look-up that BO’s Java call is doing is bypassed by the hosts file entry for that host name. I have seen log in actions for InfoView and CMC reduced from 8 seconds to less than 1 second by this change alone.

Making the Change

This is one of those changes that won’t cost you much to test, but there is a price. The truth is that there is a very big issue with hard-coding a server name and IP address in your hosts file. On the very rare occasion that your server is assigned a different IP address your application will be broken until you update the hosts file accordingly.

To make the change you need only locate the machine’s hosts file. On Windows this is usually in “\WINDOWS\system32\drivers\etc” and on Linux it will be in the “/etc/” directory. Once you locate the file I suggest creating a backup of it first. New lines are added to the file merely by adding first the IP address, then at least one space, then the fully-qualified domain name, and optionally you can add at least one more space and put in the simple short name of the machine. For example:

12.232.131.121 myserver.mydomain.com myserver

I am not an expert on editing hosts files, but I can tell you that it is worth trying out on your system in you are running at least one CMS on a machine different from the one that is running your Java Web Application Server. Give it a try and report your results by leaving a comment please. To the best of my knowledge you don't even need to restart your deployment for the change to take effect. Just make the change and test the results. Good luck.

The Over-Credited Destination Job Server

Would you get a little upset if someone else kept taking all the credit for the work you do. Would you get down right furious when that person couldn’t fix any issues relating to their ill-earned reputation when a real problem came along and then you had to come along and save the day without even getting an ounce of credit. You would have to be a saint not to get upset about that, wouldn’t you, or perhaps you would just have to be a Business Objects Reporting/Adaptive Job Server?

The truth of that matter is that the Destination Job Server is not really at fault. He was born with a very bad and confusing name. Then over time IT folk got confused with the name, fixed an issue by configuring destinations on every server and then credited the “Destination Job Server” with being the key solution. While one can learn a lot from forum discussions, some BO forums are ripe with incorrect advice to fix job destination errors by configuring the Destination Job Server’s destinations. So this little article is written in hopes of dispelling a myth and giving credit were credit is due.

Destination Job Server, What Is It Good For?

First of all, let it be known that the Business Objects XI Destination Job Server is only responsible for handling the requests submitted through the “Send To” command within InfoView. Yes, that is right. When you are in InfoView and you select a document (check the box next to it) and then proceed to select to send it to an inbox, email, or other destination. In Business Objects XI InfoView it looks like this:

When you use this InfoView “Send To” functionality you are in effect creating a job that only has the purpose of delivering a file to a destination. This, my friends, is what a Business Objects Enterprise XI Destination Job Server does; nothing more and nothing less.

So what about the “destination DLL disabled. CrystalEnterprise.Smtp:”error I get on my jobs server?

If you have this question still please read the above paragraph one more time and then read my previous article titled “Fixing the Business Objects XI “destination DLL disabled. CrystalEnterprise.Smtp:” Error” and now log in to CMC and configure your destination on all job servers in the cluster. Now you know, and knowing is half the battle.

Cleanly Stopping and Starting Business Objects Servers

I really cannot explain why it is that I have NEVER seen any documentation that advises Business Objects XI administrators on how to stop and start Business Objects properly. For those of us running part of our business on BO XI we are very concerned about minimizing errors for users and scheduled jobs while we are restarting Business Objects.

The Cleanest Method to Stop a Business Objects Environment

I have discussed this topic with my senior engineers and the following is based on the input I received from them and from my own experience and knowledge. I will label optional steps that will make your stop and start as graceful as possible; these are optional, but they are the best method to follow if you have the time to do so. Also when following the steps make certain that the step is complete and the server is completely stopped/started before proceeding to the next step. Additionally if you have a clustered environment, and you should if at all possible, then you can stop all servers of the same kind in any order or even simultaneously.:

1. Please first make note of any pre-existing disabled servers to be sure that you do not enable them mistakenly later on. Screenshots are useful and fast, just be sure to save them.

2. Shutdown your web/application layer. This will stop your users from launching new jobs and from getting strange errors as you are in the midst of your shutdown. You could just disable a proxy server (if you use one to cut off the access, but (Graceful Option) you may want to completely flush the system by completely stopping the web server.

3. (Graceful Option) Through the Central Management Console (CMC) or through the Central Configuration Manager (CCM) Disable all BO servers except for the CMS, Input FRS, Output FRS, and Destination servers.

4. (Graceful Option) Wait as long as reasonable/acceptable or until all user sessions/requests have cleared the disabled servers before proceeding to the next step. Usually 30 to 60 minutes is sufficient for any valid threads being processed.

5. Shutdown the Event Servers (use CCM or CMC). This would stop any related scheduled jobs from launching.

6. Shutdown all Job Servers (WebI, DeskI, Program…).7. Shutdown the Destination Servers.8. Shutdown all Report Servers (WebI, DeskI, Crystal…).9. Shutdown any other non-CMS Servers that are still up and running.10. Shutdown CMS. CMS should always be last. This is essential and it will make your CMS

shutdown go much faster and more smoothly. It may also help reveal any problems that your CMS may be having (for example, if it won’t shutdown you’ll know it is not because of any other lingering servers in the cluster).

11. With the CMS completely shutdown you are official and completely down.

The Cleanest Method to Start a Business Objects Environment

It is assumed that EVERYTHING is down prior to beginning these steps. If your environment is only partially down we strongly recommend that you first shut it down completely before attempting a start/restart. You want to have a clean environment so do yourself and your users this favor. When starting groups of the same kind of server you can start them one by one or simultaneously, jsut be sure that all are started before proceeding to the next step. (Graceful Option) If you disabled any servers prior to the shutdown (as part of a graceful shutdown) then you should enable them immediately after they have been started:

1. Start the CMS servers. This will take a little while depending mostly on the number of objects in your environment.

2. Start Destination Servers, Input FRS, and Output FRS.3. Start Event Servers. Also as a side note, please be aware that Event Servers must always be

restarted following the restart of any CMS in the cluster.4. Start all Report Servers.5. Start all Job Servers.6. Start any other servers that have not yet been started.7. Start your web/application layer.

The Value of the (Graceful Stop/Start Method)

Disabling Business Objects XI servers allows the servers to retain and complete their current threads/work, but it stops it from accepting any new work. Nevertheless, this requires caution. If users retain access to the environment (web and application layer are up) while you are disabling servers and you disable ALL servers, or most of them to a point below capacity demands, then you will cause errors for users! Therefore, if the users do retain access to the environment disabling servers would only be done partially to the collection of similar servers (with reason). Also do not forget to enable the servers after the servers are restarted!!!

CMS Cluster Summary and the CMSClusterMembers Registry Key

A “CMS Cluster” is a group of Business Objects XI Central Management Servers (abbreviated CMS, which is a BO service\daemon) that are interrelated and connected so that they may work in conjunction to manage the various other BO servers and provide most of the basic functionality of Business Objects. Clustering them allows for greater capacity management (each Central Management Server can generally support about 500 to 600 concurrent users, based on usage) and fail-over capability. In other words, having a CMS cluster consisting of at least 2 CMS is a VERY intelligent design decision for any Business Objects XI environment requiring any fault tolerance.

Creating a CMS cluster is not too complicated, but for now outside the scope of this article. You should understand that pretty much every other BO service will benefit by being aware of this CMS Cluster. The cluster name is defined in the specifications of each server’s CCM configuration (BusinessObjects XI R2) or that of its Server Intelligence Agent (Business Objects XI 3.X). The way that the BO server is aware of the members of the CMS cluster is through server operating system registry key. The name and path of this key on the Windows OS is:

HKEY_LOCAL_MACHINE\SOFTWARE\\Business Objects\Suite 11.5\Enterprise\CMSClusterMembers

One thing important to note about this key is that it is self-maintained. If a CMS is joined or removed from the CMS Cluster than the other BO servers running on a node will communicate with the CMS Cluster and update the registry key to reflect the valid members of the CMS Cluster. Now this is not exactly real-time updating, but rather it occurs at start-up of the BO server (I mean a service, such as Web Intelligence Report Server) or even at enabling of a disabled server.

Reflecting on what I’ve just written I a reminded that it is a good thing to create a CMS Cluster that only contains one CMS. There are no negative impacts that I know of and this allows for scalability as you can easily add in a CMS to the cluster with this already configured.

Sizing Limits to Web Intelligence Report Server Maximum Simultaneous Connections

In Business Objects XI R2 newly created Web Intelligence Report Servers default to 50 “Maximum Simultaneous Connections” (editable in the Central Management Console, or CMC). In Business Objects XI 3.X the “Maximum Simultaneous Connections” setting is defaulted to 100! Can we read anything in to these changes in default settings? Do the default settings mean anything at all with regards to the settings you should have on your system?

Sizing Limits to Maximum Simultaneous Connections

General consensus of our sources tell us that XI 3.X is better coded and can handle more connections than XIR2, with regards to WebI Report Servers. Nevertheless, it would be quite a leap to say that all other things as equal as possible that XI 3.X WebI Report Servers can handle twice as much traffic. Seriously, don’t count on it.

I feel I should add personal experience and best practice here. I see the default setting for “Maximum Simultaneous Connections” on the WebI Report Server as a maximum setting. I have personally never exceeded it nor witness it exceeded by anyone. It is generally held that if you need more Simultaneous Connections then you ought to add another WebI Report Server to your environment. Of course, keep in mind that SAP Business Objects’ general guideline is that you have no more than 1 Web Intelligence Report Server per available CPU core (for example, a server with 4 quad-core CPUs has 16 CPU cores); so there is a limit there too.

Real-World Web Intelligence Report Server Sizing

A production system will generally run better having more Web Intelligence Report Servers with lower Maximum Simultaneous Connections. For example, a server with 4 dual-core CPUs would run better having 8 WebI Rpt Servers each set at 30 Maximum Simultaneous Connections than it would having 4 WebI Rpt Servers each set at 60 Maximum Simultaneous Connections in CMC. In the real-world tuning and balancing usually are based on observed performance within configuration guidelines.

To get your sizing in the right place you should know what you maximum concurrent users are (logged-in users plus concurrent schedule jobs). This number is essentially how many Maximum Simultaneous Connections you will need. So let’s say you never have more than 100 concurrent users, but you can at those peak times also have 25 scheduled jobs. Keep in mind that most users will only do one process at a time, but some like me will be refreshing one report while editing another simultaneously. A scheduled job will always only be one connection. So you can safely say that you require only 150 simultaneous connections (with some wiggle room).

Now your server had only 4 CPU cores in it. So this one is easy 150 divided by 4 will give you 37.5. Round that up, because I suggest you have at least 150 and that you keep the same number on each WebI Report Server. So you can set each one’s “Maximum Simultaneous Connections” to 38 using the CMC. In XIR2 this might be pushing the limits, but in Business Objects XI 3.X this should be a comfortable setting.

How Do I Run a Report Against a Specific Job or Report Server? Easy!

There will come a time when for some unforeseeable reason you will want to run a particular report against a particular report server or job server, or both. When that time comes I hope you know how to make it happen or that you can easily find this article.

Why Would You Want to Run a Report Against a Specific Job or Report Server?

Usually for debugging purposes this topic comes up. For example you have logging configured on a certain WebI Report Server and now you want to force reports to run on it. You could also want to do this because that Report Server has a particular configuration you want to test. Perhaps you want to direct certain reports to certain report and job servers in order to do your own load balancing in scheduling some intense reports. One of these reasons might be part of the reason, but I would love to hear your comments on other good reasons.

How do you force a report to run of a Business Objects report server or job server?

This is rather easy once you know where to configure it and you have some Server Groups set up.

1. Log in to the Central Management Console (CMC): this is where many things start and in this case InfoView will not help you to set any part of this up, you need CMC access.

2. Create a Server Group: Go to the Server Groups section of CMC and create a new SG. After you create the SG you need to assign your targeted servers to it by clicking the “Servers” tab and clicking the “Add/Remove Servers…” button

Assign BO Servers to the New Server Groups

3. Locate the Desired Report:: The easiest way is to use the “Folders” section to navigate to the report in CMC, or use the “Objects” section to search for it. Once located click on the report.

4. Click on the “Process” tab: yeah, do that and then you will see where the power of this often ignored tab comes from. This is where the magic happens. Remember we are still using the Central Management Console.

5. Make Your Selections: You will want to almost always use the “Only use servers belonging to the selected group” option, for obvious reasons. In the corresponding drop-down box, locate the server group that you want to use. Only one server group is permitted here, but a server group can have pretty much any number of servers in it. The top setting is for scheduling only; I suggest you set this even if you don’t plan on scheduling, but your requirements may dictate otherwise. The bottom setting is for both interactive refresh and report editing (such as the JAva Report Panel for WebI Reports).

Set your server groups here

Now to Test it Out!

Ok, now you are cooking with gas! Give it a try. If you get errors, go back and check the Server Group or the assignments to the server group. If the SG is empty you WILL get errors. Sometimes errors tell you that the something is wrong with the server, such as it is not started. If a schedule stays in

“Pending” status for very long this may mean that you have not added both a Job Server and a Report Server to the server group. It all depends. Experiment, observe, and learn.

The Best Way to Stop a Business Objects Server

Minimal Impact of Administrative Actions on BOXI

I would like to think that I am unique in many ways, but I think that I am very similar to others when it comes to my desire to minimize the impact of my administrative actions on my Business Objects XI instances. One way that I accomplish this is to carefully shutdown Business Objects servers in a very particular and simple manner.

Changes to Web Intelligence Report Server Require Restart

From time to time we all make changes to our Business Objects environment. Frequently I find myself making changes to the the Web Intelligence Report Servers (WIRS). Whether it is tweaking command-line parameters, CMC properties, logging status, making some obscure registry setting, or even patching DLLs; I am changing my WIRS and I need to restart it in order for the change to take effect. In some cases, my client runs 24/7 and has no real downtime and in other cases I need to make the change immediately and I need them to take effect yesterday.

The Cleanest Restart You Can Ever Perform

In most cases the changes I make will not take effect without a restart. However, if I go directly to the Central Configuration Manager (CCM) and just stop the server(s) and then restart it, any reports (edits, paging, refreshes, etc.) will fail on the user’s side (or for the scheduled job). So what can I do to guarantee this does not happen??? Disable the server. Wait. Stop server. Restart Server. Enable the server.

Detailed Cleanest BO Server Stopping Procedure

1. Disable the server: do this either in the Central Management Console (CMC) or in CCM, which ever you prefer, but I think that internally BO engineers prefer to use the CCM (its the icon with the cylinder and check mark).

2. Wait: there is no golden rule on how long to wait, any amount of waiting is still better than none. During this period the server is finishing whatever work it was assigned and the CMS is not assigning it any new requests. You can observe this on the metrics tab of the CMC page for the server (if your server has metrics). I generally wait 30 minutes and I watch the CPU utilization (CPU time) of the WIRS’s process (yes, in Windows) to see that it is dormant.

3. Stop the server: this one is easy. Just go to the CCM and stop the server you are targeting. You can do this from any server in the cluster. You should watch the executable to be certain that it goes away, disappears, or dies.

4. Restart the server: Again stay in CCM, allow for at least 15 seconds from the stop, but this will depend a lot on how long you waited since the disabling of the server, I mean if it still has any active threads it will take longer to stop (see previous step for proper shutdown before restart). Allow the server to get fully started and registered with the CMS.

5. Enable the server: again use CCM or CMC to do this. I suggest you make sure the server is started for a period of 30 seconds before you enable it and let the CMS have it back for abuse.

Note: I believe this same technique should work well for all Business Objects server, not just Web Intelligence Report Servers.

Business Objects XIR2 SP4 Bug: Schedules Pending, Job Server Scheduling Dead

Breaking News

I have some breaking news, a scoop perhaps, on a new bug that may only impact some (not all) innocent Business Objects XIR2 SP4 shops. Apparently, my contacts tell me that within the last week, Business Objects has internally identified and even resolved a bug that can wreak havoc on the unknowing BO XIR2 users out there who make the leap to Service Pack 4 (SP4).

Symptoms of the Bug: All Schedules Pending

It seems that the bug causes a loss of communication between the BO XIR2 CMS server(s) and the job server(s). The strange part is that the only detectable error or problem is that suddenly no scheduled jobs process; they all go to an eternal “Pending” status. Every Job Server’s scheduling function ceases to function. Even stranger is that fact that this bug does not effect all Business Objects XIR2 SP4 installations. Some experience the issue and others do not. Some assume that the SP4 install may have changed their configuration or their security and caused the issue; however, it seems that all settings stayed unchanged (good!), but the job servers just ceased to do their job (very bad!).

No Fix Until Fix Pack 4.5

BO just released Fix Pack 4.3 for SP4 and the fix to this issue was not included. My resources tell me that it is planned for inclusion in BO XIR2 Fix Pack 4.5. I am not certain what the due date is for FP4.5, but I would imagine that is not going to be available until after Columbus Day and probably not until after Halloween.

Rethink Your Planned BO XIR2 SP4 Upgrade

If you are thinking about installing SP4 on your Business Objects XIR2 system I would encourage you to rethink this plan. Could your users accept the new functionality of completely disabled scheduling? I doubt it. Of course, there may be fixes to other functionality in the SP4 upgrade for which you cannot wait. If this is true then you might want to test out the fixed DLL on a test environment. Yes, BusinessObjects has already corrected the issue and released a Limited Availability patch. I have managed to get my hands on the patch info and the patch that BO’s product development group has developed.

Download the BO XIR2 SP4 XIR2.LAFix4.3.3 patch directly from Business Objects

Get the Fix for the BO XIR2 SP4 Bug

On September 24th, BO released a Limited Availability patch. In truth the fix is still considered a “beta” fix and so it comes with the standard disclaimer. Basically the LA Fix hasn’t been through full regression testing and it may inadvertently introduce other issues. BO Customer Assurance still needs to confirm the issue is fixed by this patch.” That is a pretty big disclaimer, but it is the standard one on such “hot-off-the-press” fixes. If you need this fix then test it yourself.

Regardless, I recommend that you immediately contact your BusinessObjects Account Rep and/or BO Support. The more attention this issue gets the more likely it will be officially fixed sooner.

Download the BO XIR2 SP4 XIR2.LAFix4.3.3 patch directly from Business Objects

My disclaimer: I provide no guarantees or warranties with this patch, just take it as it is. I tested it and it seems to be fine. Others have claimed that it corrected their issue. That is all I can tell you. Good luck. Oh and one last thing… PLEASE backup your SchedulerSubsystem.dll before you install the patch!

Web Intelligence

What Are the Differences Between Breaks and Sections?

Meridianblues asked us all what the difference was between breaks and sections. I started to answer the question where it was asked, but then I thought the answer might be of value if exposed more broadly in its own article:

What are Web Intelligence Sections?

In Business Objects XI Web Intelligence sections are easily created and often even by mistake (drag a single object to the report and you will see what I mean). They can be made more complex and wider (taller) with greater scope. Sections apply to the entire horizontal area of page over which they are defined (best viewed/managed in Structure View). In other words, you can have multiple tables, graphs, cells in a section if you want. The section is also defined usually by a dimension so that each unique value of the dimension becomes an instance of the section in which all content placed in the section is repeated. All of the content in that instance is filtered to that unique dimension value.

So what are Breaks then?

A break is something that I rarely seem to get these days. No seriously, a Business Objects WebI report break has similar properties to those of a section. It is a sort of pivot around each distinct value of a dimension or formula. However, a break occurs only in a single table/block. Breaks cause the table to be broken up in instances of the break, sub-tables usually. These are easily created, usually by highlighting the desired dimension’s column and then right-clicking or section the top-menu icon.

A Distinction between the Two

Sections can be applied and formatted to function more or less like breaks; however, breaks can never function as sections do, they are limited to the scope of the block on which they are defined. I tend to prefer sections as they allow for greater flexibility and they seem to have more options. Nevertheless, breaks can be excellent for grouping relatively small amounts of data and then aggregating the measures at the bottom of the table.

Personally I would love to hear you all opinions or observations on this topic. Please share openly in the comments.

Web Intelligence Hangs, Allocate More Memory to Java

Myth: Business Objects XI Web Intelligence Requires No Software Install

Business Objects IX’s Web Intelligence is a light client. You may think that it does not require you to install anything on your PC, but then you would be wrong. Firstly, obviously WebI requires that your PC have a browser. Secondly, Web Intelligence Java Report Panel requires that your client PC has a Java Runtime Environment (JRE) installation (1.5 and 1.6 are good to go).

Third, your client will need to download the WebI Java Report Panel application (in the form of temporary Internet files stored usually in your JRE installation folder). If you ever dump these (for example, as discussed in the article “Clearing the Browser and Java Cache: Do This First!“) then you will notice that your subsequent visit to the Java Report Panel (create new or modify a document) will result in a longer load time while your web client downloads the BO WebI java application.

Java Runtime Environment Installs with a Wimpy Configuration

By default I believe that the default Sun JRE’s will almost always install themselves with a very conservative (small) heap/memory consumption size. These settings can be managed on your client PC by going to “Control Panel” and double-clicking on “Java”. Once there click on the “Java” tab and then click on the “View…” button:

Java Control Panel Parameters

In here, you may have multiple JRE version installed as I do. I think you can figure out which one is being used by your WebI Java Report Panel by clicking on the toolbar icon when you are running the WebI JRP. Otherwise, you will have to disable some and see what happens, process of elimination. When you find the correct one you will see the memory configurations in the “Runtime Parameters” column.

Web Intelligence Power User? Put Your Memory Where Your Mouth Is

Once you know which one to tweak and you focus on the “Runtime Parameters” you will see that there is an “Xms” and an “Xmx” parameter here. “Xms” is the minimum starting size of the heap or memory allocated to the process. “Xmx” is the maximum or limit of the heap/memory allocation. A good starting point here is: -Xms128m -Xmx256m, but you can go higher if you feel you and your PC are up for it. This link points to a great article on the topic of mistakes to avoid when configuring these parameters. Most importantly, always include “m” or “g” at the end of the number, never exceed your machines physical memory, and I read some where else that the effective limit here is 1 gigabyte on Windows XP/Vista (-Xms1g -Xmx1g). Sun has a good guide on this topic too(link).

Making the Change

I would first back-up your current settings, email them to yourself or something like that. Make the change and follow the syntax. Change the setting(s), then click “OK”, then click “Apply” then click “View” to confirm your changes were saved. If you do not click “Apply” and you click “View” right after clicking OK your changes will be wiped out. Of course, you should probably not be using your JRE while making these changes, or at least close everything down and restart your WebI Java Report Panel after the changes are made. I don’t think rebooting your PC is necessary. Be sure to test your changes by launching Web Intelligence Java Report Panel and keep your eye on your system’s memory to see the impact there while testing your wonderful WebI skills to test the potential improvements you just made.

Why Would I Do This?

I have seen this change both improve performance and stability of the Business Objects XI WebI client machine. Truthfully, many support engineers recommend this change anytime a user reports that WebI is hanging or sluggish. I recommend increasing these settings. Now, I wonder if by increasing them I can reliably run to Java Report Panel sessions at the same time. Hmm… I wonder.

Understanding and Suppressing the Report Message “No data to retrieve”

Receiving the dreaded “No data to retrieve in …” or “There is no data corresponding to this query…” pop-up boxes can be confusing and disorienting for many end users. Heck, it even bewilders me sometimes when it pops up unexpectedly. The truth is that there are many occasions when this “error” is quite valid and even desirable. Like almost everything, it depends.

This is the error as seen from the InfoView Web Intelligence HTML Viewer

Causes for the “No data to retrieve in ” message

Firstly, let’s explain what causes this. Simply put one or more of the queries in your report is not returning any records. So what causes that to happen? Many things:

The data is missing in the database. This one can be either expected or very scary for the the business intelligence administrator.

The filters on the query are too restrictive or negating each other. Here the filters will also be a product of the values that you submit to any report-level prompts. Of course, if you for example select the value “Sacramento” for a city, the value “Chihuahua” for a state, and “India” for a country; then unless each prompt condition is joined to the other prompts with “Or” logic you should expect the “No data to retrieve” message.

Row-level restrictions and other kinds of “universe overloads” can ultimately cause the final SQL submitted to the database to evaluate false for all records in the tables. This can be more subtle and can be revealed by studying the SQL.

Universe connections can be changed and redirected to different database with similar structures but less or different data. No integrity or structure errors could be raised. This might also happen when moving a universe between environments and expecting the universe connections in all environments to be the same.

Unbound reports can suffer from this. They can attach to different universes without anyone noticing on the front-end. This is especially true when more than one universe share the exact same objects. I have seen this with universes based on calendar years, for example.

This is the error as seen from the WebI Java Report Panel

How do I suppress the “No data to retrieve” message?

There are times when you may expect that certain users will not receive data for any or all queries. In many cases, you have designed the report to handle this case, but you may not want the user to be bothered by the “No data to retrieve” message. This is a common requirement. There is a hard, non-standard way of suppressing the message using a VB macro or some other SDK integration, but I don’t recommend this route; too much overhead and too hard to maintain.

The easier way is to handle this at the query level. If you want to suppress the error, make the cause of the error go away. What? Yes! Create a union in your query such that always one of the queries returns a value. In some cases you can use queries you already have and just splice them together, but in most cases, the simplest way to create this is to add a second SQL UNION query with the same number of objects and object types in the “Result Objects” and make it always return one value. Then you may need to filter this value out of your reports by adding a simple report level filter in order to keep everything clean. You may also use this value in a variable or alerter to present the report user with your own custom message regarding the absence of data returned by the report queries.

Additional Notes

This error is seen in all forms of Web Intelligence (viewers and Java Report Panels) and Desktop Intelligence. The suppression method of creating a “UNION” in the “problem” query will work for all of the different flavors of reports. That is what also makes this a superior work around. When possible, I ALWAYS recommend avoiding the use of macros and other hacks that are not part of a standard BO solution. Of course, if you do use this 100% BO union hack then you may want to keep track of it in a document or in the report comments for future generations of report jockeys who may have to support it, migrate it, or reverse engineer it.