The Business Objects Query Builder Guide

A helpful book if you use the Query Builder a lot.

I don’t analyze blog stats very often, so it came as a bit of a surprise that the article about the SAP BusinessObjects Query Builder 4.0 is the sixth most popular on my blog. The Query Builder is crude and— generally speaking— so is my language when I’m required to use it. But the Query Builder can sometimes provide deeper insight into what’s going on than the Central Management Console (CMC) administration tool is willing to tell you.

The reason Query Builder exists is due to the cryptic nature of the SAP BusinessObjects system database, sometimes called the CMS database or repository. If you look at the database schema (BI 4.0/4.1 shown), you’ll see tables with the following names:

  • CMS_ALIASES7
  • CMS_CCFRONTIER7
  • CMS_FRONTIER7
  • CMS_IDNUMBERS7
  • CMS_INFOOBJECTS7
  • CMS_LOCKS7
  • CMS_RELATIONS7
  • CMS_ROOTFRONTIER7
  • CMS_SESSIONS7
  • CMS_VERSIONINFO

Sadly, by design these tables are very difficult- if not impossible- to query without resorting to SDK programming. The first thing to learn about Query Builder is that you don’t use it to query the actual tables in the database. Query Builder is a thin veneer atop the SDK, so you’ll instead build queries on these fictional tables:

  • CI_AppObjects
  • CI_InfoObjects
  • CI_SystemObjects

A quick Google search will turn up some simpler queries on these tables. But for more serious inquiries, you’ll want to get a copy of Julian Romeo’s The Business Objects Query Builder Guide. The latest version 1.3 that includes BI 4.0 updates was published February 1, 2012 and my electronic receipt says I purchased mine just 5 days later. The book explains how to use the step-by-step query “wizard”, write SQL in the Query Builder, and use Relationship and Path Queries. The book is easy to read and contains a lot of examples.

The book is a good value at $29 and an even better value if you convince your BI Manager to put it on the corporate credit card.

Disclosure of Material Connection: I purchased this book with my own funds. It was not a free review copy. 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.”

Don’t Fluster the Cluster

An improperly designed architecture may cause your cluster to become fluffed.

Ben and Jerry’s recently introduced a new flavor called Clusterfluff (now rebranded as the more politically correct What a Cluster), described as “peanut butter ice cream with caramel cluster pieces, peanut butter and marshmallow swirls”. Yummy. But today, let’s talk about a different cluster: SAP BusinessObjects Business Intelligence, also known as SAP BusinessObjects Enterprise.

Ben and Jerry's Clusterfluff Ice CreamA cluster in SAP BusinessObjects Enterprise is defined as a system with multiple Central Management Servers (CMS) that work together by sharing a common system, or CMS, database. Each CMS is typically on its own physical device, known as a node. SAP BusinessObjects enthusiasts who take the BOE330 training class, Designing and Deploying a Solution, get to team up with their fellow students and create clusters in class. Let’s discuss using Microsoft Windows; however, the same principles apply to Linux/Unix.

I recently encountered a BI system with a poorly devised cluster. It was the second time that I’ve seen this ill-advised configuration on the XI platform, so it seemed worth writing about. Especially since SAP BusinessObjects Business Intelligence 4 continues the server architecture of the past few versions. In both of my situations, each physical server, or node, was built with a “new” install.  Once the installation was completed, the second CMS was stopped and reconfigured to point to the same system database as the first CMS, as shown in the illustration below (click image to enlarge).

How to Cluster SAP BI - Bad

Each node has an identical configuration. There’s two of everything. Two Central Management Servers. Two Crystal Reports Processing Servers.  Two Web Intelligence Processing Servers. And, sigh, two Input File Repository Servers, or iFRS. There are two Output File Repository Servers, or oFRS, as well. Each file repository server points to local storage on the node, which is a significant flaw that could lead to corruption of the cluster.

In a SAP BusinessObjects cluster, only one FRS is actively working even when all FRS in the cluster are enabled. Just like many of your coworkers, the rest of the FRS sit around doing nothing in a passive state waiting for the active FRS to fail. All FRS connect with the CMS upon startup. The active FRS is the one that contacts the CMS first.

