Retiring Desktop Intelligence Free-Hand SQL

It’s the end of the world as we know it – time to send Desktop Intelligence reports into retirement.

Historically, it’s easier to create a SQL query and slap it into a Desktop Intelligence report than build or augment a universe. There are two methods to do this. The first method is to use the New Report Wizard to create a free-hand SQL report instead of choosing a universe. The second method uses a universe to build an initial query which is further edited in the Edit SQL panel. The Report Conversion Tool (RCT) can handle both cases – let’s take a closer look at what happens. I’ve used SAP BusinessObjects Enterprise XI 3.1 SP3 to generate my examples. In either case, I must convert these reports to Web Intelligence if I want them to continue to exist in SAP BusinessObjects Business Intelligence 4.0.

Let’s first consider a free-hand SQL query. For a simple illustration, I have used the eFashion universe to build the query and pasted its SQL into a new Desktop Intelligence document.

The Report Conversion Tool will examine the document’s SQL and build a derived table universe (first introduced in BusinessObjects Enterprise XI R2) with a single derived SQL table that embeds the query. Here is what the derived table looks like in the universe.

For the next example, I used Desktop Intelligence to build a standard query with the eFashion universe. But I then modified the SQL using the Edit SQL dialog box in the query panel. Unfortunately, it’s not immediately obvious when you open the query panel that the query has been customized. For example, the query panel below appears to show a simple query with Year, State, and Sales Revenue.

However, when the Edit SQL dialog box is opened, it becomes clear that the query has been customized with a WHERE clause limiting the year to 2004. To maintain the custom SQL and prevent the report from reverting back to the universe-generated SQL, I clicked the Do not generate SQL before running box when I authored the query.

When the Report Conversion Tool is used to convert this document, it does not create a new universe. It continues to use eFashion but also customizes the Web Intelligence SQL. As with Desktop Intelligence, this customization is not obvious from the query panel.

However, as with Desktop Intelligence, the customized SQL is visible from the View SQL dialog.

The good news is that in both cases, the Report Conversion Tool was able to convert the Desktop Intelligence report to Web Intelligence. However, there is some bad news, particularly for the free-hand SQL report. First, the Report Conversion Tool creates a cryptically named universe and places it in the Report Conversion Tool universe folder. If your environment has lots of free-hand SQL reports, you are going to end up with a large collection of small universes. It’s a support nightmare – the BI equivalent of suburban sprawl. In addition, reports that contain prompts with lists of values (LOV) will generate some pretty nasty LOV queries from the derived table. Frequently, the performance of the LOV queries is sub-par.

Although the universe LOVs can be modified to run faster, you should be always ask yourself if a universe is truly needed anytime Designer is opened. You’ll be much better served by a smaller number of universes that know how to answer many business questions and not a multitude of universes that each power a single report. When I help customers with migrations, I  prefer to perform an initial run of the Report Conversion Tool on all reports except the Free-Hand SQL reports (by leaving the “Convert reports containing free-hand SQL” box unchecked). The Report Conversion Tool will flag the Free Hand SQL reports as “not converted”. Then, a simple query on the RCT audit table will generate a list of these reports. Next, I’ll work with the customer to see if any of the reports can be retired or redesigned in Web Intelligence. Ideally, some of these reports can be recreated using a universe instead of one generated by the RCT.

If you decide that the new derived SQL universe must stay, take a moment to look at the WHERE clause of the derived table. Try to move as many restrictions as possible out of the universe and into the Web Intelligence report as Query Filters. Fewer restrictions will make the universe more generic and capable of answering more questions (and satisfying future report requests) than the original report. For commonly used restrictions, add predefined filters in the universe to make report creation easier.

If the odds are unlikely that nobody outside of IT will directly use the universe generated by the Report Conversion Tool, evaluate if it makes more sense to replace the free-hand SQL Desktop Intelligence document with a Crystal Report. Honestly, I’ve been surprised that Crystal Reports is barely mentioned by SAP when discussing Desktop Intelligence. There’s even talk of adding free-hand SQL to a future (post BI 4.0) release of Web Intelligence (see related article, Free-Hand SQL Isn’t Free). So perhaps my advice to choose Crystal Reports is ill-advised? Would love to hear everyone’s thoughts.

