Administering Analysis Services Databases

Now that you understand more about the Analysis Services server, we will look at the administration tasks needed for the databases that will ultimately be deployed and run on the Analysis Services server. The primary tasks involved with managing the Analysis Services databases include deployment to the server, performing disaster recovery activities such as backup and restore operations, and synchronizing databases to copy entire databases.

Deploying Analysis Services Databases

Obviously, without deployment there really, is no value to running an Analysis Services server. Through deployment of Analysis Services databases to the server, original and changed designs are applied to the server.

When performing administrative tasks, you can either use Management Studio to affect changes directly in a database in what is commonly referred to as online mode, or you can work within the Business Intelligence Developer Studio to affect changes via a Build and Deploy process commonly referred to as offline mode.

More specific to database deployment, you have the following options:

• Deploy changes directly from Business Intelligence Developer Studio.

• Script changes and deploy from within Management Studio.

• Make incremental deployments using the Deployment Wizard.

• Process changes using the Synchronize Database Wizard.

Many of these options are useful only in very specific circumstances and as such will not be given much attention. The most useful and complete method of deploying the databases is to use the Deployment Wizard. Alternatively, the next best tool to assist with deployment is the Synchronize Database Wizard.

The main advantage of the Deployment Wizard is that it is the only deployment method that will apply the database project definition to production and allow you to keep many of the production database configuration settings, such as security and partitioning. This is important because neither direct deployment from BIDS nor scripting from Management Studio permits the deployment to maintain existing configuration settings.

The main scenario where the Synchronize Database Wizard is useful is when you are deploying changes from a quality-assurance or test environment into a production environment. This process copies the database and the data from one server to another while leaving it available for user queries. The advantage of this option should not be understated; with this option, the availability of your database is maintained. This contrasts with other deployment options, as they most likely will require additional processing of Analysis Services objects on the server after the deployment, and this process may require taking the database offline. We will look at synchronization later in this chapter.

Let's see how the Deployment Wizard operates in order to understand how valuable it is for handling deployment.

1. Launch the Deployment Wizard from the Start Menu under SQL Server 2005Analysis Services.

2. On the Specify Source Analysis Services Database page, enter a full path to an Analysis Services database (see Figure 7-4).

3. In the Installation Target page, indicate the Server to which the database should be deployed, along with the desired database name (defaults to the file name of the database).

4. In the Specify Options for Partitions and Roles page, indicate which configuration options (Partitions and Security) should be maintained on the deployment target database and thus not overwritten by this deployment (see Figure 7-5).

5. In the Specify Configuration Properties page, select which configuration settings from the current configuration file (.configsettings) should be applied to the target database. These settings provide a very useful way to redirect things such as data source connection strings to point to production sources rather than those used for development and testing. Also important to note is that the Retain checkboxes at the top provide an elegant way to manage updates of previous deployments, as they disable overwriting of both the configuration and optimization setting (see Figure 7-6).

6. On the Select Processing Options page, enter the desired processing method and any writeback table options. To support a robust deployment, you may also select the option to include all processing in a single transaction that will roll back all changes should any part of the deployment fail. Note also the Default processing method. This method is used to let Analysis Services review the modifications to be applied and determine the optimal processing needed to be performed (see Figure 7-7).

7. Last, on the Confirm Deployment page, you have an option to script the entire deployment. This option is useful when either the person running the Deployment Wizard is not authorized to perform the actual deployment or the deployment will need to be scheduled so as not to interfere with other activities.

Figure 7-4

Figure 7-5

Figure 7-6

Figure 7-7

Processing Analysis Services Objects

Now that you understand how to deploy Analysis Services databases, you must add data to these objects by processing them. Additionally, if the cubes need to be updated to reflect development changes made after the initial deployment, you'll need to reprocess them. Last, when data sources have changes made to their information, you will need to perform, minimally, an incremental reprocessing of the cube to ensure that you have up-to-date data within the Analysis Services solution. It's also worthwhile to note that often developers will build and test designs locally. These local cubes must first be deployed to the server before performing any processing.

