BO XI Distributed Environment: The Standalone Java Application Server

When you are building a small sandbox it is great to put everything all in one place and create a Business Objects solution that is a one-stop shop (a.k.a. putting all of your eggs in one basket). This works for lots of small-to-medium sized BO systems, but sometimes you want to do a little more. Sometimes you want some fault-tolerance, high-availability, and/or fault-tolerance. In any of these cases you might decide to build something really cool: a distributed Business Objects Enterprise XI 3.X Environment.

In this article, I would like to talk about putting your Java Web Application Server on a separate dedicated machine. You could chose Apache Tomcat, or your could get a little more crazy and go with Oracle’s WebLogic. Either way, the principles are the same.

Does BO Support Mixed Mode?

Perhaps your BO CMS, job, and reporting servers are all running on Windows machines. Perhaps you are thinking that you would like to run your Java applications on a Linux server. No problem. Business Objects has documented that it is acceptable to mix different Operating Systems in your BO environment as long as all servers running a particular component are of the same Operating System. What does this mean? It means that you would be running in a unsupported mode if you ran one CMS in your cluster on Windows and another CMS in the cluster on Linux. But if you put all of your web applications on Linux you should have no problems getting support and you should not expect any unusual issues. In fact, in my opinion, if your web application servers are not clustered, you could get away with running Apache on one Windows server and WebLogic on a Linux server, since they do not communicate with each other.

How Do I Create a Dedicated BO Web Application Server?

The hardest part is setting up your Java Web Application Server. I won’t help you here, but there is plenty of help out there in the great Internet to guide you in configuring Tomcat or even setting up WebLogic. Once this is setup all that you have to do is copy the WAR files over to the server (or you can even upload them from your client machine using the WebLogic Administration Console). Yes, this is all you have to do for Business Objects XI 3.0 and later.

Don’t I Need to Install BO on the Server?

No, do not believe the documentation. You do not need to install BO on the server as long as you can generate the WAR files from some other server. It is true! The documentation would have you believe that regardless you need to copy over at least the “deployment” folder and a “java” folder, but this is not true. These are ONLY needed if you are needing to run “wdeploy” on the Java Web Application Server. In my case, I can always use a CMS machine to generate my WebLogic and Tomcat WAR files so why would I want to put BO software on another server? If I put some of the BO binaries on my Java Web Application Server then I have to worry about maintaining/patching the software on that server as well. I don’t know about you, but I don’t need more work and more things to forget to do.

Where Do I Get the WAR Files From?

As I said, go look at the CMS machine. Follow the documentation* to help you find the “wdeploy” application (it is a command-line driven application, probably found here /deployment/wdeploy.*). Since you are probably running a CMS cluster and you would like your web applications to be aware of this cluster I would recommend using wdeploy with the “predeployall” option. So basically, all you need to do is to run the command “> wdeploy weblogic10 predeployall“. You do not need to configure any “config.*” files for your Java Application Server as these are ignored when running with the option “predeployall”. After you run the command successfully, take about 4 to 7 minutes, then you will be told where your WAR files will be located, for example: /deployment/workdir/weblogic10/application/. You will then need to make your changes to these WAR files (cluster, authentication, single sign-on, enable path queries, etc.) and then get them somewhere that your Java Application Server can reach them. Then you just need to deploy them properly and you are done. Congratulations!

What About XI R2, Do I Need to Put BO on the Web App Server?

All java deployments/application except for the “webcompadapter” are standalone applications that require nothing more than a supported JVM to be installed on the Java Web Application Server. The only application that uses the “webcompadapter” is CMC, so if you are intending to provide CMC access through some other method (such as a small unadvertised Tomcat instance installed on a CMS server) then you do not need to install any Business Objects binaries or libraries on the Java Web Application Server. The issue here is that the XIR2 still has some JNI calls in CMC, it was not fully converted from COM to Java and so the “Web Component Adapter” is required by the CMC and it requires that certain binaries of Business Objects Enterprise XI R2 be installed on the server.

————-
* Recommended published BO documentation is the “BusinessObjects Enterprise XI 3.1 SP2 Web Application Deployment Guide” or if not running Service Pack 2 then use the “Web Application Deployment Guide” for your OS (hint: Unix applies to both Unix and Linux). Of course, the file names will look more like the following: xi3-1_deployconfig_unix_en.pdf or plugin-xi31_sp2_webappd_unix_en.pdf (for example)
Note: I have not played enough with IIS and .NET to know much about deploying the different application there. I am sure the process is quite different, so I would not try to use this article as a guideline for any .NET BO web applications.

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.

What is a Business Objects PAR Document?

If you are a veteran of Business Objects then you probably have heard of a PAR document. If you are a stickler for details then you may know that PAR stands for “Product Availability Report”. But the rest of us are scratching our heads now, and maybe you guys are too, when we ask ask ourselves “What is a Business Objects PAR document exactly?”.

PAR = Supported Platforms ?

I really am not sure if PAR is the deprecated Business Objects term for the documents that SAP now wants to call “Supported Platforms”. If this is true, “Amen”, I love the name change, PAR never meant anything to me. It is a strange acronym representing three words that really don’t precisely mean anything as clear as “Supported Platforms”. The SAP Support Portal puts these two document names in the same heading and they call it “Supported Platforms/PARs”. Other than that I am not sure we have anything else backing up the claim that “PAR = Support Platforms”.

PAR != Supported Platforms ?

The most recent Supported Platforms document at the time this article was written was “Business Objects Enterprise XI 3.1 SP2 for ???? – Supported Platforms”. The Linux one says on page 8 “Unless otherwise specified in the PAR”, so if we are taking this literally we are to understand that there is some other document still called a PAR that is not the Supported Platforms document. Or perhaps this was a mistake; it would not be the first time.

Conclusions

I have read on the BOB forum in at least one posting (link) that some of their resident experts (a.k.a. Forum Fanatics) confirm that “PAR” and “Support Platforms” are synonymous. However, the documents themselves seem to confound this belief. I still ask myself, what exactly is a PAR document in 2010 and where can I get one?

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.

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

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

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

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

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

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

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

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

Future Customers

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

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

Current Customers

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

Future Improvements Coming Still

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

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

To Split Mode or Not to Split Mode, That is the Question?

Wishlist Item: A Distributed Business Objects Enterprise Environment

We all want our BOE environment to perform to the best of its ability. We want to be certain that no particular component of our environment’s architecture is a weak point or bottleneck. As a result, given the time and the hardware, most of us would love to deploy a distributed, well-balanced Business Objects Enterprise environment. In many cases this would include putting each tier or group of services on their own machine/node: the Intelligence Tier (CMS, FRS, etc.), the Processing Tier (Report/Processing, Job, etc.), Web Application Tier (where InfoView and CMC are deployed), and Web Tier (HTTP/HTTPS services). Aside for using multiple servers for each tier, if you read the documentation closely, you could be tempted to take your distribution to the next level.

What is “Split Mode”, “Split-Web”, “Split Deployment”?

The SAP Business Objects “Web Application Deployment Guide” explains that “you can deploy all web application resources on a single web application server (standalone mode), or to separate dynamic and static content for deployment to de-paired web and web application servers (split mode).” The dynamic content, the part requiring actual processing would be deployed to the dedicated Web Application Server and the static content (images and client side scripts, I believe) would be deployed to a dedicated Web Server. The reason behind creating a split mode web deployment would be to better distribute the load of Web Application Server, giving the otherwise under utilized Web Server a little more responsibility.

The following is a list of WDEPLOY supported split mode server combinations:
• Tomcat 5.5 (web app) with Apache 2.x (web)
• WebSphere 6.1 (web app) with IBM IHS (web)
• WebLogic (web app) with Apache 2.x
• Sun Java Application Server 8.1 and secondary web server

Ideals and Reality, Same or Different

