Administering Analysis Services Server

In this section, we will look at some of the important administrative activities for the server instance of SSAS. We will start with a review of the configuration settings for the server, followed by detailing the services needed for SSAS to run, and end with an introduction to the Analysis Services Scripting Language (ASSL) and its use in performing administrative tasks.

Server Configuration Settings

These settings are important for configuring the behavior of the SSAS server instance. We will only highlight ones that may be useful in regular administrative duties.

Review and Adjust Server Properties

To review and adjust the server properties, perform the following steps:

1. Open SQL Server Management Studio.

2. Connect to the Analysis Services server using the Object Explorer.

3. Right-click the server (topmost node) and choose Properties. The results are shown in Figure 7-1.

Figure 7-1

Lock Manager Properties

The lock manager properties are used to resolve deadlocking issues as they pertain to server behaviors in handling locking and timeouts. Of the three properties, only DefaultLockTimeoutMS should be altered without the guidance of Microsoft support. The DefaultLockTimeoutMS property defines the lock-request timeouts in milliseconds and defaults to none (-1).

Log Properties

These log properties control how and where logging takes place. Details related to the error logging, exception logging, flight recorder, query logging, and tracing are included in this property group. Some examples include the QueryLog\QueryLogConnectionString and QueryLog\QueryLogTableName properties, which are used to direct the server as to where the query logging will get persisted (database and table).

Memory Properties

The memory properties dictate how the server will utilize system memory resources. Only the LowMemoryLimit and the TotalMemoryLimit properties should be altered without the guidance of Microsoft support. The LowMemoryLimit represents a threshold percentage of total physical memory, at which point the server will attempt to perform garbage collection for unused resources to free more resources. The default value is configured at 75 percent of total physical memory. The TotalMemoryLimit is used to tell the server how much of the total physical memory of the server hardware should be made available for use by Analysis Services. This limit is configured to 80 percent of all server memory by default.

Network Properties

The network properties are used to control the network communication resources used by the server. Most notable are the settings that dictate whether the listener uses IPv4 or IPv6 protocols and whether the server will permit the use of Binary XML for requests or responses.

OLAP Properties

The OLAP properties control how the server will perform processing of the server objects (cubes, dimensions, and aggregations). Along with the processing properties, this section includes configuration properties for the way the server will process queries. Some of these query-processing properties are useful for simulating many testing scenarios. As an example, you could adjust the IndexUseEnabled, UseDataSlice, AggregationUseEnabled properties to benchmark different query-handling scenarios to determine if some of these optimizations are providing the desired performance enhancement.

Security Properties

These security properties are responsible for controlling how the server will handle permissions. Examples of these properties include RequireClientAuthentication, which is used to configure whether clients connecting to the server require authentication, and DisableClientImpersonation, which directs the server on how it should manage client use of impersonation.

Required Services

The core Windows services required by Analysis Services include SQL Server Analysis Services, SQL Server, SQL Server Agent, and SQL Server Browser. Most of theses services are obvious as to the role they play in supporting Analysis Services, but the SQL Server Browser service needs a bit of explanation. The SQL Server Browser service supports the Analysis Services redirector used when clients connect to named instances of Analysis Services.

All of these services are configured at the operating system level via Administrative ToolsServices (see Figure 7-2). As you would expect with configuring other services, the focus is on the logon account used by the service as the context in which to operate, the startup type used to detail if the service starts when the operating system starts, the recovery that indicates the actions taken by the service in the event of failure, and the dependencies that specify which other services are required for the service to function properly.

Figure 7-2

Commonly, the logon account used by any service should be one that has the least number of privileges required to function properly. More often than not, an account that has network rights will be required, and this account would need to be granted access rights on the remote resources in addition to configuring the account to be used by the service.

Analysis Services Scripting Language

We'll now direct your attention to how many of your administrative tasks can be automated by using the built-in scripting language, Analysis Services Scripting Language, or ASSL. This language is based on XML and is what client applications use to get information from Analysis Services.

This scripting language has two distinct parts. The first part is used to define the objects that are part of the server, including the objects used to develop solutions (measures and dimensions). The other part is used to request the server to perform actions, such as processing objects or performing batch operations.

We will focus on the scripting language components that help you manage the Analysis Services server. We'll start by looking at some examples of how you can use the language to process objects. Processing enables you to fill objects with data so they may be used by end users for business analysis. Some of the objects you can process include cubes, databases, dimensions, and partitions. To perform this processing using the scripting language, you will use the language's Process command.

An example of a script that would process the AdventureWorks employee dimension follows:

 <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">   <Parallel>     <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">       <Object>         <DatabaseID>Analysis Services Tutorial</DatabaseID>         <DimensionID>Employee</DimensionID>       </Object>       <Type>ProcessUpdate</Type>       <WriteBackTableCreation>UseExisting</WriteBackTableCreation>     </Process>   </Parallel> </Batch> 

Note that you can script many of the actions that you can configure in SQL Management Studio. For example, you can generate the example script shown here by right-clicking the AdventureWorks cube and selecting the Process Menu option. This will display the Process Cube dialog (see Figure 7-3). On this dialog, you click the Script button located along the top under the title bar and then select the location in which you want to generate the script.

Figure 7-3