To use a Smart Column as a fact you will need to cache it.
The aggregation cache stores the calculated results of the specified Smart Column. Values already calculated can be retrieved from the cache quickly without the need to recalculate each time the matrix is accessed. Because of this, aggregation caching can improve performance. It is important to note that caching is intended for complex aggregations and as such performance is not always faster when cached.
A Smart Column total is the overall result calculated using the results in the column. For example, if the Fact was MAX, the total row would display the largest value from all of the results. Similarly, if a SUM Fact was used, the total row would calculate and display the sum of all the results. This functionality is not available when the Smart Column has been cached.
How to Cache a Smart Column
- Open the Smart Column you want to cache.
- Ensure you are on the Home tab in the ribbon.
- Click on the "Caching" drop-down in the caching section.
- Choose how often you would like the Smart Column to be cached.
- This decision should depend on the likelihood of the results changing, and the possibility and danger of using outdated values from the cache.
Manage Aggregation Cache Synchronizer
This synchronizer manages the cache of aggregation results. You can create a Manage Aggregation Cache synchronizer to do any of the following cache management tasks:
- Delete all cached values in the repository.
- Delete cached values not used within a certain timeframe e.g. the last month.
- Delete cached values for specific matrices – This should be done when you know that the cached values are incorrect or no longer required. For example, if you have run an Excel Import Synchronizer that affects the values used in a cached Aggregation matrix, you might want to delete the cache and then repopulate it.
- Populate the cache for a matrix – This can be done for all variable permutations or for selected variables.
If the Smart Columns cache is set to Off, then the Manage Aggregation Cache Synchronizer will not be able to override this. Instead the synchronizer will fail for that Smart Column and report that it is not cached.
Managing the aggregation cache synchronizer is demonstrated in the short video clip shown below: