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.

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

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

Getting Personal with Publications and Profiles

Today is the second day of the 2009 SAP BusinessObjects User Conference in Dallas, Texas.  This afternoon, I’ll be presenting my second of three breakouts, “Getting Personal with Publications and Profiles.”  This breakout was a bit of a challenge.  On one hand, it’s a remix of my very first user conference presentation that I gave at Insight 2006 in San Francisco, California.  On the other hand, publication capabilities on the XI 3.0/XI 3.1 platform is much more robust than what was possible on XI R2 in 2006.  Honestly, I think two hours would be required to get through everything thoroughly.  So certain topics didn’t make the final cut.  But I’m keeping to an hour and hope that everyone stays awake (I’m presenting at 4 PM – the last breakout slot).

Publications serve a noble role – the efficient bursting of personalized information to a large audience.  I’m currently helping a customer migrate from Business Objects 5.1.8.  From a distance, it looks like there are hundreds of scheduled jobs in Broadcast Agent that need rescheduling on XI.  But upon closer inspection, there are really just a handful of reports that have dozens of variations to support personalization.  Although it’s not within scope of the migration project, I hope that my client will look into publications and see how it can reduce the complexity of too many reports and too many scheduled jobs to maintain.

Do you have a “success story” with Business Objects publications?  Feel free to comment and share your success.

BusinessObjects Chop Shop (InfoView hacks)

In the spirit of Orange County Choppers, I began this week with a need to customize Business Objects InfoView in support of customer requirements. The customer needed to alter their SAP BusinessObjects Edge BI 3.1 InfoView to remove a couple of unneeded menu choices: the Open menu (home of My InfoView and Encyclopedia) and the Dashboard menu (see final screen shot below).

Customized Infoview XI 3.1

Ideally, the customization would be possible by a simple adjustment of InfoView application rights in the Central Management Console (CMC). Unfortunately, the list of configurable rights for InfoView is still not as robust as many customers would prefer.

After some digging, I found a discussion thread on the BusinessObjects Board (BOB) that was very helpful. The good news is that I can achieve the effect that I’m looking for. The bad news is that it required chopping a JSP file, which is an unsupported modification. In addition to the potential humiliation by SAP BusinessObjects tech support, one’s environment is only a single hot fix or service pack away from having the JSP overwritten.

The chop job is performed in the C:\Program Files\Business Objects\Tomcat55\webapps\InfoViewApp\jsplisting directory on the headerPlus.jsp file. I’ve updated the original BOB post with the details of my (unsupported) modification.

UPDATE: After taking a second look, I took a more surgical approach of uncommenting the lines of code that visualize the buttons without affecting the rest of the functionality. I updated the code sample on BOB with this new approach.