Testing for BI & DWH

Since ever testing is part of every IT project plan – that’s true as well for Business Intelligence (BI) & Data Warehouse (DWH) projects. The practical implementation of testing in the BI / DWH environment has confronted me with troubles in the past again and again. Often I’ve had the impression that the BI / DWH world is still back in the Stone Age regarding development processes and environments. At least it is significantly behind the maturity level I know from the software engineering domain. The below chart illustrates this gap:

rbra_testing1 (1)

Cultural differences between the software development and BI community (Source: http://agiledata.org/essays/culturalImpedanceMismatch.html)

If there is something tested at all, typically in the BI frontend area things are tested manually. In the DWH backend we see – besides manual tests – self coded test routines, e.g. in the form of stored procedures or dedicated ETL jobs. However the integration into a test case management tool and systematic evaluation of the test results doesn’t happen. This is heavily contrasting with the software engineering domain where automated regression testing combined with modern development approaches like test driven design are applied. At least for some time we find first inputs regarding BI specific testing (cf. the (German) TDWI book here). Concepts and paper are patient though. Where are we with regard to a possible tool support, namely for the area of regression tests?

Since summer 2014 we at IT-Logix are actively looking for better (tool based) solutions for BI specific testing. We do this together with the Austrian company Tricentis. Tricentis develops the Tosca product suite, one of the worldwide leading software solutions for test automation. In a first step we run a proof of concept (POC) for regression tests for BI frontend artefacts, namely typical reports. One of the architectural decisions was to use Excel and PDF export files as a base for our tests. With this choice of a generic file interface the efforts to develop BI product specific tests were omitted. And this way we reduced the implementation effort to about two days in the POC. The goal was to run “Before-After” tests in batch mode. We took 20 reports for the POC case (these were actually SAP BusinessObjects Web Intelligence reports, but you can imagine whatever tool you like as long as you can export to PDF and / or Excel). A current version of the PDF or Excel output of the report is compared with a corresponding reference file. Typical real life situations where you can use this scenario are:

  • recurringly scheduled regression tests to monitor side effects of ongoing DWH changes: The reference files are created somewhen e.g. after a successful release of the DWH. Imagine there are ongoing change requests on the level of your DWH. Then you want to make sure these changes only impact the reports where a change is expected. To make sure all the rest of your reports aren’t concerned by any side effects, you now run your regression tests e.g. every weekend and compare the hereby produced files with the reference files.
  • BI platform migration projects: If you run a migration project for example to migrate your SAP BusinessObjects XI 3.1 installation to 4.1, you’ll want to make sure reports still work and look the same in 4.1 as they did in XI 3.1. In this case you create the reference files in XI 3.1 and compare them with the ones from 4.1. (As the export drivers vary between the two versions, especially the Excel exports are not very useful for this use case. Still, PDF worked pretty fine in my experience)
  • Database migration projects: If you run a database migration project for example migrating all your Oracle databases to Teradata or SAP HANA, then you want to make sure all of your reports show still the correct data (or at least the data as was shown with the original datasource…)
rbra_testing1 (3)

Sample configuration of a test case template using the GUI of Tosca (Source: IT-Logix POC)

Tosca searches for the differences between the two files. For Excel this happens on a cell by cell basis, for PDF we used a text based approach as well as an image compare approach.

rbra_testing1 (2)

Depending on the chosen test mode the differences can be visualized differently (Source: IT-Logix POC)

Using the solution implemented during the POC we could see very quickly which reports were different in their current state compared to the reference state.

Another important aspect of the POC was the scalability of the solution approach as I work primarily with (large) enterprise customers. If I have not only 20 but hundreds of reports (and therefore test cases), I have to prioritize and manage the creation, execution and error analysis of these test cases somehow. Tosca helps with the feature to model business requirements and to connect them with the test cases. Based on that we can derive and report classical test metrics like test case coverage or test execution rate.

rbra_testing1 (1)

Requirements and test cases are tightly related (Source: IT-Logix POC)

In my eyes an infrastructure like Tosca is a basic requirement to systematically increase and keep the quality in BI / DWH systems. In addition advanced methods like test driven development are only adaptable to BI / DWH undertakings if the necessary infrastructure for test automation is available.

In this blog post I’ve shown a first, rudimentary solution for regression tests for BI frontend tools. In a next article I’ll show the possibilities to implement regression test for DWH backend components.

Event recommendation: Learn about a real life scenario to run a SAP BusinessObjects migration project in an agile manner. Hence test automation is key and explained in some more details. Join me during sapInsider’s BI2015 at Nice by mid of June. Find more information here.

(This blog post was first published by me in German here)

Testing BO BI 4.x using the cloud

Update End of December 2012: Currently the ITX Migration and Demo Environment on Cloudshare is not available anymore for public parties. IT-Logix customers of course can still apply for a shared copy of it. The reason why I have to end the public offering is due to increased workload on one hand. On the other hand we need the current environment for our customer projects. Unfortunately Cloudshare did not respond to my request to offer us a free environment soley for the purpose of sharing our migration environment.

(Update October 15th 2012 –> current machine list)

(Update October 28th 2012 –> Patch 4.5 installed)

(Update October 31st 2012 –> graphomate inlcuding demo dashboard installed –> see Cloudsrv012 in folder graphomate – here you’ll find the user manual too; or open Dashboards on Cloudclnt01 and drag n’ drop the graphomate component to the dashboard to test it yourself!)

While I discussed general migration challenges in my previous blog, this blog addresses the fact that every new release (even just a service / support package) of SAP BusinessObjects needs intensive testing (by the way I’m not talking about versions in Ramp-Up but the regular available versions like currently BO 4.0 SP2). SAP seems to work based on the banana principle:

The product ripe with the consumer.

I could now elaborate on how bad this is and how much better other vendors do (do they really?). But I won’t. I would like to share an opportunity of how you can better cope with the circumstance that you have to test, test and once again test whatever you do with SAP BusinessObjects before you “go live”.

When SAP provided its HANA developer environment to partners and customers I came first to know cloudshare. In the meanwhile I’m quite enthusiastic about cloudshare! It was never easier (and cheaper) for me to create development and test environments having the choice out of a multitude of machine templates and afterwards full admin rights on all machines. But the best thing about cloudshare is that you can easily share a virtual server environment with others for free (at least for a first period of two weeks).

This inspired me to create what finally was named the “ITX BO 4.x Migration Assessment and Demo Environment”. This is a virtual server environment in the cloud. It allows for quick and easy to use «hands-on» tests of current and upcoming releases of SAP BusinessObjects BI products. You can import parts (or everything) of an existing BO content from your XI 3.1 system into the XI 3.1 system in the cloud (using BIAR-Files). Afterwards you can test a migration to BO BI 4.0 SP4 (or you can use BO 4.0 SP4 simply for its own sake) You can get your own copy of the environment for free during two weeks. Afterwards you need a cloudshare.com subscription to further use it.

The environment also includes an installation of the products 360View+ and 360Eyes from GB and Smith (www.gbandsmith.com). I highly recommend these two products in order to streamline your migration. There will be another blog where I will detail on this.

The 4 Available Machines

The Migration Assessment & Demo Environment consists of four machines:

  • BO XI 3.1 SP3 (Server + Client Tools + 360View + 360Eyes)
  • BO BI 4.0 SP4 Patch 5 (Server + Client Tools + Visual Intelligence + 360View + 360Eyes)
  • BO BI 4.0 SP4 Patch 5 (Client Tools, Crystal Reports 2011, Crystal Reports for Enterprise, Dashboards etc.
  • BO DataServices 4.1 + Information Steward

Request your Free Copy

Please contact me to share with you a copy of the current migration environment. You’ll find my contact information in the PDF here or use Twitter with @rbranger.
Please give me some key words why you’d like to use the environment and allow up to two working days to grant you access to a copy of the system.

You’ll receive an invitation email directly from cloudshare.com including a link.

Register on Cloudshare.com

Afterwards you need to open a free account on cloudshare.com:

After your successful registration please log in to Cloudshare ProPlus. Your environment is already starting up… Click on «View environment» to see more details…

Wait until all machines are up and running. In the meanwhile read the description and get familiar with machine names etc.

Let’s «own» the environment. Click on the corresponding button! On the right side you have now much more options available. The cloudshare.com license is now valid for a longer time than only the original two days.

Testing BO BI 4.0 SP4

Let’s start with using the client tools and BI Launchpad of BI 4.0 SP4. Select «Fullscreen RDP» from the drop down menu of «CLOUDCLNT012»:

The password of the BOE Administrator is always IT-Logix32
The SP4 CMS is running on cloudsrv012 on default port 6400
Here some helpful links:
Open the BI Launchpad at http://cloudsrv012:8080/BOE/BI

Find shortcuts to the available client tools on the desktop or in the start menu.

Cloud Folders

If you need to upload files (e.g. a BIAR file with your own BO content), use «Cloud Folders» to upload files using FTP:

On the virtual machine you’ll find a shortcut on the Desktop to access your cloud folders:

Have Fun and Happy Migration!

This is it. I hope you find this new opportunity useful. At least for 14 days you can use the environment for free. Afterwards you need to purchase a subscription at cloudshare.com. By the way this is nothing expensive and I wouldn’t give back mine… Regarding BO and 360 licenses there are only temporary keys part of the environment. I recommend that you use your own keys. In case you have no keys but would like to test drive BO or 360 products please contact me for an extended trial period.

My own cloudshare.com environment which is the base for the Migration Assessment Environment is sponsored by my employer IT-Logix. Please consider IT-Logix if you need dedicated expertise for your next BO migration project.