My mother used to play a game with us (when we were children) that we called “same or different”. It quickly became and inside joke, a way of indirectly making a comment on something, for example, I would ask my mother something like “Business Objects, a simple/stable/scalable out-of-the-box experience; same or different”. Now I didn’t say that all of them were witty gems.

The truth of the matter is that even if you are successful at deploying Split Mode you won’t find that it makes your life, or that of your servers any easier. It is extremely difficult to get Split Mode working. The guides would have you believe that it is a simple matter of using different parameters with the WDEPLOY application. This may be true for creating the packages to deploy, but actually deploying them and getting them to function is quite a different task.

Benefits Outweighed by Costs

If you are able to get split web working then you have joined the ranks of a very select few and you have also probably burned through at least 3 or 4 of your SAP Business Objects Support Customer Message allocations. So what did all of that work get you? Not much, in fact, even if you are monitoring the servers closely you may not see any change in the actual load on the Web Application Server, even with a large user base. Not to mention, each time you install a FixPack or Service Pack you will have to recreate the magic all over again and ask yourself, was it really worth it?

These are not just my personal experiences and observations. Seasoned Business Objects consultants have expressed similar discontent with this feature. My limited Internet research (forums, mostly) also confirms my findings. I would love for anyone to share their own experience with split deployments in the comments.

Tips for Installing FixPacks in Business Objects XI 3.1

Lately I have been faced with the need to install a FixPack on one of my Business Objects Enterprise XI 3.1 systems. Doing so has reminded me of some key points and tips that I wanted to share with the Business Objects community. FixPacking BO 3.1 is truly a different experience:

Not Your Father’s BO FixPack

Like everything else in life, FixPacks have changed and evolved with the Business Objects product. Some of the notable changes for BO XI 3.1 FixPacks are:

• FixPacks are no longer suite wide. You need the FixPack for the specific Business Objects product you wish to patch (such as Enterprise, Live Office, Crystal Reports 2008, etc.)
• You must first install the patch on a node where a CMS server is located. According to some SAP-BO support engineers the safest choice in a distributed environment is to install the FixPack one node/machine at a time. So if you have multiple CMS nodes, install on those first one at a time, then install on the other servers one at a time. This makes for a VERY lengthy FixPack deployment, but it is the safest method. I have also had several senior support engineers confirm that the only requirement is to patch 1 node hosting a CMS first, then after that patch is completely started you can patch all of the rest, staggering them by 5-10 minutes each.
• Stop all BO servers except for a single CMS, Input/Output File Repository Servers, CMS database, and Server Intelligence Agent (SIA). If a CMS in the cluster is not up and running the install will not work properly or at all (this is true for all servers, regardless of their role). The SI Agent must also remain up on all nodes to be patched.
• Czech and Finnish Language Packs are now available (as of FP 1.3/1.4)

FixPack Installer Beware

Here are a few cautions/warnings that stand out to me:

• EVERY Business Objects component must be on the same version. If you patch a dedicated CMS server, you must also patch any other nodes that are a part of your cluster, such as processing servers or Live Office servers, etc. Also any clients, Designer and DeskI need to be patched as well. Another reason to use WebI exclusively, no client patches needed (unless we are talking about WebI Rich Client).
• Don’t forget to redeploy the patched BusinessObjects web components. The deployment method will vary based on your original web component deployment.
• Temp Directory: When working with the Windows Installer Package, be sure to either manually extract the installation files to a desired location, or to change your user’s “TEMP” environment variable to the true location to which you want these very large and many files extracted. Actually, on second thought, ALWAYS change your “TEMP” path environment variable to a location with enough space (a few GB); I have seen the installer, in the midst of installing fill up my C: drive with temporary files that it works with after all of the unpacking and hours into the patch installation. Be careful here, plan ahead.

These are just a few of my notes and pointers for now on the top of my head. I will update this article later with my more complete notes or with any of your suggestions offered through the comments below.

Denying Security Access Explicitly in Business Objects XI 3.1

Maybe I am the only one, but I have struggled twice with this topic and so I thought that I would write this short article to make sure I remember the correct workflow and hopefully help someone else out too.

Sometimes You Just Want to Say “Stay Out”

There are times when you may want to refuse access to a specific user or user group. In my case, this is usually for a specific sub-group of users where the parent group is allowed access. To be honest, there are many reasons and some may be just to appease over-cautious administrators, customers, etc.

Most Restrictive Rights Always Take Precedence

Whatever the reason, the rule of Business Objects security is always that “the most restrictive rights always take precedence”. Knowing this, some choose to deny access explicitly, even though it was never granted in the first place, just to be sure that now and in the future that user/group does not get access.

Adding the Users or Groups

In our example we will use a folder, but this translates to any object, top-level rights, or even applications. Of course, you will want to log in to the Central Management Console (CMC) using an account that is a member of the “Administrators” group. Locate your object, we are using the “BOT Expenses” folder I created for this example. By default when I select the “User Security” option from the right-click drop-down menu on the folder I see this:

By default my environment denies access to the “Everyone” group. This is done by editing the top-level folder security in advance.
Next we need to select “Add Principal” and choose the groups to whom we want to deny access.

Assign Security – What? Why? I Want to Deny not Assign!

Click the “Add and Assign Security” button and you will see the following page:

Now here is where I was getting confused. At first I was looking for an Access Level called “No Access”, this was the BO XI R2 in me. Then I thought well, maybe it is implicit here and all I have to do is “Save” and be done. I clicked “Apply” and the screen just re-drew itself, making no changes. Then I clicked “OK” and I saw this message:

Click “OK” here just returned me to the screen depicted in the first screenshot above and erased all of the “principals”. Thank you CMC, I love you too.

So How Do You Make It Work?

OK, I will just tell you how to make it work. Return back to this screen:

Now either click the “Remove Access” button or manually un-check the boxes next to “Inherit From Parent Folder” and “Inherit From Parent Group”. Either action results in the same result. Now click “OK” and you will still get this prompt:

But this is “OK” this time… so click “OK”.

Successfully Explicitly Denying Security Access in Business Objects XI 3.1

Now you should see the groups that you select with the most desired access level of “No Access”.

Congratulations, to you if you figured this out on your own and can remember this less-than-intuitive CMC security workflow. Once you look at the removing inheritance aspect it makes sense, but it still seems like there should have been an access level called “No Access”. Perhaps, an overly cautious Business Objects XI 3.1 administrator would create a custom access level that has everything explicitly denied and call this one “No Access”, unless Business Objects XI reserves that name in which case you could call it “No Access – Thanks to BusinessObjectsTips.com!”. That would be a great name for sure.

Please read Marshall’s important note below for additional explanation.

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

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.

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

Fixing the Business Objects XI “destination DLL disabled. CrystalEnterprise.Smtp:” Error

One of the most helpful error messages (sad very sarcastically) in Business Objects XI is “destination DLL disabled. CrystalEnterprise.Smtp:”. You get this one usually after recently configuring a new job server or setting up your first scheduled job in a Business Objects XI environment. This error exists in XI R2 and it is definitely present in XI 3. So what does it mean and how do you fix it?

What is the “destination DLL disabled. CrystalEnterprise.Smtp:” Error?

This error essentially means that you do not have an SMTP/Email destination configured on the job server that your job or action is attempting to use. In some cases, your job may not even being trying to send email to anyone and you will still get this error if you have not configured an SMTP/Email destination for the job server being used. In XI R2 you need to be sure that the destination is enabled as well as configured. In XI 3 your will need to add it as it is not there by default and this will also enable it. Don’t forget the “Destination Job Server” as well as this one handles non-scheduled job, such as distributions straight from InfoView (I think). Also since jobs are assigned seemingly on a random basis, if your cluster has multiple job servers check them (or the specific job error) to pinpoint to trouble-maker.

How do I fix this error?

