Administering SAS BI Dashboard

Improving the Performance of SAS BI Dashboard with Data Caching

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.

Data Caching in SAS BI Dashboard 4.2

For greater performance and scalability, SAS BI Dashboard 4.2 uses an in-memory Least Recently Used (LRU) cache. SAS BI Dashboard serves data to a data model from a cache, if the underlying data is not stale. If the underlying data is stale, then SAS BI Dashboard queries the underlying data, updates the cache, and returns the fresh data model. In addition, a background thread tries to keep the cache current by updating cached data models before they become stale. If configured properly and allowed enough memory, all data models can achieve 100% cache hit rates and no queries are ever made to the underlying data source during a user's dashboard request. If enough memory is unavailable, then the least recently used data models are dropped from the cache when the cache reaches its memory limit.

Data Caching in SAS BI Dashboard 4.3

For greater performance and scalability, SAS BI Dashboard 4.3 uses ehcache.

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. If your site has a large number of users with dashboards that retrieve large amounts of data, then increasing the data model cache size might 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, 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.

Configure a Data Cache

By default, caching is not enabled. To enable caching, you specify caching properties in the DSX file for each data source that you want cached. For a description of the DSX files, including their location, see Working with Data Source XML (DSX) Files. For information about how to modify a DSX file, see Modify a DSX File.

To enable caching for a data source, follow these steps:

To enable caching for JDBC files, add the following parameter and value in the DSX file:
```
alwaysConnectAsAdminUser="true"
```
Remove the comment delimiters that surround the <DefaultTimingCacheDirective> element in its corresponding DSX file. For example, if you want to enable caching for information maps, then remove the comment delimiters from <DefaultTimingCacheDirective> in the <infomap.dsx> file.
After you make changes to the DSX files, you must restart the Web application server before your changes take effect.

Here are descriptions of the <DefaultTimingCacheDirective> attributes:

Attributes in the <DefaultTimingCacheDirective> Element
Attribute	Description
cacheDisplayValueForRefresh	Specifies the minimum amount of time that must elapse before the data source can be refreshed. The dashboard can refresh the data only after a cached data model reaches the age indicated by this number. (You specify the unit of measure in the cacheDisplayMultiplierForRefresh 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 more than slightly out-of-date, regardless of the stale value. 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	Specifies how much time can pass before the data source becomes invalid or stale. If a cached model is older than this number, then it is invalid and a query will run during the next user's request. (You specify the unit of measure in the cacheDisplayMultiplierforStale attribute). If this value is specified to be 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. 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.

Here is example code with sample values for the attributes:

<DefaultTimingCacheDirective
     cacheDisplayValueForRefresh="15.0"
     cacheDisplayValueForStale="20.0"
     cacheDisplayMultiplierForRefresh="MINUTES"
     cacheDisplayMultiplierForStale="MINUTES"

In this example, the data is never older than 15 minutes. The background systems are not overloaded because a query executes only once every five minutes. The upper limit for the cache size is approximately 2 MB.

The values your specify for the cacheDisplayValueForRefresh and cacheDisplayValueForStale attributes depend on how often the data changes, and the extent to which your users need fresh data. With a large refresh value, fewer data retrievals are required to keep the cache updated. An increase in the value for cacheDisplayValueForRefresh also reduces demand on Pooled Workspace servers.

Configure Upper Limit for the Data Model Cache Size

The bid.maxDataModelCacheSize property specifies the approximate upper limit of the data model cache size. By default, this property's value is set to 30 MB. When additional data models are cached after this size is met, the least recently used data models are dropped from the cache.

The default value for the bid.maxDataModelCacheSize property is sufficient for most environments. If you cache data, and have an unusually high number of large queries defined for dashboard users, your environment might benefit from a larger cache if your system has the required memory available.

To modify the maximum cache size for the bid.maxDataModelCacheSize property, follow these steps:

Log on to SAS Management Console as the SAS administrator.
On the Plug-ins tab, navigate to Application Management Configuration Manager BI Dashboard 4.3.
Right-click and select Properties to display the BI Dashboard 4.3 Properties dialog box.
Click the Advanced tab.
For the property name bid.maxDataModelCacheSize, click on the property value, and then enter a value in bytes.
Click OK to exit the SAS BI Dashboard 4.3 Properties dialog box.
To enable this property to take effect, restart the Web application server.

Configure the Pooling of Dashboard JDBC Connections

SAS 9.2 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 one or more 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. While 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, follow these steps:

Add pooling attributes to the <DataSourceDef> element in the corresponding DSX file. For a description of the DSX files, including their location, see Working with Data Source XML (DSX) Files.
After you make changes to the DSX file, you must restart the Web application server to enable the changes to take effect.

For descriptions of attributes, see the following table.

Attributes and Descriptions
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 user 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.

Here is an example of pooling attributes that were added to the<DataSourceDef> element in the DSX file:

<DataSourceDef id="dboard_sas"
providerClass="com.sas.bi.dashboard.provider.JdbcProvider"
maxPoolSize="5"
maxWaitForPooledConnection="60000"
lingerTime="6000"
alwaysConnectAsAdminUser="true">
...
<
/DataSourceDef>

Top of Page