Microsoft SQL Server 2000 Analysis Services Operations Guide

Published: November 2003

Summary: This paper describes techniques you can use to operate and maintain a Microsoft® SQL Server™ 2000 Analysis Services data warehouse.

On This Page

	Introduction
	Configuration Management
	Release Management
	Change Management
	Security Administration
	Service and Availability Management
	Capacity Management
	Problem and Incident Management
	Appendix A: Checklist for Operations
	Appendix B: Resources
	Appendix C: How to Tune the Process Buffer Size
	Appendix D: Sample Script for Changing the Data Folder Location
	Appendix E: Sample Script for Creating Repository Audit Triggers
	Appendix F: Sample Script for Creating an OLAP Linked Server
	Appendix G: Sample Script To Verify Analysis Services Availability
	Appendix H: Sample Script to Determine When Lazy Processing is Complete
	Appendix I: Sample Script to Determine Whether Data Slices Have Been Set
	Appendix J: Sample Script to Determine the Analysis Services Edition
	Appendix K: Data Folder Structure
	Database Folder
	Cube Folder

Introduction

Every administrator who operates the Microsoft® SQL Server™ 2000 Analysis Services portion of a data warehouse faces certain common operational issues. Analysis Services and its environment must be configured appropriately. The Analysis Services application must be deployed from the development environment to the production environment. Change control must be employed to ensure that changes to an existing environment are fully tested, and that approved changes are deployed properly. Capacity issues must be anticipated proactively. Problems must be resolved quickly and consistently. The agreed-upon availability of the Analysis Services cubes for querying must be ensured.

This paper provides guidance to assist administrators with the operation and maintenance of their Analysis Services databases as components within an existing IT and database infrastructure. You should integrate existing structures and techniques to solve Analysis Services operational issues, rather than inventing new processes just for Analysis Services. For example, you should employ the same incident tracking and problem resolution techniques that you use with your relational databases; use the same automation techniques to schedule jobs and scripts; and employ the same change control techniques to ensure change is controlled, tested, and documented.

The guidance provided by this paper is presented within the structure of the Microsoft Operations Framework (MOF) methodology. MOF is a representation of the cyclical process that any operation goes through, which is divided into four quadrants: changing, operating, supporting, and optimizing. This paper addresses the changing, operating, and supporting quadrants in the following sections:

For information on MOF's fourth quadrant, optimizing, go to the Microsoft Technet Web site (http://www.microsoft.com/technet) and see "Microsoft Analysis Services Performance Guide."

The guidance presented here is based on the collective experience of the Microsoft Business Intelligence (BI) Practices team and the Analysis Services development team. In addition to the techniques discussed in this paper, you should also apply SQL Server 2000 Service Pack 3 (SP3) to the computer on which Analysis Services is running (the Analysis server) and update Microsoft PivotTable® Service (PTS) on each Analysis Services client computer (run Ptslite.exe in the ..\Msolap\install\pts folder for SP3). Updating PTS on each client computer is particularly important because the client-server architecture of PTS places a significant portion of the PTS code on each client computer and SP3 includes significant performance and security enhancements to the client-side components of PTS. For information on determining the level of service pack that has been applied to an Analysis Services installation or to a client computer, see "Verifying the Appropriate Service Pack Level" later in this paper. For information on automating the installation of SP3 on each client computer, go to Microsoft Knowledge Base (support.microsoft.com) and see the article "Ptssetup.exe Sample Automatically Downloads and Installs OLAP Client."

Top of page

Configuration Management