Easy, just configure an SMTP or Email destination on the job server (CMC > Servers > Job Server > Destinations). I highly recommend that you configure the SMTP/Email destination on all reporting job server (WebI, DeskI, Crystal), adaptive jobs servers (XI 3), and destination job servers. The only information that you must enter is domain, server name and port; however, your email admins might require the user id and password fields as well. Make certain that in XI R2 the destination is enabled too. This should stop the error; however, you might want to add more default information for the From/To email fields to prevent errors caused by users who will accept the defaults. I usually use the variable for the users email ID in both fields.

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.

Possible Recovery from Business Objects Error WIJ77778 and WIJ20003

Business Objects WebI report development is challenging enough already, session timeouts in BO XI R2 are mostly annoyances to the report developer, often causing the developer to lose work and time (and money of course). I have found a few ways to recover from a couple errors some of the time. These tips have saved me from losing hours of work (cumulative) in the past.

The eternal busy/hourglass in Java Report Panel?

Have you ever been working on some tedious report-level variables or alerters and found that suddenly your WebI Java Report Panel session cannot validate your variables? I mean it just sits there with a seemingly eternal busy or hourglass icon. When this happens to me the first thing I do is to stop the validator by pressing the escape button, “esc”, on my keyboard. This usually returns me to my unvalidated formula. Then I proceed to “Step 2″ (see below).

Your Web Intelligence session has timed out. Log out and log in again to InfoView. (Error: WIJ 77778)

The Variable Editor won’t do anything with my variable!

Or depending on if you are working in the variable editor the screen might just flash teh screen and “ding” at you without doing anything more when you try to validate the formula or save changes to it. If this occurs to you STOP and immediately proceed to “Step 2″.

The Formerly Dreaded WIJ 20003 Error

I used to beat on the things around my desk or my own forehead every time I receive the WIJ 20003 error. In the past, most efforts to overcome this led to me closing my Java Report Panel window and losing me work. Lately, I have not run into one of these errors form which I could not safely recover. Much of the instances of this error that I receive seem to be related to a timer that can easily be reset at the InfoView level with a… wait, proceed to “Step 2″.

Unable to retrieve the first page of the current report. Check the report for errors or contact your Database administrator. The report panel will switch to Structure View. (Error: WIJ 20003)

Have you ever thought your save was successful only to find nothing was saved?

This is one of the worst consequences of a partially timed-out session. This happened about once every few weeks for a while. Now, I think I have been able to avoid it by carefully checking my session by trying to validate a variable or cell contents and then if any issue is detected proceeding straight to “Step 2″.

IMPORTANT NOTE: A knowledgeable SAP-BO engineer once told me that clicking the “Save” button is not the first thing I should do if I suspect that my session is timed out. He suggested trying to validate a variable or the contents of a cell first, or modify the format of the report and display the results even. Then click the “Save” button. From this and some observations it seems that the “Save” button in many version of Business Objects XI Web Intelligence does not reset all session timeouts and can therefore appear to work without any error. I always keep my eye on the lower left-hand corner of my Java Report Panel window and look for the flashing “Saving document” message. I suggest you do too.

Step 2: Resetting Non-Java Report Panel Timeouts

As we have discussed in previous articles there are many many different timeout timers working against the Business Objects report developer. Not all of these timeouts are reset by most or any of the actions taken in the Web Intelligence Java Report Panel. Even regular saving may not spare you from timing out in other ways that have nothing to do with your refined report editing work practices.

Due to these often inevitable timeouts experienced during long report editing sessions, a very good practice is to do the following:

• Always use the “View my documents…” setting of “fullscreen browser window”, it is under preferences. There are actually two options and I believe that I am a power user and as such I prefer to use the “in multiple fullscreen browser windows, one window for each document” option. If you don’t use one of these “fullscreen” options then you will not be able to try this trick and you are probably working less efficiently than you would otherwise.
• Go back to the original InfoView browser window. Click on something here. I usually click on the folder that I am already logged in to. This will get all of your other session timers reset.
• Now return to your Java Report Panel session and re-attempt the action that you did not previously complete with any high level of confidence, such as variable validation. After this completes then save your report, or save a version with “Save As”.

Preventing BO WIJ77778 and WIJ20003

We all know by now that an ounce of prevention is worth a pound of cure. This remains true in Business Objects land too. So how can you prevent these errors:

• Follow “Step 2″ as detailed above. Set a timer to remind you to do this occasionally.
• If your connection to the web server is spotty/iffy/dodgy/unreliable (such as through remote network access) then always execute “Step 2″ as quickly as possible following any confirmed or suspected connection interruption once the connection is confirmed to be restored. This will in many cases allow you to return to your Java Report Panel session and save your work.
• Save often and version your saves, use “Save As”.

Business Objects Tips and Tricks for Web Intelligence Reports – October 2009

Happy Halloween to all. I am going to try to start publishing short to medium length lists of my favorite tips. These have helped me a lot over the years and I think if I just try to jot them down as I use them I can end up with a fairly decent list of Business Objects tips and tricks that I can post with some kind of regularity.

1. Use the “View my documents…” preferences setting of “fullscreen browser window”: There are actually two options and I believing that I am a power user, prefer to use the “in multiple fullscreen browser windows, one window for each document” option. If you don’t use one of these “fullscreen” options you are probably working less efficiently than you would otherwise. Do yourself a favor and try this tip out!
2. Save New Versions Often: For many years, in full client (a.k.a. Desktop Intelligence) and in Web Intelligence Report creation and modification I have regularly used the “Save As” option every time I make a change that is just a bit more complex than a few formatting changes. I use the technique of starting with a base report name and then appending the date numerically to it. I don’t get down into a numerical representation of the time as it would be tedious I just use letters, starting with “a”. For example, the first version saved off today would be saved as the name “My Awesome Report 20091029a”. Doing the date and the letters in this format allows for simple alphabetical sorting within the InfoView or CMC object list. Later if the report becomes corrupt, or my great idea causes a huge mess I just start stepping back in version until I find the last known good one.
3. Relative Positioning of Tables, Charts, and Cells: If you haven’t yet used this feature then your report design must be rather limited or just simple. I relatively position all of my tables, charts and cells. Always. Even if there is just one table on the report table I use relative positioning to get teh table exactly where I want it in relation to the page margins. Many times I am mixing tables, cells, and charts on the same report tab/page and I use relative positioning to make sure they never bleed together and that they are always spaced the same no matter how much data I pull in. My favorite trick is to create multiple tables and relatively position them to look like a single table. This is great when trying to make BO’s reports look and feel like the business user thinks they should.

   How do you do this Business Objects magic trick. Easy, just click on the table, chart, or cell until you see a border around the object with a very small checkered pattern (with tables you need to click the border, with the other just click the object anywhere). Then right-click (with table, right-click the border) and select “Position”. Then your choices are flexible: horizontal, right and left, and vertical, top and bottom. Each one allows the space to be defined in pixels (px). Experimentation will pay off in allowing to to create some very professional looking reports and possibly even satisfy some otherwise impossible business requirements!

4. What you see is not always what you get: For 98% of the components of a report what you see in the Java Report Panel is what the users will see in the regular HTML viewer they will use to open, refresh, and view the report. But there is that 2% that is not really quite the same (I just picked a low number, don’t quote me on the number). Most notably the prompts; they do not work exactly the same. Date prompts have different controls and selected dates in the HTML viewer will append the time to the date.
5. Where is my data cube? What exactly was returned by the query?: Open the WebI report in the HTML viewer and click on Document > Save to my computer as… > CSV. The output will include all of the data from each query. The first query’s data followed immediately by the second query’s data ans so on. This is a good way to check things out without any concern about whether a BO table with all of the query objects is actually aggregating the query’s data when you don’t want it to.
6. Cross-tab tables with totals in the first column: OK, this is nitpicking, but I keep finding business requirements that specify the totals should be in the first column and then the cross-tabbed data. Try to do this and you will find yourself in cross-tab hell. That is unless you click on the last dimension on the left and then add a column after. You just added a column in the “dimension” zone and now you can throw a measure variable in it. However, be prepared to format the column quite a bit to get it to look like a measure column; by default it will inherit the formatting given to measures.
7. Alerts don’t work on dimensions: Yeah, this one sucks. I try about once a year to get an alerter to allow me to manipulate a measure in the header, for example create borders around quarters. It won’t do anything. Alerters are intended to bring attention to or apply condition formatting to measures. The alternative is to come up with some compromise involving semi-complex variables using the dimension objects.

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!!!

