Improving Performance with Data Caching

Performance Requirements

Dashboards present the following performance challenges:
  • Dashboards typically render data from disparate sources and make multiple queries.
  • Some sites need real-time dashboards that obtain their data at the time the dashboard is requested.
  • Some sites must serve large numbers of concurrent users.
By default, dashboards obtain their data at the time the dashboard is requested. This default configuration can cause scalability problems and is unnecessary if the underlying data changes infrequently.
To meet different performance requirements, SAS BI Dashboard provides two main optimization mechanisms: a data cache and, for JDBC data sources, control over how JDBC connections are made.

Guidelines for Data Caching

For greater performance and scalability, you can enable data caching for SAS BI Dashboard. When data caching is configured, SAS BI Dashboard serves data to a data model from a cache (if the underlying data is not stale), and all queries are run by the SAS Trusted User for all users. The default data cache is typically sufficient for most sites. If your site has users that retrieve extraordinarily large amounts of data, then increasing the data model cache size can improve performance.
Before enabling data caching, evaluate the following:
  • In your environment, is it acceptable to have a single user (in this case, the SAS Trusted User) run the queries for the indicators that you want to cache? If the answer is yes, and you do not enforce BI row-level permissions for information maps, then you can enable data caching.
  • Do you enforce BI row-level permissions for information maps? If you do, data caching cannot be enabled for SAS BI Dashboard. BI row-level permissions require individual users to be able to execute requests to specific locations in a security associations table. SAS BI Dashboard does not allow queries from individual users, because only the SAS Trusted User executes all queries when data caching is enabled.

Overview of Data Caching for SAS BI Dashboard 4.31

In the SAS 9.3 Release, SAS BI Dashboard 4.31 offers more flexibility in determining the frequency of data caching for dashboard data. Depending on the needs of your environment, you can modify the default values for two new configuration properties that determine the frequency with which cached data is updated. If data caching is enabled, the data cache is typically refreshed periodically by a background process.
Here is a quick look at the steps that you should complete to configure and enable data caching for SAS BI Dashboard 4.31:
  1. The bid.DataCacheUpdateFrequency property in SAS Management Console has a default value of 10000 milliseconds. The value for this property determines how often the data cache is inspected for caching updates (specifying a custom value is optional). If needed, supply a custom value for your environment that determines how often the data cache process is checking to see whether the cache should be refreshed or categorized as being stale.
  2. The .dsx files reside in the SAS Content Server. Edit the applicable .dsx files and remove the common delimiters that surround the attributes for the <DefaultTimingCacheDirective> element. If desired, specify custom values for the cacheDisplayValueForRefresh and the cacheDisplayValueForStale attributes. Specifying custom values for these attributes is optional.
  3. If desired, modify the values for two new configuration properties in SAS Management Console:
    • bid.ConfigDataLatencyTolerance
    • bid.UserDataLatencyTolerance
  4. To enable these changes to take effect, restart the Web application server.

How Cached Data Is Evaluated and Refreshed

In previous versions of SAS BI Dashboard, the update frequency value for data caching was hardcoded and not visible to administrators in SAS Management Console. Using the values for the CacheDisplayValueForRefresh and CacheDisplayValueForStale parameters in the appropriate .dsx files, a check was made to determine whether cached data needed to be refreshed or if it was stale.
Beginning with SAS BI Dashboard 4.31, the default value for the bid.DataCacheUpdateFrequency property can be modified and set in milliseconds. The custom value specified for the bid.DataCacheUpdateFrequency property determines the frequency with which cached objects are evaluated. For example, if this property is set with a custom value of 30000 milliseconds, then at periodic intervals that are spaced apart by 30000 milliseconds, a check is made to determine whether a cached object is stale or needs to be refreshed.
In the .dsx files, the default values for the cacheDisplayValueForRefresh and the cacheDisplayValueForStale attributes are 15 and 20 minutes. Note that these default values are specified in minutes and not milliseconds.
Let us examine a scenario with these values:
  • The bid.DataCacheUpdateFrequency property is configured with a default value of 10000 milliseconds in SAS Management Console.
  • The cacheDisplayValueForRefresh attribute has a default value of 15 minutes.
  • The cacheDisplayValueForStale attribute has a default value of 20 minutes.
At periodic intervals that are spaced by 10000 milliseconds, the bid.DataCacheUpdateFrequency property checks to see whether the cached data for a particular query has not been accessed by a user for more than 20 minutes. If the cached data has not been accessed in more than 20 minutes, then the data is determined to be stale and it is removed from the cache. At a later time, if a user requests that same data, it is retrieved again and placed in the cache.
In this scenario, the value for the cacheDisplayValueForStale attribute (with a value of 20 minutes) is larger than the value for the cacheDisplayValueForRefresh (15 minutes). Therefore, data is never declared to be stale. If a user has accessed the data within the past 20 minutes, the data in the cache is always refreshed within 15 minutes. If a user has not accessed the data within the past 20 minutes, the data is classified as being stale and is removed from the data cache. The next time a user accesses this data, the data is again placed in the cache.

