SAP Xcelsius Challenge for Community Poll Results – Best Practices

I entered the SAP Xcelsius Challenge for Community Poll Results to flex my Xcelsius skills and also offer some fresh options for dashboarding to the SAP SDN community. I’m pleased to report that my submission received an honorable mention in the competition. Thanks to everyone who voted!

In this post I will explain the tools, goals, content and structure that I decided to work with for this challenge and my best-practice steps for developing an Xcelsius dashboard.

1. Tool selection

I started by defining the high level goal, and selecting the tool that fits the best. SAP Crystal Dashboard Design (formerly known as Xcelsius) is a great tool and platform – obviously for dashboarding – but it’s not for all purposes. SAP BusinessObjects offers a wide range of tools that can  solve specific scenarios; these are well integrated and often a combination of two or more is the best solution.

2. Purpose & Detailed Goals

Why am I building a dashboard? What insight am I interested in?

Besides displaying the poll results, my goal was to allow performance analysis of continents, regions and countries participating in the Winter Olympics, with a focus on the games in Vancouver.

In my opinion, to ask and answer the above questions is very important and often overlooked in favour of defining KPIs first. Without setting a clear goal, I wouldn’t have been able to decide which KPI was more or less useful than any others. With that defined I proceeded to answer the next questions.

3.  Content

How can I achieve the above goal? What are the best performance indicators? How can I display these KPIs?

Possible KPIs

• Number of medals

• Number of gold medals

• Weighted number of medals (gold * 3 + silver * 2 + bronze)

• Number of medals / population

• Number of medals won by country / Total number of medals

Possible display options for KPIs

• Trends – Which indicator’s trend would be best to see?

• Comparisons – What would be the dimensions to compare? 

I chose the most common KPI: Number of medals. But as you will see, this single KPI can be displayed in a number of ways. A less traditional way – in terms of the Olympics – is grouping by geographical dimension. This gives a unique view, not to mention that it allows me to showcase my DrillChart add-on.

4. Structure

How can I best organize my content? 

At this point, I decided to summarize what information I had and try to find a place for my content on the screen:

• The mandatory poll results – vertical bar chart.

• Number of medals by region – horizontal bar chart

The Poll chart has all the sports listed, so it gives an opportunity to use it as a selector too. I thought it would be good to connect these two charts and allow the user to analyze the number of medals by sports as well. Although it might not be as clear as a horizontal navigation bar spanning the header of the dashboard, I opted to use it – with clear caption –  to save some screen real estate.

The order of selection would be: Sport → Region, so I put the Poll Results to the top left side, where the viewer generally starts scanning the screen. I used the two colors of the Vancouver games – green and blue – to make a clear distinction between the poll and the medal analysis.

Olympics Dashboard 1

The two main charts consume about half of the screen, and I still had a lot to show:

• Trend lines

• Distribution of medals – Gold, Silver, Bronze

• Historical aggregations

• Comparison to the previous game

All the above information is dependent on the user selections (Sport – Continent / Region) and gives more insight into the data. I used micro-charts under the main medal chart to show the details.

Olympics Dashboard 2

5. Implementation

The final part is the actual development. Luckily this is straightforward – and much faster – when the functional and layout design are well defined, although this is always an iterative process with some modifications.  

I was happy to practice a little bit on this example, and would be happy to hear your feedback! And if you are looking for some creative dashboarding expertise, Clariba has a wealth of experience and very talented consultants in this area. Feel free to contact us at info@clariba.com.

ETL Tips & Tricks: Connecting IBM z/OS with Microsoft Windows Server

When we want to connect an IBM z/OS with a Microsoft Windows Server for ETL process purposes, it is important to understand how the IBM Mainframe network resources are configured and how to link this particular information in to the Microsoft Windows Server.

In this post I would like to explain how to configure the SNA Manager Application that is installed with Microsoft Host Integration Server (HIS) as the main link service tool. We will also look at using the IP-DLC link service (SNAIP1 windows service) to communicate with the IBM mainframe.

Understanding the basics

IBM z/OS includes a communications server with multiprotocol networking. It provides the data stream transportation corridor between the external network and the customer business applications running on z/OS. It basically uses a combination of TCP/IP and System Network Architecture (SNA) functions.

Technically, once HIS is installed in the Windows server, it communicates with the Mainframe by establishing a TN3270 session using TCP protocol. IBM’s communications server will translate this TN3270 session to SNA protocol. The application connection will be completed by acquiring a Logical Unit (LU) on behalf of the TN3270 connection created by HIS. This way, an LU-LU session is established between the communications server and the HIS.