Please also see the article “The Best Way to Stop a Business Objects Server“.

Advanced Web Intelligence Reporting Questions, Randomly Selected

I appreciate the questions that are emailed directly to me; many of them are good ideas for articles that fill a topic gap on the site. Others deserve equal attention, but they don’t exactly require their own article. This article is intended to capture and answer such questions related to Web Intelligence reporting.

My report has multiple queries (data providers) that use the same prompts how can I present the user with only one instance of the prompt?

This is part of the magic of Business Objects. Since BO 5.0, and probably before it, BO has supported
@prompt consolidation. This is to say that if you have two prompts that are exactly identical (same characters, and same options) then the user will only be prompted one time, but their submitted value(s) will be sent to all @Prompt and @Variable occurrences in report’s query(s).

Note: this question has piqued my interest because it raises two three questions:

1. What happens if the @Prompts or @Variables vary in case (upper or lower case) only?
   ANSWER: One prompt will be displayed to the user for each variation in case.
2. What happens if the @Prompts or @Variables vary in options only (such as one has “constrained” and the other has “free”)?
   ANSWER: The first prompt (from top to bottom) will take the lead and drive the properties of the prompt displayed to the user. Interestingly, if the prompts vary in “mono” and “multi” settings then this will cause an error in most cases. WebI will not permit the prompt to be displayed and raise an error (see below) and Desktop Intelligence (DeskI) will allow it to pass, but if the result violates the SQL statement (multiple values sent to an equals operator, not an IN operator) than the user will get an error.
   
   

   Error received in WebI when using two prompts that vary only in mono/multi settings

3. What happens if the two Business Objects @Prompt vary in LOV (List of Values) only?
   ANSWER: This is peculiar. If the LOVs are custom, then the first one (from top to bottom) will drive the user displayed LOV; the LOVs will not be combined. If one of the LOVs is based on an object’s LOV then this will override all LOVs and only the objects LOVs will be displayed to the user.

IMPORTANT NOTE: I experimented with both WebI and DeskI. I found WebI to be quite robust at handling quick changes to the conditions/prompts of a query. On the other hand, DeskI produced strange results because it was constantly caching the previous behavior of the previous test. So each time I tested a new option I needed to create a new report. This is interesting because it points to possible problems when creating a DeskI report. Certain changes to the DeskI reports I was creating were not properly reflected without starting a new report or completely exiting DeskI and returning to the report.

SHARE YOUR FEEDBACK: on this topic and all others I do, of course, welcome anyone’s experimentation or prior knowledge if they can send it in form of a comment or email.

Where can I find truly advanced WebI or DeskI report building resource?

Most advanced training courses of which I am aware are really only “advanced” if you have just started using BO. They probably contain a few really good snippets, but most of the content is geared towards getting beginners to the next level.

Honestly, I have found much of the best content contained in online BO reporting forums (Web 2.0 at its best) and much of it comes from the very generous contributions Business Objects experts such as Dave Rathbun and Steve Krandel (both have personally helped me out and inspired me to give back through this web site). Uncovering this great stuff has been to result of thorough researching of specific topics (both in search engines and using the search functions of the respective online sites. Please see our list of useful “links” for a good start.

If you know of any comprehensive advanced training courses or books (that don’t mostly rehash the simple stuff) please share the wealth through comments here or email me directly if you prefer.

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 to Backup a BO Universe and Maintain its CUID

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 universe
2. 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 file
4. Delete the existing universe file and folder
5. 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

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 -> scheduler
1. Excel format is generating Excel readable file — but they want exact Excel file.
2. Text file format is generating ‘Tab’ delimiter file
3. 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 Intelligence
but 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!

Archiving Historical Object Instances in Business Objects

I like to answer the really good and broad-focused emailed questions through articles on this site. The following is one such emailed question:

In Business Objects is there any way to archive historical object instances?

We need to keep objects for several years, but don’t want to keep so many objects available on the server as we are running out of space on our server. We only want to keep a years worth available to the users. We want to keep only a few of them and rest of the instances be archived. The instances are of all kinds.. Microsoft Excel, txt, Crystal, pdf…etc

BIAR File Backup

One of the cleanest ways to backup almost anything in Business Objects XI is to use a BIAR file backup (Business Intelligence Archive Resource, pronounced by most like the word “beer”). In Business Objects XIR2 this is easily accomplished using the Import Wizard tool. Essentially you set the source to be your source environment and the target to be the new BIAR file. Then you proceed to select the objects you desire to backup and don’t forget to check the checkbox for instances if you want to retain them.

BIAR files are compressed and they are generally not usable without Import Wizard. If you desire to restore a report from the BIAR file backup then you must be prepared for some consequences. Restoring from BIAR file backups will NOT create new instances of your objects unless you specify these options in Import Wizard (use MERGE and have duplicates renamed), otherwise your newer objects could get overwritten. Often, it is a safer idea to have a sandbox environment to which you restore the files you want. Please not you can pick and chose the objects you want, but the instances are an all or nothing option only.

Import Wizard Tricks

If you are familiar with Import Wizard there are some cool things you can do with it. For example, you can set the source and destination to the same environment and you can use the MERGE option (with rename duplicates) to create identical copies of your objects. Also you could use it to move copies to another “archive” environment.

Working the File Repository System

If you have played with Business Objects XI’s FRS at all then you know that it is a huge pile of sub-folders and finding a specific document is like finding a needle in a haystack. I recommend against trying to access the file directly as this will consume much time and it will usually be difficult to keep track of which document is which.

Scheduling Non-BO Delivery

As instances are created you could schedule them for delivery to email, FTP, or UNC (file shares). This may not be preferable for various reason, but I thought I should add it.

Really, these are the only archive methods that I have used or experimented with. Please leave any comments of other methods that you have successfully used. The question above is from a real person who needs our help.

@Prompt Functions: The Next Step – Optional Prompts

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 = true
true and false = false
true or false = true
false 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

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

I have been asked the following related questions a few times in the past months and I thought it would be a good idea to post a public answer to them. First the questions:

• Is there any way to get a list of reports that are using a specific universe?
• If I change this universe which reports will be affected?

The Answer – Yes, Definitely, Using Query Builder

The short answer to these questions is “Yes, you can”. The slightly longer answer is, “Yes, this can be done through Query Builder, the BO Auditing data*, or through the Business Objects SDK.” For the purposes of this article, I will confine my further comments to the Query Builder and BO Auditing data solutions; the BO SDK solution requires a set of skills and even licenses that many BO Developers and Administrators do not possess.

I haven’t been keeping track, but I expect that those who have emailed me this question have not studied The Best Query Builder Guide Ever Written. I just wanted to point out that our Query Builder Guide answers these questions, and provides so much more.

The Resolution using Query Builder

For those of you familiar with SQL or just the concept of tables you might think that all you have to do is join up the record of the specific universe with all of the records of the reports where it is used. Well, this is partially true. Firstly, if you know something about query builder you know that reports and universes are stored in separate logical tables and you know that you can’t really join two tables together. The solution here is two part: (1) query the record for the specific universe retrieving all associated report IDs and then (2) query for for each of those report IDs to get the reports’ identifying information.

Query the Universe’s Reports

This is a straightforward query, but be careful if you do not have the universe’s object ID, because it is possible to have two universes with the same name and you wouldn’t want to get wrong data from the start. Here is the SQL statement you need to put in Query Builder:

SELECT
si_id,
si_name,
si_webi,
si_cuid
FROM
CI_AppObjects
WHERE
si_name = 'UNIVERSE_NAME' AND
si_kind = 'Universe'

From this you will get some additional universe info and a list of all reports that are bound to this universe. Copy that list of report object IDs to a text editor and proceed.

Query the Report’s Identifying Information

Take that list of report object IDs from the previous step and parse the list so that each ID is separated by a comma. Then substitute that list for the string “111111,222222,33333 below in the SQL:

SELECT
si_id,
si_name,
si_universe,
si_cuid
FROM
CI_InfoObjects
WHERE
si_id IN (111111,222222,33333) AND
si_kind = 'WebI' AND
si_instance = 0

The output should be your desired list of reports. Now that you see the “si_universe" property you might ask yourself why I don’t query the report table for my universe’s object ID. Good idea, but Business Objects’ Query Builder query language doesn’t yet support filtering on “property bags”, and si_universe, just like “si_webi” are property bags. They hold multiple values in a single property.

The Resolution using BusinessObjects Auditing Data

If you have enabled auditing on your environment’s report servers then you can look to your Auditing data for an answer to this question. However, auditing data is not meta data; it’s records are created as the objects are used. Therefore if a report is never used then it will not be present in this data set. If this is not a concern, or if you would like to know which reports have been used belong to a certain universe, then querying the auditing data is an option for you.

Querying the Auditing Records

There are many ways to mine this data source for the data you need. For most folks the best, easiest method is to use the “Activity” universe. Here you will find the objects you need. A tutorial on this universe and the auditing data is out of scope here, but I’d like to put together a guide on the topic if I hear of any interest. Another way to mine your data is directly against the database tables (these tables are not encrypted like the Business Objects XI CMS Infostore. This certainly allows for better query tuning, but it will increase the complexity for you. You could even use the SQL of a report created against the “Activity” universe as a starting point, but be careful as that universe is full of derived tables.

Final Thoughts

Both of these steps might seem difficult at first, but with a little practice you will find that you can whip out answers to these kinds of questions rather quickly. In fact, now you have the tools to answer the reverse as well: “How can I get a list of universe using a specific set of reports?”. Now you know, and knowing is half the battle.

Have You Heard of Path Queries in Query Builder?

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

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

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

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

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

The Little Known Business Objects Program Object

Most people are only familiar with the various reporting tools in Business Objects. Of course, these are the reasons organizations set up a Business Objects instance in the first place. However, Business Objects XI has managed to include some additional functionality that goes beyond reporting. For example, BOXI can be used to publish “agnostic” documents, such as PDF, Word, Excel, or PowerPoint. In particular, nevertheless, I would like to discuss the unique functionality of the Business Objects Program Object.

A Business Objects Program Object can be used to execute a batch file, a shell script, or a binary executable. It can also run Java, JavaScript, and VBScript. Therefore, they is virtually no limit to what you can do with a BO Program Object, except for your imagination and standards. Once the association is made to the program object standard objects rights can be applied and most importantly you can schedule the object (which is where the power is).

There are a few caveats that you need to be aware of. The program will require a “Logon As” account that must have interactive login rights to the server on which the Program Job Server runs. Also, I should mention that you need a Program Job Server configured for your environment. When you create a program you are not creating a pointer to your script, but rather you are uploading your script/program to the CMS InfoStore, just like you would an Excel file if you were to load it to your Business Objects repository. This means that if you later alter the script you used to source the program object then you must create a replacement object and re-source the script.

Another couple points, when I create program objects I like to log in to CMC while logged in to the server since often I have created a driving batch file that calls other batch files on the server. Oh, and that brings up the very last point. If you are running multiple Program Job Servers and your script calls other scripts on the server you will need to place identical copies of the called scripts in the exact same path on all servers running a Program Job Server (anything less would be easier, but it would cause failures).

The following are some simple steps to create a functioning program object. They are basic, but they should get you on your way to lots of programmatic trouble. The grouping of the step correspond to the screenshots above. Click on each thumbnail to view the full-size image; I recommend opening them in another browser window (hint, right click the thumbnail image and select this option).

Step and Screenshot #1
a) In CMC, go to Objects and click the “New Object” button
b) Click on “Program” in the left-hand menu
c) Enter in the location of the program object source, the path needs to be local to the client you are using. You will need to create a test script to run if you don’t already have something in mind.
d) Select the “Program Type”
e) Select the target folder and/or categories
f) Click “Submit”