It’s the end of the world as we know it— time to send Desktop Intelligence reports into retirement. But thankfully, the Report Conversion Tool, even in XI R2 and XI 3.1, can help us reach our goal. Happy conversions!

Shouldn’t we all stop looking at Desktop Intelligence?

We should stop looking at Desktop Intelligence.

Today I attended an interesting SAP webinar entitled “BI 4.0 – Did you think about your upgrade?” by David Francois Gonzalez with the SAP Technology RIG Americas (the very useful slide deck can be downloaded from the SAP Community Network).  Afterward, I tweeted:


To which Pieter Hendrikx responded:

@ericvallo @oswaldxxl @dallasmarks shouldn’t we all stop looking at Deski? That’s what my [Diversified Semantic Layer] conclusion was. Better invest in #WebI

And of course, Pieter is absolutely correct.  We should stop looking at Desktop Intelligence.  But Desktop Intelligence is like a gruesome automobile accident during rush hour- some of us can’t stop looking (see related discussion on the BusinessObjects Board).  But the SAP BI roadmap is clear- Web Intelligence is the future, Desktop Intelligence is the past.  Desktop Intelligence is not supported by SAP BusinessObjects Business Intelligence 4.0.  It’s gone.  Really.  Crystal Reports 2011 and Web Intelligence 4.0 represent the future of reporting for SAP.

Some organizations already use Crystal Reports and Web Intelligence exclusively.  For them, the path to SAP BusinessObjects Business Intelligence 4.0 is relatively straightforward.  Other organizations, particularly those who have used “classic” BusinessObjects releases prior to XI Release 2 (XI R2), still may have an investment in Desktop Intelligence that has to be managed.  But the good news is you can take proactive steps today from either XI Release 2 or XI 3.1 – you don’t have to wait for BI 4.0.

Organizations on both XI R2 and XI 3.1 should immediately begin phasing out the creation of new Desktop Intelligence reports by revoking Deski application rights and retraining users to use Web Intelligence.  Some reports cannot yet be converted to Web Intelligence (I plan to address Desktop Intelligence phase out strategies in the coming weeks).  These reports may have to wait for the BI 4.0 Report Conversion Tool.  But many can be converted today with your current platform.  Organizations on XI R2 have fewer conversion options than XI 3.1 (and therefore much incentive to upgrade to XI 3.1 in the near term).  In addition, Desktop Intelligence reports are audited by the XI 3.x platform (sadly not in XI R2), so it is possible to identify obsolete reports and retire them rather than expend effort to convert them.

Any organizations still on “classic” BusinessObjects 5 or 6 (I know you’re still out there) cannot go directly to BI 4.0 as the upgrade tools only support XI R2 and higher.  These organizations should plan a migration to XI 3.1 so they are poised for the future.

For long-time Business Objects users, it’s the end of the world as we know it.  But SAP BusinessObjects Business Intelligence 4.0 will be available early next year.  And I feel fine.

Desktop Intelligence and the Report Conversion Tool

Dates and the Report Conversion Tool

or, The End of the World as We Know It (Part 2)

In Desktop Intelligence, it’s possible to display “yesterday” on a report using the following formula:


The Report Conversion Tool will convert the formula; however, Web Intelligence will not understand it, displaying the following error:


The formula can usually be corrected in either the converted Web Intelligence document or the original Desktop Intelligence document.  However, today I encountered the formula in the document header and was unable to modify the Web Intelligence cell (in Web Intelligence XI R2 – sigh – not sure if 3.x handles this item better).  So I corrected it first in Desktop Intelligence and re-ran the Report Conversion Tool.  The corrected formula should be:

RelativeDate(CurrentDate(), -1)

On a related note, I frequently find CurrentDate() displayed on a scheduled report to indicate when the report was generated.  Both CurrentDate() and LastExecutionDate() return the same date/time at the time of scheduling.  So either one can be used to schedule and distribute an Adobe PDF, for example.  However, if a user views the document instance in the InfoView portal, the CurrentDate() no longer reflects when the data was generated.  Therefore, it is recommended to always use LastExecutionDate() to display the age of the data, because it will always be correct.