All of the IBM processes are performed under a Virtual Telecommunication Access Method (VTAM) platform which provides the SNA layer network communication stack to transport data between applications and the end client.

APPN and APPC

The most common network topology configuration is the Advanced Peer-to-Peer Networking (APPN) defined by the VTAM Control Point (CP).  Its capabilities are related to enroot data in a network between two or more LU’s that do not need to be directly connected. The APPN network is enhanced with the Advanced Program-to-Program Communication (APPC), which defines the rules of how programs exchange information. This protocol is known as SNA LU 6.2. In other words, APPN defines how APPC traffic gets from one point to another in a network regardless the network configuration.

Configuring the SNA Manager

Once the HIS 2006 application has been installed in the Windows Server successfully, we have to run and configure the SNA Manager program.  This tool is designed to streamline the process of creating and managing connections to data sources in the host environment.

Once we have started up the SNA Manager program, the first step is to create a Microsoft SNA connection from the console root. Once created, Open SubDomain and select the primary configuration server. Once primary configuration server is defined, adding a new link service is mandatory. Two sections have to be defined: “Link Services setup” and the “SNA Service”. 

In the Link Services setup (managed by the SNAP1 windows service), first define the address as the primary network node server (NNS), then define the actual network name and its Control Point name in the local APPN node section. Finally, confirm “Use dynamic PU definition” check box so that independent LUs will be defined dynamically. LU’s are controlled by a Physical Unit (PU) in each node of an SNA network.

With the required link service already set up, individual resource connections between the  HIS servers and host servers must be configured as well. This is achieved by configuring an SNA service connection.

There are three steps for each SNA server connection setup (Connections, Local APPC LUs and Remote APPC LUs):

1. In connections, define the name for the connection, specify SNAP1 as the link service and “peer system” as remote end type of host system. Typically leave the rest of the tabs by default.
2. For the Local LU definition (our server), enter an 8 character length LU Alias and LU name which will identify the local system to other components on the SNA network, and specify the actual Network Name. Leave the advanced tab with default values.
3. For the Remote LU definition (IBM side), enter the LU alias and LU name, the Network Name, and Uninterpreted LU Name. These values should be requested from the SNA Network administrator. Finally in the Options tab, check “support parallel sessions” to increase the number of available connections, define QPCSUPP within “Implicit Incoming mode” as the way the Remote APPC LU is partnered with the Local APPC LU, specify the required SNA security level and uncheck Enable Syncpoint check box unless you have a Local APPC LUs configured for SyncPoint support.

It is highly recommended to run and configure the Data Access tool as well, (created by the HIS installation), as it provides an excellent opportunity to test the database connection by using a Universal Data Link (UDL) file for OLE DB connections.

After setting up the Data Source connection and linking an active SNA server with an active SNA service connection, you should be able to successfully connect to the Mainframe.

If you have any questions or feedback about this post, please feel free to leave a comment below.

Xcelsius Suggestions: Searching and Filtering

Every day we face new filter challenges depending on our customer requirements. Sometimes the answer, “it is not possible” seems to be the most logical, but after a few minutes of brainstorming, the solution becomes clear.

Xcelsius became famous thanks to its what-if analysis and filtering options. In this article I want to share the solution of searching and filtering within Xcelsius by using a couple of out of the box components.

This feature allows the user to type part of the text needed for a filter, get the list of values(suggestion), select one of them and get the output in the dashboard.

To build this solution the developer only needs 5 components (the graph, the image and the white background have been added for the demo).

 

1. The input text box is used to write the text to search/filter
2. In the excel a “like” command is implemented as =IF(AND($A$1<>"",COUNTIF(A3,"*"&$A$1&"*")>0),1,0) where we are checking if the text written in the cell A1 is contained in A3
3. The other components are meant to filter and move the desired value from the source to the destination

The result is the following:

 

And by clicking on Luca from the list of values, the result is:

 The limitation of this solution is that the user has to click on enter after typing the text in the input text box. A better solution could be written using Flex and ActionScript in order to have only one component doing all the work and without the need to press enter at any selection.

If you are interested in seeing the solution source and flash files, feel free to download the samples below. Please let us know if you have any feedback by leaving a comment below.

X-suggestions (swf) X-suggestions (zip file containing xlf)

New Insights into an Xcelsius (XLF) File