Step and Screenshot #2
a) You may edit the program object name which gets generated from the source file you loaded. The name can be anything.
b) Add other properties if desired. A good description is always helpful to others and your future self.
c) If you made changes, click “Update”.

Step and Screenshot #3
a) Click on the “Process” tab and the “Logon” sub-tab.
b) Put the account information for an account with log on rights, any rights less than interactive logon will result in a failed execution.
c) Click “Update” when done

You are welcome to experiment with the other tabs and sub-tabs, but please share you findings if you do.

Clearing the Browser and Java Cache: Do This First!

There are certain rules, tried and true practices, that you always try first before you call someone. In IT the first one is restarting an application and the second one is rebooting the computer. After those two, the order becomes debatable. Unfortunately, many of us know folks that call us before even trying one and two.

The Third One

I present to the world of Business Objects and in fact, the world of most web based applications, a third practice that you will always want to try before calling someone (or have your users try before they call you): clearing the browser cache and the java cache.

How do I clear the browser cache?

In Mozilla FireFox follow these instructions:
In the drop-down top menu navigate as follows:
Tools > Options > Privacy (tab) > Clear Now (button)
or just
Tools > Clear Private Data
Make certain that "Cache" and "Authenticated Sessions" are selected, the other options are up to you if you want to include them. You may want to add "Offline Website Data" too.

Clear the Cache for Mozilla FireFox

Clear the Cache for Internet Explorer

Sometimes You Need to Dump the Java Cache Too

There are times when the Java Query Panel won’t load or it misbehaves on the client. Instead of wasting time debugging or wishing you had “Full Client” back, just dump the Java Cache. Here is how to do it in my current Java version:
Open the Java Control Panel by looking in your PC's control panel or by double clicking the Java icon in your system tray.
On the "General" tab click the "Settings" button. Click "Delete Files". You could also click the "View" button and then hand pick the BO related applications, resources, or deleted applications that you want to dump. Be aware that the next time you access java web components, such as the java query panel, expect the initiation to take some time.

Clear the Java Cache

Errors that Indicate Issues Resolved by Clearing the Cache

My favorite error is “Internal Error: the value of parameter lang is invalid”. It is perhaps one of the least helpful in a sea of un-descriptive errors. This error is a client-side error and can be quickly resolved with just a clearing of the browser cache in many cases.

WebIntelligence Report’s Save As CSV Has a Problem?

Like a good obedient BO disciple I switched from using BO 6.5′s “full client” (now called Desktop Intelligence) to using BOXI’s Web Intelligence tool (also called WebI). To Business Objects’ credit, they have put much of Desktop Intelligence’s functionality in to WebI. But there is one disorienting difference.

BOXI Web Intelligence “Save to CSV” Is Different

My recollection tells me that if I exported a BO 6.5 Full client report to CSV that I received a CSV file with the same content that I would get through a Microsoft Excel export. Sure formatting, such as colors, column width, font, etc. were lost, but the order of the columns and even the report level variables in the table were exported to CSV from full client. Maybe, I a wrong about this (please point this out if I am), but that is the way I and some colleagues remember it.

BOXI Web Intelligence (and perhaps BO 6.x Web Intelligence) also has the ability to export to a CSV file, but this export behaves very differently. This export behaves much like the BO 6.x “View Data” or the “Data Cube” functionality. The BOXI WebI CSV export is a dump of the exact results of the first query of the report just as they were received from the database.

WebI CSV is a Query Result Export, NOTHING MORE

