Best Practice for Significant Amounts of Data
This is basically a best-practice/concept question and it spans both Xcelsius & Excel functions:
I am working on a dashboard for the US Military to report on some basic financial transactions that happen on bases around the globe. These transactions fall into four categories, so my aggregation is as follows:
Year,Month,Country,Base,Category (data is Transaction Count and Total Amount)
This is a rather high level of aggregation, and it takes about 20 million transactions and aggregates them into about 6000 rows of data for a two year period.
I would like to allow the users to select a Category and a country and see a chart which summarizes transactions for that country ( X-axis for Month, Y-axis Transaction Count or Amount ). I would like each series on this chart to represent a Base.
My problem is that 6000 rows still appears to be too many rows for an Xcelsius dashboard to handle. I have followed the Concatenated Key approach and used SUMIF to populate a matrix with the data for use in the Chart. This matrix would have Bases for row headings (only those within the selected country) and the Column Headings would be Month. The data would be COUNT. (I also need the same matrix with Dollar Amounts as the data).
In Excel this matrix works fine and seems to be very fast. The problem is with Xcelsius. I have imported the Spreadsheet, but have NOT even created the chart yet and Xcelsius is CHOKING (and crashing). I changed Max Rows to 7000 to accommodate the data. I placed a simple combo box and a grid on the Canvas u2013 BUT NO CHART yet u2013 and the dashboard takes forever to generate and is REALLY slow to react to a simple change in the Combo Box.
So, I guess this brings up a few questions:
1) Am I doing something wrong and did I miss something that would prevent this problem?
2) If this is standard Xcelsius behavior, what are the Best Practices to solve the problem?
a. Do I have to create 50 different Data Ranges in order to improve performance (i.e. Each Country-Category would have a separate range)?
b. Would it even work if it had that many data ranges in it?
c. Do you aggregate it as a crosstab (Months as Column headings) and insert that crosstabbed data into Excel.
d. Other ideas that Iu2019m missing?
FYI: These dashboards will be exported to PDF and distributed. They will not be connected to a server or data source.
Any thoughts or guidance would be appreciated.