Xcelsius is a powerful dashboarding tool, but sometimes we find limitations and need to use our imagination to find workarounds.  One example is when we are developing a dashboard without connection to the final production server using QAAWS. Before the rollout we need to open the XLF file and remap the connections one by one to the new server.

One day, by chance, I found a very nice solution to this problem. I also found out about extra capabilities that are not available through the Xcelsius interface.

Uncovering new capabilities

Getting straight to the point, there is an XLF file at the end of a standard archive file. All you need to do is rename it to .ZIP and extract it in any folder. You will find the following files inside:

• Xldoc: This is the Excel Data model used in Xcelsius (you can open it using Excel)
• Document.xml: This contains your Xcelsius definition (graphs, canvas, connections…)
• Folder(long name): This contains the pictures embedded in your Xcelsius file

The most interesting file for the purposes of this article is the Document.xml. In this file you will find the entire definition of your Xcelsius model; which provides great capabilities and opportunities! Let me give you three examples:

1. You can replace the server of your QAAWS connections: just search for your server name to find the entries of your QAAWS connections (with the credentials if they are hardcoded in the model) then replace them for the new server.
2. Change the color schema without fighting with the Xcelsius client: To change the color schema, all you need to do is replace the old color with the new one in the XML file and you are done. If you have defined a customized color schema you will also find it in the file.
3. Change the logos/pictures: You just need to overwrite the logos that you get after unzipping the XLF.

You can play around as much as you want with these files. To get your new XLF file all you need to do is add the folder back to a zip file and rename it to XLF!

If you have any feedback, questions or new functionality for this trick, I will be happy to read and reply your comments!

LiveOffice Trick for Xcelsius Developers and Designers

LiveOffice is an out of the box tool from SAP Business Objects that allows users to download reports or parts of reports from BusinessObjects (WebIntelligence and Crystal Reports). It also allows for the creation new queries like QAAWS (query as a web service).

In this blog post we would like to share a tip about an important LiveOffice setting that may be useful for Xcelsius developers and designers.

By default, LiveOffice allows us to download 512 rows and 512 columns when it is used within Xcelsius. Unfortunately this number or rows is not always enough. But rather than change the connections or the logic of our dashboard, which can be complex and time consuming, all we need to do is change the configuration of a .jar file in tomcat.

Step by Step Procedure:

1. Create a backup of dsws-liveoffice-ws.jar from server's InstallDrive:program filesbusiness objectsTomcatwebappsdswsbobjeWEB-INFlib (just in case)
2. Copy dsws-liveoffice-ws.jar from server's InstallDrive:program filesbusiness objectsTomcatwebappsdswsbobjeWEB-INFlib to your computer
3. Rename the file just copied to dsws-liveoffice-ws.jar.zip (add .zip to end of filename)
4. Create folder in root of InstallDrive: named META-INF (case sensitive), in InstallDrive:META-INF named BusinessObjects (case sensitive) and in InstallDrive:META-INFBusinessObjects named DSWS (case sensitive). You will obtain the following path: InstallDrive:META-INFBusiness ObjectsDSWS
5. Using WinZip or WinRAR, unzip the file named liveoffice_config.properties from the dsws-liveoffice-ws.jar.zip file copied from the server. Place this file in InstallDrive:META-INFBusinessObjectsDSWS
6. Open the liveoffice_config.properties file and find the following section: maxRowCount=512 maxColumnCount=512
7. Edit the maxRowCount and maxColumnCount (if needed) with the number of rows and columns that you prefer.
8. Save the file as liveoffice_config.properties (same filename)
9. Make sure that the new file is in the dsws-liveoffice-ws.jar.zip
10. Remove the .zip from the file
11. Copy it to the Tomcat location in the server: InstallDrive:program filesbusiness objectsTomcatwebappsdswsbobjeWEB-INFlib
12. Restart Tomcat

That's it! Now you are no longer limited to 512 rows and columns. If you have any questions or feedback about this approach, please feel free to leave your comments below.

Visualizing a time series with missing data

From time to time I browse through specialized forums, and recently I came across the same issue in two threads from different angles. Basically the question posed is, “how can we display missing values in a line chart?” This question comes into play when we consider what to display if we have a gap.

Missing data may occur for a number of reasons but for now let’s concentrate on the visualization. I will sketch a couple of solutions using Xcelsius, but the general idea of these options applies to other tools as well.

First let’s take a look at the possible initial situation: if we don’t have data for one or more periods in the database.

