The bug paradox: When fixing the bug leads to wrong reports

My workmate Christoph Gnodtke wrote an excellent blog about how to identify SAP BusinessObjects Web Intelligence reports which are impacted by various calculation changes in newer BO versions. What I would like to point out here is that not only BO 4.x migrations are concerend but also “simple” service / support package upgrades e.g. from XI 3.1 SP2 to SP6. In my current customer case we’ve found many many reports which obviously were created in a wrong way, namely that the table structure contains the merged dimension (e.g. [Merged Country]) where as the cells within the row use a variable containing e.g. a Where operator using the original dimension ([Query1].[Country]). In our case the business requirement would have been to use the merged dimension here as well. As outlined here, in former BO support package levels a bug resulted in the effect, that the just mentioned example still showed what the business expected. Now (e.g. in XI 3.1 SP6) that the bug is fixed, the reports start to show wrong values. Although the software 360Eyes doesn’t solve the problem, it at least helps to identify concerned reports. Unfortunately we still need to look into every single report and compare between the version running on the XI 3.1 SP2 environment and the SP6 environment. In order to speed up this process we use 360Cast. This software provides similar features like BO Publications e.g. for report scheduling and bursting. The main advantage namely in the case of report testing are two fold (compared to BO out of the box features):

  1. Report selection for a schedule job can be done using good old BO categories. That means you can assign e.g. a test category to all reports you want to test in one single run. In our customer case we use categories for each data mart. In 360Cast, instead of choosing every single report individually, we just choose to select all reports of this test category.
    CategorySelection
    In order to run all these reports with one single click there is just one thing missing: Providing all the necessary prompt values, often the same values for the same prompts (like Year) over many reports. This is where the second advantage comes into play:
  2. To provide prompt values 360Cast accepts both manual input values (where a value can be applied to a all prompts with the same name) but also values from an Excel sheet (or even from an SQL query). We usually use the Excel alternative. Based on this we can easily vary input parameters for different test purporses by simply using another Excel sheet. In addition we can specify the export format and the recipients, e.g. by providing an email address.
    PromptSettingMapping
    (The values in the drop down menues correspond to the columns in the underlying Excel spreadsheet)

After all, also 360Cast doesn’t solve the initial problem. But at least we don’t need to run every report (identified by 360Eyes earlier) on its own but can automate the refresh process and we can easily rerun reports (e.g. with different prompts by simply modifying the values in the Excel list).

Advertisements

Issue with Null Filters prior to Webi XI 3.1 SP6.3

After some more “theoretical” blog posts back in 2013 I’d like to start the new year with a short technical contribution. As some of you may know I’m trying to upgrade the BO XI 3.1 SP2.7 environment of one of our major customers to XI 3.1 SP6. This is sort of a painful experience as we are working on it since more than 12 months now. Still, there is some light at the horizon as back in December Fixpack 6.3 was released which contains an important bug fix. Not to mention that the bug wasn’t yet there in SP2.7 but was introduced somewhen between SP3 and SP6. The issue is referenced in the SAP KB1897777 and it seems to be fixed now.

What is our situation? We have Webi reports containing containing multiple queries and merged dimensions. If we use dimensions from two different queries in the same table, variables as well as filters containing “IsNull” functions do not work properly.

Here we are with the report in XI 3.1 SP2.7:

SP27

Now the result in SP6 (prior to Fixpack 6.3):

SP61

… and finally how it looks like with Fixpack 6.3 applied:

SP63

The tricky part was to detect this error (the above screenshots are very simplified tables for debugging purposes). Obviously even our business users didn’t caught this at first sight. Therefore I’m glad if I can contribute that you double check this if you are on a lower version than Fixpack 6.3. On the other hand: Please let me know if you find other (newly introduced) bugs in FP6.3…

And by the way: Happy New Year and lot’s of fun in the Business Intelligence world 😉

The Rule of Thumb for BOBJ Tool Selection

What is the right SAP BusinessObjects frontend for a given situation? A question I’m asked nearly every day. When I was confronted first with this topic  a few years ago the taken approach was a highly sophisticated Excel spreadsheet in order to assess all available BOBJ tools based on a feature list. The only problem was: At the bottom line there was never a clear winner. Next approach were the famous decision trees like the following:

BO-Tool-DecisionTree