Guidelines for Customizing Data Caching Values in the .dsx Files

Any custom values specified for the cacheDisplayValueForRefresh and cacheDisplayValueForStale attributes in the appropriate .dsx files depend on these factors:
  • the frequency with which the data changes
  • your users’ needs for fresh data
With a large refresh value, fewer data retrievals are required to keep the cache updated. An increase in the value for the cacheDisplayValueForRefresh attribute also reduces demand on Pooled Workspace servers.
The following table provides explanations about these two data caching attributes and their values.
Attributes in the <DefaultTimingCacheDirective> Element
Attribute
Description
cacheDisplayValueForRefresh
Beginning with SAS BI Dashboard 4.31, the cached object is examined to evaluate whether the current time is greater than the total value that is derived by adding the last time this object was accessed plus the value for the cacheDisplayValueForStale parameter.
If that value exceeds this total acceptable value, then the cached object is removed from the cache and it is refreshed when a new query is made. If that value does not exceed the acceptable value, then the cached object is retained for the next update cycle when it is checked again.
The value that you specify determines the minimum amount of time that must elapse before the data source can be evaluated and or refreshed. The dashboard can refresh the data only after a cached data model reaches the age indicated by this total number plus the current age calculated by using the value specified for the cacheDisplayValueForStale attribute. Enter the number in quotation marks.
You can enter a value of 0 within quotation marks (for example, “0”) to achieve near real-time data updates. This value is recommended when you have a small number of data sources. With this value, the data is never older than the value of the bid.DataCacheUpdateFrequency. However, the underlying query server (workspace server) might be overloaded if you have a large number of data sources.
cacheDisplayMultiplierForRefresh
Specifies the unit of measure for the value that is entered in cacheDisplayValueForRefresh. Valid values are "SECONDS," "MINUTES," and "HOURS." Enter the value in quotation marks.
cacheDisplayValueForStale
Beginning with SAS BI Dashboard 4.31, the value for this attribute plus the last request time for the cached object is used to compare to the last time the cache was refreshed for the requested object. If it is determined that the cached object is stale, then the stale object is removed from the cache. If a cached object is older than this number, then it is invalid and a query is executed during the next user's request. If this value is higher than the value for cacheDisplayValueForRefresh, the data available for the dashboard will be fresh and the result would be a 100% cache hit rate if a user has accessed that data within the duration of time specified for the cacheDisplayValueForStale attribute. Enter the number in quotation marks.
cacheDisplayMultiplierForStale
Specifies the unit of measure for the value that is specified for cacheDisplayValueForRefresh and cacheDisplayValueForStale. Valid values are " SECONDS," "MINUTES," and "HOURS." Enter the value in quotation marks.

Customize the Value for Frequency of Updates to the Cache

If you want to retain the default value that determines how frequently the data cache is inspected, skip these steps. To customize and modify the value, follow these steps:
  1. Log on to SAS Management Console.
  2. On the Plug-ins tab, navigate to Application Managementthen selectConfiguration Managerthen selectSAS Application Infrastructurethen selectBI Dashboard 4.3 Properties.
  3. Right-click and select Properties to display the BI Dashboard 4.3 Properties dialog box.
  4. Click the Advanced tab.
  5. For the property name bid.DataCacheUpdateFrequency, click on the property value, and then enter a new value in milliseconds.
  6. Click OK to exit the SAS BI Dashboard 4.3 Properties dialog box.
  7. To enable these changes to go into effect, restart the Web application server.

Enable Data Caching

You enable data caching by removing the comment delimiters that surround the <DefaultTimingCacheDirective> element in the appropriate .dsx files. In addition, you have the option to modify the default values for the attributes in the <DefaultTimingCacheDirective> section of the .dsx files.
To edit a .dsx file and enable data caching, follow these steps:
  1. On the Folders tab in SAS Management Console, navigate to Systemthen selectAdministrationthen selectSAS BI Dashboardthen selectSAS BI Dashboard 4.3then selectDataSourceDefinitions.
  2. In the right pane, right-click on the .dsx file (for example, dboard_sas.dsx file).
  3. Select Write Content to External File....
  4. Remove the comment delimiters that surround the <DefaultTimingCacheDirective> element in the .dsx file. 
    <DefaultTimingCacheDirective
     cacheDisplayValueForRefresh=”15.0”
     cacheDisplayValueForStale=”20.0”
     cacheDisplayMultiplierForRefresh=”MINUTES”
     cacheDisplayMultiplierForStale=”MINUTES”
  5. If desired, provide custom values for the cacheDisplayValueForRefresh and the cacheDisplayValueForStale attributes.
  6. In the dboard_sas.dsx file, add the following parameter and value at the beginning or end of the file: 
    alwaysConnectAsAdminUser="true"
    Note that this parameter and value are added only to thedboard_sas.dsx JDBC provider.
  7. Right-click and select Add Content From External File(s) or Directories.
  8. Select the .dsx file you modified, and replace the previous version of the dsx file.
  9. To enable these changes to go into effect, restart the Web application server.

