How much data can Excel dashboards handle?

Hi all,

I wondered if there was any guidance on the maximum size that is
recommended for a system where CommCare data is exported to an Excel
dashboard. Currently I am exporting forms and cases on a daily basis to
Excel dashboard. However, I expect that these forms could reach 150-200 MB
or more eventually and I am wondering if the dashboard solution will not be
suitable for such size. Should app designers consider size of data when
considering whether to use Excel dashboards or CommCare data export (or
other solution) when designing the analyses part of their CommCare systems?

Thanks!

John

Hi John,

Newer versions of excel can handle tens or hundreds of thousands of rows so
I don’t think there’s anything inherent in Excel that prevents large sizes.
But practically you’ll run into two issues.

-when the file size is that large it will take a long time for Excel to
download it and excel will effectively be frozen during that time. It’s
probably better to manually download the file at that point and save it on
your computer rather than having excel do it. If you do that you should
change the export type to xls instead of excel dashboard type and point
your dashboard file at your local file instead of the Web address.

  • depending on your computer if go up files get very large it may take a
    long time to generate the pivot tables and your computer may start running
    out of memory. This is especially true if you have additional formulas in
    excel that act on all the data. It’s pretty dependant on your computer
    specs.

To your larger point I think you likely will hit a limit as your data grows
to where excel isn’t suitable and should start looking into a statistics
program or a BI tool like Tableau. You could also use the "Comcare Export"
tool which would allow you to export only subsets of your data since older
data may be less relevant and will reduce file sizes. I’m on my phone right
now, but if search the help site for commcare export tool you should find
it. If not let me know and I can get the link.

-Nick

··· On Sep 17, 2015 9:55 PM, "John Skelton" wrote:

Hi all,

I wondered if there was any guidance on the maximum size that is
recommended for a system where CommCare data is exported to an Excel
dashboard. Currently I am exporting forms and cases on a daily basis to
Excel dashboard. However, I expect that these forms could reach 150-200 MB
or more eventually and I am wondering if the dashboard solution will not be
suitable for such size. Should app designers consider size of data when
considering whether to use Excel dashboards or CommCare data export (or
other solution) when designing the analyses part of their CommCare systems?

Thanks!

John


You received this message because you are subscribed to the Google Groups
"commcare-users" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Hi John,

Newer versions of excel can handle tens or hundreds of thousands of rows
so I don’t think there’s anything inherent in Excel that prevents large
sizes. But practically you’ll run into two issues.

-when the file size is that large it will take a long time for Excel to
download it and excel will effectively be frozen during that time. It’s
probably better to manually download the file at that point and save it on
your computer rather than having excel do it. If you do that you should
change the export type to xls instead of excel dashboard type and point
your dashboard file at your local file instead of the Web address.

  • depending on your computer if go up files get very large it may take a
    long time to generate the pivot tables and your computer may start running
    out of memory. This is especially true if you have additional formulas in
    excel that act on all the data. It’s pretty dependant on your computer
    specs.

To your larger point I think you likely will hit a limit as your data
grows to where excel isn’t suitable and should start looking into a
statistics program or a BI tool like Tableau. You could also use the
"Comcare Export" tool which would allow you to export only subsets of your
data since older data may be less relevant and will reduce file sizes. I’m
on my phone right now, but if search the help site for commcare export tool
you should find it. If not let me know and I can get the link.

(here’s the link to documentation
https://confluence.dimagi.com/display/commcarepublic/CommCare+Data+Export+Tool
)

··· > -Nick > On Sep 17, 2015 9:55 PM, "John Skelton" wrote: > >> Hi all, >> >> I wondered if there was any guidance on the maximum size that is >> recommended for a system where CommCare data is exported to an Excel >> dashboard. Currently I am exporting forms and cases on a daily basis to >> Excel dashboard. However, I expect that these forms could reach 150-200 MB >> or more eventually and I am wondering if the dashboard solution will not be >> suitable for such size. Should app designers consider size of data when >> considering whether to use Excel dashboards or CommCare data export (or >> other solution) when designing the analyses part of their CommCare systems? >> >> Thanks! >> >> John >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "commcare-users" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to commcare-users+unsubscribe@googlegroups.com. >> For more options, visit https://groups.google.com/d/optout. >> > -- > You received this message because you are subscribed to the Google Groups > "commcare-users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to commcare-users+unsubscribe@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. >

Thanks Nick and Cory, that’s helpful information. It sounds like manual
download will help up to a certain point, but in the long run a non-excel
solution would be best for large data sets.

John

··· On Friday, September 18, 2015 at 12:52:43 PM UTC, Cory Zue wrote: > > Hi John, >> >> Newer versions of excel can handle tens or hundreds of thousands of rows >> so I don't think there's anything inherent in Excel that prevents large >> sizes. But practically you'll run into two issues. >> >> -when the file size is that large it will take a long time for Excel to >> download it and excel will effectively be frozen during that time. It's >> probably better to manually download the file at that point and save it on >> your computer rather than having excel do it. If you do that you should >> change the export type to xls instead of excel dashboard type and point >> your dashboard file at your local file instead of the Web address. >> >> - depending on your computer if go up files get very large it may take a >> long time to generate the pivot tables and your computer may start running >> out of memory. This is especially true if you have additional formulas in >> excel that act on all the data. It's pretty dependant on your computer >> specs. >> >> To your larger point I think you likely will hit a limit as your data >> grows to where excel isn't suitable and should start looking into a >> statistics program or a BI tool like Tableau. You could also use the >> "Comcare Export" tool which would allow you to export only subsets of your >> data since older data may be less relevant and will reduce file sizes. I'm >> on my phone right now, but if search the help site for commcare export tool >> you should find it. If not let me know and I can get the link. >> > > (here's the link to documentation > > ) > >> -Nick >> On Sep 17, 2015 9:55 PM, "John Skelton" <jske...@lr.mercycorps.org > wrote: >> >>> Hi all, >>> >>> I wondered if there was any guidance on the maximum size that is >>> recommended for a system where CommCare data is exported to an Excel >>> dashboard. Currently I am exporting forms and cases on a daily basis to >>> Excel dashboard. However, I expect that these forms could reach 150-200 MB >>> or more eventually and I am wondering if the dashboard solution will not be >>> suitable for such size. Should app designers consider size of data when >>> considering whether to use Excel dashboards or CommCare data export (or >>> other solution) when designing the analyses part of their CommCare systems? >>> >>> Thanks! >>> >>> John >>> >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "commcare-users" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to commcare-user...@googlegroups.com . >>> For more options, visit https://groups.google.com/d/optout. >>> >> -- >> You received this message because you are subscribed to the Google Groups >> "commcare-users" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to commcare-user...@googlegroups.com . >> For more options, visit https://groups.google.com/d/optout. >> > >