Not bad as a first guess. And in an ideal world where the basic functionality would be the same for all BOBJ tools such a tree could work indeed. But given the situation that even today – nearly ten year after the aquisition of Crystal by BO – support for universes is still not exactly the same in Webi, Crystal Reports and Xcelsius (aka Dashboards) and especilly the maturity of a tool or a sub component of it is vastly different, there is no clever way to tell you which tool to use for which purpose.

Although you can’t give a distinct answer to the question “which tool to use for what”, I’m convinced that the following rule of thumb will be valid in most situations and for a majority of organisations – the only assumption is that there is no limitation out of licensing. That means I assume you have a license for all or at least the most important frontend tools. The idea behind this rule is that a priority rating is more helpful than a feature or use case driven decision tree.

Here is my rule of thumb:

  1. Try it with Web Intelligence
  2. If Webi didn’t work, try it with Crystal Reports
  3. If Crystal Reports didn’t work, try it with one of the “niche” tools

Let me share some thoughts about this priority list:

Why should we start with Web Intelligence? There are various reasons for this:

  • From a features perspective Web Intelligence provides the most widest range in the BOBJ tool suite. You can use Webi for creating classical standard reports, you can use it for dashboard like applications (think about Input Controls and the ease of use regarding drilling – e.g. compared to Xcelsius…), you can use it for self-service reporting, you can use it as a data pump using XLSX export or interface to other applications using BI Web Services etc.
  • From a maturity perspective it is one of the most stable and mature applications in the BOBJ world. I tell you this as an native “Crystal guy”. But whereas Crystal Reports 2011 runs stable the same way as it did for the last decade, the new Crystal Reports for Enterprise is just crap compared to both, the legacy CR and Webi.
  • From a data source perspective: Webi is the only tool which fully supports all kind of Universe stuff. I’ve never heard of any limitation that Webi would not support something what you can do in a Universe (by design). But let me compare this to Crystal Reports: On one hand you can use only UNX universes in CR4Ent, on the other not all type of queries are supported. Crystal still has the limitation that if a universe query results in multiple SQL statements it fails to handle it as there is no local “micro cube” as with Webi. Of course this whole argument implies that we value a “common semantic layer” to be of high “added value” to an organization and therefore should be supported in its full scope. But there is even more to add: Webi handles not only multiple SQL result sets per query, it can also leverage multiple queries and easily join them. Although I’m not a friend of “merged dimensions”, there are many situations where this capability is the only work around to get the job done at the end of the day (and not three monthes later when the data finally arrived in the DWH…). No clever way to do this in Crystal Reports or Xcelsius directly.
  • From an SAP BW perspective: Two or three years ago we had to decide for Crystal Reports often because of its better connectivity to SAP BW and all around it with hierarchy handling etc. These days are “passé”. My most recent experience with Webi using the BICS interface are very promising. Totally in contrast with CR4Ent which crashes regularly, even with the latest patch level.
  • From a usability perspective: Although SAP currently tries to position Webi to be the tool where business users develop the reports, I think its usability is equivalently valubale for IT folks too. Report development is quick and straight forward – once you’ve got used to the ribbon style menues 😉
  • From an installation footprint perspective: Given the situation that SAP releases new patches nearly every third or four week, patching client installations is an nightmare. The more valuable are fully web based deployment scenarios. Therefore once again, Webi is the favorite.

Still, Web Intelligence has some short comings. That’s why you should evaluate Crystal Reports in a second instance:

  • One of the major differentiators between Crystal Reports and all the other frontend tools is Conditional Formatting. As you may know Crystal Reports has a powerful formula language integrated. This formula language can be used to control neary every property you can set in Crystal Reports. This way you can implement what I call “guided interactivity” at its best: Let the end user choose some parameter values and use these values to control both, the data in the report but especially the layout too. The typical use here is: A customer wants to build 10 similar reports. They are not exactly same regarding the layout, but similar. For example, in Webi there is no straight forward way to show conditionally show or hide some parts of the report. In Crystal Reports such a thing is a no-brainer.
  • Interactive / proactive Alerts: As of today, only Crystal Reports based alerts can be used to send an email notification if they are triggered.
  • Export formats: Crystal Reports has a multitude of available export formats, including Word or XML, which aren’t available in any of the other tools.
  • Hierarchical Grouping for relational data sources: Crystal Reports can dynamically resolve a Child-Id-to-Parent-Id relationship and apply calculations over such a hierarchy.