The Analysis Services objects that require processing include measure groups, partitions, dimensions, cubes, mining models, mining structures, and databases. The processing is hierarchical, in that processing an object that contains any other objects will also process those objects. For example, a database includes one or more cubes, and cubes contain one or more dimensions, so processing the database would also process all the cubes contained within that database and also will process all the dimensions contained in or referenced by each of the cubes.

Processing Dimensions

Analysis Services processes dimensions by simply running queries that will return data from the data source tables for the dimensions. This data is then organized into the hierarchies and ultimately into map files that list all of the unique hierarchical paths for each dimension.

Processing Cubes

The cube contains both measure groups and partitions and is combined with dimensions to give the cube a data definition. Processing a cube is done by issuing queries to get fact-table members and the related measure values such that each path of dimensional hierarchies will include a value.

Processing Partitions

Just as in database partitioning, the goal of Analysis Services partitioning is to improve query response times and administrator processing durations. This processing is special in that you must evaluate your hardware space and Analysis Services data structure constraints. Partitioning is the key to ensuring that your query response times are fast and your processing activities are efficient.

Reprocessing

After deploying an Analysis Services database, many events will create the need to reprocess some or all of the objects within the database. Examples of when reprocessing is required include object structural/schema changes, aggregation design changes, or refreshing object data.

Performing Processing

To perform processing of Analysis Services object, you can either use SQL Server Management Studio or Business Intelligence Development Studio or run an XML for Analysis (XMLA) script. An alternative approach may use Analysis Management Objects (AMO) to start processing jobs via programming tools.

When processing, it is important to note that as Analysis Services objects are being committed, the database will not be available to process user requests. The reason for this is that the processing commit phase requires an exclusive lock on the Analysis Services objects being committed. User requests are not denied during this commit process but rather are queued until the commit is successfully completed.

Let's look at how to perform processing for an Analysis Services database from within SQL Server Management Studio.

1. Open Management Studio and connect to an Analysis Services server.

2. Right-click an Analysis Services database and select Process. The Process Database dialog is now displayed to allow you to configure the details of the processing (see Figure 7-8).

3. You can request some understanding of the impact on related objects by performing the desired processing by clicking the Impact Analysis button.

4. You can configure processing options such as the processing order by clicking Change Settings. Available options for the order include processing in parallel within a single transaction or processing sequentially within one or separate transactions. A very important option is "Process affected objects." This option controls whether all of the other objects that have dependencies on the database will also be processed (see Figure 7-9). A common architectural design employed in data warehousing involves the use of shared dimensions. These dimensions are able to be shared across the organization and allow for low maintenance and uniformity. The "Process affected objects" setting can therefore have a profound impact when you're using an architecture involving shared dimensions, as the option may force reprocessing of many other databases in which the shared dimensions are used.

5. You can also configure very sophisticated dimension key error handling (see Figure 7-10). As an example, you can configure the options to use a custom error configuration, which will convert key errors to an unknown record rather than terminating the processing. Additionally, you can determine error limits and what action to take when those limits have been exceeded. Last, you can choose to handle specific error conditions such as "key not found" or duplicate keys by reporting and continuing to process, by ignoring the error and continuing to process, and by reporting and stopping the processing.

Figure 7-8

Figure 7-9

Figure 7-10

Backing Up and Restoring Analysis Services Databases

Without question, performing backup and restore tasks are common functions within the domain of any DBA. A backup of the Analysis Services database captures the state of the database and its objects at a particular point in time to a file on the filesystem (named with an .abf file extension), while recovery restores a particular state of the database and its objects to the server from a backup file on the filesystem. Backup and recovery, therefore, are very useful for data recovery in case of problems with the database on the server at a future time or simply to provide an audit of the state of the database.

The backups will back up only the Analysis Services database contents, not the underlying data sources used to populate the database. Therefore, we strongly suggest that you perform a backup of the data sources using a regular database or filesystem backup in conjunction with the Analysis Services backup to capture a true state of both the Analysis Services objects and their sources at or about the same point in time.

The information that the backup will include varies depending upon the storage type configured for the database. A detailed message displayed at the bottom of the backup database dialog clearly communicates the various objects included in a backup based on the type of storage. Although we'll cover storage options a bit later, you need to know that available options to be included in the backup are the metadata that defines all the objects, the aggregations calculated, and the source data used to populate the objects.