When you export a WebI report to CSV you may not get what you expect. The results should be identical to those you would get if you took the query and ran it directly against the database. This may be disappointing to some because it does not include any report-level aggregation, calculations, sorting, breaks, character formatting, or column ordering. HOWEVER, if a report developer is debugging a query or a universe object this output can be invaluable. For example, it can quickly uncover issues (such as Cartesian products) that dimensions can hide in a table. There are many uses for this output.

Why Does WebI’s CSV Have This Limitation?

I don’t claim to have inside information, but I can guess at two reasons for this limitation.

1. Report Development Debugging: I know I already mentioned this, but I really want people to use the CSV export to understand more about their query and report. This previously perceived limit is actually advantageous functionality in the right hands.
2. Discouraging ETL Use of Business Objects: If CSV were to output the aggregation and calculated columns of a report it would be readily used by end users as an ETL tool (Extract, Transform, Load). Some users still use it this way, but large scale use would explode if CSV exports were more than query dumps.

What Can I Do If I Want a CSV Export of My WebI Report Table’s Data?

There is almost always a workaround. If you must have your table’s data in CSV format then the solution is simple: export to Excel, open Excel output, save to CSV. You’re welcome!

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 Data
Universe not found. See your Business
Objects 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?

The answer is “Yes”, but it depends on which version of BOXI you are running.

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.

What do the values of AUDIT_EVENT.ERROR_CODE mean?

If you have spent some time with your Business Objects XI auditing data then you have probably asked yourself what significance is of the the error code values for the AUDIT_EVENT table’s ERROR_CODE property. If you haven’t yet pondered this then I suspect you haven’t looked closely at your data, OR you don’t have enough data to have produced a suspicious error code.

What are those weird values for AUDIT_EVENT.ERROR_CODE?

Here are some of the values that I have seen over the years in my BO XI R2 auditing database. The list below is a distinct list of all error codes with their corresponding EVENT_TYPE_DESCRIPTION:

ERROR_CODE | EVENT_TYPE_DESCRIPTION
1 | List of values
5 | Get page
86 | Get page
87 | Get page
303985 | Generate SQL
2147500037 | Apply format
2147500037 | Document refresh
2147500037 | Get page
2147500037 | List of values
2147500037 | Select prompt
2147760471 | List of values
2147760472 | Document refresh
2147760472 | List of values
2147760642 | Document refresh
2147760642 | List of values
2147760644 | Document refresh
2147760644 | List of values
2147760646 | Document refresh
2147760646 | List of values
2147760675 | List of values
2147760677 | List of values
2147775010 | List of values
2147776034 | List of values
2147776072 | List of values
2147776088 | List of values
2147776301 | Get page
2147776326 | Get page
2147776341 | Apply format
2147777058 | Get page

I must state that this list is in no way comprehensive, but I think it is a good sample. It might have helped the search engine help you to reach this page. Anyway, the point here is that it doesn’t matter if we build a comprehensive list because it is near impossible/pointless to build an error code translation for each ERROR_CODE value.

What do all of those ERROR_CODE values mean?

Some of you may have that handy-dandy Business Objects error code cross-reference document (I should see about sharing it next time I find it) and you are thinking these error codes can be found there. Sorry, nice try, wrong, thanks for playing. Failing that look-up I asked around and this is what I found out:
Very Short Answer: nothing
Short Answer: they can only be used in deep debugging analysis
Long Answer: Those error codes are internal Web Intelligence error codes which depend on the workflow followed and on the specific API which failed. The error codes should be able to be referenced in the trace logs produced through active “-trace” logging on the corresponding WebI Report Server (more on this topic in our article “Business Objects Classic Logging and Standard Tracing“).

So can I find any meaning in the ERROR_CODE values?

In my experience the number of errors perceived by the users of the system exceed the number of non-zero/non-null ERROR_CODE values stored in the Business Objects XI auditing data. Truthfully, some of this makes sense. If a BOXI user gets an error immediately when they click on a report in InfoView then the CMS may never get a chance to capture the error to be able to write an auditing record. More truthfully, I don’t really know.

All I can say is that the best use of AUDIT_EVENT.ERROR_CODE values is to count them as Boolean values. Did an error occur, or did no error occur. Moreover, if you find that the ratio of errors to non-error events seems to disagree with other sources of error tracking information (or user perception) then the data still has value. It has relative value. You can track it to measure trends. If you see the ratio of errors to non-error events increase/decrease over time then you know something, and knowing is half the battle.

Mystery Solved?

My sources tell me that this is fixed in BO XI R3 SP1 (XI 3.1). In fact, if you want to dive deeper take a look at ADAPT01092225. The “fix” is to limit all AUDIT_EVENT.ERROR_CODE values to 0 or 1. So, if it were previously possible tie auditing data to Web Intelligence Report Server trace logging through the error code value, that ability is revoked in R3 SP1. Therefore, the only purpose remaining for this property is the relative trend value I detailed above. Good luck.

Querying the Busines Objects XI CMS InfoStore Database Tables

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

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

The answer: No

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

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

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

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

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

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

A New Hope for CMS_INFOOBJECTS5

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

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

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

A Sample Use of a Direct Query on the CMS InfoStore

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

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

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

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

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

What other database tables can I query?

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

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

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

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

Conclusion: Where do we go from here?

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

Business Objects Classic Logging and Standard Tracing

If you have ever had a serious issue with your Business Objects environment that wasn’t easily fixed by a service pack, fix pack, or limited availability fix then you probably have had to enable logging on some or even all of your BusinessObjects servers. This is also frequently referred to as “tracing”. I’d like to discuss this briefly, but I want to say up-front that this article is not intended to replace any instructions you receive from SAP BO. Hopefully, it will just confirm information you receive from them, give you something to experiment with, or even prompt some deeper discussion between you and your BO Support engineer.

-Trace, or Dash Trace

I believe that -Trace is new to BO with the roll-out of BOXI, or Business Objects XI. My sources say that it comes from the “Crystal” technology and their for it has its own features and limitations. For this reason it may be especially useful on a BO WebI Report Server server with a particular issue, but on a different environment with a different Web Intelligence Report Server problem it may be useless. In my experience “-trace” can drastically impact performance depending on usage of the BO server and the limiters placed on the “-trace“.

It is called “-trace” because that is the name of the parameter that is used in the BO server’s command line to enable the tracing or logging. By the way, the command line parameters of BO servers/services are edited through CCM on the server. The parameter itself accepts no settings, but I believe that additional parameters can place configurations and limits on the trace logging (for example, “-maxlogfilesize“).

All of the tracing log files will be placed in the “\Program Files\Business Objects\BusinessObjects Enterprise 11.5\Logging” directory. So this can be a problem if the hard drive to which BO is installed does not have much room on it. It seems that by default BO servers are always doing some light tracing/logging to this folder. Once you begin “-trace” logging, you should see a huge spike in the file sizes and the file names should correspond to their servers more or less.

Business Objects Classic Logging, BO Classic Logging

Although BO was migrated to the Crystal platform we know that some fundamentals and tools remained virtually unchanged (for example Designer). Apparently under the hood there are still some things that can only be examined with what is now called “BO Classic Logging”. Apparently this is usually only for servers such as WebI Report Servers.

BO Classic Trace Logging is enabled through a collection of manual configurations on the server. The settings below are representative of possible settings, an example. If you are testing this out you could use these settings, but I would not place them in your “production” environment without consulting BO or some additional authority.


Create the following environment variables on the server:

1. BO_TRACE_CONFIGFILE
   Set this variable equal to the complete path to a file on your server, such as C:\Logs\BO_Trace.ini
2. BO_TRACE_LOGDIR
   Set this variable equal to the complete path to a folder on your server, such as C:\Logs

Create the following environment variables on the server:

1. Create a file named “BO_Trace.ini”. I think it can have any name that corresponds to the environment variable value and it must be placed in the location specified by the environment vairable value as well.
2. In the file place the following code:
   if (name == "busobj") {
active = true;
size = 100 * 1000;
keep = true;
importance = xs;
}