But before you choose Crystal Reports remember there are two versions of Crystal Reports: The legacy Crystal Reports 2011 and Crystal Reports for Enterprise. The first one is mature and stable, but does not contain new features introduced only to CR4Ent. On the other hand, CR4Ent is a de facto “1.x” product regarding its code maturity. For now I simply cannot recommend to use it as your major reporting tool without intensive testing of your own use cases in your environment. On the other hand – depending on your situation – the legacy Crystal Reports does not support UNX universes at all nor does it support UNV universes as you’d expect it coming from Webi.

What about all the other tools? I call them “niche tools”. This is due to the fact that all of them have quite a narrow scope of application compared to the “generalists” Webi and Crystal, let me name a few:

  • SAP Visual Intelligence: This is a great tool for ad-hoc-analysis. But that’s it. No way (yet) to publish documents online (except over Explorer), schedule them or create more sophisticated standard reports.
  • Explorer: Not the most mature product, especilly in the context of SAP BW and BWA as a datasouce… In general, Explorer is nice for “standard” visualizations. But have you ever tried to customize even basic elements of these charts? Or have you tried to add a simple table into an Exploration View? Or export an Exploration View as a whole? As of today these basic things seem to be impossible…
  • Analysis, Edition for OLAP: Limited to OLAP data sources, no clever integration into scheduling, publishing etc.
  • Analysis, Edition for Microsoft Office: Only BW support…
  • Dashboards / Xcelsius: Limited capabilities in terms of data volume that can be processed, no straight forward way to realize drill downs, no common export formats, no full Universe support, no scheduling capabilities…
  • Design Studio: Not usable for productive environements in the current version 1.0, and even for subsequent versions I’m very sceptical… In addition the scope of the tool is focused on BI App development which as such is clearly a niche.

This doesn’t mean that these tools are not valuable in the context of specific requirements. But assuming that there is a value in reducing the number of used and supported tools to a minimum, these tools should be chosen only after having evaluated Webi and Crystal beforehand. According to my experience chances are quite high that your requirements can be covered by one of these two tools.

What is your experience with tool selection? Would you agree with my rule of thumb? Anything I missed? Looking forward to reading your comments!

Backup & Recovery in BO 4.x

This post is dedicated to the available means of backup & recovery in SAP BusinessObjects BI 4.x. There are several changes compared to the previous version XI 3.1 including some literally missing functionality.

The recovery scenario: Partial restore of report and universe objects

In my eyes the typical recovery scneario is a partial restore. It happens quite quickly that you either delete a folder with a whole bunch of reports or that you want to revert a change in a report or universe development. Especially if we consider the ad-hoc reporting capabilities of Web Intelligence you probably don’t have a local copy of the corresponding report. In addition people which do any mistake leading to a recovery procedure tend to notice that they did such a mistake only with a certain gap in time, this means they request the recovery e.g. of a given folder not immediately after its deletion but perhaps two weeks later when they realize they deleted some reports too much. In the meanwhile the system might have been used heavily, that’s why a full recovery of the system itself is not really an option. What you need in such a situation is the possibility to recover only selected objects from a backup set to the original system. In this blog I will concentrate on this scenario. I use “original” system as a term to identify the system on which I take the backup and to which I want to recover something back.

The available possibilities in BO 4.x

There are three major approaches in taking a backup of BO 4.x and recover partial content:

  1. Create some kind of BIAR file (multiple options available, see below) and try to recover selected elements back to the original system.
  2. Do a full backup, restore the full backup to a separate BO “recovery” system and finally use LCM to “promote” selected objects back to the original system.
  3. Use a professional backup & recovery solution like 360Plus from GB and Smith

Let me evaluate the above approaches in the next few sections.

The BIAR approach

The BO Admin Guide states in section 12.1.1.3 (page 466):

It is recommended that you use the Lifecycle management console for SAP BusinessObjects Business Intelligence platform to regularly back up your Business Intelligence content, such as reports, users and groups, and universes. Having current backups of your content makes it possible to restore your Business Intelligence without having to restore your entire system or your server settings.