Data Caching in SAS BI Dashboard 4.31

Caching Configuration Objects

In previous versions of SAS BI Dashboard, configuration objects were loaded from the SAS Content Server or the metadata. Therefore, any changes to data caching configuration required you to restart the Web application server.
Beginning with SAS BI Dashboard 4.31, .dsx files and configuration objects are cached and refreshed periodically. If you enable data caching or modify the values for the data caching properties or attributes, you are not required to restart the Web application server. You have the flexibility to modify the values for the data caching properties and attributes dynamically and as frequently as needed.
Here are the configuration objects that are cached and refreshed in the middle tier when SAS BI Dashboard is used:
  • data source definition files
  • contributor definitions
  • dashboard templates
  • e-mail templates
  • gauge definitions
  • GTML
  • identifiers
  • PdvHandlerConfigs
  • run-time definitions
  • view definitions

Modify the Frequency of Caching Configuration Objects

SAS BI Dashboard 4.31 includes a new configuration property in SAS Management Console. The bid.ConfigDataLatencyTolerance property and its value determine the frequency with which configuration objects are updated in the cache.
By default, the value for this property is set to 300000 milliseconds. If desired, specify a custom value in milliseconds for this property. The value specified this property determines how often a cached configuration object is compared with the corresponding object in the metadata. The cached configuration object is refreshed if a change was detected with its corresponding object in the metadata. Beginning with SAS BI Dashboard 4.31, the cached object is always returned in response to a need to read the configuration object.
This value also determines the frequency with which the table that contains the configuration objects in memory is updated. In previous versions of SAS BI Dashboard, configuration objects such as data source definitions and e-mail templates were read during system start-up, and saved into a table in memory. Requests were processed by accessing this table. Beginning with SAS BI Dashboard 4.31, the value for this property ensures a periodic check of the table in memory and updates to the table.
To modify the frequency with which configuration objects are updated in the cache, follow these steps:
  1. Log on to SAS Management Console.
  2. On the Plug-ins tab, navigate to Application Managementthen selectConfiguration Managerthen selectSAS Application Infrastructurethen selectBI Dashboard 4.3.
  3. Right-click and select Properties to display the BI Dashboard 4.3 Properties dialog box.
  4. Click the Advanced tab.
  5. For the property name bid.ConfigDataLatencyTolerance, click on the property value, and then enter a value in milliseconds (modifying the value is optional).
  6. Click OK to exit the BI Dashboard 4.3 Properties dialog box.
  7. To enable these changes to go into effect, restart the Web application server.

Caching User-Defined Content Objects

In previous versions of SAS BI Dashboard, user-defined objects were loaded from the SAS Content Server or the metadata. Therefore, any changes to data caching configuration required you to restart the Web application server.
Beginning with SAS BI Dashboard 4.31, user-defined objects are cached and refreshed periodically. If you enable data caching or modify the values for the data caching properties (in SAS Management Console) or attributes (in the .dsx files), you need not restart the Web application server. You have the flexibility to modify the values for the data caching properties and attributes dynamically and as frequently as needed.
The value for the bid.UserDataLatencyTolerance property determines the frequency with which user-defined content objects are cached and updated. When a user-defined content object is used initially, that object is cached. Subsequently, when another request is made to use that same object, it is provided to user if there is a confirmation that the object is current within the value specified for the bid.UserDataLatencyTolerance property. The default value for this property is set to 300000 milliseconds. If desired, specify a custom value in milliseconds for this property. Examples of user-defined objects that are cached in the middle tier include:
  • DashboardConfigs
  • DashboardDisplayConfigs
  • IndicatorDefinitions
  • ModelConfigs
  • RangeDefinitions
  • IndicatorConfigs
  • PromptContents
If it is determined that the cached object is older than the value specified for the bid.UserDataLatencyTolerance property, the cached object’s timestamp is compared to the corresponding object’s timestamp in the metadata. If the timestamps are identical, the cached value is provided to the user. If the timestamps are different, the cached data is updated by retrieving the new user-defined data and placing it in the cache. Then, the timestamp is updated.
If desired, specify a custom value in milliseconds for this property. Examples of objects that are created by dashboard users include dashboards, indicators, and ranges. When a request is made for a dashboard object, the cached object is retrieved and provided. Any modifications made to dashboard objects in the Dashboard Builder result in immediate updates to the cache and the resetting of the last timestamp.