With either flavor of tracing or logging you will need to stop and start the affected BO servers in order to have the tracing take effect. In fact, to “-trace” logging you will need to stop the service first to make the command line parameter addition. You will need to monitor disk space constantly so that this doesn’t bring down your Business Objects environment. Since these are text files, the files compress rather well. Expect many similar sized files that have the start time/date stamp in the file name. Keep in mind that these will be on the server’s timezone and a file dated in the file name as Jan 19th can easily have data for Jan 19th, 20th, and 21st depending on your environment’s activity.

Note: Watch the sub-directory that is created with the name “wicdztrace”. This directory fills up with lots and lots of small files. They are a mess and will need to me deleted often.

Conclusions

Remember, now you know enough to be dangerous. I can’t support you if you crash your system with this. Test it out and engage BO and demand that they help you configure your logging to meet their needs and yours.

Using BO Query Builder to Detect Report-Universe Binding Status

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

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

Business Objects Query Builder Query: Universe Binding Status

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

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

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

Business Objects Query Builder Query: Report Binding Status

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

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

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

What does an Unbound Report Look Like in Query Builder?

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

Query Builder output of a report properly bound to its universe

Query Builder output for a report not bound to ANY universe

A Note About the Query Builder “si_universe” Property

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

What about the CUIDs?

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

Important Note about Business Objects’ Import Wizard

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

Knowing is Half the Battle

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

Want to Know More About Query Builder?

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

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.

A Favorite from Query Builder – Active User List

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

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


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


About the Query

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

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

I invite you to play with this query and try to figure out what is so magical about “si_parent_folder = 41“. Please share your thoughts and discoveries by leaving a comment here. I also recommend that you take a look at our newly published “Business Objects Query Builder Guide“, it is most likely “The Most Complete Business Objects XI Query Builder Guide Ever Written” and it will help you to discover and master the secrets of Business Object’s Query Builder, such as the one discussed in this article.

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

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

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

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

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

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

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

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

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

Query Builder MAX and MIN Functionality, not Functions

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

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

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

I invite you to play with these and share your thoughts, discoveries, or comments here in the comments of this post. I also recommend that you take a look at our newly published "Business Objects Query Builder Guide", it probably is "The Most Complete Business Objects XI Query Builder Guide Ever Written" and it will help you to discover and master the secrets of Business Object's Query Builder, such as the one discussed in this article.

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.

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

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

Which Business Objects Auditing Activities or Events Capture BO Universe Name?

Some of us in the in the wide world of BO use universes as a method to segregate our reporting. This is logical since a universe is usually tied to a single database connection. Therefore little groups of reporting users tend to congregate around universes. Some of us have picked up on this little fact and we require a naming standard that allows us to identify those groups. Some may try to do this with report names, but most fail. Ok, let me get to the point…

In such a scenario you may want to track the activities of these groups. This is possible with Business Objects auditing services and the universe name recorded there; however, it does not work in every way you want it to. Universe is not a field in the auditing database tables as username or start_timestamp are. Universe will be found in the big melting pot that BO calls the Audit_Detail table. It is one of the many values that bless the detail_text field. Not bad news, but this will hurt your query performance.

So now that we know where to find the universe name in the auditing data, we need to talk about which Events or User Activities it accompanies. First the bad news: you won’t find it on any of the following BO Auditor event types: get page, edit document, or read document. This is bad news, but get over it, there is nothing you can do about it. So now for the good news: you will find it on Document Refresh and Generate SQL.

Just in case you are not already intimately familiar with these events let me tell you something about them:

Document Refresh: this is the interactive user refresh of a report, not the scheduled refresh

Generate SQL: this seems to be limited to saved changes to a query, or clicks on the “View SQL” button. Therefore, this may be an indicator of the amount of document editing and creation going on.

Please reply with your comments, I would love to hear them (especially if I have made a mistake).

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.

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.

Please let us all know if you have any comments, suggestions, or anything else on your mind loosely related to this topic by leaving a comment.

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

Business Objects Support Portal, What Happened!?!?

The SAP AG Acquisition of Business Objects

If you don’t know yet that SAP AG purchased Business Objects for a mere 4.8 billion Euro in a friendly takeover then you must have been too busy creating universes and reports; you really need to get out more often or at least surf the web a little during breaks. This all was announced in fall of 2007, I’m not sure when it became official. But I can tell you that in July 2008 BO’s support web portal got folded into SAP’s support web portal, the SAP Service Marketplace, and in the process a few things changed.

New Support Role Call

Firstly, some new roles, or names of roles, have emerged. Apparently each business customer should have a “primary contact” or “superadministrator” who retains the maximum level of authorizations in the SAP Service Marketplace. This person(s) determines which company employees will have access to BO-SAP Support and what their assigned authorizations are with regards to the support application in the SAP Support Portal. The “named contacts” will generally have access to some or all of the following:

• Display customer messages
• Create customer messages
• Send customer messages to SAP
• Confirm customer messages
• Open service connection
• Maintain system data

New BO-SAP Service Marketplace Terms

There are a few new terms that you should understand and adopt in order to make your support experience as painless as possible.
• Customer Message: Become familiar with this term, this is your new “case” or “customer case”. It seems SAP found a way to remove any negative tones from the name for cases and at the same time remove most of the logical meaning from the name. Good luck with using that term in your communications, it will confuse everyone including yourself until we fully accept and adopt it.
• Notes Database: You could probably guess this on a multiple choice test, but I will give you a huge hint, KB… ok, to be fair, I tire of all the acronyms so let me stop being a hypocrit, this is your new Knowledge Base.
• Super-Administrators: I gave this away above, generally this is the central point of contact and the person who contols the Regular Support User membership.
• Regular Support Users: These are the grunts, they women and men suffering through support issues, opening “Customer Messages” with BO-SAP, and praying for a pormpt response and resolution.
• Netviewer: the SAP standard desktop sharing tool, replacing everyone’s favorite WebEx. Hmmm… as of last week I have heard that many BO Support folks are still using WebEx, but expect this to phase out.

Links

• BusinessObjects and SAP AG would have you access http://service.sap.com/support/, but I have found this to be a bit indirect for Business Objects support access. It takes you to the main portal for all of SAP’s Service Marketplace.
• A more direct approach would be to use http://service.sap.com/bosap-support/. This takes you straight to the “Integration of Business Objects Support Infrastructure” home page.
• New email and phone numbers for country-specific BO Support: http://service.sap.com/contact-bosap.
• Create a Customer Message with the SAP Message Wizard: http://service.sap.com/message

Business Objects Support – Tips and Tricks

• Keep in mind that this SAP Support Portal is new to BO Support as well. They are still adjusting to the new terminology and functionality of the SAP Service Marketplace.
• In reported experiences Internet Explorer 6 seems to have trouble with some of the website, especially the creation of Customer Messages. I recommend using Mozilla FireFox, it seems to do well.
• Tired of Entering Your User Name and Password on Each New Page?:For efficiency and Sanity’s sake while working within the SAP Service Marketplace I strongly recommend, no I insist, that you make use of the “Single Sign-On with SAP Passports”. The “Single Sign-On Certificate” can be requested, and installed directly from the home page of SAP Service Marketplace or from the ‘My Profile’ left-hand navigation bar.
• Preferences: Mine were all set to default European ones. I recommend you update these under My Profile. This might save some confusion with date and decimal formats. It also might prevent Business Objects Support from calling your personal cell phone at 6:00 AM.
• Search Before Creation: OK, this is a strong trend everywhere support can be requested and BO-SAP are trying to help you out here. Get used to it. You can quickly execute a search and click “Create Message” button (towards the bottom, a dark gray button for me) if you know you have to open a case no matter what.
• It gets easier, it really does. The first time you have to search for your system you will feel disoriented, but your perseverance pays nicely the next time as this system will be available as a hyperlink to quick selection. You will need to select a “Component” each time, but this gets easier as you learn where the important ones are in the hierarchy.

