Improving the Performance of SAS Web Report Studio

Suggestions for Improving the Performance of SAS Web Report Studio

To optimize the performance of SAS Web Report Studio, do the following:

By default, SAS Web Report Studio is configured with server-side pooling. Server-side pooling is controlled by the server end spawner. Setting up a pool of workspace server processes eliminates the need to start a new process for each user request. Client-side pooling is also available as an option. Both types of pooling enable connections to relational workspace servers. For more information about server-side and client-side pooling, the SAS Intelligence Platform: Security Administration Guide. Also, see SAS Intelligence Platform: Application Server Administration Guide.
Modify the workspace server start-up options to specify a work library, a buffer size for writing files to the work area, and a limit on SAS memory usage. For details, the SAS Intelligence Platform: Application Server Administration Guide.
Configure your middle tier as recommended in Best Practices for Configuring Your Middle Tier in SAS Intelligence Platform: Middle-Tier Administration Guide.
Make appropriate use of pre-generated reports, such as manually refreshed reports. Use report scheduling to control when pre-generated reports are generated. Pre-generated reports offer faster performance than live reports. You can improve your site performance significantly by increasing the usage of pre-generated reports. You can schedule reports to be generated on a nightly, weekly, or monthly basis. For more information, Understanding Pre-generated Reports.
Use the query cache, which is enabled by default. You can change the location of the cache, and you can disable caching. For more information, Using the Query Cache .

Using the Query Cache

Overview of the Query Cache

By default, SAS Web Report Studio (and SAS Web Report Viewer) use a large query cache to improve performance. For reports that contain more than one data-driven object, this cache maximizes efficiency. The query cache builds a temporary common data table that can fulfill the needs of all data-driven objects in the report. When the query cache is used, complex queries that include functions such as joins and filters are run only once (to build the common data table). Each data-driven object in the report can then run simple extraction queries against the common data table.

Note: The use of the cache is determined on a per-session basis, depending on the content of each report. In the current release, cache optimization is used only for reports that are based on relational data.

During installation, the query cache is enabled and is associated with a SAS library. After installation, you can perform the following optional tasks:

change the location of the query cache library
disable the query cache

Using the query cache will likely increase performance if your reports have the following characteristics:

joins from many tables
many BY groups
multiple report objects
data sources other than SAS (for example, Teradata or DB2)
formatted data values from data sources other than SAS

Conversely, using the query cache will not increase performance for a report that has all of the following characteristics:

few joins from few tables
few BY groups
few report objects
only SAS data tables as a source, or non-formatted data values from data sources other than SAS

There is no performance penalty for using the query cache unless the report uses a large native SAS table with report-ready data.

Manage Host Access to the Query Cache Directory

By default, the query cache directory is located at your equivalent of SAS-configuration-directory\Lev1\SASApp\Data\wrstemp. In order to protect any sensitive data in the cache, ensure full functionality, and optimize performance, it is important to carefully manage operating system access to this directory. The following table provides details.

Who Needs Operating System Access to the Query Cache Directory

Server That Retrieves the Data	Account That Accesses the Directory
Server-side pooled workspace server	The launch credential of the server.
Client-side pooled workspace server	Each puddle login.
Standard workspace server	Each requesting user.

Here are some additional notes:

In a new installation, appropriate initial protections are established for you. However, if you change your server configuration or relocate the directory, make sure that appropriate access is preserved.
- On UNIX and z/OS, the directory's owner (the SAS Installer account) and the owner's primary group have Read, Write, and Execute permissions for the directory. The SAS Spawned Servers account (for example, sassrv) should be a member of the SAS installer's primary group. All other users have no access to the directory.
- On Windows, the directory inherits access from its parent directory. The configuration grants read, write, and modify permissions to the SAS Spawned Servers account (sassrv).
As a precaution, a background process deletes any tables that are not cleaned up in the course of a transaction. Host access to the tables is achieved through the pooled server's launch credential or puddle login (if no pooling is involved, the SAS Spawned Servers account, sassrv, is used).
We recommend that you specify the same query cache for all Application Server contexts within a deployment of SAS Web Report Studio.

Note: If you are setting up a restricted client-side pool in order to do secure row-level permissions, designate a separate query cache directory for the additional deployment of SAS Web Report Studio and give only the restricted puddle login account access to the query cache directory.

Change the Location of the Query Cache Library

The default location for the library that is used for the query cache is SAS-configuration-directory\Lev1\SASApp\Data\wrstemp. After installation, you can specify a different location for this library. For performance purposes, the library should be created on a dedicated fast drive that has plenty of disk space (approximately 100GB, but the needed size will vary based on your system's use and the number of users). Backups are unnecessary because the cache files are temporary. Temporary files are accessed by the relational workspace server. Therefore, the wrstemp library should ideally be located on the same server. In addition, RAID configurations decrease performance and are not recommended.

Note: Do not use the WORK or SAS WORK library for this feature. The query cache will not function correctly if you use the WORK or SAS WORK library.

For clustered environments, the folder for this library needs to be exported to all nodes in the cluster (and you should specify the network address to this folder, not the local machine address). For non-clustered environments, or for a cluster that is restricted to a single physical machine, this folder does not need to be exported.

The following display shows the BI Rep Svc Wkspace Config 4.3 Properties where the settings for caching are configured:

Settings for the Query Cache

To view the query cache library, follow these steps:

On the Plug-ins tab in SAS Management Console, navigate to Application ManagementSAS Application InfrastructureConfiguration Manager.
Right-click BI Web Services for Java 9.3 and select Properties.
In the BI Rep SVC Wkspace Config 4.3 Properties, select the Settings tab.
Verify that the appropriate values are specified for each of the following fields:
- Query cache enabled: true
- Query cache SAS library name: SASApp-wrstemp
- Query cache server: SASApp — Logical Pooled Workspace Server
- Distribution Library: SASApp-wrsdist
Click OK to exit the.
If you modified the value for any property, you should restart the Web application server to enable the properties to take effect.

Disable the Query Cache

To disable the query cache in the SAS Management Console, follow these steps:

On the Plug-ins tab in SAS Management Console, navigate to Application ManagementSAS Application InfrastructureConfiguration Manager.
Right-click on BI Rep Svc Wkspace Config 4.3 and select Properties.
In the BI Rep SVC Wkspace Config 4.3 Properties, select the Settings tab.
In the Query cache enabled field, select false.

If you are certain that you will not use the query cache in the future, modify the Query cache SAS library name field by leaving it blank. If applicable, edit the autoexec_usermods.sas file to remove the library assignment. If there is a possibility that you will re-enable the query cache, then you should leave the library in place.
Click OK to exit.
To enable this change to take effect, restart your Web application server.