Testing for BI & DWH – Part 1

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)

How to speed up connection between Translation Manager, Universe Designer and CMS

At a recent customer project my teammate and I were facing an issue with importing Webi documents and universes into Translation Manager (all on BO4.1 SP4) getting the following error message:

org.apache.axis2.AxisFault: Unable to find servers in CMS, servers could be down or disabled by the administrator (FWN 01014)

There is a KB article describing this error and a solution: http://service.sap.com/sap/support/notes/1879675

One hand we followed the instructions in the KB article and specified the hostname for each server. Although our server is multihomed (that means has more than one network interfaces) we didn’t thought about this before hand because the second network interface goes into a backup LAN and is never used for communication with e.g. the client tools. In addition everything worked fine so far – until we got the issue with Translation Manager. Still, just setting the hostname was not enough. On the server side there is the Windows firewall enabled, therefore we had to assign static request ports to several services. We already did so before our issue with the Translation Manager for the CMS, Input & Output FRS, all Webi Processing Server. We used TCPView to analyze on which ports Translation Manager was opening connections. As long as there were requests on ports which we didn’t specify in the CMC (some random port number e.g. 56487) we narrowed down all the services which Translation Manager establishes a connection. We had to specify a port for all of the following servers:

  • The Adaptive Processing Server (APS) hosting the DSL-Bridge Service
  • The APS hosting the Client Auditing Proxy Service
  • The APS hosting the Search Index Service
  • The APS hosting the Translation Service
  • The WACS (Web Application Container Server)

Besides the issue with Translation Manager we had another issue with creating new universe connections in the Universe Design Tool. When creating a new connection we had to wait up to 30 minutes ( ! ) to get to the wizard page where you can select from the list of available database drivers. Still, after 30 minutes everything worked fine and could create the connection successfully. Based on our experience with Translation Manager we run again TCPView and found that we need to assign a port number to both connection servers (32 and 64 bit) in CMC. Having done this, creating a new connection now works without any waiting time.

After all: If you have firewalls between your BO server and client tools, just assign a port to all servers available and open the port on the firewall (the only exception might be for the Event Server as I’m really not aware of any communication between this server and a component outside the BO server)

Optimizing my dashboard: Creating a visual draft

Remember my last post about the Webi dashboard? As mentioned at the end of that post aimed to show a technical trick to put an interactive kind of a button onto a Webi report. Now this post is the first in a series of posts how to optimize the layout of the initial dashboard. Let’s start with creating a draft of our optimized dashboard layout. The advantage of such a draft is that it is not yet implemented with the actual BI tool but using either pen and pencil or a wireframing tool. I did the later and chose the cloud edition of Balsamiq. To get a quick start you can use a 30 days trial account.

Let me explain briefly how the tool works. After that I’ll explain some of my thoughts behind the chosen layout.
Within the editor you can drag and drop sketched objects like a window container, rectangles, text, buttons etc.


My dashboard in the Balsamiq editor

Looking at the available chart objects, I’m not really satisfied:


Default chart elements in Balsamiq

Therefore I added my own images representing typical IBCS chart types (IBCS stands for International Business Communication Standards. I wrote a short blog post about IBCS here.). The images are based on the graphomate add-on for SAP DesignStudio and BusinessObjects Dashboards.


Typical IBCS chart types (created with graphomate)

After all, my inital dashboard draft looks like this:


My dashboard draft (chart view 1)

At the top you can see some reserved space for an appropriate title. Providing this is a major requirement stated by IBCS as one of the top ten proposals:

I adapted this to a BI specific title concept where I distinguish between general title elements (like the organization or global query filters) and object specific titles, e.g. for the table or a chart. For the table I used the default element of Balsamiq, maybe I will update this later on with an IBCS optimized one. For now it is just a placeholder.

The charts in this first view will show current year values and previous year values (where as the current year will be indicated within the global filters area) for revenue and margin. To make the analysis of available data more straight forward, I decided to add two variance charts, one for absolute values and one with percentage values. Again, this is one of the top 10 elements in IBCS:

You might have discovered the symbolic button to switch the chart view. The second view looks like this:


My dashboard draft (chart view 2)

The header and table areas stay the same. In the lower area with charts I now show historical values for revenue with actual and plan values. Instead of putting everything into one big chart I decided to use small multiples for the top 5 product lines (based on total revenue over time) as well as one chart for all other product lines. Depending on how it will look like in Webi we might decide to show more product lines or add another topic to the dashboard (as we still have space left on the bottom right corner).

In this blog I showed how to create a simple dashboard draft using a wireframeing tool like Balsamiq. In addition I pointed out how to apply two of the top ten IBCS proposals in this conceptual phase.

