1432259 - Simplifying nested Excel functions for optimal Xcelsius performance

At times, Xcelsius dashboards will use complex logic in the Excel data model to analyze large sets of embedded raw data.  Usually, this requires long nested functions to calculate multiple conditions in a single cell.  For example, column A might look like:

=FunctionA(FunctionX(FunctionZ))
=FunctionB(FunctionX(FunctionZ))
=FunctionC(FunctionX(FunctionZ))
=FunctionD(FunctionX(FunctionZ))
=FunctionE(FunctionX(FunctionZ))

This creates a conditional data table which is then bound to Xcelsius components.  As a result, the performance of the published SWF is diminished.

Environment:

• Xcelsius 4.5, 2008
• Excel 2003, XP, 2007

In Excel, enter random values into column B.  Then build column C with functions similar to the following:

=IF(B2<10,IF(B1<10,1,0),0)
=IF(B3<10,IF(B1<10,1,0),0)
=IF(B4<10,IF(B1<10,1,0),0)
=IF(B5<10,IF(B1<10,1,0),0)
=IF(B6<10,IF(B1<10,1,0),0)
=IF(B7<10,IF(B1<10,1,0),0)

The functions listed in column C contain the same nested formula of "IF(B1<10,1,0)."  This redundant function causes the Xcelsius SWF to perform the same calculation repeatedly throughout column C.  Therefore, further processing is required by the SWF file to obtain the result for each cell.

In a seperate cell, such as D1, calculate "=IF(B1<10,1,0)".  Then change the functions in column C to the following:

=IF(B2<10,D1,0)
=IF(B3<10,D1,0)
=IF(B4<10,D1,0)
=IF(B5<10,D1,0)
=IF(B6<10,D1,0)
=IF(B7<10,D1,0)

This allows the Xcelsius SWF to process "=IF(B1<10,1,0)" only once, and optimizes the calculations in column C.

Xcelsius; performance; Excel; optimize; optimization; complex functions; nested functions; calculations , KBA , BI-RA-XL , Obsolete: BusinessObjects Dashboards and Presentation Design , How To

SAP BusinessObjects Dashboards all versions