Note

In the 2005 release of Analysis Service, you are no longer limited to a backup size limit of 3GB as was the case in the 2000 release.

We'll now review what is involved with performing these functions for Analysis Services databases. Again, you can use Management Studio to assist with the setup and configuration of these tasks and script the results to permit scheduling.

1. Open Management Studio and connect to an Analysis Services server.

2. Right-click an Analysis Services database and select Backup. The Backup Database dialog is now displayed to permit configuring the details of the backup, such as applying compression, where the backup file should be located, or whether the file should be encrypted (see Figure 7-11). While we will cover storage types later, you get a very clear statement of what information is part of the backup at the bottom of this Backup Database dialog. Basically, the backup is only backing up the Analysis Services information (partitions, metadata, source data, and aggregations) available to a database based on the storage type.

3. Optionally, you can script the backup by pressing the Script button along the top the dialog. The resulting script would look like the following example (including, of course, a poor practice of including the password).

 <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">   <Object>     <DatabaseID>Adventure Works DW</DatabaseID>   </Object>   <File>Adventure Works DW.abf</File>   <Password>aw</Password> </Backup> 

Figure 7-11

Now that you have a backup of an Analysis Services database, it's time to turn your attention to recovery of Analysis Services databases. Recovery will take a previously created backup file (named with an .abf file extension) and restore it to an Analysis Services database. Several options are made available during this process:

• Using the original database name (or specifying a new database name)

• Overwriting an existing database

• Including existing security information (or skipping security)

• Changing the restoration folder for each partition (except that remote partitions cannot become local)

Following are the steps needed to perform a recovery of the database:

1. Open Management Studio and connect to an Analysis Services server.

2. Right-click an Analysis Services database and select Restore. The Restore Database dialog is now displayed to permit configuring the details of the restoration, such as including security or overwriting an existing database (see Figure 7-12).

3. Optionally, you can script the restore by clicking the Script button along the top the dialog. The resulting script would look like the following example (including, of course, a poor practice of including the password):

    <Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">   <File>C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Backup\       Adventure Works DW.abf</File>   <DatabaseName>Adventure Works DW</DatabaseName>   <AllowOverwrite>true</AllowOverwrite>   <Password>aw</Password> </Restore> 

Figure 7-12

Synchronizing Analysis Services Databases

Another very important activity to perform involves synchronizing Analysis Services databases from one server to another. This is usually done as a mechanism for deploying from a test or quality-assurance server to a production server. The reason this feature is attractive for this purpose is that users can continue to browse the production cubes while the synchronization is taking place. When the synchronization completes, the user will automatically be redirected to the newly synchronized copy of the database, and the older version is removed from the server. This differs greatly from what happens when you perform a deployment, as part of the deployment usually involves processing of dimensions and or cubes. As you may recall, certain types of processing of Analysis Services objects require that the cube be taken offline and will, therefore, not be available for the user to browse until the processing completes.

As with many other database tasks, the synchronization can be run immediately from the wizard, or the results of the selections can be saved to a script file for later execution or scheduling.

The following are the steps to perform to synchronize an Analysis Services database between servers.

1. Open Management Studio and connect to the target Analysis Services server.

2. On this target server, right-click the databases folder and select Synchronize.

3. On the Select Databases to Synchronize page, specify the source server and database, and note that the destination server is hardcoded to the server from which you launched the synchronization.

4. If applicable, on the Specify Locations for Local Partitions page, the source folder displays the folder name on the server that contains the local partition while the destination folder can be changed to reflect the folder into which you want the database to be synchronized.

5. If applicable, on the Specify Locations for Remote Partitions page, you can modify both the destination folder and server to reflect where you want the database to be synchronized. Additionally, if the location has remote partitions contained in that location that need to be included in the synchronization, you must place a check beside the Sync option.

6. On the Specify Query Criteria page, you specify a value for the security definitions to include and also indicate whether or not compression should be used. The security options include copying all definitions and membership information, skipping membership information but including the security definitions, and ignoring all security and membership details.

7. On the Select Synchronization Method page, you can either run the synchronization now or script to a file for later use in scheduling the synchronization.