Simple Implementations to Improve Xcelsius Performance

In almost all kind of dashboard developments the most important, but relatively less addressed concern is performance optimization. It generally later comes as a major change in basic architecture or struggle in existing ones, and causes increase in efforts which effects cost to production.

Xcelsius design is more than just MS Excel formulae and lot of room for improvement can be achieved following the points mentioned below.

1. Use the latest version of the tool and apply service packs and fix packs regularly, though in certain cases applying service packs, fix packs may cause problems. In some cases the upgrades can cause prompt errors and this had to be resolved with a careful analysis of hard-coded values and formulae in Excel. One of the important reason to have the latest version is that it is one of the first recommendation from the vendor, SAP.

2. During development, if it takes a long time to load the model and to create the SWF file, you should address the amount of data placed in Excel sheets first. Xcelsius is not meant to be used with tons of data, but with very targeted, highly aggregated data. Do not fill spreadsheet with all the data and wait for hours for the output. Xcelsius should be designed for presenting summarized information and tools like QaaWS or Crystal/Web-I for all the heavy aggregation and calculations in your datasets. The SWF generated by Xcelsius ideally serves as a visualization layer, and you want the data volume and calculations to be minimal during runtime. For large datasets we can use multiple parameterized queries (when used with QaaWS) to retrieve data that is fed into the dashboard depending on the analysis and number of analytics.

3. Optimization at Excel Sheet and Formula Level

• Use the latest, compatible Excel version available
• Have minimum number of required Excel sheets, keep the size of the Excel Sheet to minimum, delete unwanted cells and formatting from the sheet.
• Keep exact match lookups on the same worksheet as the data they are looking up. It’s significantly faster.
• When doing exact match lookups restrict the range of cells to be scanned to a minimum.
• Store the result of an exact MATCH and reuse it.
• Use INDEX and MATCH or OFFSET instead of (VLookUp, HLookUp, Sumif, DSUM)

Examples:

INDEX($C$4:$C$39,MATCH(E5,$B$4:$B$39,0),0)

INDEX(B4:C39,E5,2)

VLOOKUP(E5,B4:C39,2,FALSE)

All three will fetch the same result where response time would be minimum for INDEX-MATCH

4.Optimization at the QAAWS Level(If we are fetching data using QAAWS)

• Try to use minimum number of Webservices
• Try to call one web service at a time: do web service serialization
• Hard code the static values

References- http://www.decisionmodels.com