Update May 5, 2015: Internally at IT-Logix we continued the implementation of the above shown mockup; we used both Design Studio and Web Intelligence. Unfortunately the time for writing a blog post was missing so far. Still, if you are interested in the Web Intelligence part, have a look at the following sapInsider BI2015 session held by my teammate Kristof Gramm: http://bit.ly/1EGKFtA He will show how you can do pretty amazing things with Webi (by the way, using this link you’ll be granted a 300€ discount on the conference registration ;-)

A (Webi) dashboard built by a business (power) user

This blog post is inspired by a recent customer request to challenge their decision to use Design Studio for some “dashboard requirements”. Showing how you can create a dashboard in Webi doesn’t mean I told the customer not to use Design Studio. Much more it is to show that finally a dashboard as well as every other type of BI front end solution is made up of requirements and not primarily by the tool you build the solution. Please refer to my Generic Tool Selection Process for more details as well as my post regarding BI specific requirements engineering.

Having said this, let’s have a look at how we can use latest Webi 4.1 features to quickly build an interactive dashboard without the need of (much) scripting. First of all here is what the final result looks like:


You can select values from the left side bar (Product Lines), you can select States by directly clicking into the table and you can switch from the bar chart to a line chart. Here you see it in action:

The first step to achieve this, is to create the basic table and the two charts. Until the dynamic switch is implemented, I placed them side by side. Next add a simple input control in the left side bar:

02_SimpleInputControl 03_SimpleInputControlDepend

Next thing is to define the table as an additional input control – right click the table and choose “Linking” and “Add Element Link”,  choose the two chart objects as dependencies:

04_TableAsInputControl 05_TableAsInputControlDepend

Next we need to create the “switch” to toggle the two charts. As I would like to position this switch at the top right corner of the chart, I again use a table input control. To generate the two necessary table values (namely “Bar Chart” and “Line Chart”) I prepared a simple Excel spreadsheet:


In 4.1 you can now finally upload this sheet directly into the BO repository:


If you need to update the Excel sheet later on, this is now feasible as well:


Finally, in Webi add the Excel sheet as a second query:

10_ExcelQuery    10_ExcelQueryDetails

In the report we need now two tables: A visible one to represent the chart switch and a (hidden – see the “Hide always” option) dummy table to act as a dependency for the first:

13_HiddenDummyTable  12_HideDummyTable

The most tricky part is to create a variable to retrieve the selected value:


Here the formula for copy / paste:

=If( Pos(ReportFilterSummary(“Dashboard”);”Chart Type Equal “) > 0)
Then Substr(ReportFilterSummary(“Dashboard”);Pos(ReportFilterSummary(“Dashboard”);”Chart Type Equal “) + Length(“Chart Type Equal “);999)
Else “Bar Chart”

(The idea for this formula I grabed from David Lai’s Blog here)

Finally you need to configure the hide formula for both charts:


That’s it.


Positive: I’m not too technical anymore (I do more paperwork than I wish sometimes…). Therefore I don’t consider me a “developer” and I like solutions for the so called “business (power) user” more and more. Therefore I like Webi. It took me about 60 minutes to figure out how to create this kind of interactive dashboard. I didn’t need to install anything – I could do everything web based. Except for one single formula (which I didn’t need to write myself)  I could click together the above sample. And I dare to say it looks like some kind of a dashboard :-) In addition I have all the basic features of Webi like a broad range of data source support, plenty of export possibilities, Office integration and so on. Even integrating an Excel spreadsheet as a data source is now finally a no-brainer.

Negative: Clearly, Webi is not a “design tool”. For example I wasn’t able to show icons for my chart switch instead of the text lables. Putting a background image to the table doesn’t work well if the table is used as input control. When I discussed this prototype with the customer they also mentioned that there are still too many options end users might get confused with (e.g. that there is a “filter” section showing whether the Bar Chart or the Line Chart value is chosen). In Webi you can’t change that. Toolbars, tabs etc. are just there where they are. Live with it or choose a different tool.

Bottom line: Have a look at my Generic Tool Selection Process and the mentioned hands-on test. The above example is exactly what I mean with this: Create a functional prototype in one or two tools and then do a fact based decision depending on your requirements and end user expectations.

Important remark: This post focused on the technical aspect of the dashboard. The visual representation doesn’t yet fit to best practices mentioned in my earlier articels (e.g. about SUCCESS) In a next blog post I will outline how to optimize the existing dashboard in this regard.

Join my teammate Kristof Gramm during sapInsider’s BI2015 conference at Nice (June 16-18): He will go into much more details about how you can use Web Intelligence as a dashboard tool for business users. Use this link to see more infos and save 300€ on your conference registration!

Getting to Know the German Monster – The Importance of Proximity in a Globalized World

Just recently I published a blog about the following topic on sap.com

Thanks for reading!