In my next post, I’ll look at migration strategies Desktop Intelligence documents that use Free Hand SQL and “do not regenerate SQL” (see related article, Retiring Desktop Intelligence Free-Hand SQL) as we consider a world without Desktop Intelligence.

The End of the World as We Know It (Part 1)

For SAP BusinessObjects customers, the end of the world as we know it will occur in 2011 with the general availability of SAP BusinessObjects Business Intelligence 4.0.

Although many, including Hollywood, jumped on the 2012 end-of-the-world bandwagon, SAP is getting the party started earlier.  For SAP BusinessObjects customers, the end of the world as we know it will occur in 2011 with the general availability of SAP BusinessObjects Business Intelligence 4.0 (code name Aurora).  Folks in the beta program are revealing that Desktop Intelligence is not present in the SAP BusinessObjects Business Intelligence 4.0 distribution.  While the temptation for some may be to go into shock, the news of Desktop Intelligence’s demise is really old news first announced as part of the XI roadmap in 2004 after then-BusinessObjects acquired Crystal Decisions.  Desktop Intelligence was going to disappear “someday” and now we’re learning that “someday” is really 2011.

Disclaimer: I am not part of the SAP BusinessObjects Business Intelligence 4.0 beta/ramp-up program.  Although I would like to get in, if somebody could please show me the secret handshake.

Desktop Intelligence, in previous versions known simply as “Business Objects”, “Business Objects Reporter”, or “the full client”, used to be the only reporting tool in the BusinessObjects arsenal.  Although Web Intelligence had gone through several incarnations, it wasn’t until Web Intelligence XI R2 that the tool became powerful enough to handle most common reporting tasks.  However, at the same time, Crystal Reports was fully integrated into the Business Objects product portfolio.  Then Voyager appeared, soon to be reincarnated as Analytics (code-named Pioneer).  And Xcelsius, uh, I mean SAP BusinessObjects Dashboard Design.

What we’ve seen from SAP and other vendors is that there isn’t “one BI tool that rules them all”, but instead tools focused toward specific user audiences and applications.  In its day, Desktop Intelligence tried to cover all known bases.  And although Web Intelligence incorporates most Desktop Intelligence features, it still doesn’t cover all of them even in the most recent XI 3.1 SP3 release.

So over the next few weeks, I’ll be exploring The End of the World as We Know It.  Life without Desktop Intelligence.  I’ll explore certain Desktop Intelligence features in XI R2 and XI 3.x and look at some strategies for living in a world without Desktop Intelligence.

SAP BusinessObjects Web Intelligence

Jim Brogden, Heather Sinkwitz and Mac Holden have created a comprehensive guide to SAP BusinessObjects’ query and analysis tool.

UPDATE: I liked this book I helped co-write later editions. See SAP BusinessObjects Web Intelligence: The Comprehensive Guide.

SAP BusinessObjects Web Intelligence is a brand new 2010 title from SAP Press, who kindly sent me a review copy.  According to its back cover, the book will “provide you with a comprehensive functional overview of SAP BusinessObjects Web Intelligence, as well as actionable, step-by-step content to help you quickly begin creating, analyzing, an sharing enterprise reports.”  Authors Jim Brogden, Heather Sinkwitz and Mac Holden have created a comprehensive, hard-cover 574-page  guide to SAP BusinessObjects’ query and analysis tool.

Web Intelligence is used by many types of business and IT users throughout an organization.  However, it is designed primarily for business users, not IT professionals.   I was curious how the authors would define Web Intelligence, as this definition would guide the organization of the material.  From the book’s description of Web Intelligence in Chapter 1:

SAP BusinessObjects Web Intelligence is a best-in-class ad hoc query, reporting, and analysis tool designed with the business user in mind [emphasis added]…  The primary function of Web Intelligence XI 3.1 is to provide the capability of querying a set of data without any knowledge of the SQL language and interactively analyzing data to further restrict, expand, and modify the way information is displayed and delivered”

A majority of the book is devoted to Web Intelligence report design and is topically well-organized.  There is also significant coverage of universe design and the Web Intelligence SDK, topics that traditionally have different (predominantly IT) audiences.  The Web Intelligence SDK is covered neatly at the end of the book. However, universe design topics appear at several places throughout the book. My personal preference is that a book for Web Intelligence users should focus on the user experience and, to quote the Wizard of Oz, “pay no attention to that man behind the curtain”.  That man (or woman) is the universe designer, frequently an IT professional.  That said, I really appreciated the discussion in Section 2.1.1, “Design with the Business User in Mind”.  Technical challenges aside, most universe designers violate this basic rule, resulting in a universe that is not easily adopted by business users.

This book is comprehensive, giving coverage to advanced features like report bursting via Publications, related tools like Live Office, linking multiple report documents, and new features introduced in Web Intelligence XI 3.1 SP2.  Business users who are new to SAP BusinessObjects tools will benefit greatly from the book’s methodical coverage of Web Intelligence.  Traditional SAP users, in particular, should embrace this book readily, as Web Intelligence is replacing older SAP BI tools in the product portfolio.

Disclosure of Material Connection: I received this book free from the publisher. I was not required to write a positive review. The opinions I have expressed are my own. Some of the links in the post above are “affiliate links.” This means if you click on the link and purchase the item, I will receive an affiliate commission. Regardless, I only recommend products or services I use personally and believe will add value to my readers.I am disclosing this in accordance with the Federal Trade Commission’s 16 CFR, Part 255: “Guides Concerning the Use of Endorsements and Testimonials in Advertising.”

Convincing Reasons to Move to Web Intelligence

Henri Theuwissen provides some solid reasons for moving to Web Intelligence.

Henri Theuwissen has written Convincing Reasons to Move to Web Intelligence, a thoughtful and detailed review of how organizations can move from Desktop Intelligence to Web Intelligence.  Henri makes the case that

The conversion from Desktop Intelligence to Web Intelligence:
– Reduces the total cost of ownership (TCO).
– Increases the ease of deployment and upgrade.
– Offers better web-based performance and higher interactivity over the web.
– Is more closely coupled with other capabilities offered by SAP products such as Xcelsius, Explorer, Live Office, Mobile or BI widgets

Although I teach the official SAP Web Intelligence course, the course doesn’t draw parallels to Desktop Intelligence.  So I’m very thankful to Henri for putting together a comprehensive resource.  My first exposure to BusinessObjects was in 2003 with version 5.1.  I remember my mentor Jeff Bartel, now an SAP Sales Consulting Manager, telling me to “not worry about Web Intelligence”, as it really wasn’t useful.  Web Intelligence became a much more viable tool beginning with Web Intellgence 6.5, took some additional steps with XI Release 1, and came into its own with XI Release 2.  Web Intelligence XI 3.0 added the Rich Client and Web Intelligence XI 3.1 SP2 continues the trend of adding functionality currently present in Desktop Intelligence along with never before seen features like input controls, which have no Desktop Intelligence equivalent.  Henri indicates that the maturity of Web Intelligence will continue later this year with the next major release, code named Aurora.  Aurora will be the first major BusinessObjects release under SAP’s leadership, so it will be interesting to see what happens with product names (Interactive Analysis?), version numbers (XI 4.0, or something else?), and new features.

In any case, the days of Desktop Intelligence are numbered and customers should embrace Web Intelligence as much as possible.  Any advanced requirement that cannot be handled by Web Intelligence should be implemented using Crystal Reports, not Desktop Intelligence.  But there really are many convincing reasons to move to Web Intelligence and Henri Theuwissen points the way.

Displaying Web Intelligence Date Prompts using Built-in Functions

Using Web Intelligence functions to display date prompt values.

Displaying a prompt value on an SAP BusinessObjects Web Intelligence report is pretty straightforward. Web Intelligence provides the UserResponse function to do this.

string UserResponse(object data_provider; string prompt_text; Index)

But notice that the data type returned by UserResponse() is always a string. What if you are trying to display a date? For example:

UserResponse(“My Date Prompt”)

The answer will be a string like this one:

3/7/2009 12:00:00 AM

So what to do if you desire a different format? Perhaps one like mm/dd/yyyy? Web Intelligence also provides a ToDate() function that converts a string to a date using a date format string.

