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

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!