IBCS – an emerging standard for business #dataviz

In my previous post I introduced the SUCCESS model of Rolf Hichert. In this post I’d like to introduce you to the subject of IBCS – the International Business Communication Standards. IBCS’ aim is to “foster the level of understanding in reports and presentations” (Source: IBCS). Currently, IBCS consists of two main parts:

Notation of meaning
The part “Notation of meaning” describes basically the semantics of a standardized business communication language. It covers all aspects of meaning in the context of business communication and suggests an appropriate notation.

Design of components
The part “Design of components” covers rather the syntactical aspects of a standardized business communication language. It describes the basic report elements and specific rules to use them for the design of objects such as tables and charts. Several objects and additional elements make up complete pages. Although this part should not consider aspects of meaning, but only define the “grammar” of a unified communication language, some overlap to the “Notation of meaning” (semantics) is inevitable. (Source: IBCS)

IBCS and SUCCESS are closely related, therefore I will start with some explenations about how the two topics are positioned against each other. First of all SUCCESS and IBCS can be looked at from a time perspective. Clearly, SUCCESS was developed first:


From this point of view IBCS  is a refinement of certain aspects of SUCCESS (namely the Unify part in SUCCESS). This refinement can be seen in the following perspective too:


SUCCESS provides only generic guidelines without concrete implementation details, e.g. the following one:


Souce & Copyright: HICHERT + PARTNER

This guideline just tells you: If you visualize this year’s revenue with blue in chart number 1, you should also use blue in chart number 2. But the guideline doesn’t tell you to always use blue. If you wish to take brown for current revenue, it’s OK. Just use brown whenever you visualize current revenue. This and many of the other guidelines (some of them are mentioned in my previous blog post) in SUCCESS I therefore attribute to what I call “common sense” or data visualization basic recommendations. Now IBCS comes into play. Whereas SUCCESS is rather generic IBCS defines the details and writes them down. As indicated above IBCS does this in mainly two parts:

– the notation of meaning clearly defines which elements have which meaning:


Source: IBCS

This way business communication should be simplified through standardization the same way as you are used to it with geographical maps for example. Blue always means water, north is always at the top of the map etc. This “semantic layer” is something which differentiates IBCS from other data visualization and information design concepts. Most if not all of them focus on generic recommendations only. In contrast, IBCS wants to harmonize the visualizations in a business context and make them therefore easier to understand.

– the second aspect of IBCS is the design of components

Let’s have a look at the SUCCESS guidline first, e.g. for chart design:

Source & Copyright: HICHERT + PARTNER

Source & Copyright: HICHERT + PARTNER

IBCS gives you much more information, e.g. regarding legends:


Source: IBCS

You can look at IBCS and SUCCESS from a third perspective too:


In this perspective IBCS is the solid base with all the detailed rules to consider for efficient data visualizations in business communications. Yet the IBCS rules are hard to digest (as every other industry standard…) SUCCESS can be seen as an implementation methodology of them (as IBCS and SUCCESS are mostly congurent to each other). SUCCESS is an acronym of seven verbs – if you act on them, you’ll see that implementing IBCS is pretty straight forward.

IBCS is work in progress and it is open-source (based on the Creative Commons BY-SA license). Its further development is orchestrated by the IBCS Association which again is run by HICHERT + PARTNER. Simply create a login on ibcs-a.org and start to add your own ideas to further refine and extend the standard.

If you want to learn more about IBCS, SUCCESS and how it can be implemented, join me during the BOAK conference this autumn. It takes place on Tuesday, September 16th in Zurich / Switzerland. In the data visualization track (sessions E1 to E4) you’ll find several sessions dedicated to IBCS. Jürgen Faisst, CEO of HICHERT + PARTNER will elaborate on the goals of IBCS. My friend Lars Schubert will demonstrate how you can apply IBCS using the IBCS certified software “graphomate” as part of SAP Design Studio or SAP BusinessObjects Dashboards. I myself will present together with a customer showing how we implemented an IBCS oriented design in Web Intelligence. The day after I will teach these best practices during a one day workshop. A sneak peek on the Webi reports you can find on my Hichert Certified Consultant page. Looking forward to seeing you during the BOAK conference!

What do you think about IBCS and the semantic layer? Do you think it is worth the effort to harmonize data visualizations in a business context? Just add a comment now!

Wishlist for SAP BOBJ tool consolidation

Obviously the multitude of BOBJ client tools is still a hot topic – both for SAP as well as its customers. From SAP’s side we’re hearing now about their decision to consolidate BOBJ / BI client tools. There are a lot of rumours during the currently held SAPPHIRENOW conference like the following:

JayneTool roadmap

There was a Google hangout session last week with Steven Lucas, President of Platform Solutions at SAP. In this article I will summarize some key points from this talk and add my own thoughts.

