Improving the Performance of Xcelsius files with OpenOffice

During the past few months my coworkers and I have been working with Xcelsius on a regular basis to develop dashboard for our customers. Sometimes we face challenges when we generate a swf file from an xlf file in Xcelsius and we don’t know why. Other times, Xcelsius crashes during the generation of the swf file. Even when the swf generates correctly, we occasionally see dashboard performance issues (i.e. when we execute the Xcelsius swf file, the load time is very slow).  However, we have found a trick that can be used to resolve these issues.

In this post I will explain the results of two tests that we did to reduce xlf file sizes followed by the steps you can follow to achieve these results.

The main idea of this trick is to reduce the size of the xlf file using OpenOffice software. Let me start by showing you the test results:

For the purpose of this test, we created an Xcelsius file called TestCompression.xlf

First we exported the xls file from the Xcelsius file by selecting Data -> Export:

We then saved the xls file. As you can see in the screenshot below, this generated a 2,264 KB xls file, so our objective was to decrease this file size.

Next we opened the xls file with Microsoft Excel and without modifying anything we saved it using another name. We repeated the same steps but this time with OpenOffice. In the image below you can see the decrease in size of the xls file. The size difference between the original xls file and the OpenOffice xls file is quite significant.

Finally we imported the new xls file into Xcelsius by selecting Data -> Import

In the screenshot below you can see that we decreased the xlf file size using the OpenOffice xls, but the change wasn’t very significant. TestCompression-OpenOffice.xlf is 1,117 KB, compared to the original TestCompression.xlf which was 1,236 KB.

As a result, we decided to test with another xlf file, which included hard coded data, to see if the compression would be more significant. For the second test, we achieved the following results after completing the same steps as outlined above.

In this screenshot we can see a significant decrease in the file size of the OpenOffice xlf with hard coded data. The original file TestCompression2.xlf file was 1,241 KB and the final TestCompression2-OpenOffice.xlf file was less than half the size (577 KB).

As a result of these two tests, we observed the following:

• Each time we modify an Excel Sheet inside Xcelsius, the size of the xls file increases.
• When the original xls is very large, the decrease in size is more substantial when we use OpenOffice.
• If we have hard coded data in the Excel file, we notice a greater size decrease than if we have QaaWs (Query as a Web Service) or Live Office Connections in the Excel sheet.

From now on, each time we attempt to generate a swf and we have made modifications (to data or Excel formulas) inside the Xcelsius Excel Sheet, we follow these best practice steps:

1. Export from Xcelisus to xls file
2. Open xls with OpenOffice
3. Save it as xls file with new name
4. Import to Xcelsius

In terms of speed, we notice changes in the swf loading process especially if most of our data is hard coded.

Finally find below a summary of the results obtained:

 

If you have experienced a similar situation with your Xcelsius files, I would be interested to hear how you have managed to reduce the file size. Also if you have any suggestions or feedback about my methods in this post, feel free to leave a comment below.

Real Time Dashboards – Lessons Learned

There are some scenarios in which a fast pace is required for data monitoring. This is where real time dashboards come in. A real time dashboard can display information with a high degree of frequency. This information is used by the audience to make quick decisions to correct the behavior of certain foreseeable trends. Real time dashboards can also set off alerts based on business thresholds allowing the audience to react quickly based on up-to-the-minute information.

Real time business intelligence is relevant to multiple industries and departments. Dashboards can be used to monitor call centre performance, IT system usage, data security, dynamic pricing, and inventory optimization and even support fraud detection and risk management efforts. Based on our experience building a real time dashboard to monitor call center data, this article will highlight tips and lessons learned for anyone who is undertaking a similar project.

ETL

Scheduling Frequency In reality the dashboard is not real time. It is a batch job with a very short refresh/update frequency. In our case it was 1 minute, which is also a limitation on Data Services batch jobs. It is important to know these limitations and also to know how fast your entire ETL batch job can run.

If batch job total duration > batch job run frequency then you will create overlapping processes which can immediately cause two issues:

1. Overload your server or generate refresh slow downs

2. Create inconsistencies in your data if your ETL does not correctly do blocks and queues write process to DB tables.

Run Optimization Given the need presented above: batch job total duration < batch job run frequency, you must search for the optimal setting to run your batch job. In Data Services there are some situations that can easily speed up your job execution but there is a delicate balance you must consider. One example is the memory method it uses to run the batch job, which can be selected from:

