InfoSol We're passionate about inspired business intelligence
Related Articles
I was recently asked this question on how best to handle a 13 month dataset when in the dashboard the goal is to only display monthly detail and summary data. This is a good question and in my opinion worth sharing here because it is dealing with boosting Xcelsius backend performance.
Question: Working with a data set consisting of 13months of data with one row for each day (so max 380 rows) and needing to show only monthly detail/summary data in the dashboard, which of the following would you consider to be the better approach to yield the best performance?
1. Pull 380 rows in cache with date in one column -> pass STARTDATE and ENDDATE from the dashboard and pull every month’s data by writing a cache query. When the user selects a month for display in the dashboard, Excel will calculate the start and end date of the month and pass those to the cache query with a WHERE clause specifying BETWEEN ‘@STARTDATE’ and ‘@ENDDATE’
Or,
2. Load 380 rows in the dashboard Excel backend -> calculate the month from the date column within Excel then use a hidden filter to filter the month’s row based on the calculated month in the previous step and calculate the sum and average within excel?
Answer: As a general rule, if the data set is 200 rows or less, I would say to go with Option 2. If it is more than 200 rows, I would say you will get better performance with the cache query. Of course, this also depends on the cumulative total # of rows being loaded into the dashboard. For example, if you are planning on adding 10+ additional 200 row data sets, then I would definitely go with Option #1.
With too much data in the backend, Xcelsius performance suffers. InfoBurst allows us to cache a large set of data, and then use a Cache Query to extract just the portion we need to view on the dashboard, as the user makes selections. This is a tremendous help to the developer who wants to boost Xcelsius dashboard performance.