=ToDate(UserResponse(“My Date Prompt”); “M/d/yyyy hh:mm:ss A”)

This variable will now display the following:


If you hover over the variable definition in the data tab of the report manager, you’ll see that this variable is indeed a date. Perfect. But what if I want a different format? Since the result is currently a date, the FormatDate function can be used to display it in the desired format.

FormatDate(ToDate(UserResponse(“My Date Prompt”); “M/d/yyyy hh:mm:ss A”); “MM/dd/yyyy”)

will be displayed as


The last step may be to add the date prompt to a report variable that is displayed in the report’s title cell. The following formula will use the Web Intelligence document name concatenated with the report tab name (a new function in Web Intelligence XI 3.x – pretty cool) concatenated with some date prompts.

=DocumentName() + ” (” + ReportName() + “) between ” + FormatDate(ToDate(UserResponse(“My Begin Date Prompt”); “M/d/yyyy hh:mm:ss A”); “MM/dd/yyyy”)
+ ” and ” + FormatDate(ToDate(UserResponse(“My End Date Prompt”); “M/d/yyyy hh:mm:ss A”); “MM/dd/yyyy”)
which will be displayed as

Sales Report (Summary) between 01/01/2009 and 03/07/2009

The typing gets a bit lengthy and tedious. It may be preferable to create separate variables for the date prompts then reference them in another report title variable.

HINT: The date format string is where things get a bit tricky. Especially when the on-line help doesn’t include a fast link to a reference of valid date formats. Download the Building reports using the Java Report Panel document from the SAP Help Portal. A decent format string reference can be found on pages 259-262 of the XI 3.1 edition. Perhaps the reference will be just one click away in the next release of Web Intelligence?

Have fun building Web Intelligence reports!

Displaying Images on Web Intelligence Reports

How to display images on a Web Intelligence report.

A frequent reporting requirement is to display an organization’s logo as an image, such as a GIF, JPEG or PNG) on Web Intelligence documents. There are two ways to accomplish this task:

1. Place the file in a special images directory on the SAP BusinessObjects Enterprise server
2. Place the file on another web server

Let’s look at the first method.

On Microsoft Windows installations of SAP BusinessObjects Enterprise XI 3.x, the default <installdir> is C:Program FilesBusiness Objects and therefore images should be stored in the <installdir>BusinessObjects Enterprise 12.0images folder.

On Linux and Unix installations of SAP BusinessObjects Enterprise XI 3.x, the location of the images folder is <installdir>/bobje/enterprise120/images.

Once the image files are in place, Web Intelligence report developers can place a freestanding cell onto the report canvas. On the properties tab for the cell, set the Text property to boimg://, ex.: boimg://corporatelogo.jpg. Be sure to change the “Read cell content as” property to Image URL. The BOIMG prefix instructs Web Intelligence to look in the special image directory.

This technique also applies to XI R2, but the image directory is in a slightly different place. Images with BOIMG can be used in the Web Intelligence Rich Client; however, the image will not be visible until the document is exported to the CMS and viewed via InfoView.

One caveat that I was unable to resolve – the original corporate logo file had an ampersand (&) in its name. Although Web Intelligence did not display an error, it also did not display the image. My clever solution was to rename the file without the offending character, as I was unable to devise an escape character so it could be interpreted properly. If somebody is aware of a true workaround (or other misbehaving characters like &), please post a reply to this article.

Regarding the second method, type the URL (for example, into the text field. As with BOIMG, set the “Read cell content as” property to Image URL.

You’re not as smart as you think you are

The formula is correct – maybe.

You’ve got to admire the new confidence-building features of Business Objects Enterprise XI 3.0. When you validate a Web Intelligence formula (I’m using the Web Intelligence Rich Client here), you receive the following message:

Web Intelligence The Formula Is Correct

I must admit – receiving a message such as this one boosts your confidence, kind of like a Hallmark moment from Business Objects. It’s nice to know that Business Objects thinks you’re smart. Unfortunately, the message only means that your formula is syntactically correct. Whether or not the formula results are correct requires logical thinking and thorough testing.

If only Web Intelligence could ascertain that I had done my job correctly. Now that would be true Business Intelligence. Until then, perhaps “The formula is valid” would be a more accurate message.