Whoever wrote this sentence at SAP doesn’t seem to have either any concrete experience with LCM or not a clear idea what a backup & recovery tool should fullfifl in practice. Respectively let’s have a look at just any given freeware to backup your Windows files. Therefore to point this out right at the beginning: Keep your hands off in trusting LCM as your one and only backup solution for BO. LCM is a tool to promote (or in the SAP jargon ‘transport’) objects from one environment to another. LCM was never made to be a backup solution. Let me explain in some more details:

The preferred way to take a backup using LCM is exporting a LCM job into a LCMBIAR file. Finally with FP3 / SP04 you can now schedule the export to such a file. But there are some critical short comings with this (as of SP04 Patch 1; anyone having differing experience with a higher patch level please comment below!):

  • reimporting the LCMBIAR file to the original system on which you created the file will fail as soon as you delete the original LCM job. What real backup solution makes itself depending on the job object creating the backup set?
  • whenever you import a BIAR file you don’t have an option to select / unselect objects to restore. There is only black or white: Either you import all the contents from your (LCM)BIAR file or nothing.
  • LCMBIAR files do not save your successful instances. Only recurring instances are backed up. But by the way you cannot decide whether to restore recurring instances or not, as mentioned before you have to restore everything belonging to the BIAR file.

A next approach in using BIAR files is to use the new Upgrade Management Tool or the “legacy” biarengine.jar. The good news here are that LCM finally is capable to import regular BIAR files which were created by these two means. The following things should be considered:

  • In contrast to LCMBIAR files, regular BIAR files can be imported without any dependancy to any LCM job.
  • The Upgrade Management Tool as well as the biarengine.jar takes a backup of both, recurring as well as successful report instances.
  • Unfortunately SAP was so stupid – sorry to say it like this, but I couldn’t find any other term to express my feelings about this situation – to remove (or just not allow…) the possibility to import a BIAR file of the same software version using the Upgrade Management Tool. In XI 3.1 this became quite standard during a recovery procedure to load the BIAR file using Import Wizard and then select only the objects you need to recover. In combination with the short coming of LCM not to be able to select individual objects this is a real sad thing (#factoryofsadness …). Dear SAP: Just give us back basic functionalities like restoring selectively either using Upgrade Management Tool or LCM!

For those interested in the biarengine.jar – I couldn’t find any hints on it in the BI4 documentation, so I took the admin guide from XI 3.1 and it seems that everything still works as before (for more detailed infos see this blog):

First of all you need a properties file to specify what you want to be backed up:

exportBiarLocation=C:/temp/BiarEngineBackup.biar
action=exportXML
userName=Administrator
password=<your password>
CMS=cloudsrv012:6400
authentication=secEnterprise
exportDependencies=true
exportQuery=select * from ci_infoobjects where si_parent_folder = <your own id or query> OR SI_ID = <your own id or query>

Save these lines of text in a file, e.g. mybackup.properties. After all you can execute the following commands on the command line or in a batch file (replace C:\BOE4 etc. with your own BO install path):

cd “C:\BOE40\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\jre\bin”
java -jar “C:\BOE40\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java\lib\biarengine.jar” C:\Temp\mybackup.properties

You can use either the biarengine or LCM to restore content to the original system. As you can only restore the full BIAR file, I recommend to have a dedicated recovery or sandbox system in place where you can import the BIAR file as such and then use LCM to restore only what you need back to the orginal system. Such a dedicated system you need anyway for the second major approach, restoring objects from a full backup.

The full backup / restore approach

As long as you have a dedicated system available to “mount” the full backup into a running BO system this appraoch is quite straight forward and nothing to be afraid of (as long as you know what you do ;-)). The following high-level steps will guide you through the recovery process:

  1. Take a full backup of your original system on a regular basis. This includes at least a backup of your CMS system / repository database, the FileStore folder(s). As of FP3/SP04 SAP added an official “hot backup” option (see the “Settings” area in CMC), therefore you don’t need to shutdown your BO system to take the backup. Just define a time window in which you create both, first the backup of your system database and then the backup of the FileStore. In addition to system database and FileStore, please note your Cluster Key and Administrator password from the original system!
  2. Prepare the Recovery-System: I assume you have an already installed “recovery” system. This can be a sandbox or as well e.g. a QA system you want to temporarily use as your recovery system. Stop all existing SIA and Tomcat services on the «Recovery» system. Have a look into Task Manager and make sure that all CMS.exe and sia.exe processes have been stopped.
  3. Restore the System-DB: Restore the backup of your «original» system database to a new, empty database / schema. After restore, execute the following SQL statement on this restored DB to remove all server entries: Delete from CMS_INFOOBJECTS7 where ParentID=16
  4. Restore the FileStore: On the «Recovery» system rename the existing FileStore folder to «FileStore_orig». Restore the FileStore from «Orginal» to the «Recovery» system into its original location.
  5. Create ODBC source: In case your recovered system DB is hosted on a SQL server, create a 64bit ODBC source to it on the «Recovery» system.
  6. Create Recovery SIA (1/2): On the «Recovery» system, create a new SIA with a new CMS. Point the CMS to the recovered system database (probably using the ODBC source created in the previous step). Select the «Use a temporary CMS» option.
  7. Create Recovery SIA (2/2): Once the new SIA is added, change the Cluster name from the orginal name to a new name, e.g. «Recovery». Start the newly created SIA and check in Task Manager if CMS starts up and keeps up running. Then stop the SIA again.
    (if you want you can combine step 6 and 7 and add only one additional SIA)
  8. Create second SIA to add regular servers: Add a second SIA including regular servers, you can even add a second CMS. Start this SIA and Tomcat. Login to CMC on the «Recovery» system and check in the Servers area if all expected servers are up and running.
  9. Verify File Repository Servers: Check if the file path indicated in the Properties of the Input and Output File Repository Servers correspond to the location where the FileStore has been recovered.
  10. Run the Repository Diagnostic Tool: Run the Repository Diagnostic Tool in order to remove any inconsistencies between File Repository Servers and (recovered) system database.
    (replace C:\BOE4\ with your own BO install path; more info about the command line parameters you’ll find in the BO admin guide):
    cd “C:\BOE4\SAP BusinessObjects Enterprise XI 4.0\win64_x64”
    reposcan.exe -dbdriver sqlserverdatabasesubsystem -connect “UID=sa;PWD=<password>;DSN=<ODBC_Name>” -dbkey <cluster key> -inputfrsdir “C:\BOE4\SAP BusinessObjects Enterprise XI 4.0\FileStore\Input” -outputfrsdir “C:\BOE4\SAP BusinessObjects Enterprise XI 4.0\FileStore\Output”
  11. Do a «selective restore» from the recovery to the original system using LCM (or one of the other ways explained above, mostly depending whether you need to recover report instances or not)
  12. Recreate original settings on Recovery system: If you don’t need the «Recovery» system anymore, you can reset everything to match the original settings. For this simply stop the created SIAs and either set their startup mode to disabled or delete the SIAs from the system entierly (a practical how-to you’ll find here). Rename your FileStore on the Recovery system from “FileStore_orig” back to FileStore. This means you need to either delete the recovered FileStore folder or give it another name before. In addition you can remove the recovered database (schema).

