Size of spreadsheet and dashboard

[Former Member]

Hi,

a customer of ours is having trouble previewing or exporting a dashboard. The amount of data in the spreadsheet is very large, as is the number of objects on the page.

The model will attempt to compile or export for several minutes for stopping with an error message "An error occurredwhen loading the file. The SWF was not created. For more information contact the file creator or your system administrator".

We are assuming that the issue is to do with the number of Excel functions in the spreadsheet, as if we copy and paste over the formulae with plain values instead the model will compile (but obviously won't function properly with updated data).

Does anyone know of any guidelines or rules about the size of a spreadsheet, number of formulas, or number of objects on a canvas before these sorts of problems are reached? Alternatively does anyone have any tips on improving performance in large Xcelsius models?

It is not related to the 512 row limit on objects or formulas as no one object references that number of rows. We have also not used any V or HLOOKUPS, and have used INDEX and MATCH instead.

We have raised a support call but the engineer has not been very helpful with these general questions.

thanks

Keith

[Former Member]

Hi Keith,

Could you quantify some of your statements? What's the size of the xlf, the swf, the number of data cells (both being loaded from data sources and number of cells with formulas).

I've had dashboards with around 5,000 calculated cells that were a little slow (~30 seconds load time with no data connections) but loaded without errors.

First thing to check is available RAM on the machine running Xcelsius. If this runs out there's a good chance you'll get errors.

One possible solution is this: If you're using formulas to allow users to select from a number of different sets of data (to show in a chart, for example), you could replace these with a push button that instead moves entire sets of cells from one location to another. Of course it depends on the nature of your dashboard.

There's another tip floating around these forums regarding exporting the sheet to Excel and open/saving/closing the file in Open Office (a free alternative to Microsoft office). You might want to try that.

As always, if it's an option for you, upgrade to the latest fix pack (FP3.5).

Hope that helps.

[Former Member]

David,

unfortunately I don't have .xlf to hand to examine. The number of cells with formulas will be in the thousands though, and the number of objects is probably that high too.

I will forward your other suggestions on to the customer though,

thanks for replying

Keith

[Former Member]

Hi Keith,

I provide you some suggestions please follow those

1) Export the excel to local disk. Open it (outside Xcelsius) save it.(record the file size before and after saving).

This will reduce some size.

2) Increase the buffer size at Flash player settings.

You said you are using Match and index, we have one option where we can reduce the repetation of Match function

for example say

A1 A2 A3 A4 A5 A6 A7

2001 2002 2003 2004 2005 2006

Company 1 569 783 726 706 836 335

Company 2 415 528 126 552 108 827

Company 3 796 302 631 723 951 590

Output:

Match fucntion 1=MATCH($C$23,$C$11:$C$18)

2001 2002 2003 2004 2005 2006

Lookup value>> Company 1 569 783 726 706 836 335

Instead of writing match function everytime i wrote it only once, coz from 2001-2006 we write the match fucntion un necessarily.

coz all Match functions bring the same output.

This is a simple example.

Hope this might help you.

Try to upgrade the flash player version.

Regards,

AnjaniKumar C.A.

[Former Member]

Anjani,

thanks for the suggestions, I will have a look at them and forward them to the customer

thanks

Keith

[Former Member]

Xcelsius version is 2008, SP3, FP3.4 in a Windows environment.

thanks

Keith

[Former Member]

Hi Keith

I think I know which model your dealing with!

Can you push the data in the formulae back to the XDC side?

Thanks

Charles

[Former Member]

Hi Charles, yes the long term solution will be to change the data feed to the model to get rid of the majority of the formulas.

I was just hoping someone in the wider Xcelsius community might know of some general guidelines about size and number of formulas.

I guess the number of variables in the model and in the users' PC setup makes that unlikely though.

thanks

Keith