This post is fourth in a series on using Confluence as a Business Intelligence tool.
pie-chart.jpgNow that you’re pulling your data onto Confluence pages and making it into eye-popping charts, your reports might be so popular that they’re putting a strain on your database. A flashy executive dashboard that shows daily stats from every division of the company isn’t so flashy if it takes five minutes to load. Optimization to the rescue!
Confluence gives you several methods to improve the speed of reporting.
The {cache} Macro
Some SQL queries can be quite complex and require noticeable time to run. Aside from buying a faster database server or optimizing the queries themselves, there is a handy alternative.
The {cache} macro can store the results of a query, making the results available very quickly for future calls. It works as follows:

  • The query runs normally the first time, but the {cache} macro stores the results
  • Whenever the query is run again in future, the stored results are returned instantly

The {cache} macro can be configured to store, or ‘cache’, the results for a given period of time, such as 1 day or 5 minutes. The default is 1 hour.
Also, a cron expression can be provided that states exactly when the results should be refreshed. For example, if a database is updated on Mondays at 10am, the cache can be configured to ‘expire’ the cache at 10.05am each Monday.

{cache:cron=5 10 * * 1}

This means the database is queried at most once per week, or when your server needs to drop the cache results due to memory limitations.
The {future} Macro
Sometimes a slow query is unavoidable, such as the first execution of a query before the results are cached, or where you always want real-time information. Confluence waits until every {sql} query on the page has completed before it starts showing the page. This can be quite annoying for users, especially if they are seeking some of the faster content on the page.
The {future} macro can be used to identify parts of a page that are slow to render, allowing the remainder of the page to be displayed before the results are available. Once the results are available, the macro will then show the results on the page.
A parameter can also default the macro to not display the results until a button is manually clicked. This is great for seldom-run, resource-hungry queries.
The {future} macro is distributed as a JAR file. You Confluence administrator will need to install it using the manual method instead of the plugin repository.
Using a Datamart
Sometimes there are queries that take a long time to run, but you don’t want users to wait for the results to be calculated.
For example, imagine a Support department that requires a Dashboard to be updated every hour. While the {cache} macro could be used to retain results for an hour, it will still take time to execute whenever the cache is empty or expired. The requirement, however, is that the Dashboard renders very quickly regardless of when it is accessed.
To satisfy this requirement, it is possible to create a Datamart or Data Warehouse that stores the results of queries and can be quickly accessed whenever the Dashboard is displayed. It works as follows:

  1. Once an hour, a cron job on your database server executes a database query.
  2. The query can be quite complex, taking significant time to execute – this is run in the background, so no users notice the delay.
  3. The results are stored in a table within the datamart, separate to the source data.
  4. When a user accesses the Dashboard, the results are retrieved from the datamart without any data manipulation, so it runs very quickly.

If you’re thinking of using a Datamart, here are some additional tips:

  • Think about error notification if the query fails. You can configure the script to send an email alert to somebody if an error occurs.
  • Put some discipline around documentation and source control, so other people understand how you’ve configured the script and queries – we document such systems on a Confluence page.
  • If you’re wrapping your final query in a {cache} macro, allow time for the update to run before expiring the cache. As in the {cache:cron=5 10 * * 1} example given above, schedule the cache expiry for after the script finishes executing rather than the same time, otherwise old or blank results may be returned.
To learn more about using Confluence as a reporting and BI tool, visit our Reporting HOWTO at

Confluence for Business Intelligence Part 4 &#8211...