The user might not even realize it, but something is wrong with the above chart. The 3rd, 4th and 5th months are missing. The reason behind this may be that sales dropped to zero, or that sales for these months are not appearing in the database for whatever reason.

The two options below highlight that something went wrong with the sales (data) during the spring period (months 3-5).

Even though the last chart shows the actual situation in the database, the business users may expect to see a complete chart. If we know that sales did not drop to zero, but in fact records were lost for these three months, then the requirement is to connect the gap somehow.

Calculating an average or weighted average from the two closest values of non-null periods could be the solution. The weighted average is slightly more complex but in the case of a bigger gap, it will result in a linear connection between to two ends, as opposed to a flat line.

When interpolating values that we don’t have (and when we don’t know exactly what needs to be highlighted), I would suggest displaying the gap with a significantly lighter color than the normal, representing that these are calculated values.

Although actual values might differ significantly, in my opinion with proper legends, this representation might be better than breaking the line.

I would be interested to know if others have come across this issue and what you have done. Feel free to leave a comment with your opinion.

Export2Excel v3 for Xcelsius 2008 SP3 (Tomcat version)

About eight months ago we posted an Xcelsius solution called Export2Excel on the myxclesius.com blog. Thanks to support and comments from the Xcelsius developer community and some serious investigating, it is a pleasure for me to announce that the new Export2Excelv3 for Xcelsius (Tomcat version) is now up and running. Version 3 works with Xcelsius 2008 SP3 and it supports multiple languages. You will find the Export2Excelv3 files at the end of this post.*

For those who are new to Export2Excel, this component allows BI Developers to add export functionality to an Xcelsius dashboard so that end users can then filter results by selecting part of their dashboard. It is a great option for users who want to look at data outside of the predefined dashboard components.

Xcelsius 2008 SP3

When SAP Business Objects released Xcelsius 2008 SP3, we noticed that there was a change in the exported xml string. Xcelsius is now exporting the xml on different lines instead of a single line. Thanks to the help of other contributors we reached a solution that involves reading and converting all the lines. As a result, our new Export2Excelv3 only works with Xcelsius 2008 SP3. If you still have the SP2, please contact us for the v2 file (also with multi-language support).

Export to Multiple Languages

The new Export2Excelv3 can export multiple languages (e.g. Arabic, Japanese, Chinese and languages with special accents such as ö ü ÿ ä ý). We decided to add this to the new version as some of the users were asking about this functionality. In addition we are doing a lot of work in the Middle East so it is interesting for Clariba to have the Arabic characters in case any of our customers are interested.

How to Configure Export2Excelv3

Follow the steps below to configure the Export2Excelv3 solution in your Xcelsius dashboard:

1. Download the WAR file (Find the link at the end of this post)
2. Copy the ExportToExcelv3.war into your Tomcat webapps folder
3. Open the Data Manager
4. Add XML connection
5. Set the XML Data URL as http://<servername>:<port>/ExportToExcelv3/index.jsp
6. Check the “Enable Send” function. This will contain the range of cells that you choose to export.
7. Check the “Enable Load” function. Be sure that the Name is URLDummy (as highlighted in red in the screenshot below), and that the Range contains just one cell. This cell is the URL of the exported Excel file.

Note: Add a refresh connection and URL button on the Xcelsius dashboard to open this link.

Clariba Sample Dashboard

At Clariba, we have merged the functionalities of a refresh connection button and a URL button. As a result of this customized functionality, users can open an Excel file and begin their own analysis and comparisons if desired.

You will see the archive rar file for the code below. Just save and rename it to ExportToExcelv3.war and deploy it to your tomcat! If you have any questions or comments about the new version, just let me know.

Download: ExportToExcelv3.rar

* Copyright (C) 2011 Clariba. This free solution can be used, published or redistributed with appropriate credit to Clariba.

Migration from BusinessObjects XI R2 to XI R3.1 – Both Versions on the Same Machine: Disaster Recovery

In the past few weeks we have been working on a migration from BusinessObjects XI R2 to XI R3.1. This should be a simple upgrade that can be implemented by following the  BusinessObjectsXI R3.1 wizard. But what happens if something goes wrong during the upgrade? We were asking ourselves this question before migrating production for one of our customers. Then an idea came to us: we decided to leave BusinessObjects XI R2 as it was and also install XI R3.1 on the same machine. For this to be possible, we had a couple of things to consider first:

- Two tomcat servers working on the same port - BusinessObjects XI R2 and XI R3 services working on the same ports

Moreover, we decided not to use any BIAR files to migrate the BusinessObjects content to the new release.