So how can the cluster become corrupted? Let’s assume that the iFRS on node 1 becomes active. When a report is published to the system, it is stored on the iFRS default location, which is the C: drive on node 1. The CMS database contains an InfoObject containing the physical path to the report.

Next, let’s assume that the iFRS on node 1 fails. The iFRS on node 2 becomes active. When Wanda in accounting logs into InfoView to view a month-end report, the processing server will fetch the report from the active iFRS on node 2. However, because the report was originally written to the C: drive on node 1, Wanda will receive an error and call the Business Intelligence help desk.

The cluster, sadly, has become fluffed.

A better solution would be to disable the iFRS and oFRS on node 2, guaranteeing that the iFRS and oFRS on node 1 are always active and that all documents are stored locally on node 1. This configuration is shown in the following illustration (click image to enlarge).

How to Cluster SAP BI - Better

You might argue that this configuration is not fault tolerant. And it isn’t. However, neither was the first configuration. Disabling the second iFRS and oFRS is the first step to preventing additional corruption.

The best solution is to create a file system that both nodes can share, as shown in the illustration below (click image to enlarge).

How to Cluster SAP BI - BestEach FRS is configured in the Central Management Console (CMC) to use the shared space, typically by specifying a UNC path. Since the shared space is on a different device, the File Repository Servers will need to run using a domain account rather than the default Microsoft Windows Local System account. This account is specified in the Central Configuration Manager (CCM), either directly on the File Repository Server (XI R2) or vicariously through the SIA (XI 3.x, BI 4.0, BI 4.1). Be sure that separate directories are created to distinguish the Input File Repository from the Output File Repository; however, both can share a common parent directory, as they do in the default out-of-the-box configuration.

There are other design considerations for a truly fault-tolerant BI architecture, such as failover in the web application server tier. But that will have to wait until a future discussion. In the meantime, I hope you will enjoy a pint of Ben and Jerry’s Clusterfluff ice cream. And inspect your BI system to insure proper configuration.

Resources

SAP KB 1378753 – How to configure the File Repository Server (FRS) to point to the Network Attached Storage (NAS)?

What are your experiences with SAP BusinessObjects clustering? Share your thoughts (and favorite bookmarks).

BOE330

B

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.

BusinessObjects XI R2 Fix Pack 6.4

SAP BusinessObjects Enterprise XI R2 Fix Pack 6.4 is the final patch for the XI R2 platform.

Last week, I installed SAP BusinessObjects Enterprise XI R2 Fix Pack 6.4 on a Service Pack 6 virtual machine running Microsoft Windows Server 2003. This fix pack is the final patch for the XI R2 platform. Below is the Central Configuration Manager on Service Pack 6. Notice that most servers such as the CMS are at version 11.5.12.1838; however, the Web Intelligence Report Server and Performance Management servers (AA Alert, etc.) have been updated to version 11.5.12.1916.

BusinessObjects CMC for version XI R2 SP6

The patch was easy to install. After accepting the license agreement, the only two decisions are whether you want to “deploy webapps when install completed” and “restart servers when install completed”. Both are selected by default. When finished, the following screen appears:

BusinessObjects XI R2 SP6 FP6.4
When completed, the CMC reveals that the Web Intelligence Report Server and performance management servers have gone to version 11.5.12.2126.

As expected, the Web Intelligence Java Report Panel applet has also been patched to 11.5.12.2126. Notice that the certificate is signed by SAP.

Webi certificate BusinessObjects version XI R2 SP6 FP6.4

And here’s the Web Intelligence Java Report Panel splash screen confirming the applet version.

Webi About BusinessObjects version XI R2 SP6 FP6.4

After daylight savings time in the United States ended this past weekend, I created scheduled jobs for the Crystal Reports, Web Intelligence, Desktop Intelligence and Program job servers. Everything ran “on time” as expected. In addition, I confirmed that the Web Intelligence Java Report Panel and Desktop Intelligence client were capable of putting the correct refresh date in the status bar.