Once you excerised this process a few times it will serve you as a reliable way to recover (partial) elements in a reasonable amount of time. But still it is not the “elegant” way to go. And therefore I would like to introduce you to my third and favored major approach. What SAP fails to deliver is usually deliverd by one of the add-on providers.

The professional approach

As a professional BO administrator I like professional tools. 360Plus is one of my favorite tools, not only regarding backup & recovery. But this is one of the major reasons why I recommend this solution. 360Plus doesn’t keep any separate information outside the regular BO system database, it’s just an alternative view to its content in addition to the CMC.

Let the pictures speak for themselves:

First of all you need to create a backup job in the web based interface of 360Plus, you can choose from various object types. In addition you can choose whether to include subfolders, report instances or Favorites folders in case you choose groups and users:

You can schedule this job to run “now” or at a later point in time. By the way: All the jobs scheduled with 360Plus can be triggered by an external scheduler like $Universe etc.

Once having executed the backup job you’ll find a new entry in the context menu of any given folder or document:

And for folders which do not exist anymore completely you’ll find the Trash Bin icon:

After all you can choose from available recovery options as you are used to from any other professional backup & recovery solution:

That’s it. The only thing you need to do in addition is to save the 360Plus file folder on your BO server by a regular file backup tool.

A more detailed overview of the 360-approach you can find here

Are you dissatisfied with the existing backup & recovery capabilities in BO 4.x too? Or do you see different ways of improving this process? Let me and other knows and write a comment! Thanks for your participation!