Business Objects SAP Support Tips and Tricks Guides!

I have managed to get my hands on some documents being distributed by Business Objects Support personnel that provide some screenshots and tips on navigating and creating support cases, I mean customer messages on the new SAP Service MarketPlace. I don’t think BO would mind me distributing these, enjoy:
BO SAP Support – Tips and Tricks
BO SAP Support – Creating an SAP Customer Message

References

• Business Objects Press Release – “SAP to Acquire Business Objects in Friendly Takeover”
• PC World Article – “SAP Buys Business Objects for $6.78 Billion”
• ZDNet Article – Why Did SAP buy Business Objects?

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.

Use Web Intelligence (WebI) Java Report Panel Structure View

Many new BO WebI users, especially former “full-client” users, complain about how slow and inefficient it is to develop reports on WebI. Most of the time when I hear these complaints I come to discover that the complainer is missing some basic knowledge about WebI report editor.

First the Basics of BO WebI Client Server Relationship

Business Objects XI WebI is a web application that depends on the web server and CMS repository for quite a few things. This is not a stateless web application. Initially the server provides the editor, GUI, parser… to the client. Once the editor application (a.k.a. Web Intelligence Java Report Panel) loads locally the server maintains a constant communication channel with the editor. The WebI Java Report Panel starts by default in the “Results Viewer” mode.

What is the “Results Viewer” / “View Results” mode?

The results viewer mode of WebI is the complete WYSIWYG (What You See Is What You Get) mode of Web Intelligence. It present the report almost exactly as it will appear to end-users (there are a few differences). Complete communication with the BO server is required in order to generate the WYSIWYG view as it applies all formatting, filtering, aggregation, and calculating on the report’s variables and the query’s results. Some may argue that this should not require server communication beyond the first iteration; however it certainly does in the present “online” WebI versions (“off-line WebI” should change this drastically). It seems that the standard “online WebI” is in many ways a “light client” application. All of this means that EVERY change made to the report while in “View Results” or “Results Viewer” mode will require communication to and from the server and this will make editing the report VERY slow.

WebI Java Report Panel’s Structure View to the Rescue

What is surprising to many is that certain mundane actions that normally would not even register with the server do require server processing when executed in the default “Results View”, but not in the “Structure View”! To switch to the “Structure View” click on the button labeled “View Structure”. This will switch you to a template of the structure of the report you are building. Now changing fonts, cell shading, column order, header titles, cell contents and much much more become instantaneous. Most of the changes you make to the report are cached on the local client; this explains the new found speed.

Additional Advantages of the Structure View

The structure view also allows you to view and easily manipulate certain elements of the report better than the “results view”. For example, table footers can be quickly accessed and section within the report are represented with gray lines that mark their beginning and end. A cell’s object(s) or variable(s) can also be immediately visually identified in the structure view as well.

A Disadvantage of the Structure View

All of the speed of the Java Report Panel Structure View is because server communication is reduced to the minimum. This minimal level of communication means that most report formatting actions and several common report editing actions are not communicated to the server at all. Big deal? Yes, this means that the server is not being reminded that you are working hard on your report and it is certainly not saving those wonderful changes you are diligently working on. In other words, when working in the “structure view” save often!

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!

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!

Solving Business Objects XI R2 CMS Security Viewer’s Crashes

Business Objects XI R2 CMS Security Viewer is a useful administrative tool provided freely by Business Objects. It allows one to analyze your security models without having to actually alter user group membership or login as different users. The tool should not be need in Business Objects XI Release 3.0; however, it can be very useful in BO XIR2. We are still figuring out all of the uses for this tool since we recently got it up and running on our system after we got our hands on a functioning version.

Business Objects XIR2 CMS Security Viewer 2.1 Doesn’t Work

At the writing of this article, I believe that Security Viewer 2.1 is the latest version of this tool. However, we like so many others found that this tool crashed immediately, just as soon as the application opened. Through some research, testing, and BO-Support feedback it appears that BO XI R2 CMS Security Viewer 2.1 does not function with BO clusters which have installed the Productivity Pack. Apparently this little bug was planned for correction in Fix Pack 3.7, but I haven’t yet confirmed this rumor became reality.

Give BO Security Viewer a Try

The simple solution for most teams is to just use the 2.0 base version of BO Security Viewer. This version does not have any problems with the productivity pack. HOWEVER, obtaining a copy of BusinessObjects Security Viewer 2.0 can prove to be quite challenging as it seems that Business Objects does not distribute copies of previous versions on their website.

UPDATE – Business Objects Security Viewer 2.2 Available

I just added Security Viewer 2.2 to our server so you can download it from their directly. I hope I don’t upset the powers that be at SAP Business Objects. I had to navigate a lot through the SAP Support Portal web site to find the download so I thought I might save all of you just a little bit of time. Here is the link:
Business Objects XI R2 Security Viewer 2.2

Download BO Security Viewer 2.0

However, since I still have a copy BO Security Viewer 2.0, I feel obligated to share it with those who might stand in need of it. I haven’t altered the *.msi installation package in anyway, except to zip it up so as to prevent some recipients from unintentionally trying to install it directly from my web server. Believe me, just download the 10 MB zip file, extract it, and install.

Link to Download Business Objects XI R2 CMS Security Viewer 2.2
Link to Download Business Objects XI R2 CMS Security Viewer 2.0

A Hint on the JAVA_HOME Environment Variable

You may need to configure a JAVA_HOME path variable by setting creating one through Start > Control Panel > Settings > Advanced > Environment Variables, please see screenshot for an idea.

Query Builder: Improving Query Performance

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

Query Performance Concerns: Indexed Properties

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

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


Optimizing Query Builder Queries

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

For example:

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

Business Objects Enterprise: Java or .NET?

There are a lot of factors that will come into play in deciding how to deploy your Business Objects XI R2 application, that is to say in the decision whether to deploy InfoView in the Java (Apache Tomcat, WebLogic) or .NET (IIS) flavor. There will be many reasons such as available skillsets, technical familiarity, web server restrictions, etc. However, there may be one factor you are not considering… future functionality support!

Another Win for Java

At one point in time Business Objects, the company that is, announced that they would maintain all future released functionality of Business Objects XI in both the .NET and the Java flavor. However, sometime in 2007 it appears that BusinessObjects went back on this commitment. Probably due to the less-than-booming economy BO was faced to make a difficult choice and because of the overwhelming popularity of Java among BO’s customers, the decision was made to focus all of the product roadmap’s enhancements first on Java.

BO’s Stepchild: .NET and IIS

Therefore I suggest you consider this point carefully when making a decision to deploy InfoView on .NET. If you choose to do so you are ignoring the fact that Business Objects is playing favorites. If you select .NET you are selecting the stepchild. Don’t expect enhancements and fixes to be as readily available for your .NET platform as they are and will be for Java. You don’t believe me? Well go ahead and try to install Business Objects XI 3.0 on IIS and .NET. Not going to happen, at least supposedly not until sometime in the second half on 2008. That’s my point and it is just the beginning.

Business Objects XI R2 Java AND .NET?

With Business Objects XI R2 it is possible to actually run both platforms at the same time on IIS and Apache Tomcat, although most BO administrators would probably want to avoid this. However, if you require and SDK interface and you have no Java developers then do you really have a choice? Honestly, many BO administrators are doing this with BOXI R2 and so I would not want to discourage you if you have no other choice.

In the end this is your decision. We just want to be sure that you have this little bit of extra input. We would hate to hear of anyone else having to spend hundreds of person hours moving from the .NET platform to the Java platform because they didn’t know what they were getting in to. Its not that much work to switch you say? The rework can range from SDK rewrites to OpenDocument.aspx updates across every single report that uses OpenDocument. Make your choice wisely, what more can we say?

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.