Our Implementation Steps:

1. We stopped Tomcat and BusinessObjects XI R2 servers (and made sure they wouldn't start up automatically in the future).
2. Next we installed BusinessObjects XI R3.1 (during the installation we selected new installation instead of upgrade). Note: If you have Tomcat on a different machine then choose to install your web server later  (XI R3.1 allows the users to install only the webserver and connect it to the CMS by using the setup wizard).
3. Once the installation was complete we copied the FileStore folders (Input and Output) to the new locations (Business Objects Enterprise 12/FileStore/).

Configuring the CMS database:

1. From the SIA (Server Intelligence Agent), go to properties -> configuration  -> setup of the database using the Specify button.
2. Select the option to copy the database from another Data source.
3. Select that the source comes from BusinessObjects XI R2 and choose the R2 database.
4. BusinessObjects XI R3.1 will copy and adapt the R2 database to the R3 one.
5. At the end of the database configuration BusinessObjects will ask you to restart the servers and update the objects (it will interface the CMS database with the FileStore folders).

The user can now login to Business Objects R3.1 and check that the security, the schedules and all the objects are there and working fine. If the environment is not working as expected, then you can stop the new Tomcat and the BusinessObjects XI R3 servers and bring up the old Tomcat and the BusinessObjects XI R2 server once again without losing any data. The recovery takes less than 5 minutes.

We are always interested in feedback. Please let us know if you have any questions or comments about this XI Migration trick.

Best practices for geo-analysis using AnyMap add-on for Xcelsius

This article explains how to design a professional-looking flash file using the famous freely downloadable AnyMap add-on for Xcelsius.

A multitude of opportunities exisit for using AnyMap in Xcelsius. Typically we want to embed an interactive system like a layout for a car park, airport, or a typical regional map - see Figure 1 for an example. Using AnyMap we’ll be able to show different information with a mouse over or a mouse click.

The idea here is to explain the challenges that typically appear in Xcelsius, such as speed, image resolution and accuracy, and to demonstrate that by using AnyMap we can optimize them all.

Using the AnyMap add-on

Without the use of AnyMap, an Xcelsius designer can start using single image components, locating them carefully and using dynamic buttons together with the show and hide feature.

When we do this, there is a clear performance issue: the flash file takes some time to handle regions and data. To optimize this behavior, the quality of the image can be reduced; but this results in a less professional look and feel. Moreover, and especially for maps, there is no button component that fully adapts to regions, so users may be annoyed by the fact that they don’t get a response when clicking on the borders of the regions.

The objective of AnyMap is to offer new features to Xcelsius designers that make professional geo-mapping possible, enhancing performance and reducing post-project maintenance costs.

Image resolution, speed and accuracy

The best option is to place your template outside the AnyMap component, because the embedding option sometimes distorts the image quality. A high quality .jpg is better than a huge .bmp, as we want to keep the flash file size small.

High performance and accuracy are achieved using an .svg file that contains the closed perimeters of all areas and their names. This step is key and involves 1/3 of the effort for the project. This can be done using a tool called GIMP (as recommended and explained in the AnyMap manual). An .xcf source will be created and right after saved into an .svg file

This approach using one single template makes the system incredibly fast because one area item is handled instead of many items. It also improves accuracy as every pixel inside the region is sensitive to mouse moves and clicks.

AnyMap configuration and formatting

Once we have the .svg file, AnyMap is very easy to configure:

1. Define .svg file: The file will be located in the same flash file folder, and no path is needed.
2. Display data: Typically a column with the names of the regions might be provided, together with the mouse-over texts that we need to show and a selected region key in case of mouse clicking.
3. Mouse over color change effect: As a sample, turn transparency to 10% and create an alert with a grey color.

A fully configurable system that shows different texts with the move of the mouse and changes the value of a cell according the clicked area has to contain the following information (at minimum), as shown in Figure 3:

Using a typical lookup Excel function we can change the value of a cell according to a click, and show/hide other components according to this value, or change a text from an existing text component.

Unfortunately the formatting is very rigid and limited in this component. But a workaround for this is to use the HTML capability so the content of a cell is a text in itself. We can therefore change font, size, manage paragraphs and embed images in our texts, to make them look absolutely professional.

Conclusion

The methods explained in this article prove that although this product is not supported by SAP BusinessObjects it can be used by developers (at your own risk of course) in a very beneficial way. It can save a lot of man-hours and offer features that the out-of-the box Xcelsius software doesn’t yet provide.