Before you release and deploy your BI application, you must install and configure the appropriate edition of the Windows operating system and Analysis Services on the computer that will host the application. You should maintain a record of the actual configuration of this computer, the operating system, and the installed services and applications in a run book. Having a written record containing this configuration information will help you rebuild the server in the event of a disaster. You can also refer to this written record whenever you need to obtain configuration information for troubleshooting. This run book should also contain information about systems from which data is received, and contact information about people who should be contacted in the event of an emergency. For information about the detailed information that you should keep in your run book, go to the Microsoft Technet Web site (http://www.microsoft.com/technet) and see "Appendix: Contents of a Run Book" in Microsoft SQL Server 2000 High Availability Series, Volume 1: Planning Guide. While the topic list in the Planning Guide is primarily oriented to the RDBMS components of Microsoft SQL Server 2000, it does include topics specifically for SQL Server Analysis Services. In addition, there are many points in common, such as resource and contact information, details on the hardware configuration, and some operational and emergency tasks.

After you release and deploy your BI application, there are a number of configuration settings that you need to monitor to see if they require modification as conditions change. If you change any configuration settings, you must update the information in the run book to ensure that all members of your administration team can quickly determine the current configuration of Analysis Services and the Microsoft Windows® operating system. For more information on managing change, see "Change Management" later in this paper.

You should also document all of the objects in Analysis Services. For example, you can use OLAP Scribe, which is a Microsoft Word template that allows you to generate complete documentation of Analysis Services using Decision Support Objects (DSO). You can obtain this template at http://go.microsoft.com/fwlink/?LinkId=22012.

Configuring the Windows Operating System

Configuring the Windows operating system for optimum Analysis Services performance consists primarily of configuring processors, the Windows paging files, and memory. You can also disable services that are not needed.

Note   Microsoft recommends that Analysis Services not be installed on a domain controller. There are situations where this is required, such as when Analysis Services is installed with Microsoft Small Business Server or on a standalone domain, but, in general, you should avoid the configuration if possible. For more information, go to Microsoft Knowledge Base (support.microsoft.com) and see the article "INF: Running OLAP Services on a Domain Controller."

Processor

If you are running Analysis Services on a multiple processor computer, Analysis Services schedules threads on all available processors in the computer. Unlike the SQL Server service, Analysis Services does not natively support processor affinity to control the processors on which its threads will execute. Because Analysis Services is highly multithreaded, Analysis Services can consume all available processing resources. For this reason, you should use a dedicated server for Analysis Services in most cases. If you must share the computer resources with other server applications, you should select a server application that supports processor affinity, such as SQL Server. By setting processor affinity in SQL Server, you can control the processors executing the SQL Server threads and the priority of these threads, to ensure that sufficient processor resources remain available for Analysis Services threads.

If you need to control the processors on which Analysis Services threads execute, you should also consider using Microsoft Windows Server™ 2003 Enterprise Edition or Windows Server 2003 Datacenter Edition. These editions of Windows Server 2003 include the Windows System Resource Manager (WSRM), which allows an administrator to set processor and memory allocation policies for applications running on the server. WSRM enables you to select the Analysis Services process and limit Analysis Services threads to specific CPUs or to a specific threshold of processor resources. For more information on WSRM, go to the Windows Server 2003 Web site (http://www.microsoft.com/windowsserver2003/techinfo/overview/wsrmfastfacts.mspx) and see the article "Windows System Resource Manager-Fast Facts."

Paging Files

By default, Windows uses a single paging file equal to approximately 1.5 times the amount of physical memory in your computer. However, because Analysis Services makes extensive use of Windows paging files, you should always add a second paging file equal to the amount of physical memory on your computer. The SQL Server relational and multidimensional runtime engines work with memory very differently. The SQL Server relational engine directly maps and controls physical memory usage, while the Analysis Services multidimensional engine relies on the Windows operating system to allocate additional memory (physical or virtual) to the Analysis Services address space as needed. As a result, when the Windows operating system reduces the Analysis Services working set because other applications require allocations of physical memory, Analysis Services may need to use the paging file for its memory needs. You must ensure that the total paging file space is more than that configured by default, so that Analysis Services has sufficient virtual memory if the Windows operating system has insufficient physical memory.

While the Windows operating system has provisions for effectively controlling the general use of memory, Microsoft strongly recommends that customers configure servers with an adequate amount of memory so that extensive paging does not occur. If the main processing component of Analysis Services, the msmdsrv process, causes extensive paging, processing performance suffers.

Memory

Processes (such as Analysis Services) running in Windows 2000 Server or Windows Server 2003 Standard Edition can address a maximum of 2 gigabytes (GB) of RAM in the main process space. If you are working with large or complex cubes, Analysis Services may require more than 2 GB to load dimensions into memory, process dimensions, load replica dimensions, and still have sufficient memory for an effective query results cache. To allow Analysis Services to address more than 2 GB of RAM in a single process, you must install Windows 2000 Advanced Server; Windows 2000 Datacenter Server; Windows Server 2003 Enterprise Edition; or Windows Server 2003 Datacenter Edition.

Windows Server 2003 Enterprise Edition and Windows Server 2003 Datacenter Edition, are available in 32-bit and 64-bit versions. The 64-bit version supports the 64-bit version of Analysis Services. Because Windows 2000 Advanced Server and Windows 2000 Datacenter Server are 32-bit operating systems, only the 32-bit version of Analysis Services can be installed.

For more information on setting the /3GB switch, go to Microsoft Knowledge Base (support.microsoft.com) and see the article "INF: How to Enable Analysis Server To Use 3 GB of RAM.". For more information on setting the Memory conservation threshold value, see "Configuring Analysis Services" immediately following this section.

Disabling Unnecessary Services

While there is no complete list of the Windows services that are not required on an Analysis Services computer, turning off services that are not required will save memory for use by Analysis Services. Among the services that you might not need are the following:

You can turn off a service by either disabling the service or setting the service to start manually. If you set a service to start manually, Windows starts the service if it is needed.

Note   Viruses can also start services that are set to manual.

If you disable the service, Windows cannot start the service. For a complete listing of Windows 2000 services and their functions, go to the Microsoft Windows 2000 Web site (http://www.microsoft.com/windows2000) and see the article "Glossary of Windows 2000 Services."

Note   If you are running SQL Server 7.0, do not disable the remote registry service. This service is required to administer a remote Analysis Services installation.

Important   You should disable the Indexing Service to avoid locking problems and possible corruption during processing. You should also configure any anti-virus software on the machine so it does not scan the Analysis Services Data folder or the Temporary file folder. To locate these folders using Analysis Manager, right-click the server, select Properties, and then view the information that appears on the General tab.

Configuring Analysis Services

After you install Analysis Services, there are a number of configuration settings that you should always check (several of which you should generally change). Most configuration settings can be changed using Analysis Manager, although there are several settings that you must change by editing the Windows registry directly. This paper discusses the most important configuration settings. For information on additional configuration settings related to performance, go to the Technet Web site (http://www.microsoft.com/technet) and see "Microsoft Analysis Services Performance Guide." For information about the entire range of registry entries for Analysis Services, go to the Microsoft MSDN® library (msdn.microsoft.com) and see "Registry Entries for Microsoft SQL Server 2000 Analysis Services." If you want to monitor the registry keys that are written to by Analysis Services (or by any other Windows application), you can use various utilities that are available for the Windows platform. A commonly used utility is Regmon from Sysinternals (www.sysinternals.com). This freeware registry monitoring utility shows the applications that are accessing the registry, the keys they are accessing, and the registry data they are reading and writing.

Note   Some features of Analysis Services are only available with the Enterprise Edition. Use the sample DSO script provided in Appendix J, "Sample Script to Determine the Analysis Services Edition," later in this paper to determine the edition of Analysis Services that you are using.

Memory Settings

Having sufficient memory for Analysis Services increases query responsiveness and processing performance. Properly configuring available memory will maximize the use of memory, limit the use of disk resources for processing, and prevent the cleaner thread from evicting cache entries too quickly. The amount of memory used by Analysis Services for various purposes is regulated by a number of memory settings:

These settings are configured using default values or based on the amount of physical memory in the computer during installation. Changing some of these memory settings is generally recommended.

High and Low Memory Settings

Analysis Services employs a number of mechanisms to keep the amount of memory allocated to it within the range that is defined by two settings on the Environment tab of the Server Properties dialog box in Analysis Manager: the Memory conservation threshold and the Minimum allocated memory settings (the HighMemoryLimit and the LowMemoryLimit values in the registry). The default value for the Memory conservation threshold setting is the amount of physical memory on the computer at the time of installation. The default value for the Minimum allocated memory setting is half the amount of physical memory on the computer at the time of installation. If you change the amount of memory on the computer after installation, you must manually modify these values. Otherwise, Analysis Services will not properly utilize the actual amount of physical memory on the computer.

Analysis Services uses a cleaner thread to reduce the amount of memory allocated to Analysis Services when the amount of memory allocated reaches the halfway point between the Memory conservation threshold setting and the Minimum allocated memory setting. When the cleaner thread is activated, it begins evicting entries in the query results cache, based on a cost/benefit algorithm that takes into account a variety of factors, including how frequently the data in the query results cache is being used, the amount of resources that were required to resolve the entries, and the amount of space being consumed by related entries. By default, the cleaner thread runs at below-normal priority. The frequency with which it runs is determined by the BackgroundInterval registry setting. The default value is thirty seconds. This setting actually governs the number of seconds between processing periods for a variety of background tasks, including the cleaner thread, query logging, and lazy processing. If you want to set an interval for the cleaner thread separate from these other background tasks, add the CleanerInterval registry key and set a value just for the cleaner thread.

When the amount of memory used by Analysis Services exceeds the Memory conservation threshold setting, Analysis Services increases the priority of the cleaner thread to normal in order to quickly reduce the allocated memory to the Minimum allocated memory setting. If the total memory allocated to all Analysis Services tasks exceeds the memory conservation threshold by more than approximately 6.25 percent, Analysis Services immediately begins dropping the cache entries for entire cubes in order to quickly reduce the amount memory used by Analysis Services. In this scenario, because Analysis Services is shedding memory extremely quickly, the total amount of allocated memory may drop below the Minimum allocated memory setting.

If you set the Minimum allocated memory setting too low, the cleaner thread removes too many cached entries from the query results cache. This reduces query response times and requires additional resources to repopulate the query results cache. For example, suppose your computer has 2 GB of physical memory and you set the Memory conservation threshold setting to 1.4GB and the Minimum allocated memory setting to 100MB. If memory usage ever goes significantly above 1.4 GB, the cleaner thread aggressively drops entries from the query results cache, down to 100 megabytes (MB) or even less. Analysis Services must then rebuild the entries in the query results cache from newly submitted queries. A more appropriate Minimum allocated memory setting for this system is approximately 1 GB, which gives the cleaner thread room to perform its job without unnecessarily throwing away cache entries when the amount of allocated memory exceeds the Memory conservation threshold setting.

As you can see, setting the Memory conservation threshold setting too low will also reduce overall performance, and may result in out-of-memory errors. You should never set the Memory conservation threshold setting to more than the amount of physical memory on the computer (otherwise the paging files will be used excessively). If you enable the /3 GB switch, you should not set the Memory conservation threshold setting to more than approximately 2.7 GB. Setting this value slightly below the 3-GB memory limit ensures that the cleaner thread has sufficient time to respond to low memory conditions and to reduce allocated memory before Analysis Services uses the entire 3-GB address space. The memory conservation threshold does not directly limit the amount of memory used by Analysis Services, which means Analysis Services can run out of address space in the main process space or use more memory than is physically present on the computer.

Tip   If you add memory or enable the /3 GB switch in the boot.ini file, increase the Memory conservation threshold and Minimum allocated memory settings in Analysis Manager.

Note   If you have not installed Analysis Services Service Pack 3, you must modify the HighMemoryLimit value by editing the registry directly to enable Analysis Services to address more than 2 GB of memory rather than using the Memory conservation threshold setting in Analysis Manager. In SP3, Analysis Manager was changed to allow an administrator to enter a number larger than 2 GB (up to 3 GB). In SP2 and earlier, Analysis Manager would only allow settings between 1 and 2047 MB. For more information, go to Microsoft Knowledge Base (support.microsoft.com) and see the article "INF: How to Enable Analysis Server To Use 3 GB of RAM."

Very Large Dimension Memory (VLDM) Threshold

The 32-bit version of Analysis Services (the 64-bit version does not use VLDM) attempts to prevent large dimensions from using all of the available virtual memory address space by loading each very large dimension at startup into its own process space with its own virtual memory address space. A very large dimension is one that exceeds the value of the VLDMThreshold setting in the registry. The default VLDM threshold is 64 MB. While using a separate address space for each dimension that exceeds the VLDM threshold does save virtual memory address space for other uses in the main process, overall performance slows when one or more dimensions exceed the VLDM threshold. Loading all dimensions into the main process space (when possible) yields better performance, but you must ensure that there is sufficient virtual memory address space to perform the following:

If Analysis Services does not have enough virtual memory address space in the main process space, set the VLDM threshold so that only the largest dimensions are loaded into separate address spaces. For more information on how Analysis Services uses available memory and how to calculate the amount of memory required, see "Capacity Management" later in this paper.

If Analysis Services has sufficient virtual memory address space in the main process space, disable VLDM by renaming the msmdvldm.exe file in the Bin folder to some other file name (such as msmdvldm-disabled.exe). When the service starts, if it can't find the VLDM executable, the service disables it. VLDM is automatically disabled on a 64-bit system. Disabling VLDM ensures that all dimensions are loaded into the main process space.

With all of the performance and restrictions associated with VLDM, the usual best practice is that if your application is large enough to be forced into using VLDM, then you should evaluate whether SQL Server 2000 (64-bit) will provide better performance.

Important   In general, you should consider using the 64-bit version of Analysis Services if:

For more information, go to the Technet Web site (http://www.microsoft.com/technet) and see "Microsoft SQL Server 2000 (64-bit) Analysis Services: Why Migrate, and What to Expect If You Do."

Note   When the VLDM threshold is used for large dimensions, Analysis Services creates the shadow dimensions for these large dimensions during processing in the main address space. Thus, even when you are using VLDM, there is still a considerable impact on the virtual address space of the main process.

Process Buffer

Analysis Services creates a process buffer in memory for each partition it processes. It allocates memory to each buffer as it is needed, and releases this memory from each buffer when partition processing is complete. Analysis Services uses each buffer for two separate tasks.

The Process buffer size setting on the Processing tab in Analysis Manager (the ProcessReadSegmentSize value in the registry) determines the maximum size of each process buffer. By default, the maximum size of each process buffer is approximately 32 MB. For most applications, this is probably too small and should be immediately increased. A more effective setting is at least 150 to 200 MB.

If the size of each process buffer is large enough to efficiently sort and index large portions of the fact data before it is written to segments in the partition file, overall data organization and query responsiveness improve. Furthermore, if the fact table contains many duplicate records, a large process buffer allows Analysis Services to merge duplicate records in memory, saving space and improving query performance.

If Analysis Services exceeds the size of a process buffer while creating aggregations, Analysis Services changes its algorithm to use temporary files that augment the memory allocated to the process buffer. If temporary files are used, Analysis Services moves aggregations between these temporary files and the memory allocated to the process buffer as the aggregations are being calculated. Reading and writing these temporary files is much slower than in-memory calculations and very I/O-intensive. You should tune your system to eliminate the use of these temporary files by increasing the Process buffer size setting when possible. All aggregations for a partition are calculated at once and must fit into memory; otherwise temporary files are used.

When processing multiple partitions in parallel or processing an entire cube in a single transaction, you must ensure that the total memory required for the process buffers, dimensions, shadow dimensions, replicas, and other memory requirements do not exceed the Memory conservation threshold setting. If Analysis Services runs out of virtual address space for these simultaneous operations, you receive an out-of-memory error. If you have insufficient physical memory to back the virtual memory, the Windows operating system uses the virtual memory paging files to supplement the available physical memory. While the use of the paging files has performance implications if excessive paging occurs, a small amount of paging (approximately 100 to 200 MB) is generally acceptable if necessary.

On the other hand, if the process buffer setting is too large and if the number and size of the aggregates is large enough to fill the process buffer during processing, Analysis Services may exceed the memory conservation threshold (which causes the query response cache to be trimmed or dumped). Exceeding the memory conservation threshold during processing causes temporary files to start being used. Remember that if you are processing partitions in parallel, each partition uses a separate process buffer.

Tip   If you have sufficient memory, increase the Process Buffer Size setting to at least 150 - 200 MB to eliminate the use of temporary files during processing. It is not uncommon to set the process buffer size to 300 or 500 MB on servers with large cubes. To determine an appropriate process buffer size, follow the procedure in Appendix C, "How to Tune the Process Buffer Size," later in this paper.

Data and Temporary File Locations

An Analysis Services instance has a Data folder and a Temporary folder. Analysis Services uses the Data folder to store the multidimensional structures for all the objects defined on the Analysis Services instance. It uses the Temporary folder to supplement the memory allocated to each process buffer when a process buffer is too small for the aggregations being processed. The default location for both of these folders is C:\Program Files\Microsoft Analysis Services\Data. You can change the location for either or both during setup or after installation. To change the location after setup, right-click the Analysis server object in Analysis Manager, and then click Properties. You can also use the sample script provided in Appendix D, "Sample Script for Changing the Data Folder Location, to change the Data folder programmatically.

Note   If you use virus-scanning software on the Analysis Services computer, you should disable scanning of the Analysis Services Data, Temporary, and Bin folders.

You should place the Data folder on its own RAID array; RAID 10 or RAID 1 + 0 provides the best performance but RAID 5 is frequently fast enough for many Analysis Services installations. The main activity of Analysis Services is reading data from the files in the Data folder in response to user queries, not writing to files in the Data folder. Once you determine the amount of space required for the data, index, and aggregation structures, you should allocate approximately double that amount of disk space to allow sufficient space to enable you to refresh the data and hold shadow files during processing. For more information on calculating the amount of space required for the data folder, see "Disk" in the "Capacity Management" section later in this paper. For information about each type of file stored in the Data folder, see Appendix K, "Data Folder Structure."

Note   Because the Data folder stores security files that control end users' access to Analysis Services objects, you must secure the Data folder against unauthorized access. Only members of the OLAP Administrators group and the Administrators group should have access to the Data folder. If you move the Data folder location after installation, you must configure these security settings manually. For more information on securing Analysis Services, see "Security Administration" later in this paper.

Temporary Folder

You should place the Temporary folder, if it is actually used, on a RAID array that yields excellent write performance and that is on a different physical drive than the Data folder. Consider using RAID 0, 1, 0+1, or 10 depending on your budget requirements and amount of use. However, for best performance, it is more important to allocate a sufficiently large process buffer to obviate the need for temporary files during processing. If processing requires temporary files, the algorithm is an order of magnitude slower than if the process buffer was large enough to perform the processing entirely in memory. If you find that the files in the Temporary folder structure are used extensively and you cannot eliminate their use, you can add a second Temporary file folder on a different physical drive by adding the TempDirectory2 registry key (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\Current Version) and specifying a location on a separate physical drive for the second Temporary folder. When you must use temporary files, using two Temporary folders increases processing performance because the data in one Temporary folder is sequentially read, merged with new segment data, and then written to the second Temporary folder (in 64-kilobyte segments). The data in the second Temporary folder is then read, merged with new segment data, and written to the first Temporary folder. This process continues until the calculation of aggregations is complete. To determine whether the Temporary folder is being used, see Appendix C, "How to Tune the Process Buffer Size" later in this paper.

Data Source Configuration

When you create a new database within an Analysis Services instance, one of your first tasks is to define the data source for the database. A data source contains the information necessary to access source data for the database objects. The term "data source" actually refers to the data source object that is created, not the source data itself. When you define the data source in Analysis Manager, the name given to the object is either <server_name>-<database_name> or <localhost>-<database_name>. However, to eliminate confusion when the database is moved to another server, you should change the default naming convention by creating a logical name for the data source unrelated to the name of the original computer on which the database was initially created.

To create a logical name for the data source object in Analysis Manager, create the data source object. Then copy the new data source object and paste it into the same Analysis Services database. You are then prompted to define a new name for the data source object. The name you choose should reflect the logical type of data such as Sales Data, or Personal Data. After you define the new logical name, delete the original data source object. Thereafter, when you move an Analysis Services database between computers, you can simply change the underlying server and database in the connection string by modifying the properties of the data source object in Analysis Manager (or in your script).

In addition to renaming your data sources to logical rather than physical names, you should ensure that your deployment computers use the same name. If your development computer has its data source named Sales Data, then your QA computer should have its data source named Sales Data, and your production computer should have its data source named Sales Data. Using consistent names across the development, QA, and production computers makes migration of individual pieces easier by cutting and pasting between the Analysis Services databases.

If you do not change the name of the data source object before you create objects in the database, you will not be able to change the name of the data source object without using third-party utilities. For more information on tools you can use when moving a database between Analysis Services instances, see "Release Management" later in this paper.

Service Accounts

To understand the permissions required for the MSSQLServerOLAPService and SQL Server Agent service accounts, you need to understand the security context in which various operations are executed. Certain tasks are performed in the context of the logged-on user, and other tasks are performed in the security context of the MSSQLServerOLAPService service account.

It is not uncommon for users to assume that if they can create objects, then they can process them. While this is frequently the case in a simple one-computer development environment, in a multiple-computer production department you're likely to encounter problems. With source databases residing on separate servers from the Analysis Services database, and the Analysis Services instance being managed remotely, frequently the first problem you experience when you roll out such an application into production is insufficient permissions.

You must ensure that the MSSQLServerOLAPService and the SQL Server Agent service accounts have sufficient permissions for the tasks they need to perform. At a minimum, the service account must be a member of the OLAP Administrators group. This permission is needed for any user (or service that is acting on behalf of a user) that manages an Analysis Services server.

MSSQLServerOLAPService

By default, the MSSQLServerOLAPService service runs under the local system account, which has full administrator permissions on the local computer, but no access rights to remote computers. Because the local system account has no network access, in many cases you will need to change the service account to an account that can be granted network access rights. With Windows 2000, this will be a domain user account. If you are using Windows Server 2003, you can use the NetworkService account.

The account under which the MSSQLServerOLAPService service runs must have sufficient permissions to perform several different tasks. The MSSQLServerOLAPService service must be able to process Analysis Services objects, access source data during processing, and receive security credentials in a multiple-tier environment.

Tip   With the named pipes network protocol, the process (such as an Analysis Services processing operation) attempting to access an application (such as SQL Server) on a remote computer must be authenticated by the Windows operating system before the process can be authenticated by SQL Server. With TCP/IP sockets, the process does not normally have to be authenticated by the Windows operating system before presenting its authentication credentials to SQL Server.

SQL Server Agent

When you automate object creation tasks (such as for creating partitions) or object processing using SQL Server Agent, the service account used by the SQL Server Agent service must have sufficient rights to create or process the object.

Processing

When you run the MSSQLServerOLAPService service under a domain user account (or the NetworkService account), you must add this account to the OLAP Administrators local group on the Analysis Services computer to enable Analysis Services to process dimensions, partitions, and mining roles on that computer. Membership by this account in the OLAP Administrators group allows the MSSQLServerOLAPService service to access the registry, the Data folder, and the Temporary folder. User accounts that are not members of the OLAP Administrators group should not have access to these locations.

Source Data Access

The MSSQLServerOLAPService service account must also have logon account permissions to access the source data in the source database if trusted connections are used to access the source data. With trusted connections, the MSSQLServerOLAPService service account is used to connect to the data source. If trusted connections are not used, you can specify a user name and password.

The permissions required when connecting to a data source also depends on the type of storage structure used for the Analysis Services partitions. When MOLAP storage is used, the service account must have at least SELECT permissions on the source database. If ROLAP or HOLAP storage is used, the service account must have at least SELECT and CREATE TABLE permissions on the source database.

There are also permissions needed if using resources from other Analysis Services servers. If using linked cubes, then the MSSQLServerOLAPService service account must have read access on the cube and cell-level security must not be defined. If Analysis Services is a remote partition publisher, then the MSSQLServerOLAPService service account must be in the OLAP Administrators group on the machine where the remote partition is located. If Analysis Services is a remote partition subscriber, then the MSSQLServerOLAPService service account must have read access on the cube.

Receive Client Security Credentials Via Middle-Tier Application

The MSSQLServerOLAPService service account is irrelevant in the typical client-server environment. In this environment, the user application connects directly to the Analysis Services computer to execute a query or create an object, and passes the user's credentials directly to Analysis Services for evaluation. Access is granted or denied by Analysis Services based on cell-level and dimension-level security.

However, if the client application attempts to connect to Analysis Services through a middle-tier server, the authentication process is not quite so simple. Normally, security credentials cannot be passed over multiple computers. However, if the middle-tier application server and the Analysis Services computer support Kerberos authentication and delegation, the client's security credentials can be passed by the middle-tier application to Analysis Services.

For Kerberos authentication, delegation, impersonation, and mutual authentication to work, the MSSQLServerOLAPService service must run under one of the following types of accounts:

SQL Server Agent Service

The SQL Server Agent service is used to run the Analysis Services Processing task in a Data Transformation Services (DTS) package, and to perform jobs containing DSO operations executed via Microsoft Visual Basic® Scripting Edition (VBScript) scripts. To ensure that the SQL Server Agent service account has appropriate permissions to perform these tasks, run the SQL Server Agent service under a domain user account (or the NetworkService account in Windows Server 2003) and then add this account to the OLAP Administrators group on the Analysis Services computer.

Detecting permissions problems with SQL Server Agent can be somewhat confusing unless you understand that when you run a SQL Server Agent job interactively (right-click the job in SQL Server Enterprise Manager, and then click Run) the security credentials used are not the credentials of the SQL Server Agent service account. When you run a SQL Server Agent job interactively, the security credentials of the user that initiates the job are used. The only time the credentials of the SQL Server Agent service account are used is when the job is actually scheduled (unless you log on using the domain user account used by the SQL Server Agent).

Migrate the Repository

The meta data for the objects created in an Analysis Services instance (the cubes, dimensions, and so on) are stored in the Analysis Services repository. By default, this repository is a Microsoft Access database named msmdrep.mdb and is stored in the ..\Microsoft Analysis Services\Bin folder on the Analysis Services computer. The Access format is used so that users who do not use SQL Server for relational data can still use Analysis Services. However, if you do use SQL Server, migrating the repository to a SQL Server database adds enterprise-level scalability, support, and security. Migrating the repository also enables you to perform coordinated backups of the repository database with a file-based backup of the Data folder. For more information, see "Backup and Recovery" later in this paper.

Before you migrate the repository, create a dedicated database (such as a database named OLAPRepository) using a case-insensitive collation. A dedicated database enables you to back up the repository database on its own schedule. While you can create this dedicated database on a SQL Server instance located on a remote computer, for best performance you should create this database on a local SQL Server instance. To migrate the repository to SQL Server, use the Migrate Repository Wizard and choose Analysis Services native format.

Important   Under most circumstances, do not migrate the repository to the msdb database, which is the default database selected by the Migrate Repository Wizard. While the msdb database is appropriate for a single SQL Server instance dedicated to the Analysis Services repository, it is not appropriate for the typical shared environment. If you select the msdb database, the Analysis Services repository is shared with all other SQL Server system-level resources in that instance, such as database maintenance jobs, replication definitions, DTS packages, and execution logs of all different types. By using a dedicated database, you can back up and recover the repository on its own schedule and independent of the other objects stored in the msdb database.

After you migrate the repository to a SQL Server database, you cannot migrate it back to a Microsoft Access database. Migrating does not remove the msmdrep.mdb database. For added security, you should remove the msmdrep.mdb database (by deleting the file using Windows Explorer) after you successfully complete the migration. If the migration fails for any reason, Analysis Services discards any changes and continues using the msmdrep.mdb database.

Tip: To determine the location of the repository on an unfamiliar Analysis Services instance, right-click the server object in Analysis Manager and then click Edit Repository Connection String. This option was added with SP3. Before SP3, you could review the connection string in the registry. However, after SP3, this connection string in the registry is encrypted and using the Edit Repository Connection String command in Analysis Manager is the only way to view the current repository.

Logging and Error Reporting

Analysis Services records a query log to enable you to analyze query patterns and improve your aggregation design. You can configure the properties of this query log. You can also enable a processing log and enable Analysis Services error reporting.

Query Log

To enable the Usage Based Optimization Wizard to design aggregations based on past usage patterns and to enable the Usage Analysis Wizard to generate reports analyzing query usage, Analysis Services records the levels touched by every Nth query in a query log, which is stored in a Microsoft Access database. By default, every tenth query is logged. The default location for the query log is C:\Program Files\Microsoft Analysis Services\Bin\msmdqlog.mdb. This file, like any log file, should be secured from unauthorized access.

You can change the logging interval (e.g. every Nth query), stop all query logging, or clear the query log. Setting the logging frequency too low may adversely affect performance. Increasing the logging frequency above 10 might increase performance, particularly if you are on a system with hundreds of concurrent users generating many queries per second. On such a system, Analysis Services attempts to log many queries very quickly, and Access cannot write this volume of information as quickly as a high-performance database system.

If there appears to be considerable activity to the query log, or for additional stability and recoverability, consider migrating the query log to a dedicated SQL Server database. While there is no built-in migration facility within Analysis Services, it is a fairly straightforward process. Simply export the QueryLog table within the msmdqlog.mdb Access database to a SQL Server database, edit the QueryLogConnectionString registry setting, and restart Analysis Services. If you do so migrate the query log, remember to change your backup and recovery procedures accordingly.

In addition, you should consider clearing the query log after you run the Usage-Based Optimization Wizard consecutively on all cubes on the server. To modify the query log properties, right-click the Analysis server object in Analysis Manager, click Properties, and then click the Logging tab.

Note   The query log records queries on a server-wide basis, not on a per-cube basis.

Processing Log File

Analysis Services does not record a processing log by default; you must enable the recording of a processing log file. The processing log file is a system-wide text file containing all of the processing information you see displayed in the Process dialog box in Analysis Manager when you process a partition, dimension, or mining model. Recording a processing log file enables you to:

To enable a processing log file, right-click the Analysis server object in Analysis Manager, click Properties, and then click the Logging tab. Because the processing log can become quite big, you should periodically rename the old one and have Analysis Services begin a new one (perhaps once per month or once per quarter, depending upon how often you process objects). Renaming the existing processing log file periodically enables you to keep a running history in files of reasonable size.

While the system-wide processing log is normally used for most applications (such as Analysis Manager), at the DSO level you can configure applications to redirect the output to other locations. As a result, you need to be aware that the processing log might not contain all of the processing requests performed on the system. For example, the processing log generated by the DTS OLAP Processing task is redirected to the DTS log file-and its processing will not be captured in the system-wide processing log. The DTS OLAP Processing task is the only Microsoft-supplied application that redirects its activity into a separate log file.

Tip   Choose the same location on every server to ensure the file is easy to locate on each of the Analysis Services computers that you are administering.

Note   If you are using the Parallel Processing Utility (from the SQL Server 2000 Resource Kit) to process Analysis Services partitions, you can also specify a log file name on the ProcessPartition.exe command line (such as a datetime stamp) and then save these log files for troubleshooting purposes.

Error Reporting

When a fatal error occurs in Analysis Services, you can choose to have Analysis Services automatically send an error report to Microsoft. Microsoft then uses this information to improve Analysis Services, treating all user information as confidential. To enable error reporting, right-click the Analysis server object in Analysis Manager, click Properties, and then click the Error Reporting tab.

Note   This option is only available with SP3.

Performance Configuration Issues

From an operational prospective, you can improve Analysis Services performance by keeping partition sizes reasonable, setting partition data slices, defining appropriate aggregations on all partitions, and running the Optimize Schema Wizard in the cube editor. For more information on each of these configuration issues, go to the Technet Web site (http://www.microsoft.com/technet) and see "Microsoft Analysis Services Performance Guide."

Partition Size

You can divide a cube into multiple partitions if you are using SQL Server 2000 Enterprise Edition (or SQL Server 7.0 Enterprise Edition) to increase query and processing performance. As a general guideline, you should consider increasing the number of partitions if a partition file exceeds 5 GB or 20 million records. Smaller partitions require less time to query by minimizing the amount of data read on a partition scan. Multiple partitions require less overall processing time because each partition is smaller, and some partitions will not have to be processed if the new data does not affect those partitions. To determine the size of each partition, review the partition files in the Data folder. For detailed information on each type of file in the Data folder, see Appendix K, "Data Folder Structure," later in this paper.

Data Slice

When you partition a cube, you should define the data slice for each partition using the Partition Wizard. The Partition Wizard does not require you to set this data slice when you create a partition. As a result, it is possible (even easy) to create a partition without setting the data slice. In fact if you simply click through the Partition Wizard pages taking the default values, you end up with a partition without a data slice being set. You should verify that each partition of each cube in each Analysis Services database has a data slice defined. The only exception to this is if you have a cube with only one partition-in that case the data slice should not be set, because you want all of the cube data to be placed in that one partition. To verify that a data slice is defined, edit the partition in Analysis Manager and then step through the Partition Wizard pages. You can use the sample script in Appendix I, "Sample Script to Determine Whether Data Slices Have Been Set," to determine whether a data slice has been set on each multi-partition cube in an Analysis Services instance.

Defining the data slice enables Analysis Services to quickly eliminate irrelevant partitions from query processing. The data slice identifies the actual subset of data contained in each partition. Unless Analysis Services knows the range of data contained in each partition, it must query each partition, which negates much of the query performance benefit of partitions. To draw an analogy with SQL Server, creating a partition without a data slice is like creating a partitioned view without the CHECK clause. While you can do it, you force the query optimizer to scan all of the partitions in the view because you haven't given it enough meta data to figure out what partition to access when a query is issued. While the Analysis Service's runtime engine does not use a relational query optimizer (it has its own component that accomplishes a similar operation), it uses the data slice in roughly the same way: as meta data to tell it which partitions to scan if an aggregate cannot be used or is not available.

If you partition a cube by month, and have 36 months worth of data (in 36 partitions), and if you don't specify the data slice, then the runtime engine must scan all 36 partitions to answer a query. If you specify the data slice, it could potentially only have to scan 1/36^th the amount data, with an obvious improvement in performance.

Setting a data slice also causes Analysis Services to add a join and a WHERE clause to the SQL statement used for retrieving data from the source database during processing. The WHERE clause limits the data retrieved by the SQL statement to the data that belongs in the data slice. For example, if you say that a partition's data slice is June 2003, then Analysis Services adds a join to the time dimension and adds the WHERE clause:

WHERE <month field> = 'June' AND <year field> = '2003'

or whatever the appropriate member/level names are. If you do not define a data slice and you have multiple partitions, Analysis Services does not restrict the data that is retrieved from the source database. Without the data slice, if you just happen to have July 2003 data in the June partition, Analysis Services does not complain, it just double-counts the July 2003 data (for more information, see "Maintaining Partitions" in SQL Server Books Online). By specifying the data slice, the system can add these JOIN and WHERE clauses that assist in maintaining the integrity of the data.

You can suppress the automatic generation of a WHERE clause for all partitions on the Analysis server by modifying the DataCompressionSettings registry setting and add the hex value of 0x00100000 to the existing value for this key. If Analysis Services is loading data for each partition from separate tables in the source database, this may yield some performance benefits. However, you should not disable the automatic generation of the WHERE clause unless you are totally sure that the relational database partitioning is 100% correct when loading data.

Important   It is vital to reiterate that the DataCompressionSettings registry setting is a server-wide setting. You must be 100% certain of the correctness of the data in all partitions of all cubes on the server in order to use this setting safely. Without the WHERE clause for your protection, double-counting data (or many-times counting of data) may occur, which could lead to server crashes if inconsistent data is processed. If you disable the generation of the WHERE clause, you assume all responsibility for enforcing data integrity between the data source and the data slice.

Tip   If you are creating rolling monthly partitions as each month closes, you should ensure that the data slice is set for each new partition after it is created.

Aggregations

The most effective technique you can use to improve overall query responsiveness (assuming that the Analysis Services computer has sufficient memory and hard disk resources) is to design effective aggregations of fact data in each cube partition. However, too many aggregations will unnecessarily increase processing time without significantly improving query performance.

When you use multiple partitions to increase query and processing performance, it is possible to deploy a new partition with no aggregations. While deploying partitions with different aggregation designs is a common optimization technique, deploying a partition with no aggregations generally indicates an error in deployment that can cause performance problems. You should verify that a minimum number of aggregations exist in each partition. You can quickly determine whether aggregations are defined on a partition by looking at the combined sizes of the <partition>.agg.flex.data and <partition>.agg.rigid.data files for each partition. The minimum size should be at least 100 KB with most datasets, although this will obviously vary from very small to very large datasets. If you have less than that amount, it is quite possible that you have either no aggregates designed or too few.

Tip   Designing too many aggregations will slow processing and designing too few aggregations will slow querying. Ensure that all partitions have a minimum number of aggregations - perhaps 10%.

Schema Optimization

Running the Optimize Schema tool on a cube eliminates unnecessary joins between dimension tables and fact tables, if certain conditions are met. By default, when you first create a cube, Analysis Services constructs a SQL query against the fact table that is a "N+1"-way join (where N is the number of dimensions). If you have 5 dimensions, then you have a 6-way join (between the fact table and the 5 lowest-level dimension tables). From the join, the Analysis Services query extracts the lowest-level key. From that key, Analysis Services begins the aggregation process. Having a 6-way join is typically not a major performance problem in most modern relational database systems. However, if your cube has 15 or 20 dimensions, the resulting multi-table join may suffer significant performance problems. Regardless of the number of dimensions in your cube, the Analysis Services query to the relational database is resolved faster and the data flows into Analysis Services more quickly during processing if you eliminate some of these joins.

Fortunately, there is a common design technique that can greatly help the situation. Many star or snowflake schema designs are constructed in such a way that the foreign key that points from the fact table to the lowest-level dimension table is not some random number, but is also the member key itself. If that is true, then Analysis Services can "optimize away the join" and pull the member key directly from the fact table instead of using a join to the lowest-level dimension table.

However, certain conditions must be met for Analysis Services to eliminate a join between a dimension and the fact table. These conditions are:

If these conditions are met with respect to a dimension used in a cube, and the cube's schema is optimized using the Optimize Schema command, Analysis Services composes a query that does not contain a join to the dimension table in the database when processing the cube. If these conditions are met for all dimensions in the cube, the Analysis server needs to read only the fact table to process the cube. Processing time reductions often can be substantial when this optimization technique is used.

Note   Cube schema optimization applies to all partitions of the cube, whether the partitions are processed independently or as a group.

So, as a general rule, after you have designed the schema for a cube, you should run the Optimize Schema command. It removes the joins that meet the foregoing conditions. Next, you should determine which dimensions were not eliminated from the join and then determine how to meet the required conditions to eliminate the dimension table from the join. If you have partitioned your cube and specified the data slice, the dimension table used for the data slice cannot be eliminated. This join is set to protect you so that no additional, non-data slice data is included in the partition.

If you do optimize away a dimension, you should be aware that the inner join that you have just eliminated had a side effect that may expose problems with your source data. The inner join to the dimension table eliminates fact table records that do not have matching dimension table records (this is what an inner join will do). This means that when you remove the inner join and start using the fact table member keys, you may start seeing processing errors that you were not getting before. When Analysis Services processes a record in the fact table that does not have a corresponding entry in the appropriate dimension table, Analysis Services generates an error.

Important    If you recreate a cube, add a dimension to a cube, or remove and then re-add a dimension, you must rerun the Optimize Schema command to re-optimize the cube. New dimensions are always added un-optimized.

Verifying the Appropriate Service Pack or Hot Fix Level

Ensuring that you are working on an Analysis Services instance that has the latest service pack (or hot fix) can assist you in resolving problems. Similarly, when working on an Analysis Services client computer, you need to ensure that the most recent service pack (or any applicable hot fix) has been applied to that client. Unfortunately there isn't a quick and easy way to determine the service pack level (or hot fix) that has been applied to an Analysis Services instance or to an Analysis Services client computer.

To determine the level of service pack, including any hot fixes, there are four different files that you have to be concerned with to determine the level of your installation:

Top of page

Release Management

To move an Analysis Services database from the development environment to the QA and production environment, you can choose between three Microsoft supported mechanisms or an unsupported DSO/XML scripting utility distributed by Microsoft.

Microsoft Supported Mechanisms

Analysis Services provides functionality to help you archive an Analysis Services database on one instance and then restore that database to another Analysis Services instance. Analysis Services also provides functionality to copy and then paste the meta data for an Analysis Services database to a new database. Finally, you can also back up the Data folder and the repository and restore it on the destination server.

Note   You will also need to change the data source connection properties and the repository connection string after deploying an Analysis Services database on another server.

Archiving and Restoring

You can archive an Analysis Services database using Analysis Manager or by using the msmdarch.exe command directly (you cannot archive a single cube within a database). The archive file for an Analysis Services database consists of one or more .cab files that contain the entire contents of the database folder for the database being archived and meta data for the database and its objects from the Analysis Services repository. Data in remote partitions (a feature that is rarely used) and in writeback tables are not stored in the archive file. You must back up the data in remote partitions using a file-based backup method; back up the writeback tables using SQL Server backup.

Because the maximum size for a .cab file is 2 gigabytes (GB) and a file cannot span .cab files (both of these are restrictions of the .cab file technology and are not related to Analysis Services), the maximum size file within the Data folder that can be archived is 2 GB. You can archive more than 2 GB of data, provided that no single file is larger than 2 GB. The msmdarch.exe command will just create multiple .cab files. Since the partition file used to store the MOLAP fact tables is, by far, the largest data file, the partition size is typically the limiting factor. As a result, if you have any individual partition file that is larger than 2 GB, the Analysis Services database cannot be archived. If you are using the Enterprise Edition of SQL Server 2000, increasing your use of partitioning (in other words: adding more partitions, each one smaller) can reduce the size of each partition file below 2 GB to enable you to archive the entire database. (You can use the script provides in Appendix J, "Sample Script to Determine the Analysis Services Edition," to determine the edition of Analysis Services you are using.)

When you restore an archived database to an Analysis Services instance, using either Analysis Manager or msmdarch.exe directly, the Analysis Services file set and its meta data are returned to their states at the time the archive file was created. If you restore a database that has a remote partition, you must process the remote partition. If you restore a database with a write-enabled cube and its writeback table is not available, the cube must be processed before it can be used.

Note   Because archive and restore is copying the data along with the metadata, the process can take a long time.

Tip   Regularly validate your backup media by performing a restore to a test server. Besides validating the quality of your backup media, regular testing ensures that your backup and restore procedures work properly. Backups without regular validation are worthless and misleading in that they give you a false sense of security. You should validate your backup media at least monthly or quarterly.

Copying and Pasting

You can use Analysis Manager to copy the meta data for an Analysis Services database from one instance of Analysis Services to another Analysis Services instance, provided that both instances are registered in Analysis Manager. Because only the meta data is copied to the target server using this method for release management, you will need to process the Analysis Services database on the destination server (after updating the data source properties, if necessary) before users can query the data in the new location.

Because copying and pasting is so easy and quick, and because you are frequently only working with a subset of data in the development environment, copying and pasting is generally the quickest way to deploy an Analysis Services database on a different server. The downside is that it requires all of the dimensions, cubes, and partitions to be processed (which must be incurred anyway if the datasets are different). To determine your preferred approach, you need to compare the time and overhead of fully reprocessing the database with the msmdarch.exe archive and restore time. In most cases, full reprocessing is the fastest method. But you will find that it varies based on the underlying infrastructure (for example, fast network between the Analysis server and the source database) and on other uses of the source database (for example, it might be used by other applications and is thus already 80 percent loaded).

File-Based Backup and Restore

If neither of the previous two methods is suitable for your situation, you can also use a file-based backup program to back up the entire Data folder and then restore it to the destination folder. With this method, you must deploy all databases within an Analysis Services instance, rather than a single database. If you use this method, you must also back up the repository and then restore the repository on the destination server. While the repository is technically not required to run the OLAP service, it is required to run Analysis Manager and thus to properly administer the server. The meta data in the repository must match the contents and structure of the Data folder. If you have data in remote partitions (a feature that is rarely used) and in writeback tables, you must first back up the data in remote partitions using a file-based backup method and the writeback tables using SQL Server backup, and then restore them before you bring your database back online.

DSO/XML Scripting Utility

You can also deploy Analysis Services objects using DSO/XML, an unsupported utility that can create objects using definitions stored in an XML file. This utility uses DSO to query an Analysis Services instance and store XML definitions of the Analysis Services objects in the instance into an XML file. You can then use DSO/XML to read the definitions of these Analysis Services objects from the XML file and recreate them on another Analysis Services instance. Before deploying these stored definitions, you can edit the XML file with any text editor to modify the definitions of these Analysis Services objects, such as object names, data source names, and connection strings.

Microsoft provides the source code with this utility so that you can embed its functionality directly into your management application. To download or obtain more information on DSO/XML, go to http://www.microsoft.com/downloads/details.aspx?FamilyID=8d9e7a70-eef4-44c3-a0c5-deece0f8b4b4&displaylang=en.

Note   DSO/XML only deploys object definitions. You must process the objects to load the actual data from the data source.

Top of page

Change Management

Change management is the practice of administering changes with the help of tested methods and technologies to avoid introducing new errors and to minimize the impact, if any, on the service level. When implementing change, you should use Microsoft Visual Basic Scripting Edition (VBScript) with Decision Support Objects (DSO) to minimize the possibility of operator error when making the change. The next best method is the DTS Analysis Services Processing task. If neither of these methods can be used, you can make the change manually using Analysis Manager. Change should be tested in a development environment and then again in the QA environment before it is implemented in the production environment. Each change should be documented in the run book to ensure that information in the run book is kept current.

Important   Lack of change management can be a major cause of failure and service outages.

Making changes using scripts and DTS packages enables you to employ source code control, such as Microsoft Visual SourceSafe®, to provide version control. Source code control ensures that you can retrieve an older version of a script or package should the need arise, and facilitates team development.

Many Analysis Services installations have multiple individuals with permission to administer the Analysis Services database. Because any administrator can perform any task within Analysis Services and modify any object, it can be useful to track when changes are made to Analysis Services objects. While Microsoft does not provide a direct method for capturing this information, if you migrate the Analysis Services repository to SQL Server, you can add triggers to the repository database to detect when the meta data for an Analysis Services object changes and capture the value of any object before the change to an audit table. The sample script provided in Appendix E, "Sample Script for Creating Repository Audit Triggers," demonstrates how to create such repository audit triggers.

Note   Some client tools cache their own meta data. In this case, if you change the meta data in Analysis Services (such as the uniqueness of a level), you may need to notify the client tool that a change has been made and have it update its cached meta data.

Tip   If you need a way to quickly take a cube offline in order to make a change (or perform some type of maintenance), you can use virtual cubes. You can have clients connect to the virtual cube and then drop the virtual cube when you need to suspend access in order to make a change. You can then quickly recreate the virtual cube when you are ready for users to access the cube again.

Top of page

Security Administration

One of the key responsibilities of the Analysis Services administrator is ensuring that the data exposed through Analysis Services is secure. All users, whether they are administrators or end users, must be authenticated by the Microsoft Windows operating system before they can access Analysis Services objects. These users can be authenticated directly or via Microsoft Internet Information Services (IIS).

Administrator Security

When Analysis Services is installed, the setup program creates the OLAP Administrators local group on the Analysis Services computer and adds the user account of the person installing Analysis Services to this group. All members of the local Administrators group are automatically members of the OLAP Administrators group, regardless of whether they are explicitly added to the OLAP Administrators group.

The OLAP Administrators group is granted the following rights on the Analysis Services computer:

As a result, members of the OLAP Administrators local group can access Analysis Services administrative functions through Analysis Manager or programmatically with DSO. For more information, go to Knowledge Base (support.microsoft.com) and see the article "INF: Permissions That You Must Have to Administer an OLAP Server."

There is only one level of administrative access to Analysis Services. A member of the OLAP Administrators group has complete administrative access to Analysis Services objects, full read access to all cubes and dimensions, and full write access to all write-enabled cubes and dimensions (regardless of any contrary role definitions). A domain or local user that is not a member of the OLAP Administrators group can perform no administrative tasks and has read or write access to the extent permitted based on dimension-level or cell-level security.

Note   Clients performing OLAP analysis by issuing MDX queries through an OLE DB provider do not read the registry on the Analysis Services computer and do not require permission on the MsOLAPRepository$ hidden share.

End-User Security

End-user security in Analysis Services is based on Windows user accounts and groups. Before you begin configuring end-user security in Analysis Services, you must first create the user accounts and groups within Active Directory. A frequently asked question is whether Analysis Services supports other kinds of authentication. The answer is Yes and No. Yes, it can support other types using HTTP access and IIS (IIS 6.0 includes some new authentication options). However, all these authentication types must ultimately map to a Windows user account in the general sense: including domain accounts, local accounts, the guest account (if enabled), or the built-in NT AUTHORITY\ANONYMOUS LOGON account. Therefore, no, Analysis Services does not support SQL standard security or any similar technology where the authentication is not based on Windows user accounts.

For authentication, Analysis Services uses Security Support Provider Interface (SSPI) as the interface for application security. When you issue a query to Analysis Services, in the connection string (see "Authentication of Direct Connections" in SQL Server Books Online), you specify one of the following SSPI options:

Security Roles

After you have created the appropriate Windows user and group accounts, you create security roles within Analysis Services that contain Windows user and group accounts, and define the access each role has to Analysis Services data. You can use database roles, cube roles, and mining model roles.

Dimension-, Cell-, or Application-Level Security

When you use dimension-level security to limit the dimension members that are visible or updateable, Analysis Services must create a replica dimension in memory when a user connects which reflects the dimension members that user is permitted to see. For example, suppose you have an Account dimension that, at the highest level, has four regions: NorthAmerica, Europe, Asia, and Other. By creating four roles, you can specify which accounts a user can see by placing each user one of four roles: (a total of 16 role combinations).

Creating standard roles that can be reused by many different users enables Analysis Services to reuse these dimension replicas stored in memory. However, each time a user accesses a cube with a different combination of dimension members that the user has access to, Analysis Services creates a new replica in memory (from the example above, up to 15 replicas).

Replicas remain in memory until either the Analysis Services service is restarted or the base dimension is processed (full or incremental). There is no other way to unload replica dimensions from memory.

Cell-level security is an alternative to dimension security. If you use cell-level security, end users can see all the dimension members. In the example above, they can see all of the accounts-however, some of the cells are secured (if the user is not a member of any role that allows access to that cell). Because it does not need extra copies of dimensions (replicas), cell-level security does not have this memory overhead. While cell-level security scales better in terms of memory use, dimension-level security yields better overall performance. Cell-level security expressions are evaluated for every cell in the query and are not cached at all. If the MDX expression can be executed quickly, then performance is good. In the following example, an MDX expression simply compares the current member against a constant (only customers in "Europe"):

IIF(Ancestor(Customers.CurrentMember, Region).Name = "EUROPE", 1, 0)

Clearly if the expression is complex and involves a lot of processing, then cell-level security can perform poorly and consume a lot of client resources. Cell-level security expressions are evaluated for every cell in the query, and are not cached.

For more extensive, one-on-one security requirements, use dynamic security with the UserName function in the MDX statement to set security. Dynamic security allows you to give the end user a single role: a role that uses the UserName function to determine what members a user is permitted to see on a user name-by-user name basis. However, if every user name has a different set of members, then dynamic security potentially has a huge number of replicas.

Dimension security (both standard role-based security and dynamic security) is always performed on the server and is totally transparent to the client. Cell-level security is always performed on the client machine.

In practice, you should use a combination of these different role techniques: standard role-based dimension security, cell-level security, and dynamic dimension security. Where you have simple requirements, use cell-level security. It has the least memory overhead. However, in many areas cell-level security cannot be used because just the exposure of the dimension members is viewed as a breach of security. For example, knowing that your company has a particular customer in Asia might be considered confidential information, regardless of what the sales (or cells) are to that customer. In such cases, you must use dimension-level security. In this case, you can reduce the overhead of dimension-level security by using the minimum number of fixed roles. When designing roles, attempt to group the members so there is as little overlap of members as possible. Attempt to limit users to just one role. If both of these guidelines are followed (as closely as possible), then the largest total combined size of replicas is twice (2X) the size of the base dimension. Only use dynamic security for the subset of your users who really need member-by-member control based on the user name.

For even more control, you might be able to use application-level security. For example, suppose you are implementing a 3-tier Web-based application. Because all data access goes through the middle-tier application, you have an opportunity to add more extensive business rules than Analysis Services supports directly. You can choose to allow only certain kinds of operations within a certain number of days of the monthly closing date. Or, you can choose to allow only a certain type of data access if the end user also has credentials in some other security systems, such as a form-based authentication database, a Lightweight Directory Access Protocol (LDAP) server, or some other kind of third-party tool.

Normally this kind of application-level security is available only if you are writing the application yourself. However, some third-party OLAP tools also provide their own security system. For example, Panorama's Software's Novaview (see their web site at http://www.panoramasoftware.com) has an entire subsystem that adds additional controls for users that are using its thin-client, Web application server. This kind of support varies from product to product.

Domain Structure Issues

Because a user must be successfully authenticated before connection to Analysis Services, there generally must be a common domain structure between the Analysis Services computer and the client. However, if you do not have a common domain structure, you have several choices for overcoming this limitation:

Top of page

Service and Availability Management

Analysis Services administrators are responsible for ensuring that Analysis Services and its data are available for browsing by end users. The level of availability required (the amount of down time that is tolerable) depends on the business impact of the unavailability of the data in the Analysis Services cubes. The level of desired availability is generally defined in a service level agreement (SLA) and determines the elements that must be employed to ensure the agreed-on level of availability.

To ensure an agreed-on level of availability, you must develop an availability plan. When you develop your availability plan, take a holistic, system-wide approach and consider Analysis Services as only one part of the entire IT infrastructure. Consider the hardware components of the computer, all necessary software on the computer, the Microsoft Active Directory infrastructure that supports the Analysis Services installation, and the required personnel. Also consider transient information, such as user names and passwords, as well as product installation information, such as CD keys, distribution points, and original installation media.

When determining the appropriate Analysis Services components to add to your availability plan, assess the following:

Each of these elements must be addressed to determine how to achieve the desired level of availability. Once you have determined the elements of your availability plan, you must test each element of the plan to ensure that the plan works properly and smoothly in the face of both anticipated and unanticipated threats to availability. A well-trained staff that is prepared to handle any contingency is an essential part of any disaster recovery plan.

Service Continuity

You must consider how you will detect when Analysis Services stops running, so that you can respond to a service outage before your users detect the problem. If you want to grow your own detection mechanism, you can use one or more of the following three options to determine whether Analysis Services is still running:

You can also purchase a commercial product that performs this function, such as AppManager for Analysis Services from NetIQ (www.netiq.com) or ELM Enterprise Manager from TNT Software (www.tntsoftware.com).

Service Management

As the next step in your availability plan, consider how to define availability in the service level agreement (SLA). For example, Analysis Services can be unavailable for querying because dimensions are being processed due to reorganization. Should this be considered a service outage? The service issues unique to Analysis Services include the following:

How should the SLA handle cube unavailability caused by these types of user changes? At what point does unavailability caused by these issues require that you consider a 24-hour-a-day solution? For more information on continuous solutions, see "Implementing a Continuous Analysis Services Solution" later in this paper.

Backup and Recovery

Regardless of the other components of your availability plan, regular backups are an essential component. You must also ensure that these backups can be quickly restored if they are needed.

Before you back up your Analysis Services data, you must ensure that Analysis Services is not processing any dimensions, partitions, or mining models. Because Analysis Services performs some processing tasks as background processes, determining when all processing has been completed can sometimes be difficult. Also, you must ensure that another administrator is not changing any of the meta data while you are performing a backup. One way to ensure Analysis Services is quiescent is to stop Analysis Services before you perform the backup. You can use the sample script provided in Appendix H, "Sample Script to Determine When Lazy Processing is Complete," to assist you in determining when all background processing is complete.

Backup Options

Analysis Services provides two techniques for backing up an Analysis Services database: archiving and copying files.

Archiving

You can archive an Analysis Services database and the repository to one or more .cab file using the msmdarch command (msmdarch.exe), either from within Analysis Manager or from a command prompt. Msmdarch uses .cab storage algorithms, which limits the size of any single .cab file to 2 GB. As a result, no individual file in the Data folder (such as any single partition) can exceed 2 GB, or else msmdarch cannot be used for backup. When using msmdarch, always specify a log file location to capture any messages generated during the archive process. If the archive process fails, these messages can help you determine why the archive process failed. However, msmdarch does not back up the query log. To back up the query log, perform a file-based backup of the MSMDQLOG.mdb file. If you do not, a new query log is  created from scratch when you start a restored instance.

Copying Files

If you cannot use msmdarch, you can use a file copy program, such as Windows Backup, to back up all the files in the Data folder. With a file copy backup, you back up all databases on the server. With msmdarch, you can back up a single Analysis Services database. In