Modify the Frequency of Caching User-Defined Dashboard Objects

To modify the frequency with which user-defined objects are updated in the cache, follow these steps:
  1. Log on to SAS Management Console.
  2. On the Plug-ins tab, navigate to Application Managementthen selectConfiguration Managerthen selectSAS Application Infrastructurethen selectBI Dashboard 4.3.
  3. Right-click and select Properties to display the BI Dashboard 4.3 Properties dialog box.
  4. Click the Advanced tab.
  5. For the property name bid.UserDataLatencyTolerance, click on the property value, and then enter a value in milliseconds (modifying the value is optional).
  6. Click OK to exit the BI Dashboard 4.3 Properties dialog box.

Configure Caching for Configuration and Dashboard Objects

To customize and configure the values for these properties, follow these steps:
  1. Log on to SAS Management Console.
  2. On the Plug-ins tab, navigate to Application Managementthen selectConfiguration Managerthen selectSAS Application Infrastructurethen selectBI Dashboard 4.3.
  3. Right-click and select Properties to display the BI Dashboard 4.3 Properties dialog box.
  4. Click the Advanced tab.
  5. For the property name bid.ConfigDataLatencyTolerance, click on the property value, and then enter a value in milliseconds (modifying the value is optional).
  6. Click Add to specify the bid.UserDataLatencyTolerance property, and enter a value in milliseconds. Click OK to save the new property and its value.
  7. Click OK to exit the BI Dashboard 4.3 Properties dialog box.
  8. To enable these properties to take effect, restart the Web application server.

Configure the Pooling of Dashboard JDBC Connections

SAS 9.3 supports three types of pooling:
  • server-side pooling
  • client-side pooling
  • JDBC connection pooling for SAS BI Dashboard
Server-side pooling is the process by which the SAS Object Spawner maintains pools of workspace servers available for clients. The usage of servers in this pool is governed by the authorization rules set on the servers in the SAS metadata. Client-side pooling increases the efficiency of connections to workspace servers.
By default, SAS BI Dashboard uses server-side pooling. If several other applications and servers are requesting the same workspace servers concurrently, then SAS BI Dashboard and other applications might have to wait until a server in the pool is available. You can increase the number of servers in the pool, if doing so does not impact the memory or CPU resources on the data-tier machines.
For more information about pooling, see the SAS Intelligence Platform: Application Server Administration Guide.
JDBC connection pooling allows SAS BI Dashboard to control its JDBC connections and determine how many workspace servers are spawned. With SAS BI Dashboard, you configure how JDBC connections are opened and managed so that real-time SQL queries can execute quickly without consuming excessive system resources. A single dashboard can have multiple and separate indicators that point to different data and execute different SQL queries.
Regardless of whether you use data caching, you can configure pooled JDBC connections in order to improve performance. If you are caching data, you might not need to increase the number of pooled workspace servers allowed. This is because data caching significantly reduces demands on those pooled workspace servers. Although caching is a preferable optimization mechanism for scalability, caching might require more memory than desired or it might not meet your data freshness requirements.
By default, BI Dashboard uses JDBC connection pooling. Perform the following tasks only if you want to change the values that are used for pooling. No action is required in order to use pooling with the default values.
To configure pooling for a data source, add pooling attributes to the <DataSourceDef> element in the dboard_sas.dsx file. Note that beginning with SAS BI Dashboard 4.31, any changes made to the .dsx files are refreshed dynamically. Therefore, you need not restart the Web application server.
Here is an example of pooling attributes that were added to the<DataSourceDef> element in the file: 
<DataSourceDef id=”dboard_sas”
providerClass=”com.sas.bi.dashboard.provider.JdbcProvider”
maxPoolSize=”5”
maxWaitForPooledConnection=”60000”
lingerTime=”6000”
alwaysConnectAsAdminUser=”true”>
...
<
/DataSourceDef>
The following table provides descriptions for attributes that apply to JDBC pooling.
Attributes and Descriptions for JDBC Pooling
Attribute
Description
maxPoolSize
Specifies a maximum number of pooled connections. A high setting consumes more system resources, but might be necessary when you expect a large number of users.
The default value is 20.
maxWaitForPooledConnection
Specifies the number of milliseconds to wait for a pooled connection before returning an error that the connection failed.
The default value is 5000.
lingerTime
Specifies the number of milliseconds to hold a connection open after finishing a query. In dashboards, it is common to execute several queries within a single HTTP request. For that reason, it is important for connections to persist so that multiple connections do not have to be re-established within a single HTTP request.
The default value is 300000.
alwaysConnectAsAdminUser
Specifies that clients always connect as the administrator that is specified in the BIDashboard.config file. When the value is true, this setting results in a smaller number of pool connections because the same connection is used repeatedly.
The default value is false.
Copyright © SAS Institute Inc. All rights reserved.