I went to all this trouble to be sure that Fix Pack 6.4 installs cleanly; however, I am not sure that I’ll ever install the patch for a customer. We’re encouraging customers to move to XI 3.1 SP3. Professionally, it’s been a long, strange trip with XI R2 since November 2005. I’m a much different consultant than I was five years ago and its hard to believe that this blog entry may very well be my last one for that release. This week, I’m helping a customer install SAP BusinessObjects Enterprise XI 3.1 SP3 and Explorer XI 3.2 and migrate from XI R2. I’m looking forward to the ramp up of SAP BusinessObjects Business Intelligence 4.0 later this month and general availability early next year.

NOTE: As with any service pack or fix pack from any vendor, you should install and test in a separate environment before installing on your production environment. Please feel free to post comments if you have any positive or negative experiences with this final fix pack.

BusinessObjects XI R2 – Good to the Last (Code) Drop Part 2

For those who did not catch my earlier post or those who may be bitter, clinging to their guns and SAP BusinessObjects XI R2 CD’s, I found an interesting message on  the SAP Support Portal (S-ID required) while downloading XI 3.1 software this week:

How to obtain SAP BusinessObjects Enterprise XIR2 Patches and Full Builds

SAP BusinessObjects Enterprise XIR2 arrived at its product end-of-life (EOL) on June 30, 2010. SAP BusinessObjects has agreed to provide Best Effort Support for all customers until June 30, 2011. During this time, customers may still need to access full XIR2 builds, fix packs and service packs. Please read How to obtain SAP BusinessObjects Enterprise XIR2 Patches and Full Builds for information on how to locate these downloads in the SAP Support Portal.

To get a full build of SAP BusinessObjects Enterprise XI R2, please create a customer message on the SAP Service Marketplace…

Guess I better keep my XI R2 CD’s in a safe place…

BusinessObjects XI R2 – Good to the Last (Code) Drop

The BusinessObjects XI R2 Era is Over.

Today is June 30, 2010, a day that will live in infamy. It is the last day of patch support for SAP BusinessObjects Enterprise XI R2. Limited tech support will continue for an additional year, until June 30, 2011. But it means that XI R2 Service Pack 6 (XI R2 SP6 and a few fix packs, Fix Pack 6.4 being the latest) is the end of the XI R2 distribution. With the recent release of XI 3.1 Service Pack 3 (XI 3.1 SP3) and the highly anticipated BI 4.0 coming at the end of this year, SAP is signaling that it is time to move on (see related article, Customizing SAP BusinessObjects BI 4.0). I can still remember the excitement of the 2004 acquisition announcement of Crystal Decisions by a then-independent BusinessObjects, followed by road shows and road maps. XI Release 1 was to ship in early 2005 with XI Release 2 following later in the year. XI R2 indeed shipped in late November 2005 and I started my first BusinessObjects migration project in January 2006. It brings a smile to my face when I think of how my career as a business intelligence professional has changed since those days.

What does the end of patch support mean for XI R2 customers? It means that although tech support is still available to assist with issues over the next 12 months, the standard approach to anything resembling a software bug will be “upgrade to XI R2 SP6, apply its fix packs, and hope the problem disappears.”  Otherwise, move to XI 3.1. XI 3.1 thoughtfully implements many of the enhancements customers requested for XI R2, including a completely redesigned Central Management Console (with new security features), a slightly redesigned InfoView portal for users, a desktop edition of Web Intelligence (the Web Intelligence Rich Client), and more. Beginning with XI 3.1 SP2, SAP introduced additional Web Intelligence features to help customers continue the migration away from Desktop Intelligence, which will either have limited or no support in BI 4.0. BTW, I have no inside knowledge here – we’ll all learn Desktop Intelligence’s fate together when SAP introduces BI 4.0 in fall 2010 (see related article, Desktop Intelligence, Back for a Limited Time).

The table below contains product end-of-life (EOL) dates courtesy of the SAP Support Portal. If your enterprise is still using classic BusinessObjects 5.x or 6.x, now is the time to begin planning your migration to XI 3.x. Customers on XI R1 or XI R2 should also begin making plans to upgrade to a fully-supported release. Because of the CMC’s redesign and its new security features, I highly recommend that organizations budget training dollars for their administrators. Although upgrading from XI R1 or R2 is not as complex as a classic BusinessObjects migration, the new XI 3.x security features, particularly the custom Access Levels (CAL) are much easier to maintain than the Advanced rights available in XI R1 or R2.

Product and Version GA Date Patch Support EOL Tech Support EOL
BusinessObjects Enterprise XI R2 24-Nov-2005 23-Apr-2007 30-Jun-2011
BusinessObjects Enterprise XI R2 SP1 30-Mar-2006 31-Aug-2007 30-Jun-2011
BusinessObjects Enterprise XI R2 SP2 23-Apr-2007 31-Mar-2008 30-Jun-2011
BusinessObjects Enterprise XI R2 SP3 20-Nov-2007 30-Sep-2008 30-Jun-2011
BusinessObjects Enterprise XI R2 SP4 31-May-2008 30-Nov-2009 30-Jun-2011
BusinessObjects Enterprise XI R2 SP5 31-Dec-2008 30-Jun-2010 30-Jun-2011
BusinessObjects Enterprise XI R2 SP6 16-Dec-2009 30-Jun-2010 30-Jun-2011

SAP BusinessObjects on Linux and Unix – part 5

Supported Linux platforms for various versions of SAP BusinessObjects Business Intelligence.

Let’s take a minute to look at the Linux versions currently supported by SAP BusinessObjects Enterprise.  SAP supports both Red Hat Enterprise Linux (rhel) and SUSE Linux Enterprise by Novell, but the exact version depends on the version of SAP BusinessObjects Enterprise.

The SAP BusinessObjects Business Intelligence BI 4.1 SP6 – Supported Platforms guide indicates support for the following Linux editions:
Red Hat Enterprise Linux 5 Update 3
Red Hat Enterprise Linux 6 Update 2
SUSE Linux Enterprise Server 11

The SAP BusinessObjects Business Intelligence BI 4.0 SP7 – Supported Platforms guide indicates support for the following Linux editions:
Red Hat Enterprise Linux 5 Update 2
Red Hat Enterprise Linux 6 Update 2
SUSE Linux Enterprise Server 10 SP2
SUSE Linux Enterprise Server 11

The SAP BusinessObjects Enterprise XI 3.1 SP6 for Linux – Supported Platforms guide indicates support for the following Linux editions:
Red Hat Linux Enterprise Server 4
Red Hat Linux Advanced Server 4
Red Hat Linux Enterprise Server 5
Red Hat Linux Enterprise Advanced Platform Server 5
SUSE Linux Enterprise Server 9 SP3
SUSE Linux Enterprise Server 10
SUSE Linux Enterprise Server 10 SP2

The SAP BusinessObjects Enterprise XI R2 SP6 for Linux – Supported Platforms guide indicates support for the following Linux editions:
Red Hat 4.0 Advanced Server
Red Hat 4.0 Enterprise Server
SUSE Linux 9.0 Enterprise Server

Higher patches beyond the specified minimum patch requirement may be used, but they may not be officially tested by SAP. 

Next, let’s look at free versions of Linux.  These Linux distributions aren’t supported by SAP as production platforms! However, they are perfectly suited to determining if Linux is a good fit for your business intelligence system architecture.  CentOS is an “Enterprise-class Linux Distribution derived from sources freely provided to the public by a prominent North American Enterprise Linux vendor”.  That vendor is Red Hat.  On the SUSE front, Novell offers openSUSE.

Although I’ve installed XI R2 on Linux for customers multiple times, I did not until recently install XI 3.1.   Eric Vallo has a valuable set of articles related to installing BusinessObjects Enterprise on CentOS.  I’ve been using his posts as a guide and will have more to say about my own experiences soon.

Resources

Updates

  • July 22, 2015 – updated BI 4.1 to SP6. Note that earlier versions, including BI 4.0, will no longer be supported at end of 2015
  • August 28, 2013 – added BI 4.0 SP7 and BI 4.1 SP1
  • July 25, 2013 – updated XI 3.1 supported platforms for SP6 and added BI 4.0 SP6 supported platforms