Have a look at the video at about 23:00. Steven mentions that there are currently 21 BI client tools in the BOBJ/SAP portfolio. That’s why SAP already took  the decision: “We are going to consolidate our BI tools”. In addition Steven mentions that they won’t deprecate core technologies like Webi or Crystal, but maybe integrate niche solutions like Explorer into Lumira. I personally like the statement about “feature preservation, not tool preservation”.

Although there are good reasons why SAP should consolidate their BI client tool portfolio I’d like to point out where I see the root cause of the problem: Definitely the number of different tools is not the real issue. I often use the comparison with the automotive industry. Just have a look how many models certain car manufacturer have in their portfolio:


Different cars for different purposes. Different tools for different purposes. But what’s different between the shown car portfolio and SAP’s BI client portfolio? All the cars share some basic functionality like four wheels, a steering wheel, head lights etc. For the BI client portfolio we still lack some basic functionality to be included in every tool more or less in the same way: One main issue is the missing homogenity in terms of data access. For relational data the Universe might be seen as a common base. But not even 10 years after Crystal Reports was bought by BusinessObjects, and not even in the new Crystal Reports for Enterprise version which was built from scratch we see equality of how you can connect to datasource compared to e.g. Web Intelligence. The same with BW connectivity. When I was at the sapInsider conference BI2014 two weeks ago at Nice / France, I had to learn once again from Ingo Hilgefort that Web Intelligence lacks some basic functionality like Zero Suppression even now having BICS based direct connectivity to BW. The same with HANA connectivity where Crystal supports HANA connectivity using an OLAP connection but Webi doesn’t. The same with Web Services connectivity and I cut continue the list for a while. From an architectural perspective I just ask: Why?

Another commonly cited issue is the charting library. Still there is nothing like common charting capabilities, the different tools still differ quite heavily in terms of what they provide as chart types and options, not to talk about the missing option to plugin a custom charting extension to all BI tools but only specific ones.

To sum up this first part: SAP’s job isn’t done by simply reducing the number of tools. They need to make sure that the remaining tools share some commonly expected features. Don’t let data connectivity or charting options be the differentiator between the different client tools.

I already wrote certain blog posts about BOBJ tool selection. Whereas the later posts were tool agnostic, the first one was very concrete. In this article I outlined some major differences (especially short comings) between the tools. On this background let me formulate my personal wish list for the future BOBJ tool consolidation:

  • Merge Crystal Reports into Web Intelligence: I know, according to Steven’s statement above this won’t gonna happen as Crystal is considered a core technology. Still, give this thought a chance. There isn’t that much missing between Crystal Reports and Web Intelligence from a feature perspective. Conditional formatting, interactive alerts, some more export formats, hierarchical grouping for relational data and a more powerful formula language. Being a Crystal Reports consultant for more than 12 years I’m not really happy with this thought in a first instance as I really like the tool. But if imagine how I could leverage certain Crystal Reports features with the powerful capabilities of Webi, it sounds very promising to me.
  • Merge BO Dashboards / Xcelsius “visuals” and input controls into Web Intelligence, Design Studio and Lumira: Stop the thinking that “a dashboard” is a matter of the tool. From a business perspective a dashboard has more important elements than just to be fancy and highly interactive. Depending on the business requirements you can build a dashboard in many tools including Design Studio (more app style dashboards), Lumira (more the ad-hoc kind of dashboard) and Webi (if you want to have more sophisticated data  capabilities and the fully fletched platform support like scheduling / publishing, control user actions with rights etc.) So please share the visual components we find in BO Dashboards today to various tools like Webi, Design Studio and Lumira.
  • Merge Explorer and Lumira – and think about the “feature preservation”. Don’t forget to add the “Export to Webi” somehow to Lumira.
  • Merge LiveOffice into BO Analysis, Edition for Office. LiveOffice is still very powerful, but I think we don’t need two separate add-ons.
  • Merge Analysis OLAP into – I’m not sure, as I’m not very used to this tool. Regarding the BW connectivity issues I’d like to see the Analysis OLAP capabilities in Webi. And / or you can add an OLAP grid / control to Design Studio.
  • Merge the predictive tools like Infinite Insight and Predictive Analysis into one joint tool.

How do you rate my wishlist? How does your wish list looks like? I’m looking forward to reading your comments soon!

[Update June 12th 2014] The guys from EVTechnology wrote an excellent blog regarding their findings from SAPPHIRENOW. There you can find the following screenshot:


Not too far away from my wishlist though ;-)

In addition Tammy Powlas documented an interesting webcast regarding Crystal Reports for HANA. I just hope that at least the HANA direct connectivity will be added the same way to Web Intelligence…



Get every new post delivered to your Inbox.

Join 721 other followers