• Pageable

• In Memory

Pageable

Also whenever you have a table comparison process, the performance can be optimized by running the comparison as a separate process.

Table comparison

The In method runs faster when your server has enough RAM resources, however if your server does not have enough free RAM it will overload and will not be able to catch up with new spawning processes, running lower and lower in physical memory until it causes a complete  server crash.

Tomcat

Memory management Tomcat, under determined circumstances, does not perform well with memory management and garbage collection. When executing several QAAWS every minute the memory it uses can build up very quickly. Any Tomcat service on a 32bit Windows environment will have a limitation of 1.2 GB of memory to allocate. Tomcat becomes unstable when it reaches that limit and new requests are still coming in at a constant rate.

There are several tweaks on Tomcat and JVM memory  that can be done to optimize this.

One example of these tweaks is the memory limits that can be set when Tomcat is started; these can be set using windows registry or run time modificators.

Cache Optim flag The QAAWS application that comes bundled with Business Objects has a limitation set to cache data requests. When the same request is done in short periods of time the cache data is sent instead of running the query again. To avoid this and get new data every time, you need to disable this functionality on the dsws application properties:

dswsproperties

To disable it, you need to set the qaaws.cache_dpresult.optim flag to false.

qaaws cache

Restart Script In order to keep Tomcat service from memory overloads it is a good practice to schedule an overnight restart that will force garbage collection. The script can be very basic or contain additional cleanup tasks.

Script

HTML Container Placing the flash file on  html container will allow you to execute some actions prior and during the flash file execution. You can run javascripts, pass mandatory flash variables (i.e. suppressing tomcat error messages when running qaaws), etc.

The most basic html container for an xcelsius flash file will look as follows:

Xcelsius flash container

Auto refresh Java script As mentioned before, an HTML container will allow you to run javascript programs on the Explorer window executing your xcelsius flash file. Many applications can be given to javascript but one of them could be the browser page auto-refresh function. With the refresh you can wipe out outdated errors from screen.

No Error flash Variable The no error flash variable is a new functionality on Xcelisus SP4. It allows you to set the pop-up error windows off in cases where the error is not directly related to Xcelsius logic, i.e. Tomcat outages, scheduled system restarts, etc.

To add this functionality to any Xcelsius dashboard you need to add the flash variable input to your xlf file first.

Flash Variables

Finally you also you need to pass the variable to the SWF as indicated in the HTML container code example above.

I hope these lessons learned are helpful to anyone working on a real time dashboard. Feel free to leave a comment or question below if you need more information on any of the tips provided in this post.

Working with multi-sections in SAP BusinessObjects Query & Analysis tools

Sections are traditional features in the SAP BusinessObjects Query & Analysis (QA) tools. They provide a list of items and their associated partial report, including table(s), graph(s) or both. This is very powerful, as you may imagine. As an example, a mobile phone bill is created from sections by subscriber, associating every subscriber with his/her own bill.

This blog article will describe a particular requirement together with a challenge that we faced in a real scenario and how it was resolved.

The initial requirement

In this scenario we were working with a telco company that had a systems containing its billing data. From this the requirement was to develop a table with every subscriber’s call details, as well as a graph with the last 12 months total consumption. Moreover, there were three other conditions:

• The subscribers are often grouped by company, so the total bill by company must be generated – same report –
• The resulting pdf must show Company / Subscribers, in this order, and allow users to navigate across the pdf
• At the request of the customer, the report must be done in the traditional Desktop Intelligence tool

The challenge

Starting a mockup with WebI, we built the query, dragged & dropped 2 sections (1 by company, 1 by subscriber), drafted the table and the graph, adjusted the width of sections manually so each one fell into one sheet, refreshed, exported to pdf and “voilà”, the draft seemed to accomplish our specifications.

PDF document saved from WebI

Pdf document saved from WebI with navigation to sections and subsections (each one in a different page)

When we tried with DeskI, we followed the same steps; setting the “start on a new page” and the “avoid break page in block” to active, but we still ran into the issue of a blank page. This was due to the fact that between the section and the first item of the second subsection, the "start on a new page" was not applied automatically, so we had to create it ourselves. As our bill layout was quite large (and took up the whole A4 page), the report ran onto the following page (that is to say, the end of the section), so an empty page was generated just after.

Manual editing in DeskI

In DeskI the distance between the section and the 1st item of the 2nd subsection needs to be adjusted manually

Our solution:

The solution in DeskI was based on incremental actions. We have highlighted the advantages(+) and disadvantages (-) to give more insight into our situation:

1. Remove the “Start on a new page” option for the subsection (+) The blank page disappears (-) 1st sub-item navigation issue: The 2 lines from section / subsection appear in different pages, so PDF links do not work properly, as the button from 1st item in subsection stays in section page

2. Add a cell at the end of the first section table and graph (+) The 2 lines from section /subsection always appear in the same page, so PDF links work properly (-) In the specific case that a company does not have a subscriber, there is an empty page between companies, as 1st section ends up in the next page

3. Hide the auxiliary cell in cases when a company does not have a subscriber (+) Create a condition based on a count of subscribers inside the company, and hide the cells if that count is zero (-) None Conclusion

What we learned from this scenario is that traditional tools like Desktop Intelligence really do provide great flexibility but you have to have experts on hand who constantly push them to the limit. On the other hand, when possible we should tap into the power of Web Intelligence, which combines the Query & Analysis strength of its predecessor together with a functional capability tailored to the current web based world needs.

Feedback and questions are always welcome. If you have similar challenges, we would be happy to share our insight.

Connecting SAP BW and BusinessObjects Data Integrator for data extraction

In follow up to my blog article on July 7, I would like to share some insight for connecting SAP BW and SAP BusinessObjects Data Integrator for the purposes of data extraction.   

 The problem that I encountered was that I could not connect my BODS to SAP BW. The connection was correctly created into the Management console of Data Integrator but the start up was always failing. 

After what seemed like hundreds of tests and commands from the cmd, I found the solution: the services file contained in the same path as the host file (windows/system32/drivers/etc) requires a small change:

1. You need to add the following string SAPGWxx  33xx/tcp where xx is the system number of your SAP connection.
2. Then here I also configured the sapjco3.jar that is stored in tomcat (you will find it with an easy search in the folder) in the Tomcat CLASSPATH as per the previous topic posted on July 7.

To start the service I used a command from the cmd in the location: Drive:Business ObjectsBusiness Objects Data Servicesbin: RfcSvr -aRFC_ProgramID -g/H/ip or name of the SAP Application Server name/S/33xx -xsapgwxx   RfcSvr is the .exe file that starts the DI processes. If you want to know more details regarding this command, the best way is to do a quick search in Google.

After following the steps above, everything should work fine. At this point, you can use BW cubes as your data source in Data Integrator.

If you have any questions or feedback to add to this quick solution, please feel free to leave a comment below.

Connection between SAP BW and Universe Designer / BusinessObjects InfoView

In this blog post I will explain some tips that I learned while integrating SAP BusinessObjects Universe Designer/Infoview with SAP BW.

For the most part, the steps should be simple and quite standard (unless you face some unexpected issues).   First of all you need to install the platform and the integration kit for SAP. At the end of this process you will see that you can create universes on top of BW cubes or BW queries. You can easily publish the universe and retrieve your data in a report.

Now in theory, after configuring the user in the CMC (BO CMC --> Authentications --> SAP) a user should be able to log in to SAP BusinessObjects InfoView using his/her SAP credentials…

But in reality BusinessObjects will fail while importing the roles of the SAP user. Why? Because you will need a java connector, which doesn't come with the “out of the box” integration kit.

All you need to do is download the files from SAP (or from the bottom of this blog post), and make them available for your system.   Here a step by step guide:

1. Create the folders in your tomcat55 parent folder called Sharedlib
2. Copy there the sapjco.jar and the .dll files
3. Copy the .dll files into Windows/system32
4. Go to Tomcat configuration and add the complete path of the file sapjco.jar to the CLASSPATH string - restart tomcat

Notes: Do not confuse the sapjco.jar with other versions in the folder. Download the 32bit version even if your machine is a 64bit.   Now try to import your user's role in BO CMC --> Authentications --> SAP again and you will see that everything will work as expected.

If you have any questions or feedback about this solution, please let me know by leaving a comment below.

> Download this Shared file (contains sapjco.